?
Solved

Database error - Searching Access - Returning Value

Posted on 2001-06-19
7
Medium Priority
?
213 Views
Last Modified: 2010-05-02
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
Comment
Question by:flosoft
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 5

Accepted Solution

by:
GeoffKell earned 800 total points
ID: 6208432
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
 

Expert Comment

by:Zyx
ID: 6208490
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
 
LVL 5

Expert Comment

by:GeoffKell
ID: 6208500
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.

 

Author Comment

by:flosoft
ID: 6208520
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
 

Author Comment

by:flosoft
ID: 6208527
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
 

Expert Comment

by:Zyx
ID: 6208540
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
 
LVL 5

Expert Comment

by:GeoffKell
ID: 6208576
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month14 days, 8 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question