INSERT INTO with multiple parameters via VB6 & ADO

Question

I'm trying to update some legacy VB6 code by adding a simple error logging subroutine using ADO to insert a row into a SQL Server table with basic details of the error. The subroutine receives an integer parameter value (errorCode) that identifies the error type. The error will have occurred while parsing an XML file. The XML file name (obtained via objFile.Name) is to be logged along with the date, the user name and the errorCode parameter value.

The code below shows where I am so far. The table I'm trying to INSERT INTO looks like this:

[dbo].[tblXMLerrorLog](
[errorLogID] [int] IDENTITY(1,1) NOT NULL,
[runDate] [datetime2](0) NULL,
[runBy] [nvarchar](255) NULL,
[xmlFileWithError] [nvarchar](255) NULL,
[errorCode] [int] NULL)

 

Sub writeErrorLog(errorCode As Integer)

  strSQL = "INSERT INTO dbo.tblXMLerrorLog (runDate, runBy, xmlFileWithError, errorCode) 
            VALUES(GetDate(), Current_User,?,?);"

  With cmd

    .CommandType = adCmdText
    .CommandText = strSQL

    Set par1 = .CreateParameter("@xmlFileWithError", adChar, adParamInput, 50, objFile.Name)
    .Parameters.Append par1

    Set par2 = .CreateParameter("@errorCode", adInteger, adParamInput, , errorCode)
    .Parameters.Append par2

    .ActiveConnection = Cnxn
    .Execute                        ' update error log

    .Parameters.Delete ("@xmlFileWithError")
    .Parameters.Delete ("@errorCode")

  End With

End Sub

When I run the code the .Execute statement throws this error:

"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

My research of that error message suggests a field size mismatch but I don't understand that. I'd appreciate any direction.

Answer 1

It's fixed. As explained in the comments, my main (calling) program uses a parameter. So, when my subroutine's .Execute statement was reached, there were 3 parameters instead of just 2 as I'd naively expected. The preferred solution would have been to use named parameters, as one comment suggested, but I don't think VB6 supports that; I have to reference the parameters with question mark placeholders, so their order matters. My subroutine didn't work because the unexpected first parameter (from the calling program) caused a data type mismatch when the INSERT INTO statement tried to insert the 'wrong' value into a column having a different data type. So the error message I was seeing made sense. The fix had nothing to do with the subroutine. Instead, I reworked the calling program so as to delete the first parameter before calling the subroutine. Thanks to all who helped me with this.