Copying recordsets

[Mats]

I have tried to solve this problem for quite a while without any success, I just can't figure out what's going wrong.

The code opens one recordset which is retrieved from a db, then a second recordset is generated that is a copy of the first but with one boolean field added. Everything goes well until the procedure starts copying records from the first recordset to the second. All string fields are copied nicely, but numeric fields generate an error.No errorcode, just 'Multiple step procedure created error' (or something like that). At my development computer I use an Access db for testing, and everything works fine, but in production I'm accessing a MS SQL 6.5 database, and the error appears.

Can anyone figure out what's going wrong?

Thanks,
Mats

>> Below the essential part of the code

tmpRst.CursorLocation = adUseClient
tmpRst.Open SqlStr, FilterDb, adOpenStatic, adLockReadOnly

GridRst.CursorLocation = adUseClient
GridRst.CursorType = adOpenDynamic
GridRst.LockType = adLockOptimistic

'Create fields in GridRst
GridRst.Fields.Append "X", adBoolean
For i = 0 To tmpRst.Fields.Count - 1
GridRst.Fields.Append tmpRst(i).name, tmpRst(i).Type, tmpRst(i).DefinedSize, tmpRst(i).Attributes
Next i

GridRst.Open

'Copy records from tmpRst to GridRst
Do Until tmpRst.EOF
GridRst.AddNew
GridRst("X&quot = True
For i = 0 To tmpRst.Fields.Count - 1
GridRst.Fields(i + 1) = tmpRst.Fields(i)
Next i
GridRst.Update
tmpRst.MoveNext
Loop

 

[Mats]

The exact error description is 'Multiple-step operation generated errors. Check each status value'

What's even more strange is that numeric fields with value 0 are copied o.k. to the new recordset. Integer fields are also copied o.k., only decimal numbers are problematic.

Mats

 

[BlackBearDude]

I'm trying to do something really simple here, but the infamous "multiple-step operation generated errors" error. The line with the problem is shown below. Even though rs2.fields("cost&quot should contain a SUM it is NULL. I have already checked to make sure that there is a record in rs2 and there is one (as expected). I am drawing total blanks on this, I think I've stared at it too long.

I'm sure this is going to be something very simply, but I'm just not seeing the answer.

Any help is greatly appreciated.


rs1.MoveFirst
While Not rs1.EOF
strSQL2 = "SELECT SUM([cost]) AS [cost] " _
& "FROM [item] " _
& "WHERE [year] = " & intYear & " AND [depid] = " & intDepID & " " _
& "AND [catid] = " & rs1.Fields("catid&quot & " " _
& "AND MONTH([date]) = 10 "

rs2.Open strSQL2, gcon, adOpenKeyset, adLockOptimistic

problem line---> rs1.Fields("actual&quot = rs2.Fields("cost&quot

If rs1.Fields(&quot;budget&quot <> 0 Then
rs1.Fields(&quot;pct&quot = rs1.Fields(&quot;actual&quot / rs1.Fields(&quot;budget&quot
Else
rs1.Fields(&quot;pct&quot = 0
End If

rs2.Close
rs1.MoveNext
Wend

 

[CCLINT]

For the first problem from Mats (old thread) you need to first identify what the field type is.
If it is a number, boolean field, then do not set the
tmpRst(i).DefinedSize
otherwise, for text fields do so.

For BlackBearDude's question, a NULL will be returned if the query didn't find any records to Sum on.
A single record will always be returned with an agregatted function, reveiling the sum, if there is one.
But, that doesn't mean that it found criteria matching records. If it didn't, the Sum will be NULL.
If it does, even if the sum is 0, then the Sum will be returned.

Therefore, you need to use:

If Not IsNull(rs2.Fields(&quot;cost&quot.Value) Then
'Debug.Print rs2.Fields(&quot;cost&quot.Value
Else
Debug.Print &quot;No records matching the criteria were found
End If