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

Question

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?

I don't know the answer to your question, but if I were in your shoes, I would be asking these questions: Are string values losing quotation marks? Does the ODBC driver have a trace feature I can turn on? Can I catch the query from the SQL Server side and see what it is doing from there? Does it work if I manually build the query without parameterization? (If so, what does that tell me?) If you've considered those things already, I apologize. — StoneGiant, Jan 05 '19 at 18:55
Yes, try to catch the query and its parameters with Sql Server Profiler. — johey, Jan 05 '19 at 19:17
The value `3` in `.CreateParameter(, adVarChar, 3, 1024, e)` indicated it is a value for `adParamInputOutput` as compared to `adParamInput` which has the value of 1 based on the documentation of `CreateParameter`. Because you are writing an `INSERT`, it should be 1 (`adParamInput`). — shahkalpesh, Jan 05 '19 at 19:22
It's curious to see the whole string. Did you try to run it in SQL Server Management Studio? — JohnyL, Jan 05 '19 at 19:39
@deseosuho Were you able to get past the errors? What worked for you? — shahkalpesh, Jan 05 '19 at 21:13
@shahkalpesh I don't have TRACE privileges in the SQL server, so I'm continuing to work on deconstructing and trying to get simple parameterized queries to run from excel... — deseosuho, Jan 06 '19 at 00:37
I think @shahkalpesh might be on to something. I suggest you change it from 3 to 1 and see if it fixes it. — Nick.Mc, Jan 06 '19 at 11:11

score 0 · Answer 1 · edited Jan 07 '19 at 08:29

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.

In this case, I'm using Join() so there's not a trailing comma. Unfortunately, because the server is compiling the query from a list of parameters passed separately, it doesn't appear that the `Connection` object has any access to the final query that is run. — deseosuho, Jan 06 '19 at 00:32
@deseosuho Should have looked up `Join` before I answered, sorry. I've amended my answer. — BobRodes, Jan 06 '19 at 05:42

score 0 · Accepted Answer · answered Jan 08 '19 at 19:11

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

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

2 Answers2