Ado更新批次

Question

我有和使用更新批次问题。我有一个循环，通过记录集更新值。批量中至少有333件事情。当它到达第254个项目时，它就会放弃。是否有批量限制？

On Error GoTo Err_cmdProcessAll 

Dim cn    As ADODB.Connection 
Dim rs    As ADODB.Recordset 
Dim strTableName As String 
Dim strSql As String 

' 
Set cn = New ADODB.Connection 

cn.Open "Provider=sqloledb; " & _ 
    "Data Source=" & "BLD-FS-SQLVS04\PRDINST4" & ";" & _ 
    "Initial Catalog=" & "HNFS_NetProv" & ";" & _ 
    "Integrated Security=SSPI;" 
cn.CursorLocation = adUseServer 

Dim strSortField As String 
Dim additionalwhere As String 




If cboValue = "pc3Claims" Then 
    strTableName = "Seq3_PendedClaims_Ranked" 
    strSortField = "DaysSinceReceivedClaim" 
    ' additionalwhere = " AND (member_eligibility_ud Not Like '%Program%') 
    strSql = "Select * " & _ 
     "FROM " & strTableName & _ 
     " WHERE (Complete Is Null) And (AssignedTo Is Null)" & additionalwhere & _ 
      " ORDER BY cast(" & strSortField & " as int)" & strSort 

ElseIf cboValue = "pc3ContractAssignments" Then 
    strSortField = "date" 
    additionalwhere = "" 
    strSql = "Select * " & _ 
     "FROM " & strTableName & _ 
     " WHERE (Complete Is Null) And (AssignedTo Is Null)" & additionalwhere & _ 
     " ORDER BY CONVERT(varchar(10), CONVERT(datetime, [" & strSortField & "], 111), 121) " & strSort 
End If 

Set rs = New ADODB.Recordset 
    With rs 
     Set .ActiveConnection = cn 
     .Source = strSql 

     .LockType = adLockOptimistic 
     .CursorType = adOpenKeyset 
     .CursorLocation = adUseClient 
     .Open 
    End With 
'make change to above to include 
Dim i As Long 
Dim j As Long 
Dim strAssignAssociate As String 
Dim lngAllocAmt As Long 
For i = 1 To ListView6.ListItems.Count 
    If ListView6.ListItems(i).Checked Then 
     strAssignAssociate = ListView6.ListItems(i).SubItems(1) 
     Debug.Print strAssignAssociate 
     lngAllocAmt = ListView6.ListItems(i).Text 
     For j = 1 To lngAllocAmt 
     Debug.Print rs.Fields("AssignedTo") 
     rs.Fields("AssignedTo") = strAssignAssociate 
       Debug.Print rs.Fields("AssignedTo") 
     rs.MoveNext 
     Next j 
    End If 
Next i 
rs.UpdateBatch 
MsgBox "All Finished", vbOKOnly, "Inventory Control" 
Set rs = Nothing 
Set cn = Nothing 

Answer 1

我发现了这个问题。我正在使用非键控文件。我发现我在文件中有重复的内容。这已被纠正，上面的代码工作正常。