?
Solved

How to debug a SQLCommand - showing values not @parameters

Posted on 2006-04-13
20
Medium Priority
?
1,282 Views
Last Modified: 2010-08-05
Is it possible to see what ASP.NET is passing to a SQL database in terms of the SQLCommand but with all the parameters filled in with actual values??
I've created a SqlCommand but there's an error somewhere in it and I'd like to see the whole Update statement with values included, if possible.  Can it be done?

            Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings(ConnectionString"))
            Dim updateCommand As New SqlCommand
            updateCommand.Connection = myConnection
            updateCommand.CommandText = "UPDATE(myTable) SET " & _
              "Col1 = @Col1Value, " & _
              "Col2 = @Col2Value " & _
              "WHERE (ID=@ID)"
            updateCommand.Parameters.Add("@ID", SqlDbType.VarChar).Value = IDVariable
            updateCommand.Parameters.Add("@Col1Value", SqlDbType.VarChar).Value = SomeBox.Text
            updateCommand.Parameters.Add("@Col2Value", SqlDbType.VarChar).Value = AnotherBox.Text
            myConnection.Open()
            updateCommand.ExecuteNonQuery()
            updateCommand.Dispose()
            myConnection.Close()
0
Comment
Question by:Rouchie
  • 7
  • 6
  • 3
  • +2
20 Comments
 
LVL 5

Assisted Solution

by:mirmansoor
mirmansoor earned 800 total points
ID: 16444352
yes u can..

dim param as sqlclient.sqlparameter
for each param in updatecommand.parameters
  ' here u can access the parameters n check the values
next

put a breakpoint on the for statement and  add this code just before executenonquery n check the values..
HTH
0
 
LVL 6

Assisted Solution

by:apb2
apb2 earned 400 total points
ID: 16444413
Hi

The best thing i find is to use a

Try
  'Open database
  'Execute command
Catch ex as SqlException

Catch exp as Exception

Finally
  Close database connection
End Try

The catches will handles the exception. While debugging you can the add the exceptions variables to a watch

By the way

updateCommand.Parameters.Add(New SqlParameter("@ID", SqlDbType.VarChar)).Value = IDVariable
updateCommand.Parameters.Add(New SqlParameter("@Col1Value", SqlDbType.VarChar)).Value = SomeBox.Text
 updateCommand.Parameters.Add(New SqlParameter("@Col2Value", SqlDbType.VarChar)).Value = AnotherBox.Text

HTH

apb2

0
 
LVL 15

Assisted Solution

by:GavinMannion
GavinMannion earned 400 total points
ID: 16444500
I never knew you could use Parameters.Add if you where using CommandText. I thought that was for StoredProcedures only?

Anyway another way to check would be to do something along the lines of

updateCommand.CommandText = "UPDATE(myTable) SET " & _
            "Col1 = " & SomeBox.Text & ", " & _
            "Col2 = " & AnotherBox.Text & ", " & _
            "WHERE (ID=" & IDVariable

Then just look at what CommandText ends up being.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 16444872
I start the SQL profiler when I want to know exactly what is sent to the server.
0
 
LVL 25

Author Comment

by:Rouchie
ID: 16445315
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.
0
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16445357
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,
0
 
LVL 25

Author Comment

by:Rouchie
ID: 16445389
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.
0
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16445447
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
0
 
LVL 5

Expert Comment

by:mirmansoor
ID: 16445468
>>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??
0
 
LVL 25

Author Comment

by:Rouchie
ID: 16445514
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
0
 
LVL 5

Expert Comment

by:mirmansoor
ID: 16445581
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..
0
 
LVL 5

Expert Comment

by:mirmansoor
ID: 16445598
heyy sorry guys.. i typed in the wrong window.. my previous comment aint relevant here :-)
0
 
LVL 5

Expert Comment

by:mirmansoor
ID: 16445654
>> Incorrect syntax near '('.

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

Assisted Solution

by:Éric Moreau
Éric Moreau earned 400 total points
ID: 16445719
>>I wanted to see the entire constructed UPDATE statement so that I could try it in Query Analyzer and see what the result was.

you should be able to copy it from the Profiler to Query Analyzer and run it as is.
0
 
LVL 25

Author Comment

by:Rouchie
ID: 16445823
>> 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'
0
 
LVL 5

Expert Comment

by:mirmansoor
ID: 16445834
try this..

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



0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 16445874
>>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
0
 
LVL 25

Author Comment

by:Rouchie
ID: 16445941
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 :-)
0
 
LVL 5

Accepted Solution

by:
mirmansoor earned 800 total points
ID: 16445963
well.. it doesnt accept ( around table names and column names... :-)
0
 
LVL 25

Author Comment

by:Rouchie
ID: 16445995
lol, guess not!  :-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month15 days, 21 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question