Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Aug 2007
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MSSQL - problem with escape characters

    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)
    Last edited by guelphdad; 08-27-2007 at 01:02 PM. Reason: provide clearer thread title

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    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?
    I am the luckiest man in the world

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Please specify in your thread title/post which database you are using when posting in this section. there are different syntaxes for different databases.

  • #4
    New Coder
    Join Date
    Aug 2007
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Code:
    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
    Last edited by mad_girl; 08-27-2007 at 08:49 PM.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by mad_girl View Post
    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.

  • #6
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    Code:
    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?
    I am the luckiest man in the world


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •