Problem with building sql statement to handle single quote in a fi
 

Hi, I have a asp page where part of the code is as follows. This builds up
the sql statement partially.

sql01 = "UPDATE EquipmentTbl SET "
sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "



RESULT OF PARTIAL UPDATE STATEMENT USING RESPONSE.WRITE IS:

UPDATE EquipmentTbl SET SerialNumber = 'A83737', Description = 'Video
Conferencing Equipment', Location = 'Conference Rooms cabinet',



Now, in the above, I would like to be able to put the location field as
'Don's room'. In other words,
I would like to handle the aprostrophe after Don.
With this in mind I am changing the code as following:


'Old ones in data base go through here
sql01 = "UPDATE EquipmentTbl SET "
sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
sql01 = sql01 & "Location = '" & Replace((request.form(strLoc), "'",
"''") & "', "


However, when generating this sql partial sql statement, I am getting an
error as follows:
I guess I am not getting the hang of the syntax for handling the single
quote in the sql statement.



/gwisbrandnewready6mod2/WriteEquipmentExpLines.asp, line 243, column 63
sql01 = sql01 & "Location = '" & Replace((request.form(strLoc), "'", "''") &
"', "
--------------------------------------------------------------^

Any help is appreciated. Thanks in advance.

Re: Problem with building sql statement to handle single quote in a fi
 

Jack wrote:
> Hi, I have a asp page where part of the code is as follows. This
> builds up the sql statement partially.
>
> sql01 = "UPDATE EquipmentTbl SET "
> sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "',
> " sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
> sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
>
>
>
> RESULT OF PARTIAL UPDATE STATEMENT USING RESPONSE.WRITE IS:
>
> UPDATE EquipmentTbl SET SerialNumber = 'A83737', Description = 'Video
> Conferencing Equipment', Location = 'Conference Rooms cabinet',
>
>
>
> Now, in the above, I would like to be able to put the location field
> as 'Don's room'. In other words,
> I would like to handle the aprostrophe after Don.
> With this in mind I am changing the code as following:
>
>
> 'Old ones in data base go through here
> sql01 = "UPDATE EquipmentTbl SET "
> sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "',
> " sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
> sql01 = sql01 & "Location = '" & Replace((request.form(strLoc), "'",

Is this line break deliberate?
The whole Replace statement should be on a single line ...

> "''") & "', "
>
>
> However, when generating this sql partial sql statement, I am getting
> an error as follows:
> I guess I am not getting the hang of the syntax for handling the
> single quote in the sql statement.
>
>
>
> /gwisbrandnewready6mod2/WriteEquipmentExpLines.asp, line 243, column
> 63 sql01 = sql01 & "Location = '" & Replace((request.form(strLoc),
> "'", "''") & "', "
> --------------------------------------------------------------^
>
If you used parameters, this would not be a problem. Try this:

sql01 = "UPDATE EquipmentTbl SET "
sql01 = sql01 & "SerialNumber = ?,"
sql01 = sql01 & "Description = ?,"
sql01 = sql01 & "Location = ?"
sql01 = sql01 & "WHERE ..."

arParms=Array(request.form(strSerialNum), _
request.form(strDesc),request.form(strLoc))
dim cmd
set cmd=createobject("adodb.command")
set cmd.activeconnection=objConn
cmd.CommandText=sql01
cmd.commandType=1 'adCmdText
cmd.Execute ,arParms,128 'adExecuteNoRecords

See? No worries about delimiters or apostrophes (or sql injection).

However:

sql01 = "UPDATE EquipmentTbl SET "
sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
sql01 = sql01 & Replace(request.form(strLoc),"'","''") & "', "


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Problem with building sql statement to handle single quote in a fi
 

Bob Barrows [MVP] wrote:
>
> sql01 = "UPDATE EquipmentTbl SET "
> sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "',
> " sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
> sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
> sql01 = sql01 & Replace(request.form(strLoc),"'","''") & "', "
Oops. It should be:

sql01 = "UPDATE EquipmentTbl SET "
sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
sql01 = sql01 & "Location = '"
sql01 = sql01 & Replace(request.form(strLoc),"'","''") & "', "


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Problem with building sql statement to handle single quote in
 

Well, I guess there was some communication gap. It works fine now. Thanks a
lot. Best Regards.

"Jack" wrote:

> Thanks Bob for your generous help. This application has been developed by
> someone who worked before me for long time. In order to change all the sql
> code, it would take quite a bit of time which I do not have. However, for new
> projects I have started applying your concepts and it works real well
> avoiding the pitalls you have described. Coming back to the sql statment
> change you recommended i.e.
> sql01 = "UPDATE EquipmentTbl SET "
> sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
> sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
> sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
> sql01 = sql01 & Replace(request.form(strLoc),"'","''") & "', "
>
> I am now getting the following sql generated by ther response.wrtie statement:
>
> UPDATE EquipmentTbl SET SerialNumber = 'A83737', Description = 'Video
> Conferencing Equipment', Location = 'Conference Room's cabinet',Conference
> Room''s cabinet',
>
> Here the location = is giving two different values, one with single quote
> and the other with double. How do I change the code so that the generated sql
> statement would be:
>
> UPDATE EquipmentTbl SET SerialNumber = 'A83737', Description = 'Video
> Conferencing Equipment', Location = 'Conference Room''s cabinet',
>
> Thanks again for any help in advance.
>
>
>
> "Bob Barrows [MVP]" wrote:
>
> > Jack wrote:
> > > Hi, I have a asp page where part of the code is as follows. This
> > > builds up the sql statement partially.
> > >
> > > sql01 = "UPDATE EquipmentTbl SET "
> > > sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "',
> > > " sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
> > > sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
> > >
> > >
> > >
> > > RESULT OF PARTIAL UPDATE STATEMENT USING RESPONSE.WRITE IS:
> > >
> > > UPDATE EquipmentTbl SET SerialNumber = 'A83737', Description = 'Video
> > > Conferencing Equipment', Location = 'Conference Rooms cabinet',
> > >
> > >
> > >
> > > Now, in the above, I would like to be able to put the location field
> > > as 'Don's room'. In other words,
> > > I would like to handle the aprostrophe after Don.
> > > With this in mind I am changing the code as following:
> > >
> > >
> > > 'Old ones in data base go through here
> > > sql01 = "UPDATE EquipmentTbl SET "
> > > sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "',
> > > " sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
> > > sql01 = sql01 & "Location = '" & Replace((request.form(strLoc), "'",
> >
> > Is this line break deliberate?
> > The whole Replace statement should be on a single line ...
> >
> > > "''") & "', "
> > >
> > >
> > > However, when generating this sql partial sql statement, I am getting
> > > an error as follows:
> > > I guess I am not getting the hang of the syntax for handling the
> > > single quote in the sql statement.
> > >
> > >
> > >
> > > /gwisbrandnewready6mod2/WriteEquipmentExpLines.asp, line 243, column
> > > 63 sql01 = sql01 & "Location = '" & Replace((request.form(strLoc),
> > > "'", "''") & "', "
> > > --------------------------------------------------------------^
> > >
> > If you used parameters, this would not be a problem. Try this:
> >
> > sql01 = "UPDATE EquipmentTbl SET "
> > sql01 = sql01 & "SerialNumber = ?,"
> > sql01 = sql01 & "Description = ?,"
> > sql01 = sql01 & "Location = ?"
> > sql01 = sql01 & "WHERE ..."
> >
> > arParms=Array(request.form(strSerialNum), _
> > request.form(strDesc),request.form(strLoc))
> > dim cmd
> > set cmd=createobject("adodb.command")
> > set cmd.activeconnection=objConn
> > cmd.CommandText=sql01
> > cmd.commandType=1 'adCmdText
> > cmd.Execute ,arParms,128 'adExecuteNoRecords
> >
> > See? No worries about delimiters or apostrophes (or sql injection).
> >
> > However:
> >
> > sql01 = "UPDATE EquipmentTbl SET "
> > sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
> > sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
> > sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
> > sql01 = sql01 & Replace(request.form(strLoc),"'","''") & "', "
> >
> >
> > --
> > Microsoft MVP -- ASP/ASP.NET
> > Please reply to the newsgroup. The email account listed in my From
> > header is my spam trap, so I don't check it very often. You will get a
> > quicker response by posting to the newsgroup.
> >
> >
> >

Re: Problem with building sql statement to handle single quote in
 

Thanks Bob for your generous help. This application has been developed by
someone who worked before me for long time. In order to change all the sql
code, it would take quite a bit of time which I do not have. However, for new
projects I have started applying your concepts and it works real well
avoiding the pitalls you have described. Coming back to the sql statment
change you recommended i.e.
sql01 = "UPDATE EquipmentTbl SET "
sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
sql01 = sql01 & Replace(request.form(strLoc),"'","''") & "', "

I am now getting the following sql generated by ther response.wrtie statement:

UPDATE EquipmentTbl SET SerialNumber = 'A83737', Description = 'Video
Conferencing Equipment', Location = 'Conference Room's cabinet',Conference
Room''s cabinet',

Here the location = is giving two different values, one with single quote
and the other with double. How do I change the code so that the generated sql
statement would be:

UPDATE EquipmentTbl SET SerialNumber = 'A83737', Description = 'Video
Conferencing Equipment', Location = 'Conference Room''s cabinet',

Thanks again for any help in advance.



"Bob Barrows [MVP]" wrote:

> Jack wrote:
> > Hi, I have a asp page where part of the code is as follows. This
> > builds up the sql statement partially.
> >
> > sql01 = "UPDATE EquipmentTbl SET "
> > sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "',
> > " sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
> > sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
> >
> >
> >
> > RESULT OF PARTIAL UPDATE STATEMENT USING RESPONSE.WRITE IS:
> >
> > UPDATE EquipmentTbl SET SerialNumber = 'A83737', Description = 'Video
> > Conferencing Equipment', Location = 'Conference Rooms cabinet',
> >
> >
> >
> > Now, in the above, I would like to be able to put the location field
> > as 'Don's room'. In other words,
> > I would like to handle the aprostrophe after Don.
> > With this in mind I am changing the code as following:
> >
> >
> > 'Old ones in data base go through here
> > sql01 = "UPDATE EquipmentTbl SET "
> > sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "',
> > " sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
> > sql01 = sql01 & "Location = '" & Replace((request.form(strLoc), "'",
>
> Is this line break deliberate?
> The whole Replace statement should be on a single line ...
>
> > "''") & "', "
> >
> >
> > However, when generating this sql partial sql statement, I am getting
> > an error as follows:
> > I guess I am not getting the hang of the syntax for handling the
> > single quote in the sql statement.
> >
> >
> >
> > /gwisbrandnewready6mod2/WriteEquipmentExpLines.asp, line 243, column
> > 63 sql01 = sql01 & "Location = '" & Replace((request.form(strLoc),
> > "'", "''") & "', "
> > --------------------------------------------------------------^
> >
> If you used parameters, this would not be a problem. Try this:
>
> sql01 = "UPDATE EquipmentTbl SET "
> sql01 = sql01 & "SerialNumber = ?,"
> sql01 = sql01 & "Description = ?,"
> sql01 = sql01 & "Location = ?"
> sql01 = sql01 & "WHERE ..."
>
> arParms=Array(request.form(strSerialNum), _
> request.form(strDesc),request.form(strLoc))
> dim cmd
> set cmd=createobject("adodb.command")
> set cmd.activeconnection=objConn
> cmd.CommandText=sql01
> cmd.commandType=1 'adCmdText
> cmd.Execute ,arParms,128 'adExecuteNoRecords
>
> See? No worries about delimiters or apostrophes (or sql injection).
>
> However:
>
> sql01 = "UPDATE EquipmentTbl SET "
> sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
> sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
> sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "
> sql01 = sql01 & Replace(request.form(strLoc),"'","''") & "', "
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>