How to debug a SQLCommand - showing values not @parameters

I start the SQL profiler when I want to know exactly what is sent to the server.

Hi Guys.  Thanks all for the input.

>> put a breakpoint on the for statement and  add this code just before
Tried this and I got a list of the parameters in turn.  They all looked okay however.

>> updateCommand.Parameters.Add(New SqlParameter("@ID", SqlDbType.VarChar)).Value = IDVariable
Added this into the code.  I still get the same error though.

>> I never knew you could use Parameters.Add if you where using CommandText. I thought that was for StoredProcedures only?
You might be right, although years back I read something on 4Guys that used a similar technique (http://aspnet.4guysfromrolla.com/articles/071002-1.3.aspx).  I was considering the concatenation approach but I thought this was bad because it weakened the security aspect in terms of SQL injection...?

>> I start the SQL profiler when I want to know exactly what is sent to the server.
Tried this too.  All I get is the SQL command object with @Parameters shown, and then each parameter listed as a value.

To avoid SQL injection you should really be using Stored Procedures.

I don't think your params are actually working.

If you don't see it in SQL Profiler then they are not being sent,

The params do appear okay in SQL Profiler - well, I think they do.  I see the SQL statement as built in ASP, then after I see the parameters' real values, all in a long line.
I wanted to see the entire constructed UPDATE statement so that I could try it in Query Analyzer and see what the result was.

The params do not seem to be working then.....

It looks like it is trying to execute a sp on the database.

Try just either create the stored procedure on sql or use inline sql... Params for procedures and concat for inline

>>I wanted to see the entire constructed UPDATE statement so that I could try it in Query Analyzer and see what the result was

This aint really possible if you use parameters.. but you could check the parameter values with the for loop i mentioned earlier.. if they appear okay then i think your update query is okay..
can you plz tell us the error message you get??

Line 1: Incorrect syntax near '('.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '('.

Source Error:
Line 208:            Try
Line 209:                  myConnection.Open()
Line 210:                  updateCommand.ExecuteNonQuery()
Line 211:            Finally
Line 212:                  updateCommand.Dispose()

[SqlException: Line 1: Incorrect syntax near '('.]
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +195
   ASP.editapplication_aspx.updateData(Object sender, EventArgs e) in c:\inetpub\wwwroot\jobapp\editapplication.aspx:210
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1292

there is a property of GridView "AutoGenerateColumns" when it is set to true.. and in your case it is.. then it will populate the data with no code behind.. but in most other scenarios we have to set that property to false and populate the gridview with the databind() function. I guess i assumed in your case it is set to false.. my bad.. Anyway... i hope i'm clear now..

heyy sorry guys.. i typed in the wrong window.. my previous comment aint relevant here :-)

>> Incorrect syntax near '('.

regarding this.. i'l be back in a while.. sorry again..

>> Do any special options needed to be selected when the profiler starts?  I used the default and ended up with this showing in the window:

exec sp_executesql N'UPDATE(myTable) SET col1 = @Col1Value, col2 = @Col2Value WHERE (ID = @ID)', N'@ID varchar(2),@Col1Value varchar(10),@Col2Value varchar(9), @ID = '11', @Col1Value = 'Test Value', @Col2Value = 'Test Value 2'

try this..

updateCommand.CommandText = "UPDATE(myTable) SET Col1 = @Col1Value, Col2 = @Col2Value WHERE ID=@ID"

>>exec sp_executesql N'UPDATE(myTable) SET col1 = @Col1Value, col2 = @Col2Value WHERE (ID = @ID)', N'@ID varchar(2),@Col1Value varchar(10),@Col2Value varchar(9), @ID = '11', @Col1Value = 'Test Value', @Col2Value = 'Test Value 2'

run it as is from Query Analyzer

I've cracked it.  I removed the brackets from around the ID = as suggested by mirmansoor, but the error persisted.  The only other brackets were those around the table name.  After removing those the error was gone.  Why's that happening then?!?

Pasting the huge profiler data into Analyzer revealed another error which was then highlighted in .NET after the current error was solved, so that's a useful trick to know :-)

lol, guess not!  :-)