www.webdeveloper.com
Results 1 to 6 of 6

Thread: Quotes in SQL insert

  1. #1
    Join Date
    Feb 2005
    Location
    Belgium
    Posts
    79

    Quotes in SQL insert

    Hi

    I'm having problems with an insert statement.

    INSERT INTO tabel (bla, bla2, bla3) VALUES (number, 'text', number)

    in the field bla2 I am inserting a text. But sometimes the text will contain a single quote (frequently used in dutch). If a single quote is inserted in the text the SQL funtion will not work. What do I need to adjust?

    thx

  2. #2
    Join Date
    Oct 2005
    Location
    NYC
    Posts
    108
    Quote Originally Posted by Diedtje
    Hi

    I'm having problems with an insert statement.

    INSERT INTO tabel (bla, bla2, bla3) VALUES (number, 'text', number)

    in the field bla2 I am inserting a text. But sometimes the text will contain a single quote (frequently used in dutch). If a single quote is inserted in the text the SQL funtion will not work. What do I need to adjust?

    thx
    replace it with 2 quotes. Like this,

    If your query, select * from test returns '4'4'

    Then you will will do this, select * from test where testCol= '''4''4'''

    basically, replace single quote with two quote. but dont forget, the single quote to put around 'text' is still needed as a single quote.

    -Tak
    Last edited by takkie; 10-11-2005 at 09:19 AM.

  3. #3
    Join Date
    Feb 2005
    Location
    Belgium
    Posts
    79
    So the double Quote thing will not work because of the single quotes needed around the text. When I program I have to start the SQL string with double quote

    string SQL = "INSERT INTO tabel (bla, bla2, bla3) VALUES (number, 'text', number)"

    See my problem?

    thx

  4. #4
    Join Date
    Apr 2005
    Posts
    634
    diedtje...
    tak's suggesting wasn't double quote... it was 2 single quotes.

    say if you make a function:
    function string stringSwap (string txt) {
    Replace(txt,"'", "''")
    }

    and on ur sql statmnt, would look something like this:
    text = stringSwap(text);
    string SQL = "INSERT INTO tabel (bla, bla2, bla3) VALUES (number, text, number)"


    what that code does, is to replace ' with '' <-- 2 single quotes, not double quotes.


    another way to do it is use parameterized command...
    i.e:
    sql = "INSERT INTO tabel (bla, bla2, bla3) VALUES (?,?,?)"

    oldDBConnection con - new OleDbConnection(--ur connection string--);
    OleDbCommand cmd = new OleDbCommand(sql, con);

    cmd.Parameters.Add("?",number);
    cmd.Parameters.Add("?",text);
    cmd.Parameters.Add("?",number2);

    then execute ur ado.net object....

  5. #5
    Join Date
    Feb 2005
    Location
    Belgium
    Posts
    79
    So how do I have to write the SQL??

    this is the SQL that i have now: the problem is with txtText.text


    string SQLLog2 = "INSERT INTO tbTranslationTexts(transtext_fk_lang_id, transtext_fk_trans_id, transtext_text, transtext_fk_class_id, transtext_status, transtext_keywords, transtext_title) ";
    SQLLog2 = SQLLog2 + "VALUES(1," + lastNr + ", '" + txtText.Text + "', 0, -1, '" + txtKeywords.Text + "','" + txtTitel.Text + "')";

  6. #6
    Join Date
    Apr 2005
    Posts
    634
    i would make a function that make sure the integrity of your input text...
    like this (i just make this up on the fly, u need to check ur syntax):
    Code:
    Function sqlString(x)
      If IsEmpty(x) Then
        sqlString = "NULL"
      ElseIf IsNull(x) Then
        sqlString = "NULL"
      ElseIf x = "" Then
        sqlString = "NULL"
      Else
        x = Trim(x)
        x = Replace(x,"'", "''")
        sqlString = x
     End If   
    End Function
    now.. all that function does, is to put NULL in ur sql statmnt when receive something empty, space, and replace a quote ' with 2 single quotes ' '.

    and now i want that function to run through my variables to make sure my text is good for the sql stmnt
    like this:
    Code:
    lastNr = sqlString(lastNr);
    txtText.Text = sqlString(txtText.Text);
    txtKeywords.Text = sqlString(txtKeywords.Text);
    txtTitel.Text = sqlString(txtTitel.Text);
    now lastNr, txtText.Text and txtKeywords.Text, and txtTitel.Text are in place, u can generate ur sql string...
    Code:
    string SQLLog2 = "INSERT INTO tbTranslationTexts(transtext_fk_lang_id, transtext_fk_trans_id, transtext_text, transtext_fk_class_id, transtext_status, transtext_keywords, transtext_title) ";
    
    SQLLog2 = SQLLog2 + "VALUES(1,lastNr,txtText.Text,0,-1,txtKeywords.Text,txtTitel.Text);
    excute ur sql statment....

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles