[RESOLVED] DB Returning No Results

[circuits2]

Good Morning,

I posted this question in the VB.NET forum, but it looks as though I posted it to the wrong audience. I have used the structure of the code below successfully in past subs. However, this particular sub will not return values even though they exist. I am attempting to connect to table invMain in an Access Database and locate a value in the Inventory column where the PartNumber column = a value pulled from an array.

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

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

        Dim buildQty As Integer
        Dim canBuild As Integer
        Dim currentItem As String
        Dim currentQty As Integer
        Dim invQty As Integer
        Dim itemNumber As Integer
        Dim itemsShort As Integer
        Dim totalCanBuild As Integer = buildQty
        Dim selectString As String

        Try

            ' CREATE CONNECTION AND OPEN
            Dim invConString As String = _
                "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
                "Source=C:\******.mdb;" & _
                "Jet OLEDB:Database Password=******;"
            invCon = New OleDbConnection(invConString)
            invCon.Open()
            

            ' CHECK INVENTORY
            For itemNumber = 1 To bomList.Count

                ' SET CURRENT ITEM AND QTY
                currentItem = bomList.Item(itemNumber - 1).ToString
                currentQty = Integer.Parse(qtyList.Item(itemNumber - 1).ToString)

                ' CREATE COMMAND AND SET PARAMETERS
                invCmd = New OleDbCommand
                invCmd.CommandType = CommandType.Text
                invCmd.CommandText = "SELECT invMain.PartNumber, invMain.Description1, " & _
                    "invMain.Description2, invMain.Type, invMain.Inventory" & _
                    " FROM invMain" & " WHERE (invMain.PartNumber='@comp')"
                invCmd.Parameters.Add("@comp", OleDb.OleDbType.Char)
                invCmd.Parameters("@comp").Value = currentItem
                invCmd.Connection = invCon

                ' EXECUTE COMMAND
                invRdr = invCmd.ExecuteReader

                ' IF DATA, READ INVENTORY
                If invRdr.HasRows Then
                    invQty = Integer.Parse(invRdr(4).ToString)
                Else
                    MessageBox.Show("No Rows")
                End If

                MessageBox.Show(currentItem & " " & invQty.ToString)

                ' COMPARE QUANTITIES
                If invQty < currentQty * buildQty Then
                    itemsShort = itemsShort + 1
                    canBuild = invQty \ currentQty
                    If canBuild < totalCanBuild Then
                        totalCanBuild = canBuild
                    End If
                End If

                ' CLOSE READER FOR NEXT ITEM
                invRdr.Close()

            Next itemNumber

            ' IF INVENTORY IS SHORT, NOTIFY USER, RETURN PROCEED = FALSE
            If itemsShort > 0 Then
                MessageBox.Show("Insufficient inventory exists for " & itemsShort & " item(s)" & _
                                "at the selected quantity. " & vbCrLf & "You can build " & totalCanBuild & _
                                " units with current inventory levels.")
                proceed = False
            Else
                ' INVENTORY IS SUFFICIENT, RETURN PROCEED = TRUE
                proceed = True
            End If

            ' CLOSE CONNECTION
            invCon.Close()


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

    End Sub

Any guidance you can provide is greatly appreciated. Also, would it be easier if I switched to ADODB instead of using ADOX?

[brucevde]

You are not using ADOX, you are using ADO.Net

The where clause is incorrect. Remove the single quotes around @comp.
[Edit] Just read the help file. OLEDBCommand does not support named parameters. The Where clause should be

WHERE invMain.PartNumber=?"

And you need to set the Size property of the Parameter as well.

The creation of the command object only needs to be performed once. Move that code outside the loop. The only thing that needs to be inside the loop is the setting of the parameter value.

Set invQty to 0 if the data reader has no rows. Otherwise it will have the value of the previous item.

[circuits2]

Ok, I made some changes based on your information below. Now I am getting an error that sais "No data exists for the row/column". Here is the new code:

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

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

        Dim buildQty As Integer
        Dim canBuild As Integer
        Dim currentItem As String
        Dim currentItemLength As Integer
        Dim currentQty As Integer
        Dim invQty As Integer
        Dim itemNumber As Integer
        Dim itemsShort As Integer
        Dim totalCanBuild As Integer = buildQty

        Try

            ' CREATE CONNECTION AND OPEN
            Dim invConString As String = _
                "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
                "Source=C:\******.mdb;" & _
                "Jet OLEDB:Database Password=******;"
            invCon = New OleDbConnection(invConString)
            invCon.Open()

            ' CREATE COMMAND AND SET PARAMETER
            invCmd = New OleDbCommand
            invCmd.CommandType = CommandType.Text
            invCmd.CommandText = "SELECT invMain.Inventory" & _
                " FROM invMain WHERE (invMain.PartNumber=?)"
            invCmd.Parameters.Add("@comp", OleDbType.VarChar, bomList.Item(0).ToString.Length).Value = _
                bomList.Item(0).ToString
            invCmd.Connection = invCon

            ' CHECK INVENTORY
            For itemNumber = 1 To bomList.Count

                ' SET CURRENT ITEM AND QTY
                currentItem = bomList.Item(itemNumber - 1).ToString
                currentQty = Integer.Parse(qtyList.Item(itemNumber - 1).ToString)

                ' SET PARAMETERS
                currentItemLength = currentItem.Length
                invCmd.Parameters(0).Size = currentItemLength
                invCmd.Parameters(0).Value = currentItem

                ' EXECUTE COMMAND
                invRdr = invCmd.ExecuteReader

                ' IF DATA, READ INVENTORY
                If invRdr.HasRows Then
                    invQty = Integer.Parse(invRdr(0).ToString)
                Else
                    invQty = 0
                    MessageBox.Show("No Rows")
                End If

                MessageBox.Show(currentItem & " " & invQty.ToString)

                ' COMPARE QUANTITIES
                If invQty < currentQty * buildQty Then
                    itemsShort = itemsShort + 1
                    canBuild = invQty \ currentQty
                    If canBuild < totalCanBuild Then
                        totalCanBuild = canBuild
                    End If
                End If

                ' CLOSE READER FOR NEXT ITEM
                invRdr.Close()

            Next itemNumber

            ' IF INVENTORY IS SHORT, NOTIFY USER, RETURN PROCEED = FALSE
            If itemsShort > 0 Then
                MessageBox.Show("Insufficient inventory exists for " & itemsShort & " item(s)" & _
                                "at the selected quantity. " & vbCrLf & "You can build " & totalCanBuild & _
                                " units with current inventory levels.")
                proceed = False
            Else
                ' INVENTORY IS SUFFICIENT, RETURN PROCEED = TRUE
                proceed = True
            End If

            ' CLOSE CONNECTION
            invCon.Close()


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

    End Sub

[circuits2]

I was able to correct this issue on my own by rewriting the sub. Actually, you can have a named parameter in an OleDbCommand as proven in my code below. Thank you for your help brucevde!

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

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

        Dim buildQty As Integer = Integer.Parse(Me.uiQtyTextBox.Text)
        Dim canBuild As Integer
        Dim totBuild As Integer = buildQty
        Dim curQty As Integer
        Dim lowInv As Integer = 0
        Dim itemNumber As Integer
        Dim invQty As String
        Dim itemToFind As String
        Dim invConString As String = _
                "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
                "Source=C:\******.mdb;" & _
                "Jet OLEDB:Database Password=******;"

        Try

            ' CREATE CONNECTION AND OPEN
            invCon = New OleDbConnection(invConString)
            invCon.Open()

            ' CREATE COMMAND AND ADD PARAMETER
            invCmd = New OleDbCommand("SELECT * FROM invMain WHERE (PartNumber = @find)", invCon)
            invCmd.Parameters.Add("@find", OleDb.OleDbType.Char)

            For itemNumber = 1 To bomList.Count

                ' SET CURRENT ITEM AND ASSIGN TO PARAMETER
                curQty = Integer.Parse(qtyList.Item(itemNumber - 1).ToString)
                itemToFind = bomList.Item(itemNumber - 1).ToString
                invCmd.Parameters("@find").Value = itemToFind

                ' EXECUTE READER
                invRdr = invCmd.ExecuteReader

                If invRdr.HasRows Then
                    ' IF RESULTS, STORE VALUE
                    While invRdr.Read()
                        invQty = Convert.ToString(invRdr(4))
                    End While
                Else
                    ' NO RESULTS, KICK OUT
                    invQty = "0"
                End If

                ' CLOSE READER
                invRdr.Close()

                ' COMPARE INV QTY AND NEED QTY
                If Integer.Parse(invQty) < curQty * buildQty Then
                    lowInv = lowInv + 1
                    canBuild = Integer.Parse(invQty) \ curQty
                    ' HOW MANY CAN YOU BUILD
                    If canBuild < totBuild Then
                        totBuild = canBuild
                    End If
                End If

            Next itemNumber

            ' Close connections
            invCon.Close()

            If lowInv > 0 Then
                MessageBox.Show(lowInv & " item(s) lack sufficient inventory to build this qty." & vbCrLf & _
                                "With current inventory you can build " & totBuild & " unit(s).")
                proceed = False
            Else
                MessageBox.Show("You have enough inventory for this build")
                proceed = True
            End If

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

    End Sub

[brucevde]

Actually, you can have a named parameter in an OleDbCommand as proven in my code below.

I don't program in .NET and so went by the documentation.

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter.

[circuits2]

I'm still not sure why it works with the named parameter. I too read the exerpt that you quoted from documentation. All the same I still appreciate your help as your suggestions led to the eventual solution.