Solved

database query problem

Posted on 2001-07-10
11
300 Views
Last Modified: 2012-08-13
Hi all,

I have a simple problem here and was wandering if anyone cold point me in the right direction.  I am trying to carry out a simple query to a Access 2000 database.
I am using an SQL string to search for details by giving either a surname or a forename.

Here's the code...  this first checks to see if a query already exists for that search... if so it will delete it and do a new one.  It does work, a surename or forename will create a new query and display the relevent feilds.
The problem occurs on the line

Set RecSet = db.OpenRecordset(SQLString, dbOpenSnapshot)

giving a type mismatch error, maybe i've over looked something but I cannot understand why! can anyone help?

I am using Visual Basic 6 with Service Pack 5. i can send the complete database via email should that help.

Private Sub cmdSearch_Click()

Dim SQLString As String
Dim db As Database
Dim qdfTemp As QueryDef
Dim Reply As Integer
Dim RecCount As Integer
Dim RecSet As Recordset

Set db = OpenDatabase("c:\ticket\ticket.mdb")
Load stafftesterresult

'Build the SQL String according to the criteria entered

SQLString = "select * from Staff "

If txtSname <> "" Then
    SQLString = SQLString & "where Sname like '" & txtSname & "*'"
Else
    If txtFname <> "" Then
        SQLString = SQLString & "where Fname like '" & txtFname & "*'"
    End If
End If
 
    db.QueryDefs.Refresh
    For Each qdfTemp In db.QueryDefs
        If qdfTemp.Name = "qryStaffList" Then db.QueryDefs.Delete "qryStaffList"
    Next qdfTemp
    Set qdfTemp = db.CreateQueryDef("qryStaffList", SQLString)
    Set RecSet = db.OpenRecordset(SQLString, dbOpenSnapshot)
    RecCount = RecSet.RecordCount

    stafftesterresult.Show vbModeless, frmSplash
End Sub

thank you,
Byte_me.
0
Comment
Question by:byte_me
11 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 6269325
Once you have created the QueryDef object reference, you can use that Object to open the recordset:

Set RecSet = qdfTemp.OpenRecordset(dbOpenSnapshot)

Arthur Wood



0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6269396
Why not create two queries in Access as follows and call them from code

Query 1:

PARAMETERS argInSname Text;
SELECT * FROM Staff WHERE Sname Like argInSname

Query 2:

PARAMETERS argInFname Text;
SELECT * FROM Staff WHERE Fname Like argInFname

Then in your code do your usual check

If txtSname <> "" Then
   SQLString = "Query1" & " " & txtSname
ElseIf txtFname <> "" Then
   SQLString = "Query2" & " " & txtFname
End If
Set RecSet = db.OpenRecordset(SQLString, dbOpenSnapshot)
RecCount = RecSet.RecordCount

V.  
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6269420
hi is correct plus, you can use the following to change
sql statement without dropping/creating query def.
Set  qdfTemp = db.QueryDefs("qryStaffList")
qdfTemp.SQL = SQLString

also you may have some problems if you have more than one user trying to search the database.

you should use query def only if you are calling the same query with different parameters.
 
so you can create two queries bylast name and byfirst name, and use parameters collection to set your search string.
0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6269448
Using your first approach with multiple users accessing the same query will defintely cause problems using two queries with a parameter will avoid this problem.

V.
0
 

Expert Comment

by:Bowthy
ID: 6270056
I vaguely remember having a problem along these lines - the solution was to fully qualify the object variables when declaring them- e.g.

Dim myrs as DAO.Recordset

or

Dim myrs as ADODB.Recordset

depending on which object model you're using.

Might be barking up the wrong tree though.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Expert Comment

by:marksynnott
ID: 6270059
The previos answeres are all correct. But the reason why you may be getting the MISMATCH error is due to the references you have in your project. In your project look under references and see what references you are using Microsft DAO Microsft ADO etc -

Slect the correct one - Theyre problem sveral different versions of each installed - I m not sure which one should match your Jet Engine.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6270122
This is an Access 2000 Database.. set a Projct Reference to:

Microsoft DAO 3.6 Library

And then change your Types to:

Dim db As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim RecSet As DAO.Recordset

0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6271019
Sure if you really want to do it properly then use ADO

Add a reference to ADO 2.1  in your project then


    Dim con As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim param As ADODB.Parameter
    Dim rs As ADODB.Recordset
   
   
    Set cnn = New ADODB.Connection
   
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\Program Files\Microsoft Office\" & _
        "Office\Samples\Northwind.mdb;"

    ' Create the parameterized command (Jet specific)
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn
   
    cmd.CommandText = "Query1"
    cmd.CommandType = adCmdStoredProc
   
    Set param = New ADODB.Parameter
    param = cmd.CreateParameter("argIn") ' Call it what you want
    param.Type = adVarWChar
    param.Size = Len("ALFKI") 'Your FName or SNmae
    param.Value = "ALFKI"
    cmd.Parameters.Append param
   
    Set rs = New ADODB.Recordset
   
    Set rs = cmd.Execute
   
    rs.Close
   
    Set rs = Nothing
   
    Set cmd = Nothing
   
    con.Close
   
    Set con = Nothing

ADO is faster an doesn't allow you to make as many mistakes

V.
0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6271032
In that last example I used the northwind database replace this with the path to your database.

Alternatively if you are uing Odbc replace

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" etc...

with

cnn.Open "Name of your DSN"

V.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7153022
Hi byte_me,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Save as PAQ -- No Refund.
    *** many correct answers, too few points to justify a split.

byte_me, Please DO NOT accept this comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit.  Explain why.
==========
DanRollins -- EE database cleanup volunteer
0
 

Accepted Solution

by:
ComTech earned 0 total points
ID: 7182458
This question will be placed in PAQ, and points refunded.

Regards,

ComTech
Community Support Administrator  
Experts-Exchange  
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…

757 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

21 Experts available now in Live!

Get 1:1 Help Now