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..
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" .
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 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) & "')"
adapter.InsertCommand = New SqlCommand(sql, connection)
MsgBox("Row inserted !! ")
Catch ex As Exception
'here is the function to handle single quoted characters
Public Function convertQuotes(ByVal str As String) As String
convertQuotes = str.Replace("'", "''")