Using Like operator in ms access query does not fill dataset or recordset

Question

0.00/5 (No votes)

See more:

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 vb.net and try to retrieve query value in dataset or recordset,it shows no value.Does any1 know whats the problem?

Posted 18-Nov-12 21:27pm

Rachna0309

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2012-11-18T21:43:00

Solution 1

That is because "*" is not a wildcard in LIKE clauses. Try "%" instead:

VB

"Select TNAME,TADDRESS from VILLAGEMST where TADDRESS like '%" & VILNAME & "%'"

Posted 18-Nov-12 21:43pm

OriginalGriff

Updated 18-Nov-12 21:54pm

v2

Comments

Rachna0309 19-Nov-12 3:45am

But in MS ACCESS * is used as a wildcard in LIKE clause

OriginalGriff 19-Nov-12 3:53am

:blush:
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()
'Loop
'rs.Close()

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

Colin Lamarre · Answer 2 · 2014-12-29T04:25:00

Solution 2

lol did you try the % instead? Had this problem too.

Posted 29-Dec-14 4:25am

Colin Lamarre

Comments

Maciej Los 29-Dec-14 11:19am

Wrong!
I'd suggest to remove this "answer" to avoid downvoting.
[EDIT]
Do not answer such old qustions.