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
"JP SIngh" <no**@none.com> wrote in message
news:eZ**************@TK2MSFTNGP09.phx.gbl...

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

[Bob Barrows [MVP]]

JP SIngh wrote:

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
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[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!
************************************************
"JP SIngh" <no**@none.com> wrote in message
news:eZ**************@TK2MSFTNGP09.phx.gbl...

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

[Phill. W]

"JP SIngh" <no**@none.com> wrote in message
news:eZ**************@TK2MSFTNGP09.phx.gbl...

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
"Phill. W" <P.A.Ward@o-p-e-n-.-a-c-.-u-k> wrote in message
news:cf**********@yarrow.open.ac.uk...

"JP SIngh" <no**@none.com> wrote in message
news:eZ**************@TK2MSFTNGP09.phx.gbl...

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.