By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

sql UPDATE fails

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Joseph wrote:
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
Jul 19 '05 #2

P: n/a
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!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 19 '05 #3

P: n/a
MillHillBlade wrote:
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,"'","''")
<irrelevant>
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 & "', " & _

Jul 19 '05 #4

P: n/a
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

"MillHillBlade" <an*******@devdex.com> wrote in message
news:Op**************@TK2MSFTNGP11.phx.gbl...
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!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.