How to diagnose syntax error from a parameterized SQL Server query in VBA ADODB

user_4789843 0
Viewed 325

I'm using VBA adodb to write INSERT statements in a SQL Server based on Excel row contents.

I'm using parameterized queries so my VBA code has the following form:

sqlStatement = "INSERT INTO dbo.mytable (" & Join(insertElement(0), ", ") & ") VALUES (" & Join(insertElement(2), ", ") & ")"
Set cm = New ADODB.Command

With cm
      Debug.Print (sqlStatement)
            .ActiveConnection = conn
            .CommandText = sqlStatement
            .CommandType = adCmdText
             For Each e In insertElement(1)
                Set Pm = .CreateParameter(, adVarChar, 3, 1024, e)
                .Parameters.Append Pm
             Next e
             Set rs = .Execute
        End With

where insertElement(0) is an array of field names, ...(1) is an array of values, and ...(2) is an array of placeholder ?'s to support parameterization

When I run this code, I get an error

[Microsoft][ODBC Driver 13 for SQL Server][SQL Server] Incorrect syntax near 'Output'

However, when I interrogate the sqlStatement text, there is no 'Output' anywhere in the statement. The text is of the form:

INSERT INTO dbo.mytable ([my_field_1],[my_field_2],[somefieldshaveweirdcharslike+#], ...) VALUES (?, ?, ?, ...)

So, if I'm not providing the 'Output' command directly, and I can't directly see the statement because it's being processed on the server side, how can I diagnose the syntax?

adodb vba excel sql-server
2 Answers

You should be able to simply build your SQL statement by iterating your array and concatenating your values any way you see fit, rather than attempting to plug a bunch of parameters into a bunch of question marks. Something like this (I'm assuming that insertElement is really a two-dimensional array and not a collection; if it's really a collection then you'll need to do something similar with For Each):

sqlStatement = "INSERT INTO dbo.mytable (" & Join(insertElement(0), ", ") & ") VALUES ("
Set cm = New ADODB.Command

With cm
    .ActiveConnection = conn
    .CommandType = adCmdText
    Dim aCount As Integer
    aCount = Ubound(insertElement(1))
    For i = 0 To aCount - 1
        sqlStatement = sqlStatement & "'" & insertElement(1,i) & "'" & _
        Iif(i <> aCount - 1, ",", "")
    Next
    .CommandText = sqlStatement
    Set rs = .Execute
End With

All that said, if you want to get a more in-depth understanding of where your error is happening, you might try iterating the Connection object's Errors collection. Since an attempt to use a connection can generate multiple errors, it can be a little hard to troubleshoot just using the Err object.

Finally, it would be best to set up your INSERT statement in a stored procedure on the server side, and pass in the values as parameters that way, unless you have no reason to be concerned about injection attacks.

user_4904540

I didn't have trace permissions in the SQL server so I had limited options for exploring server-side logging.

I ended up resolving this the brute force way, adding one field at a time until I received the error.

For my case, the underlying issue was related to using the adodb adNumeric parameter type for decimal values. I switched the parameter type to adDecimal and then set the NumericScale and Precision values on the individual parameter objects. The vba code structure looks something like the below

            For Each p In paramArr
                If p(1) = adVarChar Then
                    Set Pm = .CreateParameter(, p(1), 1, Len(p(2)), p(2))
                Else
                    Set Pm = .CreateParameter(, p(1), 1, , p(2))
                End If
                If p(1) = adDecimal Then
                    Pm.NumericScale = 3
                    Pm.Precision = 13
                End If
                .Parameters.Append Pm
            Next p
user_47898430
Related