Net-informations.com
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..

  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



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






net-informations.com (C) 2017    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.