SQL Server receives and runs a query with wrong result

I have an ASP.NET project connecting to a database. A web page sends a query to the database. More than one, as a matter of fact, it reads from database successfully. On click of a submit button I send an UPDATE query to the SQL Server (2008).

I work with C#, sending the UPDATE query to the server with a SqlCommand object initialized as such:

Edit: Added variables to query.

string strQuery = "UPDATE [tbl] SET [f1]='" + stringValue + "', [f2]='" + (boolValue?"1":"0") + "' WHERE [id]=" + intId.ToString() + ";";
SqlCommand cmd = new SqlCommand(strQuery, connectionObject);
int QueryResult = cmd.ExecuteNonQuery();

QueryResult ends up 0, and, of course, no change done. Copy-Paste to SSMS - I get 1 row(s) affected and the data changes.

Connection works fine - when I use this to check the work on the DB - the query has been executed. (It looks something like (@1 varchar(8000), @2 varchar(8000), @3 tinyInt) UPDATE [tbl] SET [f1]=@1, [f2]=@2 WHERE [id]=@3;).

Any idea what's going on - appreciated. Thank you!

Answers


The most likely thing is that the WHERE [id]=" + intId.ToString() isn't finding the row to be updated.

Either this is because:

  • The ID is correct and the row doesn't exist yet (if you're inserting it just before hand)
  • The ID is wrong
  • stringValue contains something that is corrupting your SQL such that it executes but matches nothing (unlikely but possible).
  • You're not executing the SQL against the right database (I've done this before in Dev environments with multiple dbs around!)

If you put a breakpoint on the line after the strQuery assignment see what it contains, and maybe post its contents back into your question.


Here is an example of how to use the parameters

using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = connectionObject;
            cmd.Parameters.AddWithValue("@f1", stringValue);
            cmd.Parameters.AddWithValue("@f2", boolValue);
            cmd.Parameters.AddWithValue("@id", intId);
            cmd.CommandText = "update [tbl] set f1 = @f1 , f2 = @f2 where id = @id";
            int QueryResult = cmd.ExecuteNonQuery();

        }

Need Your Help

Assign value to Var in C# using try catch

c# lambda delegates anonymous-methods

I want to do something like this in C#. I think this is possible using Delegates or Anonymous Methods. I tried but I couldn't do it. Need help.

Dojo, creating composite widgets

dojo widget composition

I am using dojo 1.10 and am desperately trying to find a clean way to create composite widgets,