Your actual error is coming from
WHERE R = 'RowNumber'
. This is a hardcoded value, and not the variable you assigned to the variable
RowNumber.
Second thing is the SQL Injection issue. It is easy enough to do it the right way the first time. And this has the fix for the aforementioned RowNumber problem as well.
string RowNumber = i.ToString();
string UpdateCmd = "UPDATE [Monthly Service] SET Location=@Lo, Address=@Ad, Name=@Nm, Testing=@Ts, FillTime=@FT, Notes=@Ns, Code_Key=@CK, Phone=@Ph WHERE R=@Row";
SqlCommand ThisUpdateCommand = new SqlCommand(UpdateCmd, myConnection);
ThisUpdateCommand.Parameters.AddWithValue("@Lo", data.Location);
ThisUpdateCommand.Parameters.AddWithValue("@Ad", data.Address);
ThisUpdateCommand.Parameters.AddWithValue("@Nm", data.Name);
ThisUpdateCommand.Parameters.AddWithValue("@Ts", data.Testing);
ThisUpdateCommand.Parameters.AddWithValue("@FT", data.FillTime);
ThisUpdateCommand.Parameters.AddWithValue("@Ns", data.Notes);
ThisUpdateCommand.Parameters.AddWithValue("@CK", data.CodeKey);
ThisUpdateCommand.Parameters.AddWithValue("@Ph", data.Phone);
ThisUpdateCommand.Parameters.AddWithValue("@Row", RowNumber);
I question the database schema, saving INTs as string (R:RowNumber) and several column names which are special/reserved words in SQL Server (Name, Address, Location).
The last thing is that this occurs within a
FOR...NEXT
. There would be a lot less overhead if you declared the SQL Command
before the loop; and then all you need to do is to reassign the parameters within the loop.
string UpdateCmd = "UPDATE [Monthly Service] SET Location=@Lo, Address=@Ad, Name=@Nm, Testing=@Ts, FillTime=@FT, Notes=@Ns, Code_Key=@CK, Phone=@Ph WHERE R=@Row";
SqlCommand ThisUpdateCommand = new SqlCommand(UpdateCmd, myConnection);
For(i=0.....)
{
ThisUpdateCommand.Parameters.Clear();
ThisUpdateCommand.Parameters.AddWithValue("@Lo", data.Location);
ThisUpdateCommand.Parameters.AddWithValue("@Ad", data.Address);
ThisUpdateCommand.Parameters.AddWithValue("@Nm", data.Name);
ThisUpdateCommand.Parameters.AddWithValue("@Ts", data.Testing);
ThisUpdateCommand.Parameters.AddWithValue("@FT", data.FillTime);
ThisUpdateCommand.Parameters.AddWithValue("@Ns", data.Notes);
ThisUpdateCommand.Parameters.AddWithValue("@CK", data.CodeKey);
ThisUpdateCommand.Parameters.AddWithValue("@Ph", data.Phone);
ThisUpdateCommand.Parameters.AddWithValue("@Row", i.ToString());