SQL LIKE statements

[wietmie]

Hai, i'm new here, i would like to ask about SQL statements, i'm using VB6.0 and my systems if more on searching record, so the SQL statements is very important to search a record, so back to the question, i do this to search the "company name"

Private Sub cmdNameView_Click()
Dim strSQL As String
inputName = UCase(txtName.Text)
    Set rs = New ADODB.Recordset
    If inputName <> "" Then
        strSQL = "SELECT * FROM SUPPLIER WHERE sName LIKE '*" & inputName & "*'"
        MsgBox strSQL
        rs.Open strSQL, conn, adOpenKeyset, adLockPessimistic, adCmdText
        fillfields
    End If

End Sub

And here the method of fillfields()

Public Sub fillfields()
    
                NotEditable
                frameSDetail.Visible = True
                If Not (rs.BOF = True Or rs.EOF = True) Then
                    txtsName.Text = rs.Fields("sName")
                    txtsAddress.Text = rs!sAddress
                    txtPost_Code.Text = rs!Post_Code
                    txtCity.Text = rs!City
                    txtPhone.Text = rs!Phone
                    txtEmail.Text = rs!Email
                    txtFax.Text = rs!Fax
                    txtService.Text = rs!Service
                Else
                    MsgBox "No Record Match", vbExclamation, "Cannot Move"
                End If
                Exit Sub
            
End Sub

I got the msg "No Record Match" when i try the LIKE statements in the Microsoft Office access same as my code above, it return correct output. then i thought it is because of the logic error in fillfields method, then i try change the LIKE statements to

SELECT * FROM SUPPLIER WHERE sName = '" & inputName & "'

it return a correct output. For your information sName field cannot be the same and i already create a code to validate it. Can anyone can help me to trace the logic error on it?

thanks in advance

[jeffstl]

strSQL = "SELECT * FROM SUPPLIER WHERE sName LIKE '% & inputName & %'"

SQL syntax for LIKE uses % in most cases. Not *

[edwardrsmith]

I have always used astrik's but I don't think that is the problem. I would change adLockPessimist ic to adLockOptimisti c.

Private Sub cmdNameView_Click()
Dim strSQL As String
inputName = UCase(txtName.Text)
    Set rs = New ADODB.Recordset
    If inputName <> "" Then
        strSQL = "SELECT * FROM SUPPLIER WHERE sName LIKE '*" & inputName & "*'"
        MsgBox strSQL
        rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic, adCmdText
        fillfields
    End If
 
End Sub

Let me know if that doesn't work.

Edward

[jeffstl]

I have always used astrik's but I don't think that is the problem. I would change adLockPessimist ic to adLockOptimisti c.

Edward

* Isn't a universal syntax for SQL like.

% as far as I know..is more universal across multiple database platforms.

Why not to use * in SQL Like

I also do not think the problem is the locking method of opening the recordset.

That only relates to the contraints on the records of your database in terms of updating and inserting.

It has nothing to do with whether or not records will be returned in a SELECT.

[wietmie]

* Isn't a universal syntax for SQL like.

% as far as I know..is more universal across multiple database platforms.

Why not to use * in SQL Like

I also do not think the problem is the locking method of opening the recordset.

That only relates to the contraints on the records of your database in terms of updating and inserting.

It has nothing to do with whether or not records will be returned in a SELECT.

Yes!! It works!, thank u everyone, my problem solved, must use the "%" symbol to make it works

 strSQL = "SELECT * FROM SUPPLIER WHERE sName LIKE '%" & inputName & "%'"

[wietmie]

I think my problem solve, today, my Access 2003 and VB6.0 act weird. Here what i face today:

strSQL = "SELECT * FROM SUPPLIER WHERE sName LIKE '%" & inputByName & "%'"

i enter word "AM" and my VB return correct result.

strSQL = "SELECT * FROM SUPPLIER WHERE City LIKE '%" & inputByName & "%'"

i enter word "W" my program say Cannot find Record

i feel weird, then i try in Microsoft office 2003

SELECT * FROM SUPPLIER WHERE sName LIKE '%AM%'

Microsoft office return no record found

then i try in Microsoft office 2003

SELECT * FROM SUPPLIER WHERE sName LIKE '*AM*'

Microsoft office return correct result

then i try in VB6.0

SELECT * FROM SUPPLIER WHERE sName LIKE '*AM*'

VB6.0 return cannot find record

Can anyone explain to me what happen

[QVeen72]

Hi,

For VB6 + ADO + Access Combination, you need to query with %:

SELECT * FROM SUPPLIER WHERE sName LIKE '%AM%'

Regards
Veena