Solved

INSERT SQL Statement fails when name field contains an apostrophe

Posted on 2009-04-08
11
236 Views
Last Modified: 2013-11-26
I am using VB.NET and Visual Studio to develop a windows form application.  One of the functions involves reading data from an external file into an MS Access Table that is created programatically.  The problem that I'm having is if the name field contains an ' the SQL Statement fails.  When using a table defined in my dataset, I can enter names wilth ' and it takes it with no error.  How can I get ' to be accepted in my SQL statement?  The code is listed below.  Error message is:

syntax error (missing operator) in query expression ''Le'Bue','12345678','200815','ACTUAL HOURS WORKED',#7/18/2008#,71.9,'DUES FT')'.
Public Function InsertHours(ByVal tblName As String) As Boolean
 

        'Define the connectors

        Dim oConn As OleDbConnection

        Dim oComm As OleDbCommand

        Dim oConnect, oQuery As String

        Dim recAffected As Integer = 0
 

        'Define connection string

        oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=pfms.mdb"
 

        'Define the query string the creates the table

        oQuery = "INSERT INTO " & tblName & " (SSN, Fname, Lname, PersNo, Payroll, WageType, PaymentDate, Hours, EmpType) "

        oQuery = oQuery & "values('" & SSN & "', '" & FirstName & "', '" & LastName & "', '" & PersNo & _

                    "', '" & PayrollPeriod & "', '" & WageType & "', #" & PaymentDate & "#, " & Hours & ", '" & EmployeeType & "')"
 

        ' Instantiate the connectors

        oConn = New OleDbConnection(oConnect)

        oComm = New OleDbCommand(oQuery, oConn)
 

        'Try connecting and crate the table

        Try
 

            'Open the connection

            oConn.Open()
 

            'Perform the Non-Query

            recAffected = oComm.ExecuteNonQuery()
 

            'Close the connection

            oConn.Close()
 

        Catch ex As OleDb.OleDbException

            'Show error message and return failure

            MsgBox(ex.Message & vbCrLf & ex.StackTrace)

            Return False

        Catch ex As Exception
 

            'Show error message and return failure

            MsgBox(ex.Message & vbCrLf & ex.StackTrace)

            Return False
 

        Finally
 

            'Dispose the connector objects

            If Not (oConn Is Nothing) Then

                oConn.Dispose()

                oConn = Nothing

            End If

            If Not (oComm Is Nothing) Then

                oComm.Dispose()

                oComm = Nothing

            End If
 

        End Try
 

        'Return success

        If recAffected = 1 Then

            Return True

        Else

            Return False

        End If
 

    End Function

Open in new window

0
Comment
Question by:dyarosh
11 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 24103796
try double the apostrophe for Char fields, like:

SSN = SSN.Replace("'","''")
FirstName = FirstName.Replace("'","''")

....


before:

oQuery = "INSERT INTO " & tblName & " (SSN, Fname, Lname, PersNo, Payroll, WageType, PaymentDate, Hours, EmpType) "
0
 
LVL 75
ID: 24103800
Try surrounding the items with double quotes instead of single quotes.

mx
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24104347
have you tried using parameter queries instead?

You could use Convert.ToChar(34) to wrap in ddouble quotes instead of single quotes. Example below just wraps with the two name fields

        oQuery = "INSERT INTO " & tblName & " (SSN, Fname, Lname, PersNo, Payroll, WageType, PaymentDate, Hours, EmpType) "
        oQuery = oQuery & "VALUES ('" & SSN & "', " & Convert.ToChar(34) & FirstName & Convert.ToChar(34) & ", " & Convert.ToChar(34) & LastName & Convert.ToChar(34) & ", '" & PersNo & _
                    "', '" & PayrollPeriod & "', '" & WageType & "', #" & PaymentDate & "#, " & Hours & ", '" & EmployeeType & "')"

0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 24104607
A very simple example using parameters, It saves you having to worry about quotes.

'Define query, place ? where you need to put in your variables
        Dim sSql As String = "INSERT INTO tblTest (Field1, Field2, Field3) VALUES (?, ?, ?)"

'In your command object, add parameters like this. Field1, Field2, Field3 represent column names you want to fill. You just need to specify datatype
        Dim command As New OleDbCommand(sSql, conn)
        command.Parameters.Add("@Field1", OleDbType.Char).Value = "Mango"
        command.Parameters.Add("@Field2", OleDbType.Char).Value = "Chutney"
        command.Parameters.Add("@Field3", OleDbType.Numeric).Value = 99

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24104618
urm, forgot to give credit to mx regarding using double quotes. If u go that approach, use Convert.ToChar
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24105811
By the way, the reason for error is that appostrophe ' is used to enclose char values in SQL so SQL expects a pair of ' ' enclosing a char/string value but in your case the closing ' is missing hence the error.
0
 

Author Comment

by:dyarosh
ID: 24106921
When trying to use parameters, I'm getting a syntax error on my Insert command.  I'm not sure what I'm doing wrong.  I've included a copy of the code.  To try and find the problem, I reduced the insert statement to:
oQuery = "INSERT INTO " & tblName & " (SSN) Values(?)"

oConn = New OleDbConnection(oConnect)
oComm = New OleDbCommand(oQuery, oConn)
oComm.Parameters.Add("@SSN", OleDbType.Char).Value = SSN
The error I'm getting is shown in the attached file.
 

Public Function InsertHours(ByVal tblName As String) As Boolean
 

        'Define the connectors

        Dim oConn As OleDbConnection

        Dim oComm As OleDbCommand

        Dim oConnect, oQuery As String

        Dim recAffected As Integer = 0
 

        'Define connection string

        oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=pfms.mdb"
 

        'Define the query string the creates the table

        oQuery = "INSERT INTO " & tblName & " (SSN) " & _

                    "Vaules (?)"
 

        ' Instantiate the connectors

        oConn = New OleDbConnection(oConnect)

        oComm = New OleDbCommand(oQuery, oConn)

        oComm.Parameters.Add("@SSN", OleDbType.Char).Value = SSN
 

        'Try connecting and crate the table

        Try
 

            'Open the connection

            oConn.Open()
 

            'Perform the Non-Query

            recAffected = oComm.ExecuteNonQuery()
 

            'Close the connection

            oConn.Close()
 

        Catch ex As OleDb.OleDbException

            'Show error message and return failure

            MsgBox(ex.Message & vbCrLf & ex.StackTrace)

            Return False

        Catch ex As Exception
 

            'Show error message and return failure

            MsgBox(ex.Message & vbCrLf & ex.StackTrace)

            Return False
 

        Finally
 

            'Dispose the connector objects

            If Not (oConn Is Nothing) Then

                oConn.Dispose()

                oConn = Nothing

            End If

            If Not (oComm Is Nothing) Then

                oComm.Dispose()

                oComm = Nothing

            End If
 

        End Try
 

        'Return success

        If recAffected = 1 Then

            Return True

        Else

            Return False

        End If
 

    End Function

Open in new window

inserterror.png
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24106938
spelling of Values?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24106942
Also location of db. Just specifying db name, I would assume it would be current path, so you might wanna put full path
0
 

Author Closing Comment

by:dyarosh
ID: 31568361
Thanks for your help.  Don't know how many times I looked at the Insert statement and still did not see that I misspelled Values.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24107198
No worries. Using the parameters makes life easier especially for the longer sql statements.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now