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..

please post your Query here

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

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

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.

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

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..

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

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

Maincon.execute(usql) is for update query.

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?

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.

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'

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.

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.

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

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.

Please mark this thread as solved.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.