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. 4 5588
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.
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.
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Alastair Cameron |
last post by:
VB6, MSXML 3.2 installed:
Q1.
I am having a problem selecting nodes with XPATH expressions when an
attribute values contain backslashes (\\) in as part of its value:
For example the...
|
by: DLP22192 |
last post by:
I have the following single-line if statement that is evaluating true even
though it shouldn't. I have never seen this before and I am concerned that
this can happen in other areas of my code.
...
|
by: Tim::.. |
last post by:
Can someone tell me how I convert this simple SQL statement so I can use it
in ASP.NET???
I have an issue with the quotation marks and wondered if there is a simple
rule for converting the sql...
|
by: dcousineau |
last post by:
Hi! I'm working with a database of news clippings. The database has
fields for the title of the article and the text of the article (also
other fields for things like sources and categories, but...
|
by: arne.muller |
last post by:
Hello,
I've come across some problems reading strucutres from binary files.
Basically I've some strutures
typedef struct {
int i;
double x;
int n;
double *mz;
|
by: Bob Alston |
last post by:
Looking for someone with experience building apps with multiple
instances of forms open. I am building an app for a nonprofit
organizations case workers. They provide services to the elderly.
...
|
by: andy.z |
last post by:
I'm writing a PHP line to the foot of a file using another language. my
problem is I'm not sure how to write it so that the quotes (both single
and double) are corret for PHP to process.
The...
|
by: J.Bijleveld |
last post by:
Hello colleagues,
At this moment we have a real big problem using a .NET application
with an Oracle database (v8.1.6). I hope someone has encountered this
problem before and is able to help me...
|
by: John |
last post by:
Hi
This .net is driving me crazy!!
In VB6 I had a type which contained a couple of multi-dimentional arrays
which i used to create and read records:
Type AAA
:
Array1(10,10,2) as Integer
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |