?
Solved

VB ADODB ERROR MYSTERY

Posted on 2003-03-12
7
Medium Priority
?
252 Views
Last Modified: 2010-05-01
I am writing a program that interfaces an Access 2000 DB through ADODB, the following code keeps giving me an error, and i can't see the problem.

VB code:
    Dim con As New ADODB.Connection
    Dim StudentRS As New ADODB.Recordset
    Dim ClassRS As New ADODB.Recordset
    curclass = Val(Left(lstClass.Text, 2))
    con.Open "FILEDSN=" & App.Path & "\MDBGIMP.DSN"
    ClassRS.Open "Select * from Class where ID = " & str(curclass), con
    ClassRS.MoveFirst
        'Output stuff
    ClassRS.Close
    con.Close
    con.Open "FILEDSN=" & App.Path & "\MDBGIMP.DSN"
    StudentRS.Open "Select ID, Surname, First_Names, Cisco_Num from Student where ClassID = " & str(curclass), con !%%%POINT OF ERROR%%%!
    StudentRS.MoveFirst
    Do While StudentRS.EOF = False
        lstClaStu.AddItem intsigfig(StudentRS.Fields(0).value, 3) & " - " & StuRS.Fields(1).value & ", " & StuRS.Fields(2).value & " - " & StuRS.Fields(3).value
        StudentRS.MoveNext
    Loop
    StudentRS.Close
    con.Close
    End Sub

If it helps, this is the contents of the DSN file:
    [ODBC]
    DRIVER=Microsoft Access Driver (*.mdb)
    UID=admin
    UserCommitSync=Yes
    Threads=3
    SafeTransactions=0
    PageTimeout=5
    MaxScanRows=8
    MaxBufferSize=2048
    FIL=MS Access
    DriverId=25
    DefaultDir=N:\path\files
    DBQ=N:\path\files\DB1.mdb

The runtime error reads:
    Run-time error '-2147217904 (80040e10)':
    [Microsoft][ODBC Microsoft Access Driver] Too few parameters.
    Expected 1.

Can anyone see where i'm going wrong. The error only occurs on the second Recordset open, the first one works fine. I've tried renaming the RS, declaring a second adodb.connection to use for the second RS, commenting the first RS + connection operations out. Nothing works..

I am on a tight schedule so prompt solutions would be appreciated.
I am allocating all my remaining pts to this question as a reward.

Thanks in advance.
0
Comment
Question by:Howells
[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
7 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 630 total points
ID: 8118687
Hi Howells,

I have to admit there is nothing obvious here, however you are referencing ClassID as well as ID in the SQL statement, is this valid? I.e., is there a field called ID in your table, also is the classID field a numeric field or character field? If the latter then this error can be caused by the missing single-quote (') character around the value.

Tim Cottee MCSD, MCDBA, CPIM
http://www.timcottee.tk 

Brainbench MVP for Visual Basic
http://www.brainbench.com

Experts-Exchange Advisory Board Member
0
 
LVL 14

Expert Comment

by:puranik_p
ID: 8118854
the error is in most of the cases due to typo mistakes.
try
instead of.....
ClassRS.Open "Select * from Class where ID = " & str(curclass), con
this....
Dim strSQL as String
strSQL = "Select * from Class where ID = " & str(curclass)
Debug.Print strSQL

now copy strSQL from immediate window and paste it in Access Query to check if it runs.
(I bet it will throw the same error. may be problem is with str(curclass)).
all the best!
0
 
LVL 1

Expert Comment

by:MarkKoro
ID: 8118863
That error is usually from an unknown field name.

This debugging method works well for me...

Dim StrSQL as string
StrSQL="Select ID, Surname, First_Names, Cisco_Num from Student where ClassID = " & str(curclass)
StudentRS.Open StrSQL, con 'set breakpoint here

In immediate window, type in ?Strsql when the program stops on the breakpoint. If you still can't see the syntax problem, then copy that string to a new access query in SQL view mode and switch to design view. You can try to run it as well. It should be pretty clear exactly what is wrong.
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.

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 8119074
be sure that curclass contains a value
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8121295
It would be more helpful if you told us which line generates the error, but it must be that recordset's .Open statement because you haven't provided single quotes for what appears to be a text comparison.  

You should have:

ClassRS.Open "Select * from Class where ID = ' " & str(curclass) & " ' ", con

I added spaces here for clarity.  You probably shouldn't include them.  Many developers use the following technique to make it REALLY obvious, and I prefer it personally:

ClassRS.Open "Select * from Class where ID = "  & chr$(39) & str(curclass) & chr$(39), con



0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8125092
Does App.Path happen to have a space in it?

mlmcc
0
 

Author Comment

by:Howells
ID: 8135412
Thanks Tim for the solution and the prompt reply.. You saved me quite a bit of time.. Enjoy the points

Thanks to everyone else too..
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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 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…
Suggested Courses
Course of the Month10 days, 14 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