[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to get the actual SQL that will be executed from the command object....

Posted on 2006-07-01
5
Medium Priority
?
216 Views
Last Modified: 2010-04-23
Dear All,

Probably a simple one. I want to display the actual SQL that gets executed ie with the parameter substituted.

My code is:

cmdDelete.Parameters("@CustomerID").Value = Me.m_CustomerID

con.Open()
Dim rowsAffected As Integer
MessageBox.Show("Delete SQL=" & m_CustomerID & ":" & cmdDelete.CommandText)
rowsAffected = cmdDelete.ExecuteNonQuery()

I get:

DELETE FROM Customers
WHERE     (CustomerID = [@CustomerID])

I want to get:

DELETE FROM Customers
WHERE     (CustomerID = 8)

How do I do this?

Thanks,

Sam
0
Comment
Question by:SamJolly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 17024057
what is m_CustomerID? a control on a form or a variable?

If it's a textbox, then try m_CustomerID.Text
0
 

Author Comment

by:SamJolly
ID: 17024363
YZlat,

Thanks for the comment. No I was expecting the command object to offer a property that would return what I need hence the attempted use of .CommandText. m-CustomerID is just a private variable within the Customer Class..

So is it possible to get the actual executed SQL from something in ADO.NET.

Thanks,

sam
0
 
LVL 34

Accepted Solution

by:
Sancler earned 2000 total points
ID: 17024460
I don't think this is possible for the reason that, so far as I know, the CommandText with the Parameter.Value actually substituted for the parameter's marker never exists in ADO.NET.  My belief is that it is passed from .NET to the database driver with the statement in the form shown in your messagebox and with the parameter name/s and value/s passed separately.  I think it is the driver and/or the database itself that does the necessary substitution.  If that is correct, then the only way you will get a message on the lines you want is by "rolling your own".  In pseudo-code - which means I haven't tested it ;-) and it doesn't refer to a specific sort of Command, eg sql or oledb - you could do this with something like

   Dim msg As String = command.CommandText
   For Each param As Parameter In Command.Parameters
       msg = msg.Replace(param.ParameterName, param.Value.ToString)
   Next
   MsgBox(msg)

Roger
0
 

Author Comment

by:SamJolly
ID: 17026781
Roger,

So based on your advice I have created a helper function as such:

    Private Function GetSqlString(ByVal cmd As System.Data.OleDb.OleDbCommand)

        Dim strMessage As String = cmd.CommandText

        For Each param As OleDbParameter In cmd.Parameters
            strMessage = strMessage.Replace("[" & param.ParameterName & "]", param.Value.ToString)
        Next

        Return strMessage

    End Function

Thanks,

Sam
0
 

Author Comment

by:SamJolly
ID: 17026786
Thanks all for helping.

Sam
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

656 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