Click here to Skip to main content
12,697,536 members (27,929 online)
Rate this:
Please Sign up or sign in to vote.
See more: VB VB.NET
I am writing ms access query using 'like' operator
"Select TNAME,TADDRESS from VILLAGEMST where TADDRESS like '*" & VILNAME & "*'"

When I run this query in ms access query wizard,it returns value from table.But when I run the application in and try to retrieve query value in dataset or recordset,it shows no value.Does any1 know whats the problem?
Posted 18-Nov-12 22:27pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

That is because "*" is not a wildcard in LIKE clauses. Try "%" instead:
"Select TNAME,TADDRESS from VILLAGEMST where TADDRESS like '%" & VILNAME & "%'"
Rachna0309 19-Nov-12 3:45am
But in MS ACCESS * is used as a wildcard in LIKE clause
OriginalGriff 19-Nov-12 3:53am
Sorry - I didn't notice the Access bit and assumed SQL. You are right, "*" is an ACCESS wildcard.
So...what does the debugger say is the content of VILNAME?
Rachna0309 19-Nov-12 3:56am
VILNAME is the value I get from another sql query which I pass as parameter to the above query.
OriginalGriff 19-Nov-12 4:00am
Yes - but what string does it contain? You need to know because it is the relevant info that is being used for the query - if the previous request is returning empty or wrong data then the problem will show up here. The first thing to do is find out exactly what you are presenting Access with! :laugh:
Rachna0309 19-Nov-12 4:03am
VILNAME contains village name.It returns Correct data.The only problem is when using like operator,records are not fetched in recordset,but runs well in query.
OriginalGriff 19-Nov-12 4:18am
What data? Did you look at the value at the point at which the SELECT statement was constructed? What, exactly, was the SELECT Statement string once you had built it and before it was passed to Access?
Rachna0309 19-Nov-12 4:24am
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 & "*'"
' 'sql = "Select TNAME,TADDRESS from VILLAGEMST where VNAME = '" & VILNAME & "'"
' vrs.Open(sql, MainCon, 1, 3)
' If Not vrs.EOF Then
' Dim usql As String = "Update VILLAGEMST set VCODE = " & Code & " where VNAME = '" & VILNAME & "'"
' MainCon.Execute(usql)
' Else
' Dim EN As String = vrs.Fields(0).Value
' End If
' rs.MoveNext()

Here is the whole code..
from first query I get VILNAME(i.e VNAME in query)
Eg.1st value in table is Taliara(this is VNAME)
Now this VILNAME is passed to 2nd query.
OriginalGriff 19-Nov-12 4:35am
Which doesn't guarantee that VILNAME exists in the TADDRESS field of the different table - have you checked? And also checked that VILNAME does not contain spaces, punctuation, etc., etc.?

You query looks correct - so it has to be a data or wrong-field type error.
If you put a breakpoint on the "sql = " line, what exact string does it generate?
Rachna0309 19-Nov-12 4:41am
Select TNAME,TADDRESS from VILLAGEMST where TADDRESS like '*Taliara*'
This is the query when I put breakpoint.
OriginalGriff 19-Nov-12 4:49am
Which means it's data related in some way. What happens with your code from there? I.e. what happens when you single step through?
Rachna0309 19-Nov-12 4:50am
I fill dataset after this..but dataset does not show any record...
OriginalGriff 19-Nov-12 5:00am
So, use the debugger to change the string: remove most of the word "Taliara" and leave it as

where TADDRESS like '*T*'

Do you get any records then?
Rachna0309 19-Nov-12 5:05am
No not getting any records...I think problem is with like operator but dont know the exact solution..
OriginalGriff 19-Nov-12 5:14am
Try the debugger again - remove the whole WHILE clause and see what record data you get back.
(Probably a lot - it should be your whole table)
Rachna0309 19-Nov-12 6:22am
still not getting anything...
OriginalGriff 19-Nov-12 6:26am
If you aren't getting anything without the WHILE clause in there, then your table would appear to be empty. :laugh:
Have you checked that you are putting the data where you expected to?
Rachna0309 19-Nov-12 6:30am
Yes I have checked..Is there any other way to fetch part of string other than using like operator?
OriginalGriff 19-Nov-12 7:14am
Hang on - if you removed the whole WHERE clause, then that includes the LIKE part. If that returns no data, it isn't the LIKE that is causing the problem. So you need to look at the data source, not the LIKE - or have we misunderstood each other?
(Sorry if this seems to be taking a while, but I can't access your machine, so I have to try to get the info I need via email, which is painfully slow... :laugh:)
Rachna0309 19-Nov-12 23:14pm
Removing whole where clause returns whole table which I dont need as result...
OriginalGriff 20-Nov-12 2:38am
That's the idea - it proves that some data is being fetched, so it isolates the cause to the LIKE condition - which means that there is no data in the table which has the characters "Taliara" in the TAddress Column.

Which either means you are looking at the wrong table, or the wrong column of the table.
Rachna0309 20-Nov-12 2:42am
Can u send me your email Id so that I can send you the db and code...there are 2 records in table which has characters 'Taliara'.Even though records are not getting fetched in recordset
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

lol did you try the % instead? Had this problem too.
Maciej Los 29-Dec-14 11:19am
I'd suggest to remove this "answer" to avoid downvoting.
Do not answer such old qustions.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170118.1 | Last Updated 29 Dec 2014
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100