Saving query text to SQL table 1

[meldrape]

Greetings, I've created a report builder app, SQL 2000/ASP.
One of the features is that the user can save the query they build. The text of the query is saved to a separate SQL table. The problem I'm having is saving this text. I've done everything I know to handle the apostrophes. Here's an example:

SQL="select * from equipment where assetid >''"

Here's my code to save it:

if request.form("query")>"" then
queryname=request.form("queryname")
queryvalue=request.form("queryvalue")
objConn.execute("insert into rptQueries (queryname,queryvalue) values('" & queryname & "','" & queryvalue & "')")
end if

If I response.write the queryvalue, it looks file. But it saves it like this:

select * from equipment where assetid='

so when it runs again, there's an error. Any ideas would be greatly appreciated. Thanks.

 

[TheConeHead]

before you insert it run a replace to change it to:

SQL="select * from equipment where assetid >''''"

or just:

SQL="select * from equipment where assetid >'''"

try each on and see what works...

 

[Lothario]

Try this:

if request.form("query")>"" then
queryname=Replace(request.form("queryname"),"'","''")
queryvalue=Replace(request.form("queryvalue"),"'","''")
objConn.execute("insert into rptQueries (queryname,queryvalue) values('" & queryname & "','" & queryvalue & "')")
end if

-L

 

[meldrape]

Thanks Cone and Lothario. I actually tried this as well, but I'll do it again just to make sure and let you know how it turns out. Thanks again for your reply.

 

[meldrape]

Ok, this one did it:
SQL="select * from equipment where assetid >''''"

I did this:
queryvalue=replace(SQL,"'","''")

what I tried to do was this:
queryvalue=replace(SQL,"'",""'"") and that wasn't cutting it. Many thanks for your help.