The Question:
I'm trying to insert a record into database that has an apostrophe.
When I build the SQL string it generates a syntax error in the INSERT command.
How can I insert a record that has an apostrophe in some fields?
The Answer:
Let's try to reproduce the problem. We are going to build a
simple ASP.NET Web Application.
To do this, start Visual Studio.NET first and after that select
Visual Basic.NET Project from the Project Types Pane and select ASP.NET Web
Application template from the Template Pane. Change the default name of the
Web Application to SingleQuoteErrorApp. On the Web Form Page (WebForm1.aspx)
add 4 Labels (lblTitleID, lblTitle, lblError, and lblOK), 3 TextBoxes (txtTitleID,
txtTitle, and txtError which needs TextMode property to be set to MultiLine),
and 2 Buttons (btnSubmit and Button1 which button will clears Error message
displayed into txtError TextBox). Set Text Property of the lblOK Label to be
"Insert without Error". Into the Property Window, set the Visible Property of
lblOk, lblError, and txtError Labels to be FALSE.
Your Web Form should looks like this:
We are going to use the SQL Pubs Database, which is installed when you installed first
time the SQL Server or the MSDE Engine.
Now, we need to add some code that will handle Click events.
Let's do this first for the Submit button (btnSubmit). Code that you will need to add is:
Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Dim strTitle As String = "The Busy Executive's .NET Guide"
Dim pInsertQuery As String = _
"INSERT INTO Titles (title_id, title)" & _
" VALUES('" & txtTitleID.Text.ToString & "', '" & Replace(txtTitle.Text.ToString, "'", "''") & "')"
" VALUES('" & txtTitleID.Text.ToString & "', '" & txtTitle.Text.ToString & "')"
Dim strConnectionString As String
strConnectionString = "Data Source=localhost;" & _
"Integrated Security=SSPI;Initial Catalog=pubs"
Dim pConn As New SqlConnection(strConnectionString)
Dim cmd As New SqlCommand()
cmd.CommandText = pInsertQuery
cmd.Connection = pConn
Try
pConn.Open()
cmd.ExecuteNonQuery()
lblOK.Visible = True
Catch err As Exception
lblError.Visible = True
txtError.Visible = True
txtError.Text = err.Message.ToString
End Try
pConn.Close()
End Sub.
|
and for Button1_Click event is:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
lblError.Visible = False
txtError.Visible = False
lblOK.Visible = False
End Sub.
|
In the Solution Explorer Window, right click on the WebForm1.aspx and select
Set as Start Page option. Now, it's time to build the Solution. Click on Build menu and select Build Solution
or just hit F5 to build and start the Solution. You can enter some TitleID value and in the Title field enter
the following title: The Busy Executive's .NET Guide.
Click on the Submit button and Error will show-up.
This can happend anytime when you are building SQL strings that
include string fields in which users can enter text with apostrophe (Single
Quote). When you are building SQL strings, strings values are enclosed within
a pair of single quotes(apostrophe). When you run the query, MS SQL Server will
interpret single quote inside the SQL String as the End of that string ('The
Busy Executive') and will have a problem with the rest of SQL string because
there is one more single quote at the end of that string, and SQL Server will
expect to fine pair of single quotes. Here is a SQL string with values from
the Web Form Page:
"INSERT INTO Titles (title_id, title)" & _
" VALUES('DOTNET', 'The Busy Executive's .NET Guide')"
|
This problem can exist whenever we have form fields that accept
characters, and users are free to enter single quotes in those fields.
The question is what to do in this case. How to modify SQL statement
that will allow users to enter Single Quotes in text fields on Web Forms or
into text fields on Windows Forms and still to be able to insert those records
into a SQL Server database?
The solution for this is to replace Single Quote characters
with two Single Quote characters.
To do this we need to change one line of code from btnSubmit_Click
event code. The old line that needs to be changed is:
"INSERT INTO Titles (title_id, title)" & _
" VALUES('" & txtTitleID.Text.ToString & "', '" & txtTitle.Text.ToString & "')"
|
To fix this problem we are going to use REPLACE function from
VB.NET. The new format for that line that will prevent Single Quote Error is:
"INSERT INTO Titles (title_id, title)" & _
" VALUES('" & txtTitleID.Text.ToString & "', '" & Replace(txtTitle.Text.ToString, "'", "''") & "')"
|
You need to replace this line of code, rebuild the solution,
and start the solution again. Now, if you try to enter the same title as before,
there will be no error at all, because now two single quotes in a row will signal
to SQL Server that this is not end of string but actually Single Quote character
that SQL Server needs to store it in the database. Here is a screen print of
the same application with new INSERT statement line:
Notice that now there is a no error at all. Everything works OK.
Maybe a good practice would be, to avoid this from happening
in your application, to include REPLACE function whenever you are building SQL
strings. This way, you will be sure that Single Quote in Strings will not cause
the Error in the Application. This can be overlooked when you develop an application
and later-on users will start complaining about errors when they try to enter
Single Quote (apostrophe) in text fields on the Form. Think about this when
you are developing applications, because these kind of things can supprise you
later.
Summary
I hope that this article will help you avoid "Single Quote (apostrophe) Error"
and that your application will be ready to accept Single Quote (apostrophe)
in text fields.