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

5
votes

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?

2
sql-serverexcelvbaadodb
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
Yes, try to catch the query and its parameters with Sql Server Profiler. – johey
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
It's curious to see the whole string. Did you try to run it in SQL Server Management Studio? – JohnyL
@deseosuho Were you able to get past the errors? What worked for you? – shahkalpesh

2 Answers

0
votes

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.

0
votes

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