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

[Jack]

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.

 

[Bob Barrows [MVP]]

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),"'","''") & "', "

 

[Bob Barrows [MVP]]

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),"'","''") & "', "

 

[Jack]

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

 

[Jack]

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.