nesting text functions when writing to an access 2000 db

I am trying to update records in an access db using asp (IIS5). I think this
should work but ti isn't. I am wondering if when nesting functions you have
to have them in some sort of order. Here's a sample of the code -- and I am
basically trying to replace the use of an apostrophe by the user doing this:

strSQL = "UPDATE tblRequest Set " &_
"dueBy = '" & CStr(Trim(Request.Form("dueBy"))) & "', " &_
"hasAttachments = '" & CStr(Trim(Request.Form("hasAttachments"))) & "', "
&_
"relatedURL = '" & CStr(Trim(Request.Form("relatedURL"))) & "', " &_
"assignedTo = '" & CStr(Trim(Request.Form("assignedTo"))) & "', " &_
"status = '" & CStr(Trim(Request.Form("status"))) & "', " &_
"notes = '" & CStr(Trim(Replace(Request.Form("notes"),"'","''"))) & "' "
&_
"Where reqID = " & Request.Form("txtID") & ""

Re: nesting text functions when writing to an access 2000 db

Sorry, hit the send button before i was done. Anyway, this piece of code is
not working and I am wondering if it is because of the order of my nesting?
I can remove the replace function and things work okay.

"notes = '" & CStr(Trim(Replace(Request.Form("notes"),"'","''"))) & "' "
&_
"Where reqID = " & Request.Form("txtID") & ""

I am trying to update records in an access db using asp (IIS5). I think
this
should work but ti isn't. I am wondering if when nesting functions you have
to have them in some sort of order. Here's a sample of the code -- and I am
basically trying to replace the use of an apostrophe by the user doing this:

strSQL = "UPDATE tblRequest Set " &_
"dueBy = '" & CStr(Trim(Request.Form("dueBy"))) & "', " &_
"hasAttachments = '" & CStr(Trim(Request.Form("hasAttachments"))) & "', "
&_
"relatedURL = '" & CStr(Trim(Request.Form("relatedURL"))) & "', " &_
"assignedTo = '" & CStr(Trim(Request.Form("assignedTo"))) & "', " &_
"status = '" & CStr(Trim(Request.Form("status"))) & "', " &_
"notes = '" & CStr(Trim(Replace(Request.Form("notes"),"'","''"))) & "' "
&_
"Where reqID = " & Request.Form("txtID") & ""

Re: nesting text functions when writing to an access 2000 db

Kevin wrote:
> Sorry, hit the send button before i was done. Anyway, this piece of
> code is not working and I am wondering if it is because of the order
> of my nesting? I can remove the replace function and things work okay.
>
> "notes = '" & CStr(Trim(Replace(Request.Form("notes"),"'","''"))) &
> "' " &_
> "Where reqID = " & Request.Form("txtID") & ""
>
> I am trying to update records in an access db using asp (IIS5). I
> think this
> should work but ti isn't.

Please define "isn't working". Error messages? Incorrect data? Nothing
happens?

> I am wondering if when nesting functions
> you have to have them in some sort of order. Here's a sample of the
> code -- and I am basically trying to replace the use of an apostrophe
> by the user doing this:
>

I presume you've response.written your strSQL variable to make sure it
contains what you expect it to contain. If seeing the result of
response.write does not show you your problem, show us the result.

Having said that, your real problem is your use of dynamic sql instead of
parameters, forcing you to resort to using replace to escape delimiters, as
well as leaving you open to sql injection attacks:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection. pdf
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

A better approach is to use parameters, either via a saved parameter query:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

or by using ODBC parameter markers with a Command object:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e


Bob Barrows
--
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: nesting text functions when writing to an access 2000 db

Thanks Bob.

here is the error I get.

==============================================
Error Type:
Microsoft JET Database Engine (0x80040E14)

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression ''Will you create a stop
by the OCLC booth #2201 during your trip to ALA sort of listserv
announcement, please? I'm going to be on the booth on Saturday and Monday
and I would like for folks to know a Western representative will be there to
answer any que'.
/wlnweb/comm/assignRequestFind.asp, line 340
==============================================

line 340 is ---- objCmd.Execute

I am going to follow up on your other suggestions re: use of parameters

I also did try a Response.Write strSQL statement write before the
objCmd.Execute as such:

Dim objCmd
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = cnnSimple
objCmd.CommandText = strSQL
objCmd.CommandType = adCmdText
Response.Write strSQL
objCmd.Execute
%>

but when I include an apostrophe in the notes field -- I get the above error
but nor strSQL wrote out -- so I am missing something on that trick.


"Bob Barrows [MVP]" wrote in message
news:edjKv0afFHA.1284@TK2MSFTNGP14.phx.gbl...
> Kevin wrote:
> > Sorry, hit the send button before i was done. Anyway, this piece of
> > code is not working and I am wondering if it is because of the order
> > of my nesting? I can remove the replace function and things work okay.
> >
> > "notes = '" & CStr(Trim(Replace(Request.Form("notes"),"'","''"))) &
> > "' " &_
> > "Where reqID = " & Request.Form("txtID") & ""
> >
> > I am trying to update records in an access db using asp (IIS5). I
> > think this
> > should work but ti isn't.
>
> Please define "isn't working". Error messages? Incorrect data? Nothing
> happens?
>
> > I am wondering if when nesting functions
> > you have to have them in some sort of order. Here's a sample of the
> > code -- and I am basically trying to replace the use of an apostrophe
> > by the user doing this:
> >
>
> I presume you've response.written your strSQL variable to make sure it
> contains what you expect it to contain. If seeing the result of
> response.write does not show you your problem, show us the result.
>
> Having said that, your real problem is your use of dynamic sql instead of
> parameters, forcing you to resort to using replace to escape delimiters,
as
> well as leaving you open to sql injection attacks:
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
> http://www.nextgenss.com/papers/advanced_sql_injection.pdf
> http://www.nextgenss.com/papers/more_advanced_sql_injection. pdf
> http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
>
> A better approach is to use parameters, either via a saved parameter
query:
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl
>
> or by using ODBC parameter markers with a Command object:
>
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e
>
>
> Bob Barrows
> --
> 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: nesting text functions when writing to an access 2000 db

Kevin wrote:
> Thanks Bob.
>
> here is the error I get.
>
> ==============================================
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error (missing operator) in query expression ''Will you create
> a stop by the OCLC booth #2201 during your trip to ALA sort of
> listserv announcement, please? I'm going to be on the booth on
> Saturday and Monday and I would like for folks to know a Western
> representative will be there to answer any que'.
> /wlnweb/comm/assignRequestFind.asp, line 340
> ==============================================
>
> line 340 is ---- objCmd.Execute
>
> I am going to follow up on your other suggestions re: use of
> parameters
>
> I also did try a Response.Write strSQL statement write before the
> objCmd.Execute as such:
>
> Dim objCmd
> Set objCmd = Server.CreateObject("ADODB.Command")
> Set objCmd.ActiveConnection = cnnSimple
> objCmd.CommandText = strSQL
> objCmd.CommandType = adCmdText
> Response.Write strSQL
> objCmd.Execute
> %>
>
> but when I include an apostrophe in the notes field -- I get the
> above error but nor strSQL wrote out -- so I am missing something on
> that trick.
>

Prevent the error by using "response.End" (no quotes of course). Your goal
during this debug phase is to see string contained by the strSQL variable,
not attempt to run the query.

Actually, you should have your string completely built BEFORE creating and
opening the connection. Your other goal should be to have your connection
open for as short a time as possible.You should rearrange your code as
follows:

dim strSQL
strSQL = "UPDATE ..."
'******************************************
'comment/delete these two lines when finished debugging
Response.Write strSQL
Response.End
'******************************************
Dim cnnSimple
etc.

Bob Barrows
PS. Here's a couple more links about using saved parameter queries:
http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl
--
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.