[RESOLVED] Need an Expert! ExecuteReader haywire

[circuits2]

Good morning!

I have been staring at this code for two days now and I absolutely cannot figure out why it throws an error at bomRdr = bomCmd.ExecuteReader. The error thrown is: No value given for one or more required parameters. However, I have tried inserting a messagebox before the ExecuteReader to show all necessary values at runtime and everything looks good. Furthermore, most of this code was copy and pasted from another of my subroutines that functions perfectly. Please help!

Sub CheckForSufficientInventory(ByRef proceed As Boolean, ByRef bomList As StringCollection, _
        ByRef qtyList As StringCollection)

        ' CHECK STRINGCOLLECTION ARRAY AGAINST INVENTORY
        ' PROCEED IF SUFFICIENT INVENTORY OR
        ' NOTIFY USER OF DEFICIENCIES
        ' GIVE OPTION TO OVERRIDE

        Dim canBuild As Integer
        Dim itemsShort As Integer = 0
        Dim itemNumber As Integer
        Dim itemToFind As String
        Dim buildQty As Integer = Integer.Parse(Me.uiQtyTextBox.Text)
        Dim totalCanBuild As Integer = buildQty
        Dim bomConString As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
            "Source=….mdb;" & _
            "Jet OLEDB:Database Password=******;"

        ' CREATE CONNECTION AND OPEN
        bomCon = New OleDbConnection(bomConString)
        bomCon.Open()

        Try

            ' CHECK INVENTORY
            For itemNumber = 1 To bomList.Count

                ' CREATE COMMAND AND SET PARAMETERS
                itemToFind = Convert.ToString(bomList.Item(itemNumber - 1))
                bomCmd = New OleDbCommand("SELECT Inventory.invMain FROM invMain WHERE (PartName = @comp)", bomCon)
                bomCmd.Parameters.Add("@comp", OleDb.OleDbType.Char)
                bomCmd.Parameters("@comp").Value = itemToFind

                ' EXECUTE COMMAND
                bomRdr = bomCmd.ExecuteReader

                While bomRdr.Read

                    Dim invQty As Integer = Integer.Parse(Convert.ToString(bomRdr(0)))

                    ' COMPARE QUANTITIES
                    If invQty < Integer.Parse(qtyList.Item(itemNumber - 1)) * buildQty Then
                        itemsShort = itemsShort + 1
                        canBuild = invQty \ Integer.Parse(qtyList.Item(itemNumber - 1))
                        If canBuild < totalCanBuild Then
                            totalCanBuild = canBuild
                        End If
                    End If
                End While

                ' CLOSE READER FOR NEXT ITEM
                bomRdr.Close()

            Next itemNumber

            If itemsShort > 0 Then
                MessageBox.Show("Insufficient inventory exists for " & itemsShort & " item(s)" & vbCrLf & _
                                "at the selected quantity. You can build " & totalCanBuild & vbCrLf & _
                                "units with current inventory levels.")
                proceed = False
            Else
                proceed = True
            End If

            bomCon.Close()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            proceed = False
        End Try

    End Sub

[bmahler]

you need to set the connection for bomCmd

bomCmd.Connection = New OleDbConnection(bomConString)
bomCmd.Connection.Open()

This is what i use to return a set with SQL Server, however the principles should be the same

Dim oCmd As SqlClient.SqlCommand
                Dim intVal As Int32 = -1
                Dim dr As SqlClient.SqlDataReader
 
                oCmd = New SqlClient.SqlCommand
 
                Try
                    With oCmd
                        .Connection = New SqlClient.SqlConnection(connString)
                        .Connection.Open()
                        .CommandType = CommandType.Text
                        .CommandText = strSQL
                        dr = .ExecuteReader
                    End With
                    dr.Read()
                    userID = dr.Item(0)
                    userLevel = dr.Item(1)
                    dr.Close()
                    getUserInfo = True
                Catch ex As Exception
                    intVal = -1
                Finally
                    If Not oCmd Is Nothing Then
                        oCmd.Dispose()
                    End If
                End Try

[circuits2]

The connection is set through bomCon and then related at the end of the bomCmd = statement. Is this not syntactically correct?

bomCon = Connection
bomConString = Connection String
bomCmd = Command

[circuits2]

It also appears as though you are using VB 6. Is this correct?

[bmahler]

no, I am using VB 2005, and you need to define the connection for the command. It the reader has no defined connection it will just error. In your example you are opening a connection, but you are not telling the command what connection to use.

[circuits2]

I defined the connection through statement bomCmd = New OleDbCommand (Select Statement, Connection) as shown in code.

[bmahler]

try this

For itemNumber = 1 To bomList.Count
                Dim bomCmd As OleDb.OleDbCommand
 
                ' CREATE COMMAND AND SET PARAMETERS
                itemToFind = Convert.ToString(bomList.Item(itemNumber - 1))
                bomCmd.Connection = New OleDb.OleDbConnection(bomConString)
                bomCmd.Connection.Open()
                bomCmd.CommandType = CommandType.Text
                bomCmd.CommandText = "SELECT Inventory.invMain FROM invMain WHERE (PartName = @comp)"
                bomCmd.Parameters.Add("@comp", OleDb.OleDbType.Char)
                bomCmd.Parameters("@comp").Value = itemToFind
 
                ' EXECUTE COMMAND
                bomRdr = bomCmd.ExecuteReader
 
                While bomRdr.Read
 
                    Dim invQty As Integer = Integer.Parse(Convert.ToString(bomRdr(0)))
 
                    ' COMPARE QUANTITIES
                    If invQty < Integer.Parse(qtyList.Item(itemNumber - 1)) * buildQty Then
                        itemsShort = itemsShort + 1
                        canBuild = invQty \ Integer.Parse(qtyList.Item(itemNumber - 1))
                        If canBuild < totalCanBuild Then
                            totalCanBuild = canBuild
                        End If
                    End If
                End While
 
                ' CLOSE READER FOR NEXT ITEM
                bomRdr.Close()
                bomCmd.Dispose()
 
            Next itemNumber

[bmahler]

What is your error when it errors?

[circuits2]

I tried adding your code from above and still get the same error. This leads me to believe that both syntax formats, my original and your suggested, are correct.

The error I get is "No value given for one or more required parameters". However, if I add a messagebox that prints to screen all possible parameters, I get good values.

[GaryMazzone]

The error seems to point to a problem in your SQL statement. By that I mean that the Inventory.invMain FROM invMain WHERE (PartName = @comp) is wrong. If PartName is the correct field name in the DB then is invMain a field in Inventory. Shouldn't the paramter field be inclosed withing single qoutes if you are testing for a string value.

Also post the SQL value that is being passed as it is rexaclty in the app.

[circuits2]

MY HERO! The problem was the single quotes around my parameter. THANK YOU BOTH!!!!