[Resolved]SQL Statement error

[dawgfather]

Hi

Im trying to search the database for the first name. and I get an error " Too few parameters. Expected 2". Could someone help me out. Maybe its very simple and ive gone mad looking at the statement for 5 hrs. Thanks in advance.

sqlsearch = "SELECT Contact.FirstName, Contact.LastName From Contact WHERE (((Contact.FirstName) Like '" & frmMain.txtSearch & "*')) ORDER BY Contact.FirstName, Contact.LastName;"
 
Set db = OpenDatabase("P:\Contact.mdb")
Set rs = db.OpenRecordset(sqlsearch)

[szlamany]

Still too many "-quotes...

sqlsearch = "SELECT Contact.FirstName, Contact.LastName From Contact WHERE (((Contact.FirstName) Like '" & frmMain.txtSearch & "*')) ORDER BY Contact.FirstName, Contact.LastName;"

[dawgfather]

k I took out all the parentheses and put the text from the textbox to a string, but i still get the same error.

strtest = frmMain.txtSearch.Text
 
sqlsearch = "SELECT Contact.FirstName, Contact.LastName From Contact WHERE Contact.FirstName Like 'strtest*' ORDER BY Contact.FirstName, Contact.LastName;"
 
Set db = OpenDatabase("P:\TDContact.mdb")
Set rs = db.OpenRecordset(sqlsearch)

[szlamany]

You need to concatenate the string into the SQL string...

strtest = frmMain.txtSearch.Text

sqlsearch = "SELECT Contact.FirstName, Contact.LastName From Contact WHERE Contact.FirstName Like '" & strtest & "*' ORDER BY Contact.FirstName, Contact.LastName;"

Debug.Print sqlsearch ' Will print the string so you can see it!

Set db = OpenDatabase("P:\TDContact.mdb")
Set rs = db.OpenRecordset(sqlsearch)

[kfcSmitty]

sqlsearch = "SELECT Contact.FirstName, Contact.LastName From Contact WHERE Contact.FirstName=" + Like strtest & "*" + "ORDER BY Contact.FirstName, Contact.LastName;"

That work?

I've nver used like in an SQL query so I am guessing..again

[szlamany]

kfcSmitty - LIKE is an operator just like =

The difference is that it takes a wildcard.

SELECT * FROM SOMETABLE WHERE NAMECOLUMN LIKE 'SMITH*';

This will return all the rows with SMITH as the starting characters in a column called NAMECOLUMN.

ACCESS uses * for the wildcard.

MS SQL Server uses % for the wildcard.

In MS SQL Server you would say:

SELECT * FROM SOMETABLE WHERE NAMECOLUMN LIKE '%JOHN%'

That would return all the rows with JOHN anywhere in the NAMECOLUMN.

[kfcSmitty]

ahh, i knew about wildcards, but I assumed the select statement needed the "=" and then the Like for the comparison.

Thanks for clearing that up

[dawgfather]

Here, maybe the problems right there, and i can't see it.

Private Sub Form_Load()
    Dim strSearchBy As String
    Dim strtest As String
    Dim db As Database
    Dim rs As Recordset
       
    strtest = frmMain.txtSearch.Text
    strSearchBy = frmMain.strOption
    Select Case strSearchBy
        Case "First Name"
                     sqlsearch = "SELECT Contact.FirstName, Contact.LastName From Contact WHERE Contact.FirstName Like '" & strtest & "*' ORDER BY Contact.FirstName, Contact.LastName;"

        Case "Last Name"
            sqlsearch = "SELECT Contact.FirstName, Contact.LastName From Contact WHERE (((Contact.LastName) Like '" & frmMain.txtSearch & "*')) ORDER BY Contact.FirstName, Contact.LastName;"
     End Select
    Debug.Print sqlsearch
    Set db = OpenDatabase("P:\Contact.mdb")
    Set rs = db.OpenRecordset(sqlsearch)
End Sub

[kfcSmitty]

dawg, is strtest a variable? if so, youre treating it like a string. szlamany's example above shows how to use it as a variable

[dawgfather]

lol...sorry about that. the las tmesage had a typo. But I still get the error.

Private Sub Form_Load()
    Dim strSearchBy As String
    Dim strtest As String
    Dim db As Database
    Dim rs As Recordset
       
    strtest = frmMain.txtSearch.Text
    strSearchBy = frmMain.strOption
    Select Case strSearchBy
        Case "First Name"
            sqlsearch = "SELECT Contact.FirstName, Contact.LastName From Contact WHERE Contact.FirstName Like '" & strtest & "*' ORDER BY Contact.FirstName, Contact.LastName;"

        Case "Last Name"
            sqlsearch = "SELECT Contact.FirstName, Contact.LastName From Contact WHERE Contact.LastName Like '"  & strtest & "*' ORDER BY Contact.FirstName, Contact.LastName;"
     End Select
    Debug.Print sqlsearch
    Set db = OpenDatabase("P:\Contact.mdb")
    Set rs = db.OpenRecordset(sqlsearch)
End Sub

[szlamany]

Where exactly is the error occuring?

It seems to me that the SQL statement is working.

You can test that by filling sqlSearch with "SELECT 'TEST','HELLO';" and seeing if that query runs.

[dawgfather]

the error occurs here.

 Set rs = db.OpenRecordset(sqlsearch)

[szlamany]

We don't use .OPENRECORDSET ...

But the help/examples I get in VB show this...

Set rstTemp = dbsNorthwind.OpenRecordset( _
"SELECT * FROM Employees", dbOpenDynaset, dbReadOnly)

Are you missing some parameters from the call??

[dawgfather]

The call statement looks good. I have attached the database file,if that helps.

[Ecniv]

Im trying to search the database for the first name. and I get an error " Too few parameters. Expected 2". Could someone help me out. Maybe its very simple and ive gone mad looking at the statement for 5 hrs. Thanks in advance.

sqlsearch = "SELECT Contact.FirstName, Contact.LastName From Contact WHERE (((Contact.FirstName) Like '" & frmMain.txtSearch & "*')) ORDER BY Contact.FirstName, Contact.LastName;"

Set db = OpenDatabase("P:\Contact.mdb")
Set rs = db.OpenRecordset(sqlsearch)

The error message you got means that either you are not sending two parameters (fields or filters) that are required, or you have the wrong fieldnames.

I find it weird that it is saying two parameters yet you are only passing one external parameter (the txt box or string as you have it now). I would have said it was the Order By part that was erroring, but they are the same fields as you used in the select fields bit. Are the names correct?

In the last post you are debugging the sql statement. What happens when you copy that from the immediates window and put it into a query in Access (northwind says you are trying the tutorials with access? - guessing).



Szlamany:
.OpenRecordset is DAO... probably why you do not use it!

[dawgfather]

Hey I really appreciate all the help. I finally found the problem, I was pointing to a different database..son#$#@#.
But now I get a "Type mismatch error" on "Set rs = db.OpenRecordset(sqlsearch)".
Someone suggested I run the query in access. Im not too good with Access. Is there a way to copy and paste the SQL Query?

I have added my project with this message.

[Ecniv]

Sorry, I assumed as you were using DAO and northwind that you were using access.

If you are using Sql, there is a query builder/runner in that. You should be able to paste the complete sql statement into it and see if it gives you a more useful error.

Type mismatch is usually where you are trying to filter on a number but you use text. Or visa versa. Infact you are using Access

Set db = OpenDatabase("P:\Contact.mdb")

Open access, open that mdb.
On the main database window, change the tab to Queries
Create a new query (do not use the wizard)
On the choose tables pop up, click close (with no tables)
On the top left on the toolbar, there is a drop down that should say SQL, or go via the view menu and change to Sql view.
Paste your sql statement in.
Press the Red Exclamation mark (toolbar middle) to run.
It should error but may highlight or should you where it doesn't like it.

[dawgfather]

Ok. I tested the SQL Statement and it works fine. I get the field.

Im still getting the same damn "type mismatch error" ...lol

[dawgfather]

yeeeeeeeehaaawwwwwwwwwww!!!
It works, im gonna dance and cry at the same time.

Apparently I have to reference DAO berfore the ActiveX object.
http://support.microsoft.com/?kbid=181542