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 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 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.
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.1152@.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 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.
> --
> George Hester
> __________________________________
>