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.
-- 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().
-- 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
> __________________________________
> >|||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
> __________________________________
> >|||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
> __________________________________
>

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
> __________________________________
>

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
> __________________________________
>

Does format file for bulk insert allow mix of native and character format?

I tried to place this question to the .Net framework Data Access and Storage forum and got no answer, so I am trying to move it on this forum.

So I have a module which require me to import big amount of data. I believe that the native format data files with format files will be the most efficient way of implementation. I am trying to programmatically produce a BCP like exported files of native format(it means without type conversion) from tables with nullable and nonnullable values.I prefere to be able to not produce computed or identity or rowguid fields,so I need format files.

I don't have problems producing different kinds of int or float( which are the majority of fields) or char or nchar fields.

Problems are emerging with the datetime or smalldatetime or decimal fields because I don't know how to convert to them from the strings or from the CLR types.

So I trying to find a way to find a native format of those fields or to find if a bulk insert will accept mixed format files with some of the fields in the native format without field terminators and some with field terminators or to use char format with field terminators only plus maybe format files.

So if the answer to above question is positive I can partially resolve the problem, if negative I will have to use the character format.

Unless you can educate me on the convertion to the SQL server internal formats of datetimes and decimals from the CLR types.

See SQL Server 2005 Books Online topics:

SQL Server Data Types and Their .NET Framework Equivalents
http://msdn2.microsoft.com/en-us/library/ms131092.aspx
Specifying File Storage Type by Using bcp
http://msdn2.microsoft.com/en-US/library/ms189110.aspx

Data Type Conversion (Database Engine)
http://msdn2.microsoft.com/en-us/library/ms191530.aspx