• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

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

0
Richard Gouette
Asked:
Richard Gouette
2 Solutions
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now