How to handle single quotes
The Microsoft .NET Data Providers consist of a number of classes used to connect to a data source, execute commands, and return records. While we are inserting data into the database table we came across the messages like..
System.Data.SqlClient.SqlException: Incorrect syntax near Incorrect syntax near '..'. Unclosed quotation mark after the character string ')'.
We are getting the above error message because there is a problem while inserting single quoted character using in sql statements. For ex: We want to insert a string like "Microsoft's" , the system shows the above error messages, because we are trying to insert a single quoted character using in sql statement.
We can solve this problem by replace any single quote with two quotes like "Microsoft''s" .
insert into tablename (field1) values('Microsoft''s')
For avoiding each time adding another single quote to the string , here we implement a function to replace the string with two single quotes.
Public Function convertQuotes(ByVal str As String) As String
convertQuotes = str.Replace("'", "''")
End Function
Full Source VB.NET
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim connection As SqlConnection
Dim adapter As New SqlDataAdapter
Dim sql As String
Dim field1 As String = "Microsoft's"
Dim field2 As String = "VB.NET"
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
connection = New SqlConnection(connetionString)
sql = "insert into tablename (field1,field2) values('" & convertQuotes(field1) & "','" & convertQuotes(field2) & "')"
Try
connection.Open()
adapter.InsertCommand = New SqlCommand(sql, connection)
adapter.InsertCommand.ExecuteNonQuery()
MsgBox("Row inserted !! ")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
'here is the function to handle single quoted characters
Public Function convertQuotes(ByVal str As String) As String
convertQuotes = str.Replace("'", "''")
End Function
End Class