PRB: Invalid Parameter Number Error Calling RDO BatchUpdate

• Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0

SYMPTOMS

When calling the RDO Resultset BatchUpdate method to a Microsoft SQL Server database and setting the Resultset BatchSize property to a large value, the following error may occur:

Run time error '40002': S1093: [Microsoft][ODBC SQL Server Driver]
Invalid Parameter Number

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=?; 

RESOLUTION

The only way to avoid this problem is to lower the value of the BatchSize property.

STATUS

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open a new project in Visual Basic. Form1 is created by default.



2. Place three CommandButtons on Form1.



3. From the Project menu, select References, and then select Microsoft Remote Data Objects 2.0.



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




      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=sa; PWD=; 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
 

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Ron Nelson, Microsoft Corporation

Additional query words:

Keywords : kbnokeyword kbVBp kbVBp500 kbVBp600 kbDSupport
Version : WINDOWS:5.0,6.0
Platform : WINDOWS
Issue type : kbprb