how to wrote ' inside string in VB.net

[cjcclee]

Dim strSQL As String = "Select * from Doctors where first_name= '" & First_name.Text & "' and last_name= '" & Last_name.Text & "' "
Dim da As SqlDataAdapter = New SqlDataAdapter(strSQL, DBConn)
Dim ds As DataSet = New DataSet
da.Fill(ds)

My problem is: Last_name may contain ', for example O'Brien
the query will fail. strSQl= "select * from Doctors where last_name=O'Brien"

how can I wrote ' inside the string? for my textbox : Last_name.Text
it may contain ', what 's correct syntax to write my query?
Thanks!

[Harsh Gupta]

Instead of ', you can write chr(39). 39 is the ASCII value of '

[wild_bill]

Dim sqlCmd As New SqlClient.SqlCommand("Select * from Doctors where first_name=@First and last_name=@Last")
            sqlCmd.Parameters.Add("@First", First_name.Text)
            sqlCmd.Parameters.Add("@Last", Last_name.Text)

[cjcclee]

thanks for your reply.
but How can I write query? strSQL="select * from Doctors where last_name=
' " & Last_name.text & " ' "

Last_name.text is the texbox value. sometimes it may contain ', how can I do it? Thanks!

[cjcclee]

sorry, I did not see last reply. It works. I appreciated your help. Thanks!

[jmcilhinney]

Just one point to note: if you're using VB 2005 it will be AddWithValue rather than Add. Please specify your version in future threads. Also, don't forget to resolve your thread from the Thread Tools menu if your question has been answered.

[Negative0]

And just for future reference your problem is a SQL problem, not a VB problem. To get around this, you can simply replace all single quotes, with two single quotes. So in your example, O'Brien would be inserted as O''Brien, and the SQL statement would work.

To do it in your code, it is as simple as:

Dim strSQL As String = "Select * from Doctors where first_name= '" & First_name.Text.Replace("'","''") & "' and last_name= '" & Last_name.Text.Replace("'","''") & "' "

[Shuja Ali]

And just for future reference your problem is a SQL problem, not a VB problem. To get around this, you can simply replace all single quotes, with two single quotes. So in your example, O'Brien would be inserted as O''Brien, and the SQL statement would work.

To do it in your code, it is as simple as:

This is not a good idea. Using Parametrized queries is the proper and correct solution.

[Mr.No]

Instead of handling single quotes in dynamically built SQL statements I'd recomment using the SqlCommand object and set the parameter values. If this is not feasible then a function like below might help.

' This should be in a Utils class and invoked as Utils.HandleQuote("O'Brien")
 
public shared Function HandleQuote(byval data as string) As String
  ' replace single occurrences of single quote with two single quotes
  ' For O'Brien function will return 'O''Brien'
  Return "'" & String.Replace(data, "'", "''") & "'"
End Function