syntax error in replace statement

Discussion in 'ASP General' started by middletree, Oct 24, 2003.

  1. middletree

    middletree Guest

    What's wrong with this code?

    strLongDesc =
    Replace(Replace(Replace(Replace(Trim(Request.Form("LongDesc")),"'","''"),vbC
    rLf,"<br>"),"<",&lt;),"<",&gt;)

    Background:
    This field is a textarea, and I needed to account for apostrophes, which I
    had already done, and replaced line breaks with html line breaks on my page
    which displays this stuff. That works fine. But then a user entered this
    line, pasted from a log file:
    SQL Statement: <SELECT * FROM etc., etc.

    Which resulted in an actual dropdown box being displayed, and all the rest
    of the description after that point was not displayed. So I tried to put in
    code to replace the < and > with a &lt; and &gt; and the code I get when the
    page loads is:

    Microsoft VBScript compilation (0x800A03EA)
    Syntax error
    /AddToTicket.asp, line 75, column 106
    strLongDesc =
    Replace(Replace(Replace(Replace(Trim(Request.Form("LongDesc")),"'","''"),vbC
    rLf,"<br>"),"<",&lt;),"<",&gt;)
    ----------------------------------------------------------------------------
    -----------------------------^
     
    middletree, Oct 24, 2003
    #1
    1. Advertising

  2. middletree

    middletree Guest

    Well, I found the problem with the syntax, but now it simply doesn't work.

    Here is my code:

    strLongDesc =
    Replace(Replace(Replace(Replace(Trim(Request.Form("LongDesc")),"'","''"),vbC
    rLf,"<br>"),"<","&lt;"),">","&gt;")

    and of course, I insert strLongDesc into a field in SQL Server, and when I
    open it up in SQL Server, it still shows what I typed into the textarea,
    which is <select>, whereas I should see &lt;select&gt;

    What am I doing wrong?



    "middletree" <> wrote in message
    news:...
    > What's wrong with this code?
    >
    > strLongDesc =
    >

    Replace(Replace(Replace(Replace(Trim(Request.Form("LongDesc")),"'","''"),vbC
    > rLf,"<br>"),"<",&lt;),"<",&gt;)
    >
    > Background:
    > This field is a textarea, and I needed to account for apostrophes, which I
    > had already done, and replaced line breaks with html line breaks on my

    page
    > which displays this stuff. That works fine. But then a user entered this
    > line, pasted from a log file:
    > SQL Statement: <SELECT * FROM etc., etc.
    >
    > Which resulted in an actual dropdown box being displayed, and all the rest
    > of the description after that point was not displayed. So I tried to put

    in
    > code to replace the < and > with a &lt; and &gt; and the code I get when

    the
    > page loads is:
    >
    > Microsoft VBScript compilation (0x800A03EA)
    > Syntax error
    > /AddToTicket.asp, line 75, column 106
    > strLongDesc =
    >

    Replace(Replace(Replace(Replace(Trim(Request.Form("LongDesc")),"'","''"),vbC
    > rLf,"<br>"),"<",&lt;),"<",&gt;)
    > --------------------------------------------------------------------------

    --
    > -----------------------------^
    >
    >
     
    middletree, Oct 24, 2003
    #2
    1. Advertising

  3. (a) you need double quotes around "&lt;" and "&gt;"

    (b) how about:

    strLongDesc = trim(server.HTMLEncode(Request.Form("LongDesc")))
    strLongDesc = replace(replace(strLongDesc,"'","''"),VBCrLf,"<br>")




    "middletree" <> wrote in message
    news:...
    > What's wrong with this code?
    >
    > strLongDesc =
    >

    Replace(Replace(Replace(Replace(Trim(Request.Form("LongDesc")),"'","''"),vbC
    > rLf,"<br>"),"<",&lt;),"<",&gt;)
    >
    > Background:
    > This field is a textarea, and I needed to account for apostrophes, which I
    > had already done, and replaced line breaks with html line breaks on my

    page
    > which displays this stuff. That works fine. But then a user entered this
    > line, pasted from a log file:
    > SQL Statement: <SELECT * FROM etc., etc.
    >
    > Which resulted in an actual dropdown box being displayed, and all the rest
    > of the description after that point was not displayed. So I tried to put

    in
    > code to replace the < and > with a &lt; and &gt; and the code I get when

    the
    > page loads is:
    >
    > Microsoft VBScript compilation (0x800A03EA)
    > Syntax error
    > /AddToTicket.asp, line 75, column 106
    > strLongDesc =
    >

    Replace(Replace(Replace(Replace(Trim(Request.Form("LongDesc")),"'","''"),vbC
    > rLf,"<br>"),"<",&lt;),"<",&gt;)
    > --------------------------------------------------------------------------

    --
    > -----------------------------^
    >
    >
     
    Aaron Bertrand [MVP], Oct 24, 2003
    #3
  4. > when I open it up in SQL Server,

    Where in SQL Server? Don't use Enterprise Manager for viewing data (e.g.
    Return all rows). It is liable to do all sorts of funky things in order to
    present the data to you in a "friendly" way (for some other issues see
    http://www.aspfaq.com/2455). Run a SELECT query in Query Analyzer. Also,
    response.write(sql) to make sure the replacements were done.

    Another piece of friendly advice: store the statement as is, and use
    Server.HTMLEncode when you *retrieve* and *display* it. HTML formatting has
    little use/place inside the database.
     
    Aaron Bertrand [MVP], Oct 24, 2003
    #4
  5. middletree

    middletree Guest

    OK, I've not gotten familiar with HTMLEncode. That will take care of the <
    and other characters, then?

    I'll try it out. Thanks, very much.

    I also never knew that that you said about Enterprise Mgr vs. Query analyzer
    in the other post. thanks


    "Aaron Bertrand [MVP]" <> wrote in message
    news:...
    > (a) you need double quotes around "&lt;" and "&gt;"
    >
    > (b) how about:
    >
    > strLongDesc = trim(server.HTMLEncode(Request.Form("LongDesc")))
    > strLongDesc = replace(replace(strLongDesc,"'","''"),VBCrLf,"<br>")
    >
    >
    >
    >
    > "middletree" <> wrote in message
    > news:...
    > > What's wrong with this code?
    > >
    > > strLongDesc =
    > >

    >

    Replace(Replace(Replace(Replace(Trim(Request.Form("LongDesc")),"'","''"),vbC
    > > rLf,"<br>"),"<",&lt;),"<",&gt;)
    > >
    > > Background:
    > > This field is a textarea, and I needed to account for apostrophes, which

    I
    > > had already done, and replaced line breaks with html line breaks on my

    > page
    > > which displays this stuff. That works fine. But then a user entered this
    > > line, pasted from a log file:
    > > SQL Statement: <SELECT * FROM etc., etc.
    > >
    > > Which resulted in an actual dropdown box being displayed, and all the

    rest
    > > of the description after that point was not displayed. So I tried to put

    > in
    > > code to replace the < and > with a &lt; and &gt; and the code I get when

    > the
    > > page loads is:
    > >
    > > Microsoft VBScript compilation (0x800A03EA)
    > > Syntax error
    > > /AddToTicket.asp, line 75, column 106
    > > strLongDesc =
    > >

    >

    Replace(Replace(Replace(Replace(Trim(Request.Form("LongDesc")),"'","''"),vbC
    > > rLf,"<br>"),"<",&lt;),"<",&gt;)

    >
    > --------------------------------------------------------------------------
    > --
    > > -----------------------------^
    > >
    > >

    >
    >
     
    middletree, Oct 24, 2003
    #5
  6. middletree

    middletree Guest

    Well, I tried it exactly as you have it in (b) below, and it didn't work.
    Also tried it with double quotes around the &lt, and it still stored my text
    of <select> as <select>, which displayed as a dropdown.


    "Aaron Bertrand [MVP]" <> wrote in message
    news:...
    > (a) you need double quotes around "&lt;" and "&gt;"
    >
    > (b) how about:
    >
    > strLongDesc = trim(server.HTMLEncode(Request.Form("LongDesc")))
    > strLongDesc = replace(replace(strLongDesc,"'","''"),VBCrLf,"<br>")
    >
    >
    >
    >
    > "middletree" <> wrote in message
    > news:...
    > > What's wrong with this code?
    > >
    > > strLongDesc =
    > >

    >

    Replace(Replace(Replace(Replace(Trim(Request.Form("LongDesc")),"'","''"),vbC
    > > rLf,"<br>"),"<",&lt;),"<",&gt;)
    > >
    > > Background:
    > > This field is a textarea, and I needed to account for apostrophes, which

    I
    > > had already done, and replaced line breaks with html line breaks on my

    > page
    > > which displays this stuff. That works fine. But then a user entered this
    > > line, pasted from a log file:
    > > SQL Statement: <SELECT * FROM etc., etc.
    > >
    > > Which resulted in an actual dropdown box being displayed, and all the

    rest
    > > of the description after that point was not displayed. So I tried to put

    > in
    > > code to replace the < and > with a &lt; and &gt; and the code I get when

    > the
    > > page loads is:
    > >
    > > Microsoft VBScript compilation (0x800A03EA)
    > > Syntax error
    > > /AddToTicket.asp, line 75, column 106
    > > strLongDesc =
    > >

    >

    Replace(Replace(Replace(Replace(Trim(Request.Form("LongDesc")),"'","''"),vbC
    > > rLf,"<br>"),"<",&lt;),"<",&gt;)

    >
    > --------------------------------------------------------------------------
    > --
    > > -----------------------------^
    > >
    > >

    >
    >
     
    middletree, Oct 24, 2003
    #6
  7. middletree

    middletree Guest

    "Aaron Bertrand [MVP]" <> wrote in message
    news:...
    > > when I open it up in SQL Server,

    >
    > Where in SQL Server? Don't use Enterprise Manager for viewing data (e.g.
    > Return all rows). It is liable to do all sorts of funky things in order

    to
    > present the data to you in a "friendly" way (for some other issues see
    > http://www.aspfaq.com/2455). Run a SELECT query in Query Analyzer. Also,
    > response.write(sql) to make sure the replacements were done.


    As it turned out, the Query A vs. Ent Mgr were both displying correctly, but
    I will make sure i view the data correctly from now on. But the problem is
    that the replace function is not working. I verified this per your
    suggestion with the response.write statement. It does just fine with the
    <br> and quotes. Very puzzling and frustrating


    >
    > Another piece of friendly advice: store the statement as is, and use
    > Server.HTMLEncode when you *retrieve* and *display* it. HTML formatting

    has
    > little use/place inside the database.
    >
    >
     
    middletree, Oct 24, 2003
    #7
  8. Then my guess is there are no < or > characters for replacement? Compare
    this to the completed SQL statement:

    Response.write(request.form("whatever_the_variable_was"))




    "middletree" <> wrote in message
    news:#SAj7$...
    > "Aaron Bertrand [MVP]" <> wrote in message
    > news:...
    > > > when I open it up in SQL Server,

    > >
    > > Where in SQL Server? Don't use Enterprise Manager for viewing data

    (e.g.
    > > Return all rows). It is liable to do all sorts of funky things in order

    > to
    > > present the data to you in a "friendly" way (for some other issues see
    > > http://www.aspfaq.com/2455). Run a SELECT query in Query Analyzer.

    Also,
    > > response.write(sql) to make sure the replacements were done.

    >
    > As it turned out, the Query A vs. Ent Mgr were both displying correctly,

    but
    > I will make sure i view the data correctly from now on. But the problem is
    > that the replace function is not working. I verified this per your
    > suggestion with the response.write statement. It does just fine with the
    > <br> and quotes. Very puzzling and frustrating
    >
    >
    > >
    > > Another piece of friendly advice: store the statement as is, and use
    > > Server.HTMLEncode when you *retrieve* and *display* it. HTML formatting

    > has
    > > little use/place inside the database.
    > >
    > >

    >
    >
     
    Aaron Bertrand [MVP], Oct 24, 2003
    #8
  9. middletree

    middletree Guest

    Well, had typed:

    <select>

    into the textarea, and verified that this is what went in, both by
    response.write, and looking into SQL Server.




    "Aaron Bertrand [MVP]" <> wrote in message
    news:...
    > Then my guess is there are no < or > characters for replacement? Compare
    > this to the completed SQL statement:
    >
    > Response.write(request.form("whatever_the_variable_was"))
    >
    >
    >
    >
    > "middletree" <> wrote in message
    > news:#SAj7$...
    > > "Aaron Bertrand [MVP]" <> wrote in message
    > > news:...
    > > > > when I open it up in SQL Server,
    > > >
    > > > Where in SQL Server? Don't use Enterprise Manager for viewing data

    > (e.g.
    > > > Return all rows). It is liable to do all sorts of funky things in

    order
    > > to
    > > > present the data to you in a "friendly" way (for some other issues see
    > > > http://www.aspfaq.com/2455). Run a SELECT query in Query Analyzer.

    > Also,
    > > > response.write(sql) to make sure the replacements were done.

    > >
    > > As it turned out, the Query A vs. Ent Mgr were both displying correctly,

    > but
    > > I will make sure i view the data correctly from now on. But the problem

    is
    > > that the replace function is not working. I verified this per your
    > > suggestion with the response.write statement. It does just fine with the
    > > <br> and quotes. Very puzzling and frustrating
    > >
    > >
    > > >
    > > > Another piece of friendly advice: store the statement as is, and use
    > > > Server.HTMLEncode when you *retrieve* and *display* it. HTML

    formatting
    > > has
    > > > little use/place inside the database.
    > > >
    > > >

    > >
    > >

    >
    >
     
    middletree, Oct 24, 2003
    #9
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. sean
    Replies:
    2
    Views:
    570
    Kevin Spencer
    Dec 2, 2003
  2. compuglobalhypermeganetz0r

    adapter update problem Syntax error in INSERT INTO statement.

    compuglobalhypermeganetz0r, Dec 8, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    854
    compuglobalhypermeganetz0r
    Dec 8, 2003
  3. Mark Sandfox
    Replies:
    1
    Views:
    3,445
    Mark Sandfox
    May 7, 2004
  4. Saber
    Replies:
    5
    Views:
    549
    Jo Inferis
    Jul 20, 2004
  5. Mark Richards
    Replies:
    3
    Views:
    348
    Tad McClellan
    Nov 18, 2007
Loading...

Share This Page