dyarosh
asked on
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','2008 15','ACTUA L HOURS WORKED',#7/18/2008#,71.9,' DUES FT')'.
syntax error (missing operator) in query expression ''Le'Bue','12345678','2008
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
Try surrounding the items with double quotes instead of single quotes.
mx
mx
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 & "')"
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 & "')"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
urm, forgot to give credit to mx regarding using double quotes. If u go that approach, use Convert.ToChar
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.
ASKER
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.
oQuery = "INSERT INTO " & tblName & " (SSN) Values(?)"
oConn = New OleDbConnection(oConnect)
oComm = New OleDbCommand(oQuery, oConn)
oComm.Parameters.Add("@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
inserterror.png
spelling of Values?
Also location of db. Just specifying db name, I would assume it would be current path, so you might wanna put full path
ASKER
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.
No worries. Using the parameters makes life easier especially for the longer sql statements.
SSN = SSN.Replace("'","''")
FirstName = FirstName.Replace("'","''"
....
before:
oQuery = "INSERT INTO " & tblName & " (SSN, Fname, Lname, PersNo, Payroll, WageType, PaymentDate, Hours, EmpType) "