Microsoft KB Archive/186276

= PRB: Invalid Parameter Number Error Calling RDO BatchUpdate =

Article ID: 186276

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q186276



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.



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



Keywords: kbprb KB186276

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.