Using LIKE operator in ms access query doesnt retrieve records in recordset

[Rachna0309]

I am using Like operator in ms access query to get part of string.This query runs well in ms access query wizard but when I use this query to retrieve records in recordset,recordset is null.
Also I am using update query with like operator.Records are not updated and also no error message is shown.
Please help me to solve this..

[Pgmer]

please post your Query here

[Rachna0309]

"Select TNAME,TADDRESS from VILLAGEMST where (TADDRESS like '" & [VILNAME] & "')"
Here VILNAME is value retrieved from the query below:
sql = "Select VCODE,VNAME from VILLAGECODE "

[Pgmer]

Try this.
Select TNAME,TADDRESS from VILLAGEMST where TADDRESS like &'(' & '[' VILNAME & ']'&')'
I could see some problem in your Qery in concatinating the brackets

[Rachna0309]

Your query doesnt work as ms access uses * as wildcard..
And the query which I wrote retrieves record in ms access but when i use breakpoint and debug vb.net application,recordset doesnt show records.

[Pgmer]

Then post your complete code. I never worked with access DB. I work with MS SQL i thought syntax will b same..

[Rachna0309]

Private Sub btnClick_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClick.Click

    sql = "Select VCODE,VNAME from VILLAGECODE "
    rs.Open(sql, MainCon, 1, 3)
    Do While Not rs.EOF
        Code = rs.Fields(0).Value
        VILNAME = rs.Fields(1).Value

        Dim vrs As New ADODB.Recordset
        sql = "Select TNAME,TADDRESS from VILLAGEMST where (TADDRESS like '*" & [VILNAME] & "*')"
        vrs.Open(sql, MainCon, 1, 3)
        If Not vrs.EOF Then
            Dim usql As String = "Update VILLAGEMST set VCODE = " & Code & " where (TADDRESS like '*" & [VILNAME] & "*')"
            MainCon.Execute(usql)
        End If
        rs.MoveNext()
    Loop
    rs.Close()
    MsgBox("Records Successfully Inserted.")
End Sub

Here is the code..

[Pgmer]

VILNAME at line 5 does get any value when you execute in debug mode? and MainCon.Execute(usql)?

[Rachna0309]

VILNAME value I get from
sql = "Select VCODE,VNAME from VILLAGECODE "
(VNAME is the value for VILNAME)

Maincon.execute(usql) is for update query.

[Pgmer]

I hope VILNAME is variable name. And i am asking you what you get into this variable when below line executes VILNAME = rs.Fields(1).Value
You are assiging rs.Fields(1).Value to VILNAME correct? So you should get something in it correct?

[Rachna0309]

Yes I am getting value in VILNAME(It is a village name eg.Valsad)
Eg when I debug,I get rs.fields(1).value as 'Valsad'
Main problem I am facing is "LIKE" clause does'nt work with either "Select" or "UPDATE" query.

[Pgmer]

When you have only one field why you want to use like? just say = in Where condition like
Update VILLAGEMST set VCODE = " & Code & " where TADDRESS = 'VillageName'

[Rachna0309]

Here TADDRESS have values like "Kashi Street,Taliara","Devdha,Taliara" etc.
And VILNAME will have value only 'Taliara'.So I have to find word 'Taliara'in TADDRESS.
As a result I cant use = operator in this query.

[adam_k]

Inside Access the wildcard is *, but over ADODB you should use % as the wildcard.
Give it a try and let us know how it goes.

[Rachna0309]

If I use % as wildcard,It does'nt retrieve records in ms access query..

[Rachna0309]

Thanx Adam_k for solving my problem..I used % as wildcard in vb.net for passing parameter in query and it gave me desired result.

[adam_k]

Please mark this thread as solved.