I have the following lines of code:
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);
DataTable dtExceldata = new DataTable();//just added
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();
OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", my_con);
da.Fill(dtExceldata);
if (dtExceldata.Rows.Count > 0)
{
//foreach (DataRow row in dtExceldata.Rows)
for (int i = 0; i <= dtExceldata.Rows.Count - 1; i++)
{
string ex_dir = dtExceldata.Rows[i]["website_a"].ToString();
//string ex_dir = row["website_a"].ToString();
string ex_email = dtExceldata.Rows[i]["email_id"].ToString();
// string ex_email = row["email_id"].ToString();
string ex_email1 = dtExceldata.Rows[i]["email_id2"].ToString();
//string ex_email1 = row["email_id2"].ToString();
string ex_email2 = dtExceldata.Rows[i]["email_id3"].ToString();
//string ex_email2 = row["email_id3"].ToString();
string ex_company = dtExceldata.Rows[i]["company"].ToString();
//string ex_company = row["company"].ToString();
string ex_contact = dtExceldata.Rows[i]["contact_name"].ToString();
//string ex_contact = row["contact_name"].ToString();
string ex_proposal = dtExceldata.Rows[i]["proposal_status"].ToString();
// string ex_proposal = row["proposal_status"].ToString();
string ex_reason = dtExceldata.Rows[i]["reason"].ToString();
//string ex_reason = row["reason"].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();
date = DateTime.ParseExact(txtsentdate.Text, "MM/dd/yyyy", null);
string SentDateString = date.ToString("yyyy/MM/dd");
//DateTime date = new DateTime();
//if (!string.IsNullOrEmpty(ex_date))
// {
//DateTime date = new DateTime();//added by chetan
//ex_date = ex_date.Split(' ')[0];//added by chetan
//date = DateTime.ParseExact(ex_date, "MM/dd/yyyy", null);//added by chetan
//string SentDateString = date.ToString("yyyy/MM/dd");//added by chetan
//DateTime SentDate = Convert.ToDateTime(SentDateString).Date;//added by chetan
//// 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
{
string 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", SentDateString);
// 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
}//for//foreach
}//while //if
// dr.Close();//commented by chetan
my_con.Close();
if (System.IO.File.Exists(Excel_path))
{
System.IO.File.Delete(Excel_path);
}
}
suppose there are 2 entries in the excelsheet file.When trying to import,it is inserting those 2 rows entries into the database table.but the problem is that, the reader does not stop its execution.After reading 2 rows entry,it is reading 3rd row which is blank.i have used dr.close and it keeps on reading the rows entries which is blank.
Instead of checking if there are rows left (dr.Read()) as your loop condition, you could check if the first cell of the row is blank (dr[0].ToString() != String.Empty)
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();
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", my_con);
OleDbDataReader dr = command.ExecuteReader();
dr.Read();
while (dr[0].ToString() != String.Empty)
{
ex_id = dr[0].ToString();
string ex_uid = dr[1].ToString();
//get second row data and assign it ex_name variable
string ex_date = dr[2].ToString();
//get thirdt row data and assign it ex_name variable
string ex_dir = dr[3].ToString();
//get first row data and assign it ex_location variable
string ex_email = dr[4].ToString();
string ex_email1 = dr[5].ToString();
string ex_email2 = dr[6].ToString();
//string ex_company = dr[7].ToString();
string ex_company = dr[7].ToString();
string ex_contact = dr[8].ToString();
string ex_proposal = dr[9].ToString();
string ex_reason = dr[10].ToString();
...............
//Insert operation
...............
dr.Read();
}
dr.close();
my_con.close();
The premise of course is, that the first column is always filled if the rest of the row isn't blank. If that isn't the case you can also check other columns in the loop condition.