SiteMap  | About    

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.

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) & "')"

            adapter.InsertCommand = New SqlCommand(sql, connection)
            MsgBox("Row inserted !! ")
        Catch ex As Exception
        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 (C) 2019    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.