none
SqlCmd

    Question

  • sqlCmd.CommandText = "Select cust_name from cust where cust_id = @id"
    sqlCmd.Parameters.Clear()
    sqlCmd.Parameters.AddWithValue("@id", "'007'")

    dim s as string
    s =  ?????? <----- Q*  result > Select cust_name from cust where cust_id = '007'


    Q:how to get the sql statement after sqlcmd parsed the parameters ?

     

    Wednesday, July 13, 2011 1:42 AM

Answers

All replies

  • Try something like this:

    imports System.Data.SqlClient
    Imports System.Text.RegularExpressions
    
    Module Module1
    	''' <summary>
    	''' Takes an SqlCommand called "cmd" and makes a reasonable attempt
    	''' at returning its explicit query string with the parameters
    	''' replaced by actual values
    	''' </summary>
    	''' <param name="cmd">
    	''' The SqlCommand that you want to make more explicit
    	''' </param>
    	''' <returns>
    	''' A more explicit version of the CommandText with the query 
    	''' parameters replaced by the actual values that will be used 
    	''' </returns>
    	''' <remarks>
    	''' Shawn Eary 12-JUL-2011: This is Public Domain
    	''' </remarks>
    	Function GetExplicitQueryString( _ 
    		ByVal cmd As SqlCommand) As String
    
    		Dim explicitText As String = cmd.CommandText
    		For Each param As SqlParameter In cmd.Parameters			
    			explicitText = RegEx.Replace( _ 
    					input:=explicitText, _
    					pattern:=param.ParameterName, _
    					replacement:=param.Value)			
    		Next
    		Return explicitText
    	End Function
    
    	Sub Main()
    		Dim sqlCmd As SqlCommand = New SqlCommand
    		sqlCmd.CommandText = 
    			"Select cust_name from cust where cust_id = @id"
    		sqlCmd.Parameters.Clear()
    		sqlCmd.Parameters.AddWithValue("@id", "'007'")
    		Dim s as String = GetExplicitQueryString(sqlCmd)
    	End Sub
    
    End Module
    
    


    Wednesday, July 13, 2011 3:25 AM
  • since you are searching by cust_id, assuming this is a unique column, the query should only return 1 record. In which case using ExecuteScalar would be useful.
    you also don't need to enclose the parameter value (in your case the 007 in quotes when using parameters
    not sure how you are going to get the cust_id; in the example below it uses a TextBox to enter it. Then it uses CInt to convert the TextBox Text to an integer. If you do something like this, be sure to add some validation that what is entered is actually an Integer
      Dim sqlConn As SqlServerCe.SqlCeConnection
      Dim sqlCmd As SqlServerCe.SqlCeCommand
      
      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Using sqlConn As New SqlServerCe.SqlCeConnection(My.Settings.CustomerConnectionString)
          sqlConn.Open()
          sqlCmd = New SqlServerCe.SqlCeCommand("SELECT cust_name FROM cust WHERE cust_id = @id", sqlConn)
          sqlCmd.Parameters.AddWithValue("@id", CInt(TextBox1.Text))
         
          Dim FoundCust As String = sqlCmd.ExecuteScalar().ToString
          
          Label1.Text = FoundCust
        End Using
      End Sub
    
     
    Wednesday, July 13, 2011 5:34 AM
  • Vedee,

    I'm curious do you want to execute the command or do you want to have that what would have been created if you not had used parameters.

    In the later case I'm curious why. The parameter is a subject of SQL and is not translated to a kind of string.

     


    Success
    Cor
    Wednesday, July 13, 2011 6:55 AM
  • Shawn, thanks.

    Jwavilla, thanks also, but it cant help much.

     

    Cor,

    More and less, what Shawn suggested is correct.

    But his modules need to detect the data type, so it will generate a correct SQL string when i want to test it on SQL server mngmt studio.

    The idea is, i just want to know , whether the SqlCmd has parsed my Sql statement correctly or not.

    Below are the some of the codes:

     

    sqlCmd.CommandText = strSQL
          sqlCmd.Parameters.Clear()

          sqlCmd.Parameters.AddWithValue("@param1", strParam1)
       sqlCmd.Parameters.AddWithValue("@param2", strParam2)
       sqlCmd.Parameters.AddWithValue("@param3", strParam3)
           :
        :
        ' there are lots of param :(
        :
       sqlCmd.Parameters.AddWithValue("@paramN", strParamN)
          sqlCmd.ExecuteNonQuery()
          trnSQL.Commit()

        Catch ex As SqlException
      
      ' Question: What should i put here to detect/debug if one of param has gone wrong ?
    ' and i will be easily find out, which param cause the exception. thanks.

      
        Finally

        End Try

     



    Thursday, July 14, 2011 11:34 AM
  • Hi Vedee,

    I am afraid we might not be able to detect the exact param cause the exception.

    Anyway, please read the http://msdn.microsoft.com/en-us/library/686y883x.aspx to see can get some ideas or not.

     

    Best Regards,


    Kee Poppy [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 20, 2011 10:00 AM
    Moderator
  • Duh,

    It seems that Kee awake me, there is a tool for what you ask. 

    The SQL profiler, which is a package which comes with SQL serverr.

    http://msdn.microsoft.com/en-us/library/ms181091.aspx

     

     


    Success
    Cor
    Wednesday, July 20, 2011 11:51 AM