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

Richard GouetteIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Máté FarkasDatabase Developer and AdministratorCommented:
2 suggestions:
1. Run a server side trace to capture real sql query sent to SQL Server and see what happens.
2. Add length definition to the length of column dbo.inv_mast.item_id when you add new item to Parameters collection:
command.Parameters.Add("@itemID", SqlDbType.VarChar, 100)

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Where are you using param2 variable?
Can you post here the transformed queryString2 value, just before the execution of the SqlCommand?
0
Robberbaron (robr)Commented:
you are doubling up on settings, probably as a result of trying to get it to work.  This is the way my app does the same thing...
        Dim command As New SqlCommand(queryString2, connection2)

        command.CommandType = CommandType.Text

        command.Parameters.Add("@itemID", SqlDbType.VarChar).Value = MyItemIDValue

Open in new window


the query string is added within the new SqlCommand line, and the parameter is added with it's value in line

your query itself seems to be wrong also, or at least doesnt make sense to me as you are applying the alias twice.
c.default_product_group AS 'INV_MAST',c.item_id as 'INV_MAST'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard GouetteIT ManagerAuthor Commented:
all set gents.
a combination of not doubling up on fields, and observing in SQL profiler helped to nail it down.

thanks much,
Rich
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.