Updating SQL table with the contents of a datatable

Question

I am creating an application in WPF using C# where users populate a datagrid and the information is then stored in a DataTable called smb1. The following code works for inserting the data into the SQL database but when I modify the code for updating it does not work. Does anyone know how I can modify my code to allow Updates as there are no errors thrown when I run my application in Visual Studio. I must add that the Equipment column cannot be edited in the datagrid so the returned data is the same as the data taken from the database so that the updated rows will be matched to the equipment rows in the SQL database using the WHERE clause. Below is the original insertion code plus my attempt for updating the database.

Insert Code

SqlConnection con = new SqlConnection(MyConnectionString);
string SqlCmdText = "Insert into SHIFTLOG Values(@EQUIPMENT,@BATCHNO,@PRODUCTNO,@STATUS,@DATE,@PERIOD,@MACHINE)";
SqlCommand sc = new SqlCommand(SqlCmdText, con);
con.Open();
foreach (DataRow row in smb1.Rows)
{
    sc.Parameters.Clear();
    sc.Parameters.AddWithValue("@EQUIPMENT", row["EQUIPMENT"]);
    sc.Parameters.AddWithValue("@BATCHNO", row["BATCHNO"]);
    sc.Parameters.AddWithValue("@PRODUCTNO", row["PRODUCTNO"]);
    sc.Parameters.AddWithValue("@STATUS", row["STATUS"]);
    sc.Parameters.AddWithValue("@DATE", DateTime.Now.ToString("yyyy-MM-dd"));
    sc.Parameters.AddWithValue("@PERIOD", DateTime.Now.ToString("tt"));
    sc.Parameters.AddWithValue("@MACHINE", "SMB1");

    sc.ExecuteNonQuery();
}
con.Close();

Attempt for Update Code

SqlConnection con = new SqlConnection(MyConnectionString);
string SqlCmdText = "UPDATE SHIFTLOG SET EQUIPMENT='@EQUIPMENT',BATCHNO='@BATCHNO',PRODUCTNO='@PRODUCTNO',STATUS='@STATUS',DATE='2013-09-12',PERIOD='@PERIOD',MACHINE='@MACHINE' WHERE EQUIPMENT='@EQUIPMENT'";
SqlCommand sc = new SqlCommand(SqlCmdText, con);
con.Open();
foreach (DataRow row in smb1.Rows)
{
    sc.Parameters.Clear();
    sc.Parameters.AddWithValue("@EQUIPMENT", row["EQUIPMENT"]);
    sc.Parameters.AddWithValue("@BATCHNO", row["BATCHNO"]);
    sc.Parameters.AddWithValue("@PRODUCTNO", row["PRODUCTNO"]);
    sc.Parameters.AddWithValue("@STATUS", row["STATUS"]);                
    sc.Parameters.AddWithValue("@PERIOD", DateTime.Now.ToString("tt"));
    sc.Parameters.AddWithValue("@MACHINE", row["MACHINE"]);

    sc.ExecuteNonQuery();
}
con.Close();

Thanks for any help.

score 5 · Accepted Answer · answered Oct 03 '13 at 12:47

Remove the single quotes around the parameters.

string SqlCmdText = "UPDATE SHIFTLOG SET EQUIPMENT=@EQUIPMENT,BATCHNO=@BATCHNO,PRODUCTNO=@PRODUCTNO,STATUS=@STATUS,DATE='2013-09-12',PERIOD=@PERIOD,MACHINE=@MACHINE WHERE EQUIPMENT=@EQUIPMENT";

Also, I think the update section for EQUIPMENT=@EQUIPMENT is redundant, as the where clause will not be correct if it has changed. So you could use

string SqlCmdText = "UPDATE SHIFTLOG SET BATCHNO=@BATCHNO,PRODUCTNO=@PRODUCTNO,STATUS=@STATUS,DATE='2013-09-12',PERIOD=@PERIOD,MACHINE=@MACHINE WHERE EQUIPMENT=@EQUIPMENT";

@ astander & Damith.I can't believe I never spotted that when comparing the 2 examples! Thanks so much for the help guys! — Callum A Macleod, Oct 03 '13 at 13:01

score 3 · Answer 2 · edited May 23 '17 at 12:21

3

remove the '' in all the parameters in your sql statement

"UPDATE SHIFTLOG SET BATCHNO=@BATCHNO,....... WHERE EQUIPMENT=@EQUIPMENT

if you use quotes all your parameters take as string values, not as SQL parameters

And also you need to use columns with [] like [DATE] if those are reserved keywords

How to deal with SQL column names that look like SQL keywords?

edited May 23 '17 at 12:21

Community

1
1

answered Oct 03 '13 at 12:47

Damith

62,401
13
102
153

Updating SQL table with the contents of a datatable

2 Answers2