Showing parsed SQL statement?

In classic ASP, I used to be able to do a response.write(SQL) to view a parsed SQL statement. Can I do this in .NET? I'm using a SQLDataSource container and want to see what I'm passing through in my INSERT statement. All I can seem to get are @parameters.

Thanks!
Stace

0
Stace
12/29/2008 9:01:58 PM
asp.net.getting-started 91979 articles. 3 followers. Follow

13 Replies
382 Views

Similar Articles

[PageSpeed] 32

Add OnInserting="SqlDataSource1_Inserting"  to your SqlDataSource and check the InsertCommand and parameter values as shown below. Another way is to use SQL Profiler from SSMS (SQL Server Management Studio) to trace what has been passed to SQL Server.

 

protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)

{

 

Response.Write(e.Command.CommandText.ToString()

+
"<Br />" + e.Command.Parameters[0].ToString()

+ ": " + e.Command.Parameters[0].Value.ToString());

}


Limno

0
limno
12/29/2008 9:46:22 PM

Thanks, but this only returns the first parameter. I guess I need to figure out how to loop it...

0
Stace
12/29/2008 10:50:12 PM

Try this :

 

     string mystring=e.Command.CommandText.ToString();


        for (int i = 0; i < e.Command.Parameters.Count; i++)

            mystring += "<Br />" + e.Command.Parameters[i].ToString()
       + ": " + e.Command.Parameters[i].Value.ToString();

        Response.Write(mystring);
 
Limno

0
limno
12/29/2008 11:04:28 PM

I put this into the VB translator and it came out as below. This did not return anything, unfortunately. I fixed my SQL problem but I'd still really like to have a debugger to use on future applications. Does anything here seem off-kilter? Thanks...

Protected Sub SqlDataSource1_Inserting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)

Dim mystring As String = e.Command.CommandText.ToString()

Dim i As Integer = 0

While i < e.Command.Parameters.Count

mystring += "<Br />" + e.Command.Parameters(i).ToString() + ": " + e.Command.Parameters(i).Value.ToString()

System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1)

End While

Me.lblDebug.Text = mystring.ToString

End Sub

0
Stace
12/29/2008 11:56:49 PM

Here is VB code:

  Protected Sub SqlDataSource1_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Inserting


        Dim mystring As String = e.Command.CommandText.ToString()

        Dim i As Integer = 0

        For i = 0 To e.Command.Parameters.Count - 1

            mystring += "<Br />" + e.Command.Parameters(i).ToString() + ": " + e.Command.Parameters(i).Value.ToString()
        Next

        Me.lblDebug.Text = mystring.ToString
    End Sub
 
Limno

0
limno
12/30/2008 12:15:10 AM

It seems to not like e.Command.Parameters(i).Value.ToString(). When I leave that in, I get "Object reference not set to an instance of an object." and it returns nothing. If I take that out, it returns the unparsed SQL statement and a list of all of the parameters. Any suggestions?

Thanks,
Stacy

0
Stace
12/30/2008 3:56:07 PM

Could you show your code and your SqlDataSource?

I tested both vb.net and c# code with insertparameters. We may need to see how your code looks like to figure out why you have that error.


Limno

0
limno
12/30/2008 4:09:18 PM

The problem is null values in the table... if I set i = 0 to 19, it's fine. Value 21 is a NULL, so if I make it i = 0 to 20, it breaks.

I've tried playing with If IsDBNull... statements but they're not working.

Hm. (Used to be so much simpler in classic ASP!) Wink

0
Stace
12/30/2008 11:11:17 PM

Oops, I should've mentioned that I'm playing with an UPDATE statement, not an INSERT statement now...

0
Stace
12/30/2008 11:11:52 PM

Here is the code which should work for you.

 Protected Sub SqlDataSource1_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Updating

        Dim myCollection As SqlParameterCollection = e.Command.Parameters()

        Dim ie As IEnumerator = myCollection.GetEnumerator
        Dim strValue As String = ""
        While (ie.MoveNext())
            Dim param As System.Data.SqlClient.SqlParameter = ie.Current()
            strValue += param.ParameterName() & "=" & param.Value() & "<BR />"

        End While

        Me.lblDebug.Text = strValue.ToString

    End Sub
 
C#:
 
 protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)
    {

    SqlParameterCollection myCollection = (SqlParameterCollection)e.Command.Parameters; 
    
    IEnumerator ie = myCollection.GetEnumerator(); 
    string strSQL = string.Empty; 
    while ((ie.MoveNext())) {
        SqlParameter param = (SqlParameter)ie.Current;
        string strValue = string.Empty;
        if (param.Value ==null)
            strValue = "NULL";
        else
            strValue = param.Value.ToString();

        strSQL += param.ParameterName.ToString() +"=" + strValue + "<BR />"; 
    } 
    
    
    Response.Write(strSQL.ToString()); 
    
}
 
 
Limno

0
limno
12/31/2008 5:02:54 AM

Thanks a lot for this, looks very helpful, especially in cases we don't have access to profiler.


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
12/31/2008 5:55:53 AM

Works beautifully. Thank you so much for your patient responses. Have a wonderful new year!

Stace

0
Stace
12/31/2008 4:19:42 PM

There are a few changes in code to make it work with ObjectDataSource. Here it is:

C#:
//using System.Collections.Specialized;
 
protected void ObjectDataSource1_Updating(object sender, ObjectDataSourceMethodEventArgs e)
    {
        IOrderedDictionary myCollection = (IOrderedDictionary)e.InputParameters;

        IEnumerator ie = myCollection.GetEnumerator();
        string strSQL = string.Empty;
        while ((ie.MoveNext()))
        {
            DictionaryEntry param = (DictionaryEntry)ie.Current;
            string strValue = string.Empty;
            if (param.Value == null)
                strValue = "NULL";
            else
                strValue = param.Value.ToString();

            strSQL += param.Key.ToString() + "=" + strValue + "<BR />";
        }


        Response.Write(strSQL.ToString()); 

    }
 

VB.NET: 

 Protected Sub ObjectDataSource1_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceMethodEventArgs) Handles ObjectDataSource1.Updating
        Dim myCollection As IOrderedDictionary = e.InputParameters()
        Dim ie As IEnumerator = myCollection.GetEnumerator
        Dim strSQL As String = ""
        While (ie.MoveNext())
            Dim param As System.Collections.DictionaryEntry = ie.Current()
            strSQL += param.Key & "=" & param.Value() & "<BR />"

        End While
        Response.Write(strSQL.ToString())
    End Sub

 

Limno

0
limno
1/1/2009 9:10:38 PM
Reply:

Similar Artilces:

Getting table records count with SQL statement VB.net
I have a table named USERS with a column named USERNAME. I am trying to get the count and cannot seem to get any results back. Code I've tried. cmd2.CommandText = "SELECT USERNAME AS RETURNCOUNT FROM USERS" rs = cmd2.ExecuteReader intCount = rs("RETURNCOUNT") ************Did not return any*************  ******************************************************************************************************************************* cmd2.CommandText = "SELECT COUNT(0) AS RETURNCOUNT FROM USERS" rs = cmd2.ExecuteReader intCount = rs("R...

Free .Net and Sql Server Training Videos for Developers just getting started
I figured since this is the Getting started forum I would mention a great .Net training video site with some free videos.http://www.TechnicalVideos.netThanksMike James...

RE: SQL::Statement cannot parse valid Postgresql statement
> From: Terrence Brannon [mailto:metaperl@mac.com] > > On Monday, December 10, 2001, at 09:16 AM, Jeff Zucker wrote: > > > Terrence Brannon wrote: > >> > >> The following SQL is not parseable by SQL::Statement: > >> > >> INSERT > >> INTO thot_log (thot_fk,thot_type_fk,thot_temporality_fk,date) > >> VALUES (?,?,?,CURRENT_TIMESTAMP) > > What I have is a program which reads a SQL statement from a file. > It parses it with SQL::Statement, then it prepares this > statement, then does an ...

Is it possible to modify the SQL statement before the ODBC driver get the statement ?
Hi all, Is it possible to modify the SQL statement before the ODBC driver get the SQL statement ? I need to modify some of my SQL statement to support multiple database. e.g. to solve the reserved word problem in Oracle or auto uppercase problem on unquoted column name in DB2. I need a solution for all my datawindow, datastore, embeded SQL, dynamic SQL statement. Thanks. Regards, Larry Chiu Sure, use getSqlSelect to get it and setSqlSelect to set it. Of course in the case of the Dynamic Datawindow you are going to have to do a syntaxFromSql and then a create using that s...

Get SQL Statement
Good Morning, I'm just wondering if something is possible. In my application I execute a batch script then loop through all the results with ct_result. After calling ct_result is there any way to get the SQL statement that created the resultset? Thanks, Brad ...

Getting Start With .net
hi i'm intersted to learn asp.net (vb or delphi) . i programming with delphi6 but i'd like to tp know something about programming in internet specially with .net . i hear about microsoft.net with delphi 7 that i can programming with delphi in .net .... is it right ? what 's our opinian about it ? and better to learn Vb.Net or continu with delphi(or delphi.net) thanks. Ali Darabian I think there is some support for Delphi in .Net. However, you will find much more support from the user community if you choose a more popular language such as VB.Net or C#. Since your al...

SQL Where Statement that shows all
I have a MSAccess database that I'm practicing on where I've set-up a dataset with a tableadapter. I have a query set-up that says  SELECT ID, SKU, DESCRIPTION, CUBE, CATEGORY, MANUFACTURER FROM AutocadProduct WHERE MANUFACTURER = ?  Is there a way to call this query where it selects all Manufacturer's if none is specified?  I currently have it running by rewritting the query, but it seems like there should be an easier way (my end result will have several conditions, so rewritting the query becomes ugly). I don't knwp abour Access, but in...

sql statement parsing
hi guys, how can i parse the where clause in an SQL::Statement object instance? $statement = SQL::Statement->new("select 1,2,3 from table where x=y"); now when i do this: $where = $stmt->where(); it gives me error: Can't locate object method "where" via package "SQL::Statement" (perhaps you for got to load "SQL::Statement"?) at blah line 34. any idea? thanks -- Hytham Shehab At 07:34 PM 8/10/02 +0300, Hytham Shehab wrote: > how can i parse the where clause in an SQL::Statement object instance? >$statement = S...

parsing SQL statement
Anyone having suggestion parsing SQL statement? It should able to parse: BLA BALA BALA... VALUES( 'abcd efg',,999, 'some \"STRING\" and \'STR2\' STR3',,,, 'abcd, def, fghi' ) I'm using tr and then split by "," but it will fall when seeing comma inside the single quote. Thanks. --budhi On Fri, 2008-05-30 at 19:24 +0700, beast wrote: > Anyone having suggestion parsing SQL statement? > > It should able to parse: > > BLA BALA BALA... > > VALUES( > 'abcd efg',,999, 'so...

how to get start with .NET
the net help people installed  to my desktop 1-microsoft visual studio .NET enterprise developer 2003 2-microsoft .NET framework 1.1 what else do i need ...???  and how can i try with some simple code in VS.Net and run it to see wat happen...????? please help If you want to develope web applications, then you'll also need IIS on your PC.  That'll require XP-Professional or Windows 2000/2003, but XP-Home edition won't allow IIS installation. As for how to get started, you can search the web for some tutorials or buy a good book on VS.  The topic is way too vast to...

Parsing SQL Statements
Has anyone run across any code or utilities for getting information from complex SQL statements? I want to be able to collect the table names and column names from SQL statements with complex sub-queries. Any help or pointers would be appreciated. joe white joe@lemma.net Joe -- Check out n_cst_sql in the PFC. It has a function that will break up a sql command into individual components (select,from,where,order,...) and another that will recombine the components into a single command. One caveat about this. When I used the 5.0.3 version, it had problems where nested sele...

combining 2 sql statements using Access version and not sql server to show results in a data list
Hi, I have 2 sql statements which will run in Access: private string m_cmdInitial = "Select Distinct Mid([Attribute Name],1,1) AS Initial FROM [Attributes]";  private string m_cmdCustmrs = "SELECT * FROM [Attributes] WHERE(Attributes.[Attribute Name]) LIKE '{0}*'"; The thing is that using SQL server the two sql statements can be combined using the format Function of ASP.net just by putting semicolon between the statements. The purpose is as follows: I am using 2 data lists. The first one is just a list of letters on top from which the user can choose a letter on which the term starts...

Getting line number to show during editing, getting values to show during debug, getting MSDN help to know that I have it installed
G'day, Sorry I feel so dumb but these issues just push me over the edge. I've recently had to re-install VS and now none of these features that I once had now don't work.  I obviously have to tweak something but what?  It doesn't seem too obvious (I hope!)Help please!Thanks,Geoff Line numbers - that can found under Tools > Options > Text Editor > (Language) > (somewhere there, there is is a check box for line numbers) When you are in the debug mode of the IDE, go into the Debug > Windows > (whatever windows you need - locals/autos are what you're l...

SQL statement and If statement
I've problem, it's in sqlcommand it's in "where" condition, which if it's exist in sql the "ELSE" not work, and if the condition not in sqlcommand the "ELSE" appear, could someone help  it's in selectcommand.CommandText = "SELECT Weekno FROM Weeks where Weekno='" + Weekno.Text + "'" if i delete the condition the " ELSE " appear. otherwise it's not appear whole code Dim myconnection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\football.mdf;Integrated S...

Web resources about - Showing parsed SQL statement? - asp.net.getting-started

User:Jimbo Wales/Statement of principles - Wikipedia, the free encyclopedia
As we move forward with software and social changes, I think it is imperative that I state clearly and forcefully my views on openness and the ...

Category:Articles containing potentially dated statements from July 2011 - Wikipedia, the free encyclopedia ...
This is an administration category . It is used for administration of the Wikipedia project and is not part of the encyclopedia. It contains ...

Malcolm Turnbull's statement to UN climate conference in Paris
Malcolm Turnbull joined other world leaders in Paris for the United Nations climate talks on Monday. This is his statement.

Trump's statement after being accused of mocking reporter's disability - Business Insider Deutschland ...
... Times over its coverage of his 9/11 claims. AP Photo/Alan Diaz Donald Trump. Real-estate mogul Donald Trump released a lengthy, fiery statement ...

Election 2016 News Today Dominated by Media Furor over These Trump Statements
Here is today’s top election 2016 news: The media attacked GOP presidential front-runner Donald Trump this week for saying thousands of New Jersey ...

President Obama delivers a statement on national security
x YouTube Video Wednesday, Nov 25, 2015 · 5:08:06 PM +00:00 · Laura Clawson We’ll have a more in-depth account coming soon, but the summary ...

Why Chancellor George Osborne's Autumn Statement Is Good News For Entrepreneurs
Why the UK Chancellor's statement is mostly good news for Britain's business owners.

Trump Tweets Lengthy Statement About Reporter Serge Kovaleski, Demands Apology from New York Times
Donald Trump responded to critics who accused him of imitating the mannerisms of New York Times reporter Serge Kovaleski in a lengthy statement ...

WWE News: Update On Why WWE Sent Out A Statement After The Controversial Paige And Charlotte RAW Promo ...
When WWE gets the wrestling world talking, that is usually a very good thing. However, that does not seem to be the case when it comes to Paige ...

Planned Parenthood shooting suspect's statements could suggest motive
Los Angeles Times Planned Parenthood shooting suspect's statements could suggest motive Los Angeles Times Scott Dontanville, a co-pastor at ...

Resources last updated: 12/1/2015 1:06:54 PM