使用标签更新状态，同时importingexcel到sql [英] Update status with label whilst importingexcel to sql

问题描述

亲爱的所有人。我是C＃的新手，并且大部分来自这里的一些代码。 （谢谢代码项目）。

我需要帮助用字符串serial更新我的标签。我还想使用进度条来更新标签状态。

查找以下用于从excel导出到sql的代码。

我调用函数openFileFile（）在导入按钮中。

Dear All. Am new to C# and mostly dubb some codes from here. (Thanks Code project).
I need help to update my label with the string "serial". I also would like to use a progressbar to update the label status.
Find below the code am using to export from excel to sql.
I call the function openFileFile() in an import button.

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();
                   //MessageBox.Show("Object reference not set.\nError: Empty worksheet.", "Inventory");
               }

           }
           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;
                   //MessageBox.Show("Error importing duplicate serial number:" + "\n" + serial, "Inventory");

               }

           }
           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 = CommandType.Text;
                   con.Open();
                   cmd.ExecuteNonQuery();
                   timer.Start();
                   lblstat.Text = "Importing File with serial number: " + serial;
                   //MessageBox.Show("File import succesful for serial number: " + serial, "Inventory");
                   con.Close();
               }
               catch (Exception ex)
               {
                   MessageBox.Show(ex.ToString());
               }
           }
       }

推荐答案

，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 = < SP class =code-keyword> false ;
}
else
{
lblstat.Text = 对象引用未设置.Empty工作表。;
timer.Stop（）;
// MessageBox.Show（对象引用未设置。\ nnError：空工作表。，库存）;
}

}
catch （例外情况）
{
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（连接）;
string seek = select * from productx其中serial =' + serial + ';
SqlCommand cmd2 = con.CreateCommand（）;
cmd2.CommandText = seek;
con.Open（）;
SqlDataReader dr;
dr = cmd2.ExecuteReader（）;
if （dr.HasRows）
{
while （dr .Read（））
{
lblstat.Text = 输入重复序列号时出错： + serial;
// MessageBox.Show（输入重复序列号时出错：+\ n+ serial ，库存）;

}

}
其他
{

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 =名称;
cmd.Parameters.AddWithValue（ @ model，SqlDbType.NVarChar）.Value =模型;
cmd.Parameters.AddWithValue（ @ serial，SqlDbType.NVarChar）.Value =串行;
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 =成本;
cmd.Parameters.AddWithValue（ @ quantity，SqlDbType.NVarChar）.Value =数量;
cmd.Parameters.AddWithValue（ @ stat，SqlDbType.NVarChar）.Value =活性;
cmd.CommandType = CommandType.Text;
con.Open（）;
cmd.ExecuteNonQuery（）;
timer.Start（）;
lblstat.Text = 使用序列号导入文件： + serial;
// MessageBox.Show（文件导入成功序列号：+ serial，Inventory） ;
con.Close（）;
}
catch （Exception ex）
{
MessageBox.Show（ex.ToString（））;
}
}
}

", 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(); //MessageBox.Show("Object reference not set.\nError: Empty worksheet.", "Inventory"); } } 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; //MessageBox.Show("Error importing duplicate serial number:" + "\n" + serial, "Inventory"); } } 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 = CommandType.Text; con.Open(); cmd.ExecuteNonQuery(); timer.Start(); lblstat.Text = "Importing File with serial number: " + serial; //MessageBox.Show("File import succesful for serial number: " + serial, "Inventory"); con.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } }

这篇关于使用标签更新状态，同时importingexcel到sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！