asked on

Why is my SQL parameter (in VB.NET) failing, but explicitly assigning the value works?

I'm wondering why the following query string works great if I assign an explicit
value to c.item_ID, as so:

 Dim connectionString2 As String = "Server=SQLSERVER;Database=DB;Trusted_Connection=True;"
        connection2 = New SqlConnection(connectionString2)
        connection2.Open()
        MsgBox("heres the ItemID from above: " + MyItemIDValue)
        Dim myparam2 As New SqlParameter("@itemID", MyItemIDValue)
        MsgBox("just assigned: " + MyItemIDValue + " to @itemID...hopefully")
        Dim queryString2 As String = "SELECT TOP 1 a.product_group_id as 'PRODUCT_GROUP',b.product_group_id as 'INV_LOC',
                            c.default_product_group AS 'INV_MAST',c.item_id as 'INV_MAST'
                            FROM product_group a
                            inner JOIN inv_loc b
                            ON a.product_group_id = b.product_group_id
                            inner join inv_mast c
                            ON c.default_product_group = b.product_group_id
                            WHERE c.item_id = 'STB 850077'"
        '        Dim queryString2 As String = "SELECT top 1 * from inv_mast
        'WHERE item_id = [b]@itemID[/b]"

        Dim command As New SqlCommand(queryString2, connection2)
        command.CommandText = queryString2
        command.CommandType = CommandType.Text
        command.Parameters.Add("@itemID", SqlDbType.VarChar)
        command.Parameters("@itemID").Value = MyItemIDValue

        Try
            Dim MyReader2 As SqlDataReader = command.ExecuteReader
            'If The Reader Finds Rows
            If MyReader2.HasRows Then
                MsgBox("Reader2 has rows")
                'Retrieve The Content, For Each Match
                While MyReader2.Read()
                    If MyReader2.Item("PRODUCT_GROUP") Then
                        MsgBox("ItemID: " + MyItemIDValue + "  " + "Product group: " + MyReader2.Item("PRODUCT_GROUP"))
                    End If
                End While
            Else
                MsgBox("Reader2 has NO rows")
            End If
        Catch abcd As Exception
            MsgBox(abcd.ToString)
        End Try
        connection2.close()

Open in new window

but replace with my parameter, and it returns No Rows, as in below:

 Dim connectionString2 As String = "Server=SQLSERVER;Database=DB;Trusted_Connection=True;"
        connection2 = New SqlConnection(connectionString2)
        connection2.Open()
        MsgBox("heres the ItemID from above: " + MyItemIDValue)
        Dim myparam2 As New SqlParameter("@itemID", MyItemIDValue)
        MsgBox("just assigned: " + MyItemIDValue + " to @itemID...hopefully")
        Dim queryString2 As String = "SELECT TOP 1 a.product_group_id as 'PRODUCT_GROUP',b.product_group_id as 'INV_LOC',
                            c.default_product_group AS 'INV_MAST',c.item_id as 'INV_MAST'
                            FROM product_group a
                            inner JOIN inv_loc b
                            ON a.product_group_id = b.product_group_id
                            inner join inv_mast c
                            ON c.default_product_group = b.product_group_id
                            WHERE c.item_id = @itemID"
        '        Dim queryString2 As String = "SELECT top 1 * from inv_mast
        'WHERE item_id = [b]@itemID[/b]"

        Dim command As New SqlCommand(queryString2, connection2)
        command.CommandText = queryString2
        command.CommandType = CommandType.Text
        command.Parameters.Add("@itemID", SqlDbType.VarChar)
        command.Parameters("@itemID").Value = MyItemIDValue

        Try
            Dim MyReader2 As SqlDataReader = command.ExecuteReader
            'If The Reader Finds Rows
            If MyReader2.HasRows Then
                MsgBox("Reader2 has rows")
                'Retrieve The Content, For Each Match
                While MyReader2.Read()
                    If MyReader2.Item("PRODUCT_GROUP") Then
                        MsgBox("ItemID: " + MyItemIDValue + "  " + "Product group: " + MyReader2.Item("PRODUCT_GROUP"))
                    End If
                End While
            Else
                MsgBox("Reader2 has NO rows")
            End If
        Catch abcd As Exception
            MsgBox(abcd.ToString)
        End Try
        connection2.close()

Open in new window

SOLUTION