SQL INSERT Problem in ASP…

Results 1 to 4 of 4

Thread: SQL INSERT Problem in ASP…

  1. #1
    Lisa B. Guest

    Default SQL INSERT Problem in ASP…

    I’m having a problem INSERTING a value in an SQL statement that contains an apostrophe ( ‘ ). This, I assume, is obviously because the values in SQL are defined by being between apostrophes, so my value containing the apostrophe will through the statement off. For example:<BR>&#060;% Dim MyValue, HisValue,<BR>MyValue = “My Treasures”<BR>HisValue = “Dave’s Treasures”<BR><BR>SQL = “INSERT INTO DataTable(Field1, Field2) VALUES(‘”& MyValue &”’, ‘”& HisValue &”’)” %&#062;<BR>Since HisValue contains an apostrophe between Dave and the “s”, I get an error message when I try to execute the script.<BR><BR>I’m pretty sure I’m not the first one who’s experienced this problem, so I wonder if anyone out there has a SIMPLE quick fix to this problem? Or must I create a special complicated “function” that will remove all apostrophes from all my string values?<BR><BR>Anybody?<BR><BR>Lisa

  2. #2
    Simhan Guest

    Default RE: SQL INSERT Problem in ASP…

    There are many ways of tackling this problem. The simplest of them is to escape the single quote. And the escape character in SQL is &#039 itself.<BR><BR>So try using this<BR><BR>MyValue = Replace(MyValue,"&#039","&#039&#039")<BR>HisValue = Replace(HisValue,"&#039","&#039&#039")<BR><BR>I think your problem is solved now.<BR><BR>ps: while querying the database and displaying the results, you don&#039t have to escape the single quote again. SQL does that automatically.<BR><BR>Regards & Good Luck<BR><BR>Simhan<BR>simhan@3rdagenda.com

  3. #3
    Join Date
    Dec 1969

    Default RE: SQL INSERT Problem in ASP…

    look at http://support.microsoft.com/support/kb/articles/Q178/0/70.ASP<BR><BR>for code to replace &#039 and &#124<BR><BR>Peter

  4. #4
    Lisa B. Guest

    Default RE: SQL INSERT Problem in ASP…

    Thank you, both for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts