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