Dear All.
Can anyone help with a code to import from excel to sql using progress bar, where it update the label with the items being imported, If item exist, it should display in label.
My previous post shows sample of code i used for the copy but i seem to be lost as to how to use the progressbar in it
private void openFileFile()
{
string name;
string model;
string serial;
string serial2;
string windowsuser = txtuserid.Text;
string createdate = txtcreated.Text;
string cost = txtcost.Text;
string quantity = txtquantity.Text;
string active = "1";
string filepath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "product.xls");
bool flag = true;
Econ = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0");
try
{
OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", Econ);
Econ.Open();
OleDbDataReader odr = ocmd.ExecuteReader();
if (odr.HasRows)
{
while (odr.Read())
{
name = odr.GetValue(0).ToString();
model = odr.GetValue(1).ToString();
serial = odr.GetValue(2).ToString();
serial2 = odr.GetValue(3).ToString();
importexcel(name, model, serial, serial2, windowsuser, createdate, cost, quantity, active);
}
flag = false;
}
else
{
lblstat.Text= "Object reference not set.Empty worksheet.";
timer.Stop();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void importexcel(string name, string model, string serial, string serial2, string windowsuser, string createdate, string cost, string quantity, string active)
{
con = new SqlConnection(connection);
string seek = "select * from productx where serial='" + serial + "'";
SqlCommand cmd2 = con.CreateCommand();
cmd2.CommandText = seek;
con.Open();
SqlDataReader dr;
dr = cmd2.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
lblstat.Text = "Error importing duplicate serial number: " + serial;
}
}
else
{
try
{
con.Close();
string insertexcel = "insert into productx(name, model, serial, serial2,windowsuser,Ondate,cost,quantity,status)values(@name, @model, @serial, @serial2,@wuser,@date,@cost,@quantity,@stat)";
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = insertexcel;
cmd.Parameters.AddWithValue("@name", SqlDbType.NVarChar).Value = name;
cmd.Parameters.AddWithValue("@model", SqlDbType.NVarChar).Value = model;
cmd.Parameters.AddWithValue("@serial", SqlDbType.NVarChar).Value = serial;
cmd.Parameters.AddWithValue("@serial2", SqlDbType.NVarChar).Value = serial2;
cmd.Parameters.AddWithValue("@wuser", SqlDbType.NVarChar).Value = windowsuser;
cmd.Parameters.AddWithValue("@date", SqlDbType.NVarChar).Value = createdate;
cmd.Parameters.AddWithValue("@cost", SqlDbType.NVarChar).Value = cost;
cmd.Parameters.AddWithValue("@quantity", SqlDbType.NVarChar).Value = quantity;
cmd.Parameters.AddWithValue("@stat", SqlDbType.NVarChar).Value = active;
cmd.CommandType = Com