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.
> >
> >
> >
Similar Threads
