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
        Try
            ' 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=;"
                aCnn.Open()
                ' 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)
                Next
                aCnn.Close()
            Else
                Exit Function
            End If
        Catch e As Exception
            Label1.Text = e.ToString & vbNewLine & strSQL
            Label1.Visible = True
            aCnn.Close()
        End Try
        LoadSubData = True
    End Function

Open in new window

LVL 16
Chuck WoodAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JbondCommented:
Stab in the dark here. Try wrapping Yards and Count values in single quotes
0
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.
-Chuck
0
JbondCommented:
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.
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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?
-Chuck
0
JbondCommented:
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.
0
Chuck WoodAuthor Commented:
OK. Thanks for trying.
-Chuck
0
JbondCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chuck WoodAuthor Commented:
Brilliant! It works. I should have caught that myself. Thanks so much for the help.
-Chuck
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.