Solved

Database error - Searching Access - Returning Value

Posted on 2001-06-19
7
195 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
  • 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now