sql error -saying syntex error near low'.

[shifar]

I have a problem when I try to save a textbox.text in to SQL sever with ADO .net using VB.net I cannot pass ;' charectors say
eg. low'
I get the error saying syntex error near low'.

I use the sql Insert commnad to insert the data.

Do I need to use another method. I also upgraded to MSDE 8 but still problem persists.
any ideas.
thanks

 

[SonOfEmidec1100]

Can you post your sql statement, this will give a better understanding of whats wrong.

 

[rdroske]

You can't include a single single quote in the SQL statement because that is the SQL string terminotor. Replace all single quotes in the string with '' (two of them before using it to build the SQL.

 

[shifar]

Sorry for the miunderstanding:
with all declarations;
cmd.CommandText = "INSERT INTO [" & EXAM & "] (qno,question,ans1,ans2,ans3,ans4) VALUES (" & x & " ,'" & RichTextBox1.Text & "', '" & RichTextBox2.Text & "','" & RichTextBox3.Text & "','" & RichTextBox4.Text & "','" & RichTextBox5.Text & "')"
sql statment work fine if I did not type ;' charectors Richtextbox once I typed and try to save I get this error.
datatype qno =int
all others are varchar.
Thanks

 

[SonOfEmidec1100]

SQL query is OK in itself, refer previous response re single quotes. Using them is bad news you will have other issues later. Use replace in code to either remove or replace them, to replace I normally use the ` character (chr(96)). For example

instead of RichTextBox1.Text
use Replace(RichTextBox1.Text, "'","`&quot

Hope this helps.

 

[rdroske]

Or two single quotes like I said above. You can use single quotes, just need to use two when you are not terminating the string.

 

[shifar]

Thank;SonOfEmidec1100
Can you post full sql query with ` .
I belive the sql query is ok.
If I typed in Richtextbox say: The value'...
and I tried to save them in database I get this error
Message line 1: Incorrect syntax error near value'. I think saving special charectors like '; is a problem not in the SQl statments.
Have any clues?

 

[zhavic]

An example:

Let's say the value entered into RichTextBox3 is

Value 'something' blah'blah

That value contains 3 chars '
You need them translate to CHAR(39) function, so the value you pass to SQL will be:

"'Value ' + CHAR(39) + 'something' + CHAR(39) + 'blah' + CHAR(39) + 'blah'"

so as value inserted use this: ( I used REPLACE() function, in VB there is similar function )
( replace character ' with string '+CHAR(39)+' )
"'" & REPLACE( RichTextBox3, "'", "'+CHAR(39)+'" ) & "'"

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[shifar]

Thank you Zhavic:
I use the following method and it worked.
Dim cmd As New SqlCommand("INSERT INTO tablename VALUES (@field1)", conn)

cmd.Parameters.Add(New SqlParameter("@field1", textbox1.text)

many thanks.