Help: SQL Update

[j1e1g1]

I'm using the .NET compact framework, and I'm trying unsuccessfully to do a SQL update. My code is as follows:

SqlConnection myConnection = new SqlConnection("User ID=abc;Password=123;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=factory;Data Source=servername");
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.UpdateCommand = new SqlCommand("UPDATE sn_allocatedblocks SET ab_status = 'Warehouse' WHERE ab_schedule = '"+schedule+"' AND ab_schedline = '"+schedline+"'", myConnection);
myConnection.Close();

In the debugger, this code seems to work fine....it hits every line and goes through the function successfully. However, when I open up SQL Analyzer and check to see if it worked, nothing is changed. I'm positive that the user has permissions and that the query is correct, I just can't get it working in my program. I also tried using the ExecuteNonQuery method, but that always throws a SQLException:

SqlConnection myConnection = new SqlConnection("User ID=abc;Password=123;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=factory;Data Source=servername");
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.UpdateCommand = new SqlCommand("UPDATE sn_allocatedblocks SET ab_status = 'Production' WHERE ab_schedule = '"+schedule+"' AND ab_schedline = '"+schedline+"'", myConnection);
adapter.UpdateCommand.ExecuteNonQuery();
myConnection.Close();

[j1e1g1]

Sorry about the smileys, those are semicolons followed by the letter 'p'.

[Heath Stewart]

You should really read the documentation for the SqlDataAdapter in the .NET Framework SDK. You're not using it right, and it's not even necessary here. A DataAdapter is for filling and updating DataSets using the various command properties. All you're doing in the first example is assigning it. That won't do anything. Just don't use the SqlDataAdapter at all. The bottom fragment is almost correct, except - again - the SqlDataAdapter isn't necesary. Just instantiate the SqlCommand, assign it to a variable, and execute it. Since you didn't tell us what the exception was or what it said (always help), I'm betting it's because you're not using parameterized queries, and instead doing the old kludge of string concatenation. If that variable has a quote (either single or double), the SQL statement is invalid. Instead, the correct code - using ADO.NET how it's supposed to be used - is:

SqlConnection conn = new SqlConnection(...);
using (conn)
{
SqlCommand update = new SqlCommand("UPDATE sn_allocatedblocks " +
"SET ab_status = 'Production' WHERE ab_schedule = @schedule " +
"AND ab_schedline = @schedline", conn);
update.Parameters.Add("@schedule", SqlDbType.NVarChar, 20).Value = schedule;
update.Parameters.Add("@schedline", SqlDbType.Int).Value = schedline;
update.ExecuteNonQuery();
}

The using block makes sure that even in case of error the connection is closed and disposed (important). Also dispose of objects who's classes implement IDisposable. Change the parameter types to whatever you want (not knowing what they were supposed to be, I just made them up). If that schedline variable and parameter is not a string or date/time type, do not use quotes (quoting a numeric value will result in error as well). Using parameterized queries signficantly reduces the attack surface (I could easily provide values for your string variables that would terminate the SQL statement and clear-out your master table since you're not checking user input - never trust user input), eliminates the need to properly encode strings (due to single or double quotes screwing up the resultant SQL statement), and allows you to perform bulk updates, inserts, and deletes (since you instantiate the command and add parameter definitions only once; assign the parameters to variables; for each iteration of a loop, change the SqlParameter.Value property for each