Solved

Database error - Searching Access - Returning Value

Posted on 2001-06-19
7
208 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 200 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

717 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