[RESOLVED] DB Returning No Results
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.
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 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?
Re: DB Returning No Results
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.
Re: DB Returning No Results
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:
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
Re: DB Returning No Results
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!
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
Re: [RESOLVED] DB Returning No Results
Quote:
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.
Quote:
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.
Re: [RESOLVED] DB Returning No Results
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.