CODEWITHSUNDEEP
asp.netsqlsql-serversql-server-2008
JNF
JNF

Reputation: 3730

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!

Upvotes: 0

Views: 285

Answers (2)

Dxsolo
Dxsolo

Reputation: 51

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();

        }

Upvotes: 2

Jon Egerton
Jon Egerton

Reputation: 41569

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.

Upvotes: 2

Related Questions