[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

Database error - Searching Access - Returning Value

Hi,
I am getting an error and can't get this to work.
This is for a Point of sale system - I scan the SKU into a textbox with a Wedge type scanner, then The program needs to search the Items table and return the Description, Price and Type of item and add these to list boxes and labels so a reciept can be prepared - The code I have is this:

Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset
Const DBLocation = "C:\MYDATA\MYDATABASE.MDB"

Private Sub Form_Load()
 Dim cnString As String
 cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBLocation & ";" & _
            "Mode=Read|Write|Share Deny None;Persist Security Info=False"
 cn.Open cnString
 txtItemNum.SetFocus
End Sub



Private Sub GetItem()
Dim strSQL As String
If txtItemNum.Text = txtLastSKU Then Exit Sub
txtLastSKU = txtItemNum.Text
strSQL = "SELECT SKU, ItemDesc, Type, SellPrice from Items " & _
         "WHERE SKU = '" & txtItemNum.Text & "'"
On Error Resume Next
rs.Close
On Error GoTo 0
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly
If rs.BOF And rs.EOF Then
  txtItemNum.SetFocus
  txtItemNum.SelStart = 0
  txtItemNum.SelLength = Len(txtItemNum.Text)
  Else
lstDesc.AddItem rs!Description
lstPrice.AddItem rs!SellPrice
' Set focus to SKU and select current text for replacement
 
txtItemNum.SetFocus
  txtItemNum.SelStart = 0
  txtItemNum.SelLength = Len(txtItemNum.Text)
End If
End Sub


I get an error on this line:

rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly

The error is:
No value given for one or more required parameters.

I can't get this to go and have tried everything.
any help will be greatly appreciated.

Thanks,
Tom
0
flosoft
Asked:
flosoft
  • 3
  • 2
  • 2
1 Solution
 
GeoffKellCommented:
The three common reasons for this error are :-

1. Not enough parameters
2. Misspelt column names
3. Incorrect delimiters with SELECT queries

1 and 3 look to be OK from your query. Can you check your spelling of the column names

Regards
GK
0
 
ZyxCommented:
It seems like in your SQL statement ("SELECT SKU, ItemDesc, Type, SellPrice from Items WHERE SKU = '" & txtItemNum.Text & "'") one of your fields is not properly written.

You wrote "ItemDesc" in your SQL statement but at the bottom your wrote "lstDesc.AddItem rs!Description" so make sure you use the exact name for your field than the one in your database. It might be Description and not ItemDesc. Make sure all of your fields are properly written.




0
 
GeoffKellCommented:
Zyx - please don't propose answers. See comments at foot of page. Even more so when it duplicates a previous comment i.e. mine.

Regards
GK
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
flosoftAuthor Commented:
GeoffKell,
You were right on - I had SCU in the database and SKU in the program - If you could propose that as an answer - I can give you the points.

Thanks,
Tom
0
 
flosoftAuthor Commented:
Talk about a reminder on how important naming conventions are! Thanks alot! I will be posting a few more questions while I finish this POS System and hope I get great help like I did here - Thanks to both of you.

Tom
0
 
ZyxCommented:
No one had an answer so far when I was typing mine. By the time it took for me to send the answer, yours was sent already. If you look at the time of the post you'll see that yours was at 2:54, mine was at 3:13. So I didn't copy yours I just didn't know someone sent something already.

Regards


0
 
GeoffKellCommented:
Sorry for the accusation. However it is considered bad practice to post answers for the reasons given at the bottom of the page. It will bring you to the attention of the topic moderators.

Regards
GK
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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