Why Do I Get This Error?

The error is shown below. The SQL syntax is shown below that. The SQL syntax works in the Access database. I have provided the code that produces the error. Why won't this code append the data to the Access table?

Error: System.Runtime.InteropServices.COMException (0x80040E14): Syntax error in INSERT INTO statement. at ADODB.ConnectionClass.Execute(String CommandText, Object& RecordsAffected, Int32 Options) at Containers.MasContainers.LoadSubData(Object[,]& avarData) in \\cagold01app01\WebSite\Containers\MasContainers.aspx.vb:line 48

strSQL: INSERT INTO tblSubscription (BU, LOB, SvcCode, Description, Yards, Count, Type) VALUES ('0050','C','BKF','BULK PICKUP FEL',0.5,1,'4')
Protected Function LoadSubData(ByRef avarData(,) As Object) As Boolean
        Dim lngRow As Long, strSQL As String = "", objRecords As Object = vbNull, intCmdText As Int32 = 1
        Dim aCnn As New ADODB.Connection
        LoadSubData = False
            ' if some data was found,
            If avarData(0, 0) <> "~!@" Then
                aCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=\\cagold01app01\WebSite\Containers\App_Data\Containers.mdb;" & _
                    "User Id=admin;Password=;"
                ' clear the table
                strSQL = "DELETE * FROM tblSubscription WHERE BU='" & Right("000" & strBU, 4) & "'"
                aCnn.Execute(strSQL, objRecords, intCmdText)
                ' loop through the data array 
                For lngRow = 0 To UBound(avarData, 2)
                    strSQL = "INSERT INTO tblSubscription (BU, LOB, SvcCode, Description, Yards, Count, Type) " & _
                        "VALUES ('" & Right("000" & avarData(0, lngRow), 4) & "','" & avarData(1, lngRow) & _
                        "','" & avarData(2, lngRow) & "','" & avarData(3, lngRow) & "'," & _
                        avarData(4, lngRow) & "," & avarData(5, lngRow) & ",'" & avarData(6, lngRow) & "')"
                    aCnn.Execute(strSQL, objRecords, intCmdText)
                Exit Function
            End If
        Catch e As Exception
            Label1.Text = e.ToString & vbNewLine & strSQL
            Label1.Visible = True
        End Try
        LoadSubData = True
    End Function

Open in new window

LVL 16
Chuck WoodAsked:
Who is Participating?
JbondConnect With a Mentor Commented:
Wait, I have another Idea. A couple of your column names are using reserved keywords.
try wrapping the Count and type columns in brackets..

INSERT INTO tblSubscription (BU, LOB, SvcCode, Description, Yards, [Count], [Type])

See if that helps.
Stab in the dark here. Try wrapping Yards and Count values in single quotes
Chuck WoodAuthor Commented:
Both fields are Number data types (Double). All the other fields are Text. I tried the SQL syntax in an Access Append query and it works fine. I just can't figure out why it doesn't work here.
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

I probably can't explain this as well as others may be able to. Access Append is probably using something other than ADODB(I'm not an Access person). Thats probably why it works when you do the Access Append and not when done programatically.

Try it and see if it works. I know SQL Server will convert it from a string to the correct data type.
Chuck WoodAuthor Commented:
I tried it and it gave the same error. I have used ADODB in Windows apps and in Access databases for years and the SQL syntax is correct and would work in a Windows app and in an Access database. Thanks for the suggestion. Any other ideas?
No Sorry. I know this isn't helpfull but the syntax looks okay. Waiting for an answer on another issue, figured I would try to help someone while waiting.
Chuck WoodAuthor Commented:
OK. Thanks for trying.
Chuck WoodAuthor Commented:
Brilliant! It works. I should have caught that myself. Thanks so much for the help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.