DOCUMENT:Q186276  30-MAY-1998  [vbwin]
TITLE   :PRB: Invalid Parameter Number Error Calling RDO BatchUpdate
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:5.0
OPER/SYS:WINDOWS
KEYWORDS:

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Enterprise Edition for Windows, version 5.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"

The BatchSize property controls the number of rows updated/inserted/deleted
in a single round trip.

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."

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
---------------------------

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.

1. Open a new project in Visual Basic 5.0. 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

Additional query words: kbDSupport kbdse kbVBp500

(c) Microsoft Corporation 1998, All Rights Reserved.
Contributions by Ron Nelson, Microsoft Corporation
======================================================================
Version           : WINDOWS:5.0
Platform          : WINDOWS
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1998.