Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Database error - Searching Access - Returning Value

Posted on 2001-06-19
7
Medium Priority
?
220 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

636 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