Let me explain by way of example

You have a table called Staff with among others columns StaffName. If you
want to find all records where the staff name contain "Mc" then the
following statement applies


SELECT * FROM Staff  WHERE StaffName LIKE '%Mc%'
To construct this in code you would

        dim strCriteria as string
        dim strSQL as string

        strCriteria = txtName.txt
        strSQL = _
                "SELECT * FROM Staff " & _
                " WHERE StaffName LIKE '%" & strCriteria & "%'"

Please note the position and spaces where the sigle quotation mark is

if you would like to select all the records where the name is exactly like
"John" then the following statement applies

SELECT * FROM Staff WHERE StaffName = 'John'

To construct this in cod you would

        dim strCriteria as string
        dim strSQL as string

        strCriteria = txtName.txt
        strSQL = _
                "SELECT * FROM Staff " & _
                " WHERE StaffName = '" & strCriteria & "'"

I hope this works

Regards Kioko

-----Original Message-----
From: paul [mailto:pd_smith@b...]
Sent: Wednesday, February 27, 2002 10:35 PM
To: professional vb
Subject: [pro_vb] SQL in Visual Basic


Hi

Does anyone know the CORRECT method of passing a string to an SQL
statement in vb. For example, if I have a text box called txtName.text
and I want to search for customers by entering a name in this box, and
clicking on a Search button, what is the correct SQL statement for this.
For the purposes of this example let's assume the recordset is closed and
we want to open it using the SQL statement (i.e. don't bother filtering).

I'm concerned over the use of quotation/double quotation marks in the SQL
command. An explanation of why each quotation mark is used would be most
appreciated.

An example using both the LIKE and = operators would be helpful.

Further to this question, what is the correct method for passing numbers
to an SQL statement. I've always used statements such as,

Customers.Open(SELECT * FROM Customers WHERE [Customer No] = " &
txtCustomerNo.text & ")

...is this correct or is there a better method of doing this.

Thanks