[RESOLVED] Not Reading Database Values

[circuits2]

This is a continuation from a question I asked yesterday. The initial problem has been fixed, but now I am encountering an alltogether different problem. The structure of the code below works well in other subs, but this one in particular does not return any values from the database (Even though I know they exist). Your help is greatly appreciated.

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

        ' CHECK STRINGCOLLECTION ARRAY 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=******.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 & " " & Convert.ToString(invQty))

                ' 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

[GaryMazzone]

Once again is partnumber a textfield? IF so don't you need the single qoutes around the parameter?

[circuits2]

I tried with and without single quotes and get the same results. I just happened to paste my code into the post above before adding them back in. I inserted a message box before the ExecuteReader to view the parameters and all values are being passed to the reader as they should. I do not think this is the problem.

[GaryMazzone]

Are you getting the No Rows messagebox or something else?

[circuits2]

I'm getting the No Rows message box.

[circuits2]

I was able to resolve this issue on my own by moving the OleDbCommand creation outside of the loop. Also, I had to remove the single quotes from the named parameter for it to work correctly. Here is the fully functional 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 = 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