Error while inserting data in to sql server from vb.net 2003
Hello,
I have a problem while inserting data in to sql server from vb.net. the code given below works perfectly but when i try to insert string with single quotes it is not working properly. Ex. string: 'This is a sample question'
myCommand =
New SqlCommand("Insert into " & TableName() & " (Questions,Option1,Option2" & _",Option3,Option4,CorrectAnswer,LODType) values ('" & strQuestion & "','" & strOption1 & _
"','" & strOption2 & "','" & strOption3 & "','" & strOption4 & "','" & strCAns & "','" & strLODType & "')", conn)
I think the problem in the insert query is single quotes which contains in the string which we are inserting as well as in the insert statement. like this:
dim str as string
insert into tablename(question) values(' 'This is a sample string with single quotation mark.' ')
Is there any way to insert data which will accept all the strings..
Thank you in Advance,
Jagan
just to add, a thing I noticed is you have 2 single quote which you dont need in the example:
insert into tablename(question) values(' 'This is a sample string with single quotation mark.' ')
should be:
insert into tablename(question) values('This is a sample string with single quotation mark.')
yes i know that... but the problem is .. i want to insert a statement which have single quotes at the begining and at the end of the statement., ex. statement.. ' this is a sample statement which i want to insert in db with single quotes at the begining and at end.'
ok I see, thats better!
Looking back at the code, what you have done seems ok - what happens if you place 2 quotes instead (at the beginning and end)? I tried doing this in C# (just single quotes) and works fine, but I know you are using VB.NET
myCommand = New SqlCommand("values (''" & strQuestion & "'','" & strOption1 & "'','" & strOption2 & "'','" & strOption3 & "'','" & strOption4 & "'','" & strCAns & "'',''" & strLODType & "'')", conn)
It shouldnt matter if you are using VB.NET or C#, since its being placed in a string - I tried it with inserting 'test' and it inserted just like that 'test' with the single quotes around it
 ahmedilyas wrote: |
ok I see, thats better! Looking back at the code, what you have done seems ok - what happens if you place 2 quotes instead (at the beginning and end)? I tried doing this in C# (just single quotes) and works fine, but I know you are using VB.NET myCommand = New SqlCommand("values (''"& strQuestion & "'','" & strOption1 & "'','" & strOption2 & "'','" & strOption3 & "'','" & strOption4 & "'','" & strCAns & "'',''" & strLODType & "'')", conn) It shouldnt matter if you are using VB.NET or C#, since its being placed in a string - I tried it with inserting 'test' and it inserted just like that 'test' with the single quotes around it |
|
Interesting. I just tried the same thing by writing an INSERT Query in
SQL Server Enterprise Manager and it errored out on the string with
single quotes (as I suspected it would). It did, however, allow me to
insert the text without the additional single quotes. Is there a reason
why you want to store the string with single quotes? Personally, If I
needed them I'd just add them after having read the string from SQL.
To input your text use command parameters in the command text. This will allow the text with quotes and is also best practice for commands of type text [default] . Stored procedures would generally be better. Note my surname as the parameter value.
Dim cmd As New SqlClient.SqlCommand
Dim con As New SqlClient.SqlConnection
cmd.Connection = con
con.ConnectionString = "[Connection String]"
cmd.CommandText = "Insert Into DBTable (FirstName,Surname) Values(@FirstName,@Surname)"
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 20).Value = "Martin"
cmd.Parameters.Add("@Surname", SqlDbType.VarChar, 20).Value = "O'Keefe"
Try
con.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
System.Diagnostics.Debug.WriteLine(ex.ToString)
Finally
con.Close()
con.Dispose()
cmd.Dispose()
End Try
Regards,
Martin.
