Unless your DB is set to require data for all fields you shouldn't convert null strings to an empty string
- even then an empty string will cause DB Updates errors
Instead your DB update code should check if the field has any data in it before writing
IF Len(request.form("text1"))>0 THEN
' your DB update string code for writing text1 here
END IF
You don't need to remove double quotes from your form fields / data when writing to the database as long as you are using single
quotes in your SQL Update code as delimiters
You don't need to strip apostrophes, single quotes, from your data
- but you need to "double" them when you write to a DB
(so they don't break your single quote delimiters in the SQL code)
Shown below with extra spaces for readability:
text1 = replace(request.form("text1"))," ' ", " ' ' ")
Show the way it should be in your code w/o the extra spaces
text1 = replace(request.form("text1")),"'", "''"))
--
_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________
| Thanks Ron but how do I get it to allow an apostrophe, they are used quiet a
| lot and stripping them out is not an option?
|
| M
|
|
|
|
| | > You need to validate the results before adding to or updating the
| > database. Part of the validation will be to deal with apostrophes and
| > quotes.
| >
| > For example -
| >
| > text1 = request.form("text1") & ""
| > replace(text1,"''","")
| > replace(text1,"""","")
| >
| > The first line converts a null string to an empty string
| > Second line replaces any apostrophe with nothing
| > Third line replaces any quote with nothing.
| >
| > The sql (for text1) becomes
| >
| > & "',text1='" & text1 & "', text2=
| >
| > If you do this for every field (except id1) then the SQL becomes:
| >
| > SQL = "UPDATE tcont Set title='" & title & "',subtitle='" & subtitle &
| > "',text1='" & text1 & text2='"& text2 & "',text3='" & text3 & "',text4='"
| > & text4 & "',auth='" & auth & "',date1='" & date1 & "',date2='" & date2 &
| > "' WHERE id1=" & request.form("id1") & ";"
| >
| > One caveat with this validation is a name such as O'Neal will be stored in
| > the database as ONeal.
| > --
| > Ron Symonds - Microsoft MVP (Expression)
| > Reply only to group - emails will be deleted unread.
| >
http://www.rxs-enterprises.org/fp
| >
| > | >> Thanks Ron
| >>
| >> It works! However if anyone uses '
| >> It causes an error, is there a way round this?
| >>
| >> M
| >>
| >>
| >> | >>> Try this:
| >>>
| >>> <%Dim DSN_Name1
| >>> DSN_Name1 = Application("karuna_ConnectionString")
| >>> set Connection = Server.CreateObject("ADODB.Connection")
| >>> Connection.Open DSN_Name1
| >>> Set RSCat = Server.CreateObject("ADODB.RecordSet")
| >>> SQL = "UPDATE tcont Set title='" & request.form("title") &
| >>> "',subtitle='" & request.form("subtitle") & "',text1='" &
| >>> request.form("text1") &
| >>> "',text2='"& request.form("text2") & "',text3='" & request.form("text3")
| >>> &
| >>> "',text4='"& request.form("text4") & "',auth='" & request.form("auth") &
| >>> "',date1='" & request.form("date1") & "',date2='" &
| >>> request.form("date2") & "' WHERE
| >>> id1=" & request.form("id1") & ";"
| >>> RSCat.Open SQL, Connection
| >>> %>
| >>>
| >>> This assumes that date1 and date2 are both string fields in the
| >>> database. If they are Date/Time fields, use:
| >>>
| >>> &
| >>> "',date1=#"&request.form("date1") & "#,date2=#" & request.form("date2")
| >>> & "# WHERE id1=" & request.form("id1") & ";"
| >>>
| >>> Also assumes id1 is an integer or autonumber field.
| >>> All the SQL statement should be on 1 line.
| >>> --
| >>> Ron Symonds - Microsoft MVP (Expression)
| >>> Reply only to group - emails will be deleted unread.
| >>>
http://www.rxs-enterprises.org/fp
| >>>
| >>> | >>>> Hi,
| >>>>
| >>>> thanks for your help Ronx, however
| >>>>
| >>>> <%Dim DSN_Name1
| >>>> DSN_Name1 = Application("karuna_ConnectionString")
| >>>> set Connection = Server.CreateObject("ADODB.Connection")
| >>>> Connection.Open DSN_Name1
| >>>> Set RSCat = Server.CreateObject("ADODB.RecordSet")
| >>>> SQL = "UPDATE tcont Set title=" & request.form("title") &
| >>>> ",subtitle="&request.form("subtitle") & ",text1="&request.form("text1")
| >>>> & ",text2="&request.form("text2") & ",text3="&request.form("text3") &
| >>>> ",text4="&request.form("text4") & ",auth="&request.form("auth") &
| >>>> ",date1="&request.form("date1") & ",date2="&request.form("date2")&"
| >>>> WHERE id1=" & request.form("id1") & ";"
| >>>> RSCat.Open SQL, Connection
| >>>> %>
| >>>>
| >>>>
| >>>> Now produces the following error...
| >>>>
| >>>> "Microsoft JET Database Engine error '80040e14'
| >>>>
| >>>> Syntax error (missing operator) in query expression 'Title text
| >>>> content'.
| >>>>
| >>>> /dbedit3.asp, line 13 "
| >>>>
| >>>> "Title text content" is the text within ("title") that comes from the
| >>>> form data
| >>>>
| >>>> Where am I going wrong?
| >>>>
| >>>> M
| >>>>
| >>>>
| >>>>
| >>>> | >>>>> In the SQL, replace '::fieldname::' with " &
| >>>>> request.form("fieldname") & "
| >>>>> Example:
| >>>>>
| >>>>> SQL = "UPDATE tcont Set title=" & request.form("title") & ",subtitle="
| >>>>> & request.form("subtitle") & " WHERE id1=" & request.form("id1") & ";"
| >>>>>
| >>>>> It would be better if each field were validated before adding to the
| >>>>> database, since null values might cause problems - validation will
| >>>>> also reduce chances of SQL injection.
| >>>>> --
| >>>>> Ron Symonds - Microsoft MVP (Expression)
| >>>>> Reply only to group - emails will be deleted unread.
| >>>>>
http://www.rxs-enterprises.org/fp
| >>>>>
| >>>>> | >>>>>> What is wrong with this???
| >>>>>>
| >>>>>> <%Dim DSN_Name1
| >>>>>> DSN_Name1 = Application("karuna_ConnectionString")
| >>>>>> set Connection = Server.CreateObject("ADODB.Connection")
| >>>>>> Connection.Open DSN_Name1
| >>>>>> Set RSCat = Server.CreateObject("ADODB.RecordSet")
| >>>>>> SQL = "UPDATE tcont Set
| >>>>>>
title='::title::',subtitle='::subtitle::',text1='::text1::',text2='::text2::',text3='::text3::',text4='::text4::',auth='::auth::',date1='::date1::',date2='::date2::'
| >>>>>> WHERE id1=::id1::"
| >>>>>> RSCat.Open SQL, Connection
| >>>>>> %>
| >>>>>>
| >>>>>> All fields are text and memo fields inc date1 etc.
| >>>>>>
| >>>>>> Any suggestions??
| >>>>>>
| >>>>>> M
| >>>>>>
| >>>>>>
| >>>>>
| >>>>
| >>>>
| >>>
| >>
| >>
| >
|
|
