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?
thxHi
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.
-TakSo 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?
thxdiedtje...
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....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 + "')";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):
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:
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...
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....
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?
thxHi
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.
-TakSo 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?
thxdiedtje...
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....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 + "')";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):
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:
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...
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....