Getting "Multiple-step operation generated errors" In ASP Classic Using Merged Data Set Function
I am using a function I read about here to merge a series of recordsets that are generated by a stored procedure that is called in a loop. I have used this function before in other stored procedure cases and it has never produced this problem.
The research I did online points to basically one of two reasons:
1) I am trying to update or insert a date/time value that is not formatted correctly into a SQL Server 2005 table
2) I am trying to insert a, for example, CHAR(60)
string into a CHAR(50)
field.
Reason one is not applicable here since I am not using dates (in datetime format at least).
Reason two seemed to be the most likely issue. So I did a series of Response.Write()
to spit out the objField.Name
, objField.Type
, objField.ActualSize
, and if it was a numeric field the objField.Precision
and objField.NumericScale
.
Let us say that the stored SQL procedure is called twice as I am querying for values that are occurring in the same time frame but 2 different states. The loop I have in the ASP page does a For Each on the state in the state list and calls the stored procedure for each of the elements in the state list. I then call the MergeRecordSets function so it combines the 2 results into one. The general rule is that the data types and sizes of the columns in each resultset must be the same. With my Response.Write()
checks of each of the columns returned in the 2 data sets I have found that they are identical. Doing my checks I also found that it breaks on the first column that is a NUMERIC
column. The previous columns it was OK with were all CHAR
or VARCHAR
.
Is there any other reason why this error would come up?
The following is how I am calling the record merger function. The oQueryResult
is going to be the final output (the combined records). objSingleRS
is the result set returned by the stored procedure.
If oQueryResult Is Nothing Then
Set oQueryResult = objSingleRS
Else
Set oQueryResult = MergeRecordSets(Array(oQueryResult, objSingleRS))
End If
Here is the merge function. The line in which the code breaks is marked below.
Function MergeRecordSets(arrRecordsets)
Dim x, y, objCurrentRS
Dim objMergedRecordSet, objField, blnExists
Set objMergedRecordSet = Server.CreateObject("ADODB.Recordset")
For x=0 To UBound(arrRecordsets)
Set objCurrentRS = arrRecordsets(x)
For Each objField In objCurrentRS.Fields
blnExists = False
For y=0 To objMergedRecordSet.Fields.Count-1
If LCase(objMergedRecordSet.Fields(y).Name) = Lcase(objField.Name) Then
blnExists = True : Exit For
End If
Next
If Not(blnExists) Then
objMergedRecordSet.Fields.Append objField.Name, objField.Type, objField.DefinedSize
'objMergedRecordSet.Fields(objMergedRecordset.Fields.Count开发者_如何学运维-1).Attributes = 32 'adFldIsNullable
End If
Next
Next
objMergedRecordSet.Open
For x=0 To UBound(arrRecordsets)
Set objCurrentRS = arrRecordsets(x)
Do Until objCurrentRS.EOF
objMergedRecordSet.AddNew
For Each objField In objCurrentRS.Fields
If Not(IsNull(objField.Value)) Then
'Response.Write(objField.Name & "<br>")
'Response.Write(objField.Type & "<br>")
objMergedRecordSet.Fields(objField.Name).Value = objField.Value 'Here is where it throws the Error.
End If
Next
objCurrentRS.MoveNext
Loop
Next
objMergedRecordSet.MoveFirst
Set MergeRecordSets = objMergedRecordSet
End Function
Here is the full error message returned:
Microsoft Cursor Engine error '80040e21'
Multiple-step operation generated errors. Check each status value.
/includes/funcs/Common.asp, line 4109
You mentioned that you have numeric
columns, but you never set the Precision
and NumericScale
properties when you create the new Field in objMergedRecordSet
. You need to set these properties for adNumeric
and adDecimal
fields.
objMergedRecordSet.Fields.Append objField.Name, objField.Type, objField.DefinedSize
With objMergedRecordSet.Fields(objField.Name)
.Precision = objField.Precision
.NumericScale = objField.NumericScale
End With
Also make sure you are not trying to put a NULL into a column that will not accept a NULL value. There is also the possibility of a type mismatch to cause this error so make sure you are passing a numeric value. - Freddo
精彩评论