does SQL have an escape character

In a asp I made using the Paramater object a user can submit a form and =
the data will be inserted in the database. But if the user submits a =
name in the form O'Reilly then it will error. So I made a VBScript =
function which parses the input data and if ' exists in the name I add =
an extra ' to the ' in the form data and the user can insert the data. =
Trouble is when I return the recordset it looks like this O''Reilly. So =
I am wondering if there exists an escape chatacter to avoid this issue? =
Thanks.
--=20
George Hester
__________________________________It seems like you both replace every quote with two quotes and then use a pa
rameter object which
does exactly the same. This is why you get double. You should only need to u
se the parameter object.
If you post the code (either here or on ASP group) you might get some help..
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message
news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
In a asp I made using the Paramater object a user can submit a form and the
data will be inserted in
the database. But if the user submits a name in the form O'Reilly then it w
ill error. So I made a
VBScript function which parses the input data and if ' exists in the name I
add an extra ' to the '
in the form data and the user can insert the data. Trouble is when I return
the recordset it looks
like this O''Reilly. So I am wondering if there exists an escape chatacter
to avoid this issue?
Thanks.
George Hester
__________________________________|||Here is a snippet:
<%
strCompanyName =3D EncodeString(Request.Form("txtCompanyName"))
If strCompanyName <> "" Then
If Len(strCompanyName) =3D 0 Then
'
blnErrorFound =3D True
End If
If Not blnErrorFound Then
Set cmd =3D Server.CreateObject("ADODB.Command")
Set pReturnCode =3D Server.CreateObject("ADODB.Parameter")
Set pCompanyNameDesc =3D Server.CreateObject("ADODB.Parameter")
With cmd
.CommandType =3D adCmdStoredProc
.CommandText =3D "AddSuppliers"
Set .ActiveConnection =3D cn
Set pCompanyNameDesc =3D .CreateParameter("CompanyNameDesc")
With pCompanyNameDesc
.Type =3D adVarWChar
.Size =3D 40
.Direction =3D adParamInput
.Value =3D strCompanyName
End With
.Parameters.Append pCompanyNameDesc
End With
With cn
.BeginTrans
With cmd
.Execute lRecs,, adCmdStoredProc + adExecuteNoRecords
End With
If Err.Number <> 0 Then
.RollbackTrans
Else
.CommitTrans
With cmd
If .Parameters("@.RETURN_CODE").Value =3D 0 Then
strMsg =3D "Company Name"
Response.Write strMsg
sSQL =3D "SELECT * FROM Suppliers ORDER BY SupplierID DESC"
Set rs =3D Server.CreateObject("ADODB.Recordset")
With rs
.CursorType =3D adOpenForwardOnly
.LockType =3D adLockReadOnly
.Open sSQL, cn
Response.Write(.Fields("CompanyName").Value)
End If
End With
End If
End With
End If
End If
%>
I send to strCompanyName the value in a Text Box in a form. The =
EncodeString just replaces occurances of '
with '' so that the rs.Open sSQL, cn does not fail if the user entered =
say O'Reilly. But the recordset returns
O''Reilly. Yes I can Replace() that out easy enough. I was hoping in =
the sSQL string there was an escape in
SQL so that I didn't have to use EncodeString().
--=20
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote =
in message news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then =
use a parameter object which
> does exactly the same. This is why you get double. You should only =
need to use the parameter object.
> If you post the code (either here or on ASP group) you might get some =
help...
>=20
> --=20
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form =
and the data will be inserted in
> the database. But if the user submits a name in the form O'Reilly =
then it will error. So I made a
> VBScript function which parses the input data and if ' exists in the =
name I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I =
return the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape =
chatacter to avoid this issue?
> Thanks.
>=20
> --=20
> George Hester
> __________________________________
>=20
>|||I'm sorry. I meant the cmd.Execute will error if I don't replace the ' =
in O'Reilly with O''Reilly inserting in the database. I believe.
--=20
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote =
in message news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then =
use a parameter object which
> does exactly the same. This is why you get double. You should only =
need to use the parameter object.
> If you post the code (either here or on ASP group) you might get some =
help...
>=20
> --=20
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>=20
>=20
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form =
and the data will be inserted in
> the database. But if the user submits a name in the form O'Reilly =
then it will error. So I made a
> VBScript function which parses the input data and if ' exists in the =
name I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I =
return the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape =
chatacter to avoid this issue?
> Thanks.
>=20
> --=20
> George Hester
> __________________________________
>=20
>|||The ADO command and parameter handling should replace each ' with '' for you
. I suggest you run a profiler
trace and see what happens.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Hester" <hesterloli@.hotmail.com> wrote in message news:ed2G5u8XEHA.1
152@.TK2MSFTNGP09.phx.gbl...
I'm sorry. I meant the cmd.Execute will error if I don't replace the ' in O
'Reilly with O''Reilly inserting
in the database. I believe.
George Hester
__________________________________
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:u9qoKn0XEHA.3512@.TK2MSFTNGP12.phx.gbl...
> It seems like you both replace every quote with two quotes and then use a
parameter object which
> does exactly the same. This is why you get double. You should only need to
use the parameter object.
> If you post the code (either here or on ASP group) you might get some help
..
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "George Hester" <hesterloli@.hotmail.com> wrote in message
> news:elfsLX0XEHA.2388@.TK2MSFTNGP11.phx.gbl...
> In a asp I made using the Paramater object a user can submit a form and th
e data will be inserted in
> the database. But if the user submits a name in the form O'Reilly then it
will error. So I made a
> VBScript function which parses the input data and if ' exists in the name
I add an extra ' to the '
> in the form data and the user can insert the data. Trouble is when I retu
rn the recordset it looks
> like this O''Reilly. So I am wondering if there exists an escape chatacte
r to avoid this issue?
> Thanks.
> --
> George Hester
> __________________________________
>