上传带有一些详细信息的文件时，它们在SQL DB中正在增加2行

Question

使用单个保存按钮插入数据和文件上传后，在数据库中创建2行。我想将两行合并为一行。请建议。详情附后。应用程序图形用户界面

设计与数据库

{
con.Open ();
SqlCommand cmd = new SqlCommand ("spStockInWard", con);
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.AddWithValue("@Action", "Insert");
cmd.Parameters.AddWithValue ("@SerialNumber", textBox1.Text.Trim ());
cmd.Parameters.AddWithValue ("@AssetType", comboBox1.Text);
cmd.Parameters.AddWithValue ("@AssetMake", comboBox2.Text);
cmd.Parameters.AddWithValue ("@AssetModel", textBox2.Text);
cmd.Parameters.AddWithValue ("@HDDSize", comboBox5.Text);
cmd.Parameters.AddWithValue ("@RAMSize", comboBox6.Text);
cmd.Parameters.AddWithValue ("@MonitorType", comboBox7.Text);
cmd.Parameters.AddWithValue ("@StockReceivedDate", textBox3.Text);
cmd.Parameters.AddWithValue ("@InvoiceDate", textBox4.Text);
cmd.Parameters.AddWithValue ("@InvoiceNumber", textBox5.Text);
cmd.Parameters.AddWithValue ("@AssetStatus", comboBox3.Text);
cmd.Parameters.AddWithValue ("@WorkingStatus", comboBox4.Text);
cmd.Parameters.AddWithValue ("@Remarks", textBox6.Text);
cmd.Parameters.Add ("@ERROR", SqlDbType.Char, 500);
cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery ();
string message = (string) cmd.Parameters["@ERROR"].Value;
MessageBox.Show (message.ToString (), "Userinfo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
con.Close ();
} else
{
MessageBox.Show ("Please Fill All Details!");
}
try {
string filename = System.IO.Path.GetFileName (openFileDialog1.FileName);
if (filename == null) {
MessageBox.Show ("Please select a valid document.");
} else {
con.Open ();
SqlCommand cmd = new SqlCommand ("insert into tblStockInWard (document) values('D:\AMS-Docs\StockInWard\" + filename + "')", con);
string path = Application.StartupPath.Substring (0, (Application.StartupPath.Length - 50));
System.IO.File.Copy (openFileDialog1.FileName, @"D:\AMS-Docs\StockInWard\" + filename);
cmd.ExecuteNonQuery ();
con.Close ();
MessageBox.Show ("Document uploaded.");
}
} catch (Exception ex) {
MessageBox.Show (ex.Message);
}

这些都是我的代码。

Answer 1

正如我在评论中所说 - 您需要先插入您的详细信息，然后您需要使用剩余信息更新您刚刚插入的那行(甚至更简单 - 为什么你不能在初始INSERT中设置document列的值？？

另外：我强烈建议(a( 远离使用.AddWithValue()，并为参数使用适当的数据类型 - 由于您还没有向我们展示您的存储过程，我只能猜测这些可能是什么 - 根据需要进行调整。

// get the "serial no" - assuming this is the **primary key** for the table
string serialNo = textBox1.Text.Trim();
// create your INSERT stored procedure call
SqlCommand cmd = new SqlCommand("dbo.spStockInWard", con);
cmd.CommandType = CommandType.StoredProcedure;
// add parameters with the ".Add()" method, also specifying the datatype used    
cmd.Parameters.Add("@SerialNumber", SqlDbType.VarChar, 50).Value = serialNo;
cmd.Parameters.Add("@AssetType", SqlDbType.VarChar, 50).Value = comboBox1.Text;
cmd.Parameters.Add("@AssetMake", SqlDbType.VarChar, 50).Value = comboBox2.Text;
cmd.Parameters.Add("@AssetModel", SqlDbType.VarChar, 50).Value = textBox2.Text;
cmd.Parameters.Add("@HDDSize", SqlDbType.Int).Value = Convert.ToInt32(comboBox5.Text);
cmd.Parameters.Add("@RAMSize", SqlDbType.Int).Value = Convert.ToInt32(comboBox6.Text);
cmd.Parameters.Add("@MonitorType", SqlDbType.VarChar, 50).Value = comboBox7.Text;
cmd.Parameters.Add("@StockReceivedDate", SqlDbType.DateTime).Value = DateTime.Parse(textBox3.Text);
cmd.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = DateTime.Parse(textBox4.Text);
cmd.Parameters.Add("@InvoiceNumber", SqlDbType.VarChar, 50).Value = textBox5.Text;
cmd.Parameters.Add("@AssetStatus", SqlDbType.VarChar, 50).Value = comboBox3.Text;
cmd.Parameters.Add("@WorkingStatus", SqlDbType.VarChar, 50).Value = comboBox4.Text;
cmd.Parameters.Add("@Remarks", SqlDbType.VarChar, 500).Value = textBox6.Text;
cmd.Parameters.Add("@ERROR", SqlDbType.Char, 500);
cmd.Parameters["@ERROR"].Direction = ParameterDirection.Output;
// open connection, insert details
con.Open();
cmd.ExecuteNonQuery();
string message = (string)cmd.Parameters["@ERROR"].Value;
MessageBox.Show(message.ToString(), "Userinfo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// second step - **UPDATE** the newly inserted row with your file info
string filename = System.IO.Path.GetFileName(openFileDialog1.FileName);
if (filename == null)
{
MessageBox.Show("Please select a valid document.");
con.Close();
}
else
{
// define the UPDATE statement against the same row as inserted above       
SqlCommand cmd = new SqlCommand("UPDATE tblStockInWard SET document = @document WHERE SerialNumber = @SerialNo";

// re-use "serial no" from above
cnd.Parameters.Add("@SerialNo", SqlDbType.VarChar, 50).Value = serialNo;
cmd.Parameters.Add("@document", SqlDbType.VarChar, 250).Value = "D:\AMS-Docs\StockInWard\" + filename;

cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Document uploaded.");
}