Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Solved
database query problem
Posted on 2001-07-10
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
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\ti
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("qryStaf
Set RecSet = db.OpenRecordset(SQLString
RecCount = RecSet.RecordCount
stafftesterresult.Show vbModeless, frmSplash
End Sub
thank you,
Byte_me.
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
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\ti
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("qryStaf
Set RecSet = db.OpenRecordset(SQLString
RecCount = RecSet.RecordCount
stafftesterresult.Show vbModeless, frmSplash
End Sub
thank you,
Byte_me.
4
- +5
11 Comments
Once you have created the QueryDef object reference, you can use that Object to open the recordset:
Set RecSet = qdfTemp.OpenRecordset(dbOp
Arthur Wood
Set RecSet = qdfTemp.OpenRecordset(dbOp
Arthur Wood
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
RecCount = RecSet.RecordCount
V.
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
RecCount = RecSet.RecordCount
V.
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.
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.
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.
V.
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.
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.
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.
Slect the correct one - Theyre problem sveral different versions of each installed - I m not sure which one should match your Jet Engine.
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
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
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.OL
"Data Source=c:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.
' 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
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.
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.OL
"Data Source=c:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.
' 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
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.
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.OL
with
cnn.Open "Name of your DSN"
V.
Alternatively if you are uing Odbc replace
cnn.Open "Provider=Microsoft.Jet.OL
with
cnn.Open "Name of your DSN"
V.
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
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
Featured Post
Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
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…
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 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 Month7 days, 11 hours left to enroll
584 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.