PRB: Invalid Parameter Number Error Calling RDO BatchUpdate (186276)

CAUSE

This error occurs because there is a limit to the number of parameters that can be called within a single batch statement. That limit is 500 for the current version of SQLSVR32.DLL, the SQL server ODBC driver. The version tested is 3.50.0305. The limit of 500 is a factor of the number of parameters in the SQL statement multiplied by the number of rows in the batch update. What occurs is that ODBC is sending out the SQL statement that contains parameters for the new values, the PK value, and the old values. This SQL statement is repeated for the number of rows to be updated up to the value of the BatchSize property value. As an example in the code that follows, the UPDATE statement that is sent to SQL Server looks like this:

   UPDATE tblBatchUpdate SET fldValue=? WHERE ID=? AND fldValue=?;

Note that there are three parameters that give the number of parameters in the batch when multiplied by the BatchSize property value. If this number exceeds the parameter limit of 500, you will get the error "Invalid Parameter Number."

MORE INFORMATION

Steps to Reproduce Behavior

The following steps create an application to reproduce the error. Create a SQL Server table in the pubs database by clicking the Make Table button. Fill the table with 200 records by clicking the Fill Table button. Click the "Batch Update" button to produce the error.

Open a new project in Visual Basic. Form1 is created by default.
Place three CommandButtons on Form1.
From the Project menu, select References, and then select Microsoft Remote Data Objects 2.0.

Place the following code in the General Declarations section of Form1. You will need to alter the database connection information in the EstablishConnection procedure:

Note You must change User UID=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database.

      Option Explicit
          Dim Cn As New rdoConnection
          Dim Rs As rdoResultset

      Private Sub Form_Load()
         EstablishConnection
         Command1.Caption = "Create Table"
         Command2.Caption = "Fill Table"
         Command3.Caption = "Batch Update"
         Command2.Enabled = False
         Command3.Enabled = False
      End Sub

      Private Sub Command1_Click()
          Dim strCreateTable As String
          Dim Response As Integer
          strCreateTable = "CREATE TABLE dbo.tblBatchUpdate (" _
              & "ID int IDENTITY (1, 1) NOT NULL PRIMARY KEY," _
              & "fldValue int NULL)"
          Debug.Print strCreateTable

          If Not Cn.rdoTables("tblBatchUpdate").Updatable Then
                'table exists
              Response = MsgBox("Table exists" & vbCrLf & _
              "Do you what to delete it?", vbYesNo)
          End If

          If Response = vbYes Then
              Cn.Execute ("Drop table tblBatchUpdate")
              Debug.Print "Creating new table..."
              Cn.Execute strCreateTable
          End If

          Command2.Enabled = True
          Command3.Enabled = True

      End Sub

      Private Sub Command2_Click()
          Dim i As Integer
          Dim strSQLInsert As String
          MousePointer = vbHourglass

          For i = 1 To 200
              strSQLInsert = "INSERT tblbatchupdate (fldValue) " _
              & "VALUES (" & i & ")"
              Cn.Execute (strSQLInsert)
          Next i

          MousePointer = vbNormal
      End Sub

      Private Sub Command3_Click()
          RefreshRS
          MousePointer = vbHourglass
          If Rs.RowCount > 0 Then
              Rs.MoveFirst
              Do While Not Rs.EOF
                  Rs.Edit
                      Rs(1) = Rs(1) + 1
                  Rs.Update
                  Rs.MoveNext
              Loop
              Rs.BatchUpdate
          End If
          MousePointer = vbNormal
      End Sub

      Private Sub EstablishConnection()
          With Cn
             .Connect = "UID=<username>; PWD=<strong password>; Database=pubs;" _
              & "Server=MySQLServer;Driver={SQL Server}"
             .CursorDriver = rdUseClientBatch
             .EstablishConnection rdDriverNoPrompt, False
          Debug.Print Cn.Connect
          End With
      End Sub

      Private Sub RefreshRS()
         Dim Sql As String
         Dim rdoQuery1 As rdoQuery

         Sql = "SELECT ID, fldValue FROM tblbatchupdate;"
         Set rdoQuery1 = Cn.CreateQuery("sql", Sql)
         rdoQuery1.RowsetSize = 1000

         Set Rs = rdoQuery1.OpenResultset( _
                           Type:=rdOpenKeyset, LockType:=rdConcurBatch)

         Rs.rdoColumns(0).KeyColumn = True
         Rs.BatchSize = 167   'Value of 166 works and 167 fails
         'Reason - 166 * 3 = 497 -Works and
         '167 * 3 = 501 - Fails
         'Because - this is the update statement sent out -
         '"UPDATE tblbatchupdate SET fldValue=? WHERE ID=? AND fldValue=?;"
         'Notice 3 parameters.  No. of parameters * no. of rows cannot
         'exceed 500.
         '
         Rs.UpdateCriteria = rdCriteriaUpdCols

      End Sub

      Private Sub Form_Unload(Cancel As Integer)
          Cn.Close
      End Sub

PRB: Invalid Parameter Number Error Calling RDO BatchUpdate (186276)

SYMPTOMS

CAUSE

RESOLUTION

STATUS

MORE INFORMATION

Steps to Reproduce Behavior