sql UPDATE fails

[Joseph]

Hi,

I've got a page which updates a record, but it fails if the text used
contains an apostrophe. I understand I need to replace apostrophes
with double quotes in order for it to work?

I also know there's a piece of code which can be used to do this, and
replace all apostrophes so that it works.

Any help would be greatly appreciated!

Cheers,
Joseph

 

[Bob Barrows]

Hi,

I've got a page which updates a record, but it fails if the text used
contains an apostrophe. I understand I need to replace apostrophes
with double quotes in order for it to work?

No. you need to replace the apostrophes with two apostrophes. A double quote
is a single character: ". This is not what you need to do.

I also know there's a piece of code which can be used to do this, and
replace all apostrophes so that it works.

Dim sText
sText = request("data")
'data contains "O'Conner's Place"
sText = Replace(sText,"'","''")

This replaces each apostrophe with two apostrophes.

HTH,
Bob Barrows

 

[MillHillBlade]

Hi Bob,

Thanks for that. Where exactly should that code go, I've placed it
before mu update code, ie:

Dim sText
sText = request("ReportGuideDescription")
sText = Replace(sText,"'","''")

Set conn = Server.CreateObject ("ADODB.Connection")
conn.ConnectionTimeout = Application("DevReportMaint_ConnectionTimeout")
conn.CommandTimeout = Application("DevReportMaint_CommandTimeout")
conn.Open "DevReportMaint", "DevReportMaintUser", "Spike1"

conn.Execute "UPDATE tblReport SET" & _
" ReportName = '"& request("ReportName") &"', " & _
" ReportCatName = '"& request("ReportCatName") &"', SelectionCriteria =
'"& request("SelectionCriteria")& "', " & _
" ReportGuideDescription = '"& request("ReportGuideDescription")& "',
" & _
" FullReportDescription = '"& request("FullReportDescription")& "', " &
_
" VersionCreatedFor = '"& request("VersionCreatedFor")& "', " & _
" ClientCreatedFor = '"& request("ClientCreatedFor") &"', ReleasedTo =
'"& request("ReleasedTo") &"', " &_
" TidyUpDescription = '"& request("TidyUpDescription") &"', MultiPage =
'"& request("MultiPage") &"', " & _
" HeadingCellsShowBorders = '"& request("HeadingCellsShowBorders") &"',
" & _
" Chart = '"& request("Chart") &"', Landscape = '"&
request("Landscape") &"', " & _
" ReleasedToOthers = '"& request("ReleasedToOthers") &"' " & _
" WHERE ReportId = '"& request("ReportId") &"'"

conn.Close
set conn = Nothing


Also, I replaced "data" in the line 'sText = request("data")' with the
name of the field I wanted to replace the text in.

However it still doesn't work!!


Thanks,
Joseph

Phil Jagielka- the best young footballer in Britain, no question!!

 

[Bob Barrows]

Hi Bob,

Thanks for that. Where exactly should that code go, I've placed it
before mu update code, ie:

Dim sText
sText = request("ReportGuideDescription")

This should be the name of the variable, not the name of the field, unless
it's the same.

sText = Replace(sText,"'","''")

conn.Execute "UPDATE tblReport SET" & _
" ReportName = '"& request("ReportName") &"', " & _
" ReportCatName = '"& request("ReportCatName") &"', SelectionCriteria
= '"& request("SelectionCriteria")& "', " & _
" ReportGuideDescription = '"& request("ReportGuideDescription")& "',

Why wouldn't you use sText here instead of
request("ReportGuideDescription")? It seems fairly obvious doesn't it?
You've gone to the trouble of creating the sText variable up above, and then
you don't use it? Just in case you're missing my point:
" ReportGuideDescription = '"& sText & "', " & _

 

[Ray at]

You set sText to be your safened value from your form (or querystring?) but
then don't use it. Try this:



Sub SafenSQL(ByRef sText)
sText = Replace(sText, "'", "''")
End Sub

Dim sSQL
Dim sReportName, sReportCatName, sSelectionCriteria,
sReportGuideDescription, sFullReportDescription, sVersionCreatedFor,
sClientCreatedFor, sReleasedTo, sTidyUpDescription, sMultiPage,
sHeadingCellsShowBorders, sChart, sLandscape, sReleasedToOthers, sReportID
'''It appears that you aren't using any integers, bits, or any other
non-text data type. Hmm.

sReportName = Request("ReportName")
sReportCatName = Request("ReportCatName")
sSelectionCriteria = Request("SelectionCriteria")
'''etc. etc.

SafenSQL sReportName
SafenSQL sReportCatName
SafeSQL sSelectionCriteria
'''etc. etc.



sSQL = "UPDATE tblReport SET ReportName = '" & sReportName &
"',ReportCatName = '" & sReportCatName & "',SelectionCriteria='" &
sSelectionCriteria & _
"',ReportGuideDescription = '" & sReportGuideDescription &
"',FullReportDescription = '" sFullReportDescription & "',VersionCreatedFor
= '" & _
sVersionCreatedFor & "',ClientCreatedFor = '" sClientCreatedFor &
"',ReleasedTo ='" sReleasedTo & "', TidyUpDescription = '" &
sTidyUpDescription & _ "', MultiPage ='" & sMultiPage &
"',HeadingCellsShowBorders = '" & sHeadingCellsShowBorders & "',Chart = '"
sChart & "', Landscape = '" & _
sLandscape & "',ReleasedToOthers = '" & sReleasedToOthers & "' WHERE
ReportId = '" & sReportId & "'"






Set conn = Server.CreateObject ("ADODB.Connection")
conn.ConnectionTimeout = Application("DevReportMaint_ConnectionTimeout")
conn.CommandTimeout = Application("DevReportMaint_CommandTimeout")
conn.Open "DevReportMaint", "DevReportMaintUser", "Spike1"
conn.Execute sSQL
conn.Close
set conn = Nothing

Ray at work