none
VB.NET - Parameterised query for SQLDataAdapter - The parameter is not getting replaced with actual value RRS feed

  • Question

  • I am trying to query SQl Server from VB.NET. I am using parametrised query (sqldataadapter). I am opening the connection and program is querying the table, however the parameter in the query is not getting replaced with actual value. 

    Console.WriteLine(command.CommandText) - is printing the original query without replacing

    the parameter. The output from console is -

    SELECT MicroMessagesDownStreamApplicationId, ProcessStatusCode,Product,PolicyAction,

    BilledPremium,GeniusBilledPremium FROM MicroMessageDownStreamTransactionDetail

    WHERE Reference LIKE @pol OR MasterRef LIKE @pol;

    I have verified that there is record in db for the value I am trying to fetch.Connection

    string is taken by connStr = My.Settings.SitConnStr

     Public Sub GetPolicyNumber(polNumber As String)
    
            Dim queryMicroMessageDownstreamTransactionDetail As String = "SELECT MicroMessagesDownStreamApplicationId, ProcessStatusCode,Product,PolicyAction,BilledPremium,GeniusBilledPremium FROM MicroMessageDownStreamTransactionDetail " _
                                                                         & "WHERE Reference LIKE @pol OR MasterRef LIKE @pol;"
            Dim sqlDa As SqlDataAdapter = New SqlDataAdapter()
            objConn.Open()
            Dim command As SqlCommand = New SqlCommand(queryMicroMessageDownstreamTransactionDetail, objConn)
            command.CommandType = CommandType.Text
            command.CommandTimeout = 0
            command.Parameters.Add("@pol", SqlDbType.NVarChar)
            command.Parameters("@pol").Value = polNumber
            sqlDa.SelectCommand = command
            Dim dt As DataTable = New DataTable()
            Try
                Console.WriteLine(command.CommandText)
                sqlDa.Fill(dt)
    
                For Each row As DataRow In dt.Rows
                    For Each col As String In row.ItemArray
                        Console.WriteLine(col)
                    Next
    
                Next
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            Finally
                objConn.Close()
            End Try
    
            If objConn Is Nothing Then
                objConn.Close()
            End If
        End Sub



    Saturday, October 14, 2017 1:21 PM

All replies

  • The best way to test this out is to create a new text file in the project, write your query and test it e.g.

    You could also test it via creating a concrete class via the IDE data wizards (delete it when done). Here I added the same query as above in the .xsd file to test it with the same results.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, October 14, 2017 3:28 PM
    Moderator
  • I think that the replacement is done by SQL server, not by VB objects. If you are interested in the executed queries and received parameters, try the tracing features of the tool called “SQL Server Profiler”.

    Saturday, October 14, 2017 7:49 PM
  • Hi Tushar,

    I use your code and it works fine at my side, can you replace the following code,

    command.Parameters.Add("@pol", SqlDbType.NVarChar)
            command.Parameters("@pol").Value = polNumber
    


    Use this code and try it again.

    Command.Parameters.AddWithValue("@pol",polNumber)

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 16, 2017 8:21 AM
    Moderator