SQL Breaks ASP

[JP SIngh]

In one of our applications we have product id field which sometime contains
'

When doing a search the sql breaks due to this. I cannot use replace
function to remove this is there anything else I can do so that this works

strProductId = 2277'778

sqlstr = " Select * from productdetails where id ='" & strProductId & "'"

In this instance the sql breaks and gives me an error. Any suggestions how i
can solve this

Regards

 

[Martin Walke]

Hi JP,

The normal way would be to replace the single quote with 2 of them, but you
say you can't do that? Why?

Martin

 

[Bob Barrows [MVP]]

In one of our applications we have product id field which sometime
contains '

When doing a search the sql breaks due to this. I cannot use replace
function to remove this is there anything else I can do so that this
works

strProductId = 2277'778

sqlstr = " Select * from productdetails where id ='" & strProductId &
"'"

In this instance the sql breaks and gives me an error. Any
suggestions how i can solve this

Regards

The easiest thing to do would be to use a saved parameter query if you are
using Access (look for posts by me containing those keywords), or a stored
procedure if you are using SQL Server.

Why can't you use Replace? It would look like this:

sqlstr = " Select * from productdetails where id ='" & _
Replace(strProductId,"'","''") & "'"

Bob Barrows

 

[Cowboy \(Gregory A. Beamer\) [MVP]]

You are going to have to replace ' with ''. When sent to the database, this
will be seen as '.

The only other option is to create a stored procedure and send the string in
as a parameter.

If both options are impossible, you are stuck.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************

 

[Phill. W]

In one of our applications we have product id field which sometime
contains '

When doing a search the sql breaks due to this.

Your solution is to double-up the single quotes as you build the SQL
string, as in

strProductId = "2277'778"
strProductId = Replace( strProductId, "'", "''" )

I cannot use replace function to remove this

You don't need to "remove" it, just make it safe to put into your SQL,
as above.

HTH,
Phill W.

 

[JP SIngh]

thank you all

The replacement solution works fine