sql UPDATE fails

Discussion in 'ASP General' started by Joseph, Jun 30, 2003.

  1. Joseph

    Joseph Guest

    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
     
    Joseph, Jun 30, 2003
    #1
    1. Advertisements

  2. Joseph

    Bob Barrows Guest

    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.
    Dim sText
    sText = request("data")
    'data contains "O'Conner's Place"
    sText = Replace(sText,"'","''")

    This replaces each apostrophe with two apostrophes.

    HTH,
    Bob Barrows
     
    Bob Barrows, Jun 30, 2003
    #2
    1. Advertisements

  3. 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!!
     
    MillHillBlade, Jun 30, 2003
    #3
  4. Joseph

    Bob Barrows Guest

    This should be the name of the variable, not the name of the field, unless
    it's the same.
    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 & "', " & _
     
    Bob Barrows, Jun 30, 2003
    #4
  5. Joseph

    Ray at Guest

    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
     
    Ray at, Jun 30, 2003
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.