Syntax error in "like" in sql statement

[danae]

Hi,

I've got a problem with searching for record(s) that match the staffID in my "Staff" table in the database. I have a string named mstrSql, a txtStaffID text box and a sql statement that goes

mstrSql = "Select * from Staff where staffID like '" & txtStaffID.Text.Trim() & "%" & "'"

I ran the code and it returned an error: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'like'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)

I've also tried

mstrSql = "Select * from Staff where staffID like '%" & txtStaffID.Text.Trim() & "%'"

That didn't work either. Can someone please provide me a correct syntax to run the above? Thanks.

[ManicCW]

What is the type of your StaffId column?

[danae]

Hi ManicCW,

Thanks, but I have solved the problem eventually. The type of my staffID column is varchar with length of 4. The second way which i have posted turned out to be correct, it couldn't run correctly in the first place was because of some mistake in the subsequent code. And, I have tried to replace the '%' sign with '*', thinking it might work. However, it didn't and the '%' sign is correct.

[Inanna]

it couldn't run correctly in the first place was because of some mistake in the subsequent code.

What mistake? Is the query string what you thought it was? What happens if you print the query instead of run it and then paste it into SQL to run manually?

[danae]

What mistake? Is the query string what you thought it was? What happens if you print the query instead of run it and then paste it into SQL to run manually?

Uh, actually it is hard for me to describe my mistake without showing you the entire thing. I actually need to concatenate the sql with another sql statement, like this one

mstrSql = mstrSql & " where staffID like '%" & txtStaffID.Text.Trim() & "%' "

where

mstrSql = "Select * from Staff"

And, because I forgot to place a space before the "where" word, so it'd become "Select * from StaffWhere". Some careless mistake. Anyway, this

mstrSql = "Select * from Staff where staffID like '%" & txtStaffID.Text.Trim() & "%' "

can run. I mean, there's no mistake in the syntax near the "like". Hope I did answer your question. Of course, if you paste the sql into sql query, it would have to be without the '&' and txtStaffID value.

[Paladine]

Uh, actually it is hard for me to describe my mistake without showing you the entire thing. I actually need to concatenate the sql with another sql statement, like this one

mstrSql = mstrSql & " where staffID like '%" & txtStaffID.Text.Trim() & "%' "

where

mstrSql = "Select * from Staff"

And, because I forgot to place a space before the "where" word, so it'd become "Select * from StaffWhere". Some careless mistake. Anyway, this

mstrSql = "Select * from Staff where staffID like '%" & txtStaffID.Text.Trim() & "%' "

can run. I mean, there's no mistake in the syntax near the "like". Hope I did answer your question. Of course, if you paste the sql into sql query, it would have to be without the '&' and txtStaffID value.

This is correct. But as an aside, it is not good practice to use the LIKE clause for something like a staffID, which should be unique or a primary key.

But that depends on the application and its design.