ASP.NET

ASP.NET RSS

Syntax error in "like" in sql statement

Please support our ASP.NET advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Jun 2006

Posts: 7

Reputation: danae is an unknown quantity at this point

Solved Threads: 0

danae

Offline

Newbie Poster

Syntax error in "like" in sql statement

Oct 7th, 2006

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

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
mstrSql = "Select * from Staff where staffID like '" & txtStaffID.Text.Trim() & "%" & "'"
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

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
mstrSql = "Select * from Staff where staffID like '%" & txtStaffID.Text.Trim() & "%'"
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.

Last edited by tgreer; Oct 7th, 2006 at 10:37 am.

•

Join Date: Nov 2005

Posts: 95

Reputation: ManicCW is an unknown quantity at this point

Solved Threads: 11

ManicCW

Offline

Junior Poster in Training

Re: Syntax error in "like" in sql statement

Oct 7th, 2006

What is the type of your StaffId column?

http://www.manicsite.com

•

Join Date: Jun 2006

Posts: 7

Reputation: danae is an unknown quantity at this point

Solved Threads: 0

danae

Offline

Newbie Poster

Re: Syntax error in "like" in sql statement

Oct 8th, 2006

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.

•

Join Date: Sep 2006

Posts: 90

Reputation: Inanna is on a distinguished road

Solved Threads: 6

Inanna

Offline

Junior Poster in Training

Re: Syntax error in "like" in sql statement

Oct 8th, 2006

•

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?

•

Join Date: Jun 2006

Posts: 7

Reputation: danae is an unknown quantity at this point

Solved Threads: 0

danae

Offline

Newbie Poster

Re: Syntax error in "like" in sql statement

Oct 9th, 2006

•

Originally Posted by Inanna

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

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
mstrSql = mstrSql & " where staffID like '%" & txtStaffID.Text.Trim() & "%' "
mstrSql = mstrSql & " where staffID like '%" & txtStaffID.Text.Trim() & "%' "

where

Help with Code Tags

ASP.NET Syntax (Toggle Plain Text)

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

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
mstrSql = "Select * from Staff where staffID like '%" & txtStaffID.Text.Trim() & "%' "
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.

•

Join Date: Feb 2003

Posts: 793

Reputation: Paladine has a spectacular aura about

Solved Threads: 26

Paladine

Offline

Master Poster

Re: Syntax error in "like" in sql statement

Oct 10th, 2006

•

Originally Posted by danae

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
Help with Code Tags

ASP.NET Syntax (Toggle Plain Text)
mstrSql = mstrSql & " where staffID like '%" & txtStaffID.Text.Trim() & "%' "
mstrSql = mstrSql & " where staffID like '%" & txtStaffID.Text.Trim() & "%' "
where
Help with Code Tags

ASP.NET Syntax (Toggle Plain Text)
mstrSql = "Select * from Staff"
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
Help with Code Tags

ASP.NET Syntax (Toggle Plain Text)
mstrSql = "Select * from Staff where staffID like '%" & txtStaffID.Text.Trim() & "%' "
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.

Assistant Manager, Pharmacy Informatics
Wordpress Learning Blog
Updated : ASP.Net Login Code