Date Name
11/21/2017 rahul
02/03/2018 rohan
This is my 2 entries which i wanted to upload in the database.
I am using reader to read it and insert it into the database
So it is reading the 1st entry and 2nd entry and after reading 2nd entry,it is going to the 3rd row of excel
which is empty and throws a error "string is not recognised as valid datetime" bcoz date here is " ".(there is no date on 3rd row).
i checked in the database. 1st entry and 2nd entry is getting saved in database(backend).(but with error)(frontend).
protected void btnUpload_Click(object sender, EventArgs e)
{
MySqlTransaction transaction;
string ex_id = "";
string file_name = Path.GetFileName(FileUpload1.FileName);
string Excel_path = Server.MapPath("~/Excel/" + file_name);
FileUpload1.SaveAs(Excel_path);
OleDbConnection my_con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Excel_path + ";Extended Properties=Excel 8.0;Persist Security Info=False");
my_con.Open();
// get the excel file data and assign it in OleDbcoomad object(o_cmd)
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", my_con);
// read the excel file data and assing it o_dr object
OleDbDataReader dr = command.ExecuteReader();
/////////////////////////////////////////////
while (dr.Read())
{
//get first row data and assign it ex_id variable
//ex_id = dr[0].ToString();
// string ex_uid = dr[1].ToString();
string ex_uid;
//get second row data and assign it ex_name variable
string ex_date = dr[0].ToString();
//get thirdt row data and assign it ex_name variable
string ex_dir = dr[1].ToString();
//get first row data and assign it ex_location variable
string ex_email = dr[2].ToString();
string ex_email1 = dr[3].ToString();
string ex_email2 = dr[4].ToString();
//string ex_company = dr[7].ToString();
string ex_company = dr[5].ToString();
string ex_contact = dr[6].ToString();
string ex_proposal = dr[7].ToString();
string ex_reason = dr[8].ToString();
//string ex_type = dr[11].ToString();
int chk = 0;
int type = 0;
int dup = 0;
int dir = 0;
if (ddlwebsites.SelectedIndex != 0)
{
dir = Convert.ToInt32(ddlwebsites.SelectedValue);
if (dir == 8)
{
type = 1;
}
}
foreach (ListItem lstAssign in ddlevents.Items)
{
if (lstAssign.Selected == true)
{
chk = 1;
}
}
if (type == 1 && chk == 0)
{
evyerror.Text = "Please Select the Event!!";
return;
}
else
{
string querycomp = "", compID = "";
querycomp = "Select * from barter_company where website like '%' '" + ex_dir + "' '%'";
string connStr = ConfigurationManager.ConnectionStrings["BarterConnectionString"].ToString();
connect = new MySqlConnection(connStr);
connect.Open();
transaction = connect.BeginTransaction();
try
{
ClassDtBaseConnect clsDtResult = new ClassDtBaseConnect();
DataTable dt = clsDtResult.GetDataTable(querycomp);
if (dt.Rows.Count > 0)
{
compID = dt.Rows[0]["comp_id"].ToString();
ViewState["comp_id"] = compID;
if (type == 1)
{
dup = checkforDuplicates(Convert.ToInt32(compID));
if (dup == 1)
{
Confirm_MP.Show();
// ScriptManager.RegisterStartupScript(this, this.GetType(), "script", "confirmation();", true);
}
}
//return;
}
else
{
string queryStr = "insert into barter_company (comp_name,website) values(?comp,?website)";
MySqlCommand cmd = new MySqlCommand(queryStr, connect, transaction);
cmd.Parameters.AddWithValue("?comp", ex_company);
cmd.Parameters.AddWithValue("?website", ex_dir);
cmd.ExecuteNonQuery();
cmd.CommandText = "Select LAST_INSERT_ID()";
compID = cmd.ExecuteScalar().ToString();
transaction.Commit();
connect.Close();
}
}
catch
{
transaction.Rollback();
}
if (dup == 0)
{
// create a connection string with your sql database
string connStr1 = ConfigurationManager.ConnectionStrings["BarterConnectionString"].ToString();
connect = new MySqlConnection(connStr1);
connect.Open();
//DateTime date = new DateTime();
//if (!string.IsNullOrEmpty(ex_date))
// {
if (!string.IsNullOrEmpty(dr[0].ToString()))
{
DateTime date = DateTime.Parse(ex_date);//added by chetan
} // }
//date = DateTime.ParseExact(ex_date, "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture);//added by chetan
//date = DateTime.ParseExact(ex_date, "MM/dd/yyyy", null);
// string SentDateString = date.ToString("yyyy/MM/dd");
transaction = connect.BeginTransaction();
try
{
ex_uid = Session["session_barterUser_id"].ToString();
MySqlCommand cmd = new MySqlCommand("insert into barter_proposals(user_id, sent_date, website_a, email_id, email_id2, email_id3, company, contact_name, proposal_status, reason,type) values(?uid,?sentdate,?dir,?email,?email2,?email3,?comp,?cont_name,?pro_status,?reason,?type)", connect);
cmd.Parameters.AddWithValue("?uid", ex_uid);
cmd.Parameters.AddWithValue("?comp", compID);
cmd.Parameters.AddWithValue("?sentdate", date);
// command.Parameters.AddWithValue("?event", eventname);
cmd.Parameters.AddWithValue("?dir", dir);
// command.Parameters.AddWithValue("?bar_type", ddlbartertype.SelectedValue);
// command.Parameters.AddWithValue("?website_b", txtwebsite.Text);
//cmd.Parameters.AddWithValue("?comp", ex_company);
cmd.Parameters.AddWithValue("?cont_name", ex_contact);
cmd.Parameters.AddWithValue("?email", ex_email);
cmd.Parameters.AddWithValue("?email2", ex_email1);
cmd.Parameters.AddWithValue("?email3", ex_email2);
cmd.Parameters.AddWithValue("?pro_status", ex_proposal);
cmd.Parameters.AddWithValue("?reason", ex_reason);
cmd.Parameters.AddWithValue("?type", type);
// command.Parameters.AddWithValue("?type", type);
cmd.ExecuteNonQuery();
if (type == 1)
{
cmd.CommandText = "Select LAST_INSERT_ID()";
Int64 CurrentProId = Convert.ToInt64(cmd.ExecuteScalar());
int eventAssignID;
string QueryInqEventAssign = "insert into barter_propeventassign(prop_id,event_id) values(?pro_id,?event_id)";
foreach (ListItem lstAssign in ddlevents.Items)
{
if (lstAssign.Selected == true)
{
cmd = new MySqlCommand(QueryInqEventAssign, connect, transaction);
cmd.Parameters.AddWithValue("?pro_id", CurrentProId);
eventAssignID = Convert.ToInt32(lstAssign.Value);
cmd.Parameters.AddWithValue("?event_id", eventAssignID);
cmd.ExecuteNonQuery();
}
}
}//end of if
transaction.Commit();
connect.Close();
Response.Write("<script type=\"text/javascript\">alert('Proposal Added Successfully!!!');</script>");
}//end of try
catch (Exception ex)
{
transaction.Rollback();
Response.Write("<script>alert('There is an Error Ocurred:" + Server.HtmlEncode(ex.Message) + "')</script>");
}
finally
{
connect.Close();
}
}//ifdupzero
}//else
}//while
//if (File.Exists(Excel_path))
// {
// File.Delete(Excel_path);
// }
dr.Close();
my_con.Close();
if (System.IO.File.Exists(Excel_path))
{
System.IO.File.Delete(Excel_path);
}
}
ANy help will be highly appreciated.
