Run-Time Error '-2147217900 (80040e14)': Incorrect Syntax near 's'.

[EpicDave]

I am trying to insert data into a SQL table from an excel table. I am able to get this code to work about 6 or 7 times but it hits a point where it hits this run time error.

'Setup Command Update
Dim BTcmd As ADODB.Command
Set BTcmd = New ADODB.Command
Dim r As Range
BTcmd.ActiveConnection = connex
For Each r In Range("A2", Range("A2").End(xlDown))
BTcmd.CommandText = Getinserttext(r.Value, r.Offset(0, 1).Value, r.Offset(0, 2).Value, r.Offset(0, 3).Value, r.Offset(0, 4).Value, _
r.Offset(0, 5).Value, r.Offset(0, 6).Value, r.Offset(0, 7).Value, r.Offset(0, 8).Value, r.Offset(0, 9).Value, r.Offset(0, 10).Value, _
r.Offset(0, 11).Value, r.Offset(0, 12).Value, r.Offset(0, 13).Value, r.Offset(0, 14).Value, r.Offset(0, 15).Value, r.Offset(0, 16).Value, _
r.Offset(0, 17).Value, r.Offset(0, 18).Value, r.Offset(0, 19).Value)
BTcmd.Execute
Next r


End Sub

Function Getinserttext(Invoice As String, Invoicestatus As String, DateRan As Date, Branchname As String, PayorName As String, LastID As String, EndDOS As Date, DSO As Integer, Type1 As String, Gross As Integer, Net As Integer, CA As Integer, Payments As Integer, AR As Integer, Difference As Integer, Percentage As Integer, Secondary As String, Lastworkeddate As String, Daysworked As Integer, OpenDt As Date) As String
Dim SqlStr As String
SqlStr = _
"INSERT INTO dbo.Dashboard (" & _
"Invoice,InvoiceStatus,<wbr>DateRan,BranchName,PayorName,<wbr>LastID,EndDOS,DSO,Type1,Gross,<wbr>Net,CA,Payments,AR,Difference,<wbr>Percentage,Secondary,<wbr>LastWorkedDate,DaysWorked,<wbr>OpenDt)" & _
"Values (" & _
"'" & Invoice & "'," & _
"'" & Invoicestatus & "'," & _
"'" & DateRan & "'," & _
"'" & Branchname & "'," & _
"'" & PayorName & "'," & _
"'" & LastID & "'," & _
"'" & EndDOS & "'," & _
"'" & DSO & "'," & _
"'" & Type1 & "'," & _
"'" & Gross & "'," & _
"'" & Net & "'," & _
"'" & CA & "'," & _
"'" & Payments & "'," & _
"'" & AR & "'," & _
"'" & Difference & "'," & _
"'" & Percentage & "'," & _
"'" & Secondary & "'," & _
"'" & Lastworkeddate & "'," & _
"'" & Daysworked & "'," & _
"'" & OpenDt & "')"

Getinserttext = SqlStr

 

[Ruddles]

Here:-

DaysWorked,<wbr>OpenDt[B][COLOR=#FF0000])[/COLOR][/B]" & _
    "Values (" & _

I suspect you're missing a space after that closing parenthesis, otherwise SqlStr contains "OpenDT)Values".

Put a Debug.Print SqlStr statement after you've built the SQL and check what it looks like.

 

[EpicDave]

That didn't seem to do anything. I am able to run through it with F8 for a few times and then it hits this error when I try to execute. My first guess is how I was defining the variable because the debug print is giving me an overflow error message when I remove the error execute and change the debug.

 

[Ruddles]

Reduce the number of line continuations. I think you're hitting the limit: https://msdn.microsoft.com/en-us/library/office/gg264236.aspx