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]

Re: sql UPDATE fails

Joseph wrote:[color=blue]
> 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?[/color]

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.
[color=blue]
>
> I also know there's a piece of code which can be used to do this, and
> replace all apostrophes so that it works.
>[/color]
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]

Re: sql UPDATE fails

Hi Bob,

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

Dim sText
sText = request("Report GuideDescriptio n")
sText = Replace(sText," '","''")

Set conn = Server.CreateOb ject ("ADODB.Connect ion")
conn.Connection Timeout = Application("De vReportMaint_Co nnectionTimeout ")
conn.CommandTim eout = Application("De vReportMaint_Co mmandTimeout")
conn.Open "DevReportMaint ", "DevReportMaint User", "Spike1"

conn.Execute "UPDATE tblReport SET" & _
" ReportName = '"& request("Report Name") &"', " & _
" ReportCatName = '"& request("Report CatName") &"', SelectionCriter ia =
'"& request("Select ionCriteria")& "', " & _
" ReportGuideDesc ription = '"& request("Report GuideDescriptio n")& "',
" & _
" FullReportDescr iption = '"& request("FullRe portDescription ")& "', " &
_
" VersionCreatedF or = '"& request("Versio nCreatedFor")& "', " & _
" ClientCreatedFo r = '"& request("Client CreatedFor") &"', ReleasedTo =
'"& request("Releas edTo") &"', " &_
" TidyUpDescripti on = '"& request("TidyUp Description") &"', MultiPage =
'"& request("MultiP age") &"', " & _
" HeadingCellsSho wBorders = '"& request("Headin gCellsShowBorde rs") &"',
" & _
" Chart = '"& request("Chart" ) &"', Landscape = '"&
request("Landsc ape") &"', " & _
" ReleasedToOther s = '"& request("Releas edToOthers") &"' " & _
" WHERE ReportId = '"& request("Report Id") &"'"

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!

[Bob Barrows]

Re: sql UPDATE fails

MillHillBlade wrote:[color=blue]
> Hi Bob,
>
> Thanks for that. Where exactly should that code go, I've placed it
> before mu update code, ie:
>
> Dim sText
> sText = request("Report GuideDescriptio n")[/color]
This should be the name of the variable, not the name of the field, unless
it's the same.
[color=blue]
> sText = Replace(sText," '","''")
>[/color]
<irrelevant>
[color=blue]
> conn.Execute "UPDATE tblReport SET" & _
> " ReportName = '"& request("Report Name") &"', " & _
> " ReportCatName = '"& request("Report CatName") &"', SelectionCriter ia
> = '"& request("Select ionCriteria")& "', " & _
> " ReportGuideDesc ription = '"& request("Report GuideDescriptio n")& "',[/color]

Why wouldn't you use sText here instead of
request("Report GuideDescriptio n")? 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:
" ReportGuideDesc ription = '"& sText & "', " & _

[Ray at]

Re: sql UPDATE fails

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, sSelectionCrite ria,
sReportGuideDes cription, sFullReportDesc ription, sVersionCreated For,
sClientCreatedF or, sReleasedTo, sTidyUpDescript ion, sMultiPage,
sHeadingCellsSh owBorders, sChart, sLandscape, sReleasedToOthe rs, sReportID
'''It appears that you aren't using any integers, bits, or any other
non-text data type. Hmm.

sReportName = Request("Report Name")
sReportCatName = Request("Report CatName")
sSelectionCrite ria = Request("Select ionCriteria")
'''etc. etc.

SafenSQL sReportName
SafenSQL sReportCatName
SafeSQL sSelectionCrite ria
'''etc. etc.



sSQL = "UPDATE tblReport SET ReportName = '" & sReportName &
"',ReportCatNam e = '" & sReportCatName & "',SelectionCri teria='" &
sSelectionCrite ria & _
"',ReportGuideD escription = '" & sReportGuideDes cription &
"',FullReportDe scription = '" sFullReportDesc ription & "',VersionCreat edFor
= '" & _
sVersionCreated For & "',ClientCreate dFor = '" sClientCreatedF or &
"',Released To ='" sReleasedTo & "', TidyUpDescripti on = '" &
sTidyUpDescript ion & _ "', MultiPage ='" & sMultiPage &
"',HeadingCells ShowBorders = '" & sHeadingCellsSh owBorders & "',Chart = '"
sChart & "', Landscape = '" & _
sLandscape & "',ReleasedToOt hers = '" & sReleasedToOthe rs & "' WHERE
ReportId = '" & sReportId & "'"






Set conn = Server.CreateOb ject ("ADODB.Connect ion")
conn.Connection Timeout = Application("De vReportMaint_Co nnectionTimeout ")
conn.CommandTim eout = Application("De vReportMaint_Co mmandTimeout")
conn.Open "DevReportMaint ", "DevReportMaint User", "Spike1"
conn.Execute sSQL
conn.Close
set conn = Nothing

Ray at work





"MillHillBl ade" <anonymous@devd ex.com> wrote in message
news:OpImxRwPDH A.1216@TK2MSFTN GP11.phx.gbl...[color=blue]
> Hi Bob,
>
> Thanks for that. Where exactly should that code go, I've placed it
> before mu update code, ie:
>
> Dim sText
> sText = request("Report GuideDescriptio n")
> sText = Replace(sText," '","''")
>
> Set conn = Server.CreateOb ject ("ADODB.Connect ion")
> conn.Connection Timeout = Application("De vReportMaint_Co nnectionTimeout ")
> conn.CommandTim eout = Application("De vReportMaint_Co mmandTimeout")
> conn.Open "DevReportMaint ", "DevReportMaint User", "Spike1"
>
> conn.Execute "UPDATE tblReport SET" & _
> " ReportName = '"& request("Report Name") &"', " & _
> " ReportCatName = '"& request("Report CatName") &"', SelectionCriter ia =
> '"& request("Select ionCriteria")& "', " & _
> " ReportGuideDesc ription = '"& request("Report GuideDescriptio n")& "',
> " & _
> " FullReportDescr iption = '"& request("FullRe portDescription ")& "', " &
> _
> " VersionCreatedF or = '"& request("Versio nCreatedFor")& "', " & _
> " ClientCreatedFo r = '"& request("Client CreatedFor") &"', ReleasedTo =
> '"& request("Releas edTo") &"', " &_
> " TidyUpDescripti on = '"& request("TidyUp Description") &"', MultiPage =
> '"& request("MultiP age") &"', " & _
> " HeadingCellsSho wBorders = '"& request("Headin gCellsShowBorde rs") &"',
> " & _
> " Chart = '"& request("Chart" ) &"', Landscape = '"&
> request("Landsc ape") &"', " & _
> " ReleasedToOther s = '"& request("Releas edToOthers") &"' " & _
> " WHERE ReportId = '"& request("Report Id") &"'"
>
> 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![/color]