[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

INSERT SQL Statement fails when name field contains an apostrophe

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
dyarosh
Asked:
dyarosh
1 Solution
 
Ryan ChongCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try surrounding the items with double quotes instead of single quotes.

mx
0
 
rockiroadsCommented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
rockiroadsCommented:
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
 
rockiroadsCommented:
urm, forgot to give credit to mx regarding using double quotes. If u go that approach, use Convert.ToChar
0
 
CodeCruiserCommented:
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
 
dyaroshAuthor Commented:
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
 
rockiroadsCommented:
spelling of Values?
0
 
rockiroadsCommented:
Also location of db. Just specifying db name, I would assume it would be current path, so you might wanna put full path
0
 
dyaroshAuthor Commented:
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
 
rockiroadsCommented:
No worries. Using the parameters makes life easier especially for the longer sql statements.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now