Please Help.. I got nothing when using LIKE expression (1 Viewer)

Hi All, I'm trying to use LIKE expression on my VB.NET code.
I have tried that with Access Query and got the result. But when
I use VB.NET to do the query, the datatable return zero data.

here's what I do;

=====================================================
dim sSQL as string = "SELECT * FROM Table1 WHERE INF1 LIKE '*cool*'
dim mcon as oledbconnection = new oledbconnection(sconnectionstring)
dim dt as new datatable

try
mcon.open
dim s as new oledbdataadapter(sSQL, mcon)
result.fill(dt)
catch ex as exception
msgbox ex.message
end try

dim dTotal as long = dt.rows.count
dt.dispose
=====================================================

The dTotal variable will be zero, but when you test the SQL statement with Access, it result something.. why do this happen? anyone can explain? what should I do? help please...

Try % as a wildcard instead of *.

pbaldy said:

Try % as a wildcard instead of *.

Click to expand...

@pbaldy, thank you for your answer, I have use the %, ?, and * - but still it doesn't work.. any suggestion? could it be something wrong with my PC? should I reinstall the application?

To be honest, I'm not conversant in VB.NET, but I know ADO likes the % as a wildcard, which was why I jumped in (plus no one else had replied). To narrow down the problem, do you get results with "SELECT * FROM Table1"? That will tell you if it's a wildcard problem or if your methodology to get the records is the problem.

again, thank you for replying. and yes, I get result from every query, from the simple SELECT to the JOINED SELECT, which why I wonder, why only the LIKE expression didn't work at all.. is there any compatibility problem with .NET? I use VB.NET 2005 with Access 2007, but save the mdb file with 97/2003 format

dim sSQL as string = "SELECT * FROM Table1 WHERE INF1 LIKE '*cool*'

Click to expand...

Where's the close quote (")?

Do a MsgBox() on sSQL and see what it looks like. Then try that exact SQL in an Access query.

I'm experiencing the same problem with VB .NET 2008

objadapter.SelectCommand = New OleDbCommand
objadapter.SelectCommand.Connection = DataConnection.GetLRSConn
objadapter.SelectCommand.CommandText = _
"SELECT name FROM tblEmployee WHERE name LIKE '%" & _
nameString1 & "%' or name LIKE '" & nameString1 & "%'
objadapter.SelectCommand.CommandType = CommandType.Text

tblEmployee 'name' column values:

Kalaskie, David L.
Smith, John Q.
Doe, Jane M.
​

when nameString1 is "David", I get "Kalaskie, David L." returned, but if nameString1 is "Kalaskie" or even "skie" for example, I get nothing. ????

I also tried '*' as the wildcard. Plus, I tried '*namestring1*' in Access .mdb query designer itself and it works fine.

I'm kinda surprised you're not getting a syntax error. You didn't use a closing quote mark. Any particular reason you're checking the same exact condition twice in your where clause?

Oops!
George, Thank you for responding. The missing quote is just a typo on my part within the posting only. When I got nothing with:
SELECT name FROM tbl WHERE name LIKE '%kalaskie%'
I guessed that the leading % caused "Kalaskie, David L" to be missed because 'K' is the first letter of the string and that the leading % was looking for something BEFORE the 'K', so I added the:
or 'nameString1%' to the WHERE name LIKE '%nameString1%'
thinking that would help. It did not of course.

Next thing to worry about is the name of the "name" column. "name" is a reserved word in Access. You might try putting brackets ([]) around it.

Another thing is to build your query into a string and then output that string to the immediate window. Then see if that exact string works in the query builder.

Another thing: supposedly Access and SQL Server are case insensitive. I am always a sceptic. You might try putting the correct case in your criteria and see if it makes a difference (I know it's a stretch).

Also, these posts are confusing when you post something other than what you're actually working with. Try to stay consistent to keep us from chasing rabbits.

George, again, thank you. I'm afraid I'm guilty on not posting exactly what my code looks like, but I am using 'name' in the actual code, I just retyped the code, abbreviated in the post, thinking it would be simpler and clearer. I'll refrain from that in the future. I tried the brackets with no luck.

Also, I had previously assigned the SQL text to a string and displayed it in a message box to review and it checked out ok. Plus, I've been testing the case issue as well. In my first post when I mentioned when it DID work, "DaViD" was even found as I was experimenting, so you are correct, it's case insensitive.

I do appreciate your time on this, thank you. I've coded IBM brand SQL in COBOL for 19 years and worked in Access database apps for roughly 8 so I feel rather comfortable with the SQL side. I'm very new to .NET and have been purchasing books left and right, digging online and pestering co-workers but they are new to "VB anything" and the VB gurus that do work here I can bother only so much. Hence my attempt at this forum. Plus I like to find things on my own as much as possible because as you know, more is always learned along the way, even if I don't find the anwer I want right away. I digress . . . sorry.

If/when I find the answer to this, I will post it, especially since I feel it was originally asked in June by Izoel.