...

View Full Version : MSSQL - problem with escape characters



mad_girl
08-27-2007, 07:55 AM
the problem is i wanna make this piece of code work and i don know what the problem is , i think it's with the combination of charachters ' and "
Code:

set @query=@query+ "cast(ID as varchar) like'%'+ltrim(@id)+'%' "
set @query=@query+ @option+" userName like'%'+ltrim(@name)+'%' "
set @query=@query+ @option+" cast(referrer as varchar) like'%'+ltrim(@ref)+'%' "
set @query=@query+ @option+" age=cast(@age as int) "
exec(@query)

Roelf
08-27-2007, 08:13 AM
debugging is the easiest if you display the complete query before you execute it.

Can you show us the query which you are sending to the database?

guelphdad
08-27-2007, 02:02 PM
Please specify in your thread title/post which database you are using when posting in this section. there are different syntaxes for different databases.

mad_girl
08-27-2007, 09:36 PM
I'm using SQL database sorry for not saying that ,thought it's the default..
Roelf :
here's the code of my procedure if that what u asked for


alter proc findUsers
@id varchar(10),@name varchar(15),@ref varchar(10),@age varchar(2),@option varchar(3)
as
declare @query char(80)
set @query='select * from users where '

set @query=@query+ "cast(ID as varchar) like '%'+ltrim(@id)+'%' "
set @query=@query+ @option+" userName like '%'+ltrim(@name)+'%' "
set @query=@query+ @option+" cast(referrer as varchar) like '%'+ltrim(@ref)+'%' "
set @query=@query+ @option+" age=cast(@age as int)"
exec(@query)
go

guelphdad
08-28-2007, 02:21 PM
I'm using SQL database sorry for not saying that ,thought it's the default..

no, no default here. our main forum is for mysql questions. this one is a generic one for other database questions as we don't get that many. thanks for clarifying.

Roelf
08-29-2007, 07:17 AM
alter proc findUsers
@id varchar(10),@name varchar(15),@ref varchar(10),@age varchar(2),@option varchar(3)
as
declare @query char(80)
set @query='select * from users where '

set @query=@query+ 'cast(ID as varchar) like ''%'+ltrim(@id)+'%'' '
set @query=@query+ @option+' userName like ''%'+ltrim(@name)+'%'' '
set @query=@query+ @option+' cast(referrer as varchar) like ''%'+ltrim(@ref)+'%'' '
set @query=@query+ @option+' age=cast(@age as int)'
exec(@query)
go
when you are building a string which eventually will become a query with text delimiters (quotes), you have to double the quotes when building the string. So the double quotes are in fact two single quotes!

Personally, i dont like building a string and execute that, but that is a matter of taste i think. What is that option parameter doing so many times in the query?

Can you print out the query before you execute it?

print(query)
exec(query)

And post what it looks like?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum