inserting apostrophes into DB?

Discussion in 'ASP General' started by Lord Merlin, Jul 1, 2004.

  1. Lord Merlin

    Lord Merlin Guest

    When I insert info into a DB from a form, it cuts the string off at the
    first apostrophe (").

    How would I make it insert the data as-is, with the apostrophes?
    Here is the code used to insert the Data:



    strsubject = " " & GetFormData("strsubject") & " "
    incident = " " & GetFormData("incident") & " "
    solution = " " & GetFormData("solution") & " "


    InsertQuery="INSERT INTO comments " &_
    "(NUserID,thedate, currentdate, commenttype, userid, username,
    supplier, person, subject, description, solution, industry, country,
    province, city, area, emailsent, clientresponse, compliment,
    complaint,telno,subscriber)" &_
    " VALUES (" & Session("NUserID") & ", '" &_
    thedate & "','" &_
    currentdate & "','" &_
    strcomment & "'," &_
    Session("NUserID") & ",'" &_
    alias & "','" &_
    Replace(companyname,"'","''") & "','" &_
    person & "','" &_
    Replace(strsubject, "'", chr(39) & chr(39)) & "','" &_
    Replace(incident, "'", chr(39) & chr(39)) & "','" &_
    Replace(solution, "'", chr(39) & chr(39)) & "','" &_
    industry & "','" &_
    country & "','" &_
    province & "','" &_
    city & "','" &_
    area & "','" &_
    "no" & "','" &_
    "" & "'," &_
    compliment & "," &_
    complaint & ",'" &_
    telno & "','" & ticksubscriber & "');Select @@IDENTITY as id;"

    The problem lies with these three:
    strsubject, incident, solution

    What can I do?

    --


    Kind Regards
    Rudi Ahlers
    +27 (82) 926 1689

    Greater love has no one than this, that he lay down his life for his friends
    (John 15:13).
     
    Lord Merlin, Jul 1, 2004
    #1
    1. Advertising

  2. Lord Merlin

    Steven Burn Guest

    Server.HTMLEncode() ?

    --

    Regards

    Steven Burn
    Ur I.T. Mate Group
    www.it-mate.co.uk

    Keeping it FREE!


    "Lord Merlin" <_SPAM> wrote in message
    news:cc1s0m$o7o$...
    > When I insert info into a DB from a form, it cuts the string off at the
    > first apostrophe (").
    >
    > How would I make it insert the data as-is, with the apostrophes?
    > Here is the code used to insert the Data:
    >
    >
    >
    > strsubject = " " & GetFormData("strsubject") & " "
    > incident = " " & GetFormData("incident") & " "
    > solution = " " & GetFormData("solution") & " "
    >
    >
    > InsertQuery="INSERT INTO comments " &_
    > "(NUserID,thedate, currentdate, commenttype, userid, username,
    > supplier, person, subject, description, solution, industry, country,
    > province, city, area, emailsent, clientresponse, compliment,
    > complaint,telno,subscriber)" &_
    > " VALUES (" & Session("NUserID") & ", '" &_
    > thedate & "','" &_
    > currentdate & "','" &_
    > strcomment & "'," &_
    > Session("NUserID") & ",'" &_
    > alias & "','" &_
    > Replace(companyname,"'","''") & "','" &_
    > person & "','" &_
    > Replace(strsubject, "'", chr(39) & chr(39)) & "','" &_
    > Replace(incident, "'", chr(39) & chr(39)) & "','" &_
    > Replace(solution, "'", chr(39) & chr(39)) & "','" &_
    > industry & "','" &_
    > country & "','" &_
    > province & "','" &_
    > city & "','" &_
    > area & "','" &_
    > "no" & "','" &_
    > "" & "'," &_
    > compliment & "," &_
    > complaint & ",'" &_
    > telno & "','" & ticksubscriber & "');Select @@IDENTITY as id;"
    >
    > The problem lies with these three:
    > strsubject, incident, solution
    >
    > What can I do?
    >
    > --
    >
    >
    > Kind Regards
    > Rudi Ahlers
    > +27 (82) 926 1689
    >
    > Greater love has no one than this, that he lay down his life for his

    friends
    > (John 15:13).
    >
    >
     
    Steven Burn, Jul 1, 2004
    #2
    1. Advertising

  3. Lord Merlin

    Mark Schupp Guest

    Are you sure it is truncating in the database (not on a form after it is
    extracted from the database)?

    The Replace statements in your code should take care of the apostrophes in
    the insert statement.

    --
    Mark Schupp
    Head of Development
    Integrity eLearning
    www.ielearning.com


    "Lord Merlin" <_SPAM> wrote in message
    news:cc1s0m$o7o$...
    > When I insert info into a DB from a form, it cuts the string off at the
    > first apostrophe (").
    >
    > How would I make it insert the data as-is, with the apostrophes?
    > Here is the code used to insert the Data:
    >
    >
    >
    > strsubject = " " & GetFormData("strsubject") & " "
    > incident = " " & GetFormData("incident") & " "
    > solution = " " & GetFormData("solution") & " "
    >
    >
    > InsertQuery="INSERT INTO comments " &_
    > "(NUserID,thedate, currentdate, commenttype, userid, username,
    > supplier, person, subject, description, solution, industry, country,
    > province, city, area, emailsent, clientresponse, compliment,
    > complaint,telno,subscriber)" &_
    > " VALUES (" & Session("NUserID") & ", '" &_
    > thedate & "','" &_
    > currentdate & "','" &_
    > strcomment & "'," &_
    > Session("NUserID") & ",'" &_
    > alias & "','" &_
    > Replace(companyname,"'","''") & "','" &_
    > person & "','" &_
    > Replace(strsubject, "'", chr(39) & chr(39)) & "','" &_
    > Replace(incident, "'", chr(39) & chr(39)) & "','" &_
    > Replace(solution, "'", chr(39) & chr(39)) & "','" &_
    > industry & "','" &_
    > country & "','" &_
    > province & "','" &_
    > city & "','" &_
    > area & "','" &_
    > "no" & "','" &_
    > "" & "'," &_
    > compliment & "," &_
    > complaint & ",'" &_
    > telno & "','" & ticksubscriber & "');Select @@IDENTITY as id;"
    >
    > The problem lies with these three:
    > strsubject, incident, solution
    >
    > What can I do?
    >
    > --
    >
    >
    > Kind Regards
    > Rudi Ahlers
    > +27 (82) 926 1689
    >
    > Greater love has no one than this, that he lay down his life for his

    friends
    > (John 15:13).
    >
    >
     
    Mark Schupp, Jul 2, 2004
    #3
  4. Lord Merlin

    Jeff Cochran Guest

    On Thu, 1 Jul 2004 22:24:18 +0200, "Lord Merlin"
    <_SPAM> wrote:

    >When I insert info into a DB from a form, it cuts the string off at the
    >first apostrophe (").
    >
    >How would I make it insert the data as-is, with the apostrophes?


    See this:

    Crossposting vs. Multiposting:
    http://www.blakjak.demon.co.uk/mul_crss.htm

    Then check the respone I posted in another group you posted in.

    Jeff


    > strsubject = " " & GetFormData("strsubject") & " "
    > incident = " " & GetFormData("incident") & " "
    > solution = " " & GetFormData("solution") & " "
    >
    >
    >InsertQuery="INSERT INTO comments " &_
    > "(NUserID,thedate, currentdate, commenttype, userid, username,
    >supplier, person, subject, description, solution, industry, country,
    >province, city, area, emailsent, clientresponse, compliment,
    >complaint,telno,subscriber)" &_
    > " VALUES (" & Session("NUserID") & ", '" &_
    > thedate & "','" &_
    > currentdate & "','" &_
    > strcomment & "'," &_
    > Session("NUserID") & ",'" &_
    > alias & "','" &_
    > Replace(companyname,"'","''") & "','" &_
    > person & "','" &_
    > Replace(strsubject, "'", chr(39) & chr(39)) & "','" &_
    > Replace(incident, "'", chr(39) & chr(39)) & "','" &_
    > Replace(solution, "'", chr(39) & chr(39)) & "','" &_
    > industry & "','" &_
    > country & "','" &_
    > province & "','" &_
    > city & "','" &_
    > area & "','" &_
    > "no" & "','" &_
    > "" & "'," &_
    > compliment & "," &_
    > complaint & ",'" &_
    > telno & "','" & ticksubscriber & "');Select @@IDENTITY as id;"
    >
    >The problem lies with these three:
    > strsubject, incident, solution
    >
    >What can I do?
     
    Jeff Cochran, Jul 2, 2004
    #4
  5. Replace ' with '' not two chr(39)s.

    Here is how I do it:

    Function doubleApost(str)
    doubleApost = Replace(str, "'", "''")
    End Function
    sql = "INSERT tbl(col) VALUES('" & doubleApost(Request.Form("foo")) & "')"

    If you are using SQL Server 2000, use SCOPE_IDENTITY, not @@IDENTITY. And
    consider using a stored procedure. Your string building will be much
    easier, especially if you use a parameters collection. And your chances for
    SQL injection attacks will go to nearly nil.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)




    "Lord Merlin" <_SPAM> wrote in message
    news:cc1s0m$o7o$...
    > When I insert info into a DB from a form, it cuts the string off at the
    > first apostrophe (").
    >
    > How would I make it insert the data as-is, with the apostrophes?
    > Here is the code used to insert the Data:
    >
    >
    >
    > strsubject = " " & GetFormData("strsubject") & " "
    > incident = " " & GetFormData("incident") & " "
    > solution = " " & GetFormData("solution") & " "
    >
    >
    > InsertQuery="INSERT INTO comments " &_
    > "(NUserID,thedate, currentdate, commenttype, userid, username,
    > supplier, person, subject, description, solution, industry, country,
    > province, city, area, emailsent, clientresponse, compliment,
    > complaint,telno,subscriber)" &_
    > " VALUES (" & Session("NUserID") & ", '" &_
    > thedate & "','" &_
    > currentdate & "','" &_
    > strcomment & "'," &_
    > Session("NUserID") & ",'" &_
    > alias & "','" &_
    > Replace(companyname,"'","''") & "','" &_
    > person & "','" &_
    > Replace(strsubject, "'", chr(39) & chr(39)) & "','" &_
    > Replace(incident, "'", chr(39) & chr(39)) & "','" &_
    > Replace(solution, "'", chr(39) & chr(39)) & "','" &_
    > industry & "','" &_
    > country & "','" &_
    > province & "','" &_
    > city & "','" &_
    > area & "','" &_
    > "no" & "','" &_
    > "" & "'," &_
    > compliment & "," &_
    > complaint & ",'" &_
    > telno & "','" & ticksubscriber & "');Select @@IDENTITY as id;"
    >
    > The problem lies with these three:
    > strsubject, incident, solution
    >
    > What can I do?
    >
    > --
    >
    >
    > Kind Regards
    > Rudi Ahlers
    > +27 (82) 926 1689
    >
    > Greater love has no one than this, that he lay down his life for his

    friends
    > (John 15:13).
    >
    >
     
    Aaron [SQL Server MVP], Jul 2, 2004
    #5
    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. Chris Huddle

    SQL and apostrophes

    Chris Huddle, Dec 10, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    762
    Steve C. Orr [MVP, MCSD]
    Dec 10, 2003
  2. Andy Fish

    HtmlEncode with apostrophes

    Andy Fish, Apr 6, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    8,777
    Nicole Calinoiu
    Apr 6, 2005
  3. darrel

    URLEncode doesn't like apostrophes?

    darrel, Jul 8, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    518
    darrel
    Jul 8, 2005
  4. mister-Ed

    Escaping apostrophes inserting into sql

    mister-Ed, Oct 5, 2007, in forum: ASP .Net
    Replies:
    1
    Views:
    402
    David Wier
    Oct 5, 2007
  5. Eric Osman
    Replies:
    2
    Views:
    432
    Eric Osman
    Apr 14, 2004
Loading...

Share This Page