Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: 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 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

1 solution

Rate this: bad
good
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 & "%'"
  Permalink  
v2
Comments
Rachna0309 at 19-Nov-12 3:45am
   
But in MS ACCESS * is used as a wildcard in LIKE clause
OriginalGriff at 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 at 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 at 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 at 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 at 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 at 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 at 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 at 19-Nov-12 4:41am
   
Select TNAME,TADDRESS from VILLAGEMST where TADDRESS like '*Taliara*' This is the query when I put breakpoint.
OriginalGriff at 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 at 19-Nov-12 4:50am
   
I fill dataset after this..but dataset does not show any record...
OriginalGriff at 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 at 19-Nov-12 5:05am
   
No not getting any records...I think problem is with like operator but dont know the exact solution..
OriginalGriff at 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 at 19-Nov-12 6:22am
   
still not getting anything...
OriginalGriff at 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 at 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 at 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 at 19-Nov-12 23:14pm
   
Removing whole where clause returns whole table which I dont need as result...
OriginalGriff at 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 at 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

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 9,225
1 OriginalGriff 5,395
2 Peter Leow 4,100
3 Maciej Los 3,540
4 Abhinav S 3,333


Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 19 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid