Problem updating field in recordset

[BlackBearDude]

I've created a record set in which I include (in the SQL) some additional fields (e.g., 0 AS a, 0 AS b, etc.) with the intention of looping through the recordset and filling in these fields with data from other sources.

The recordset is created fine. The problem is the infamous "multiple-step operation generated errors" when trying to update a field in the recorset.

What issues exist in this situation? I thought that updating fields in this fashion would be relatively straight-forward.

Here is the SQL used to create recordset:

strSQL1 = "SELECT [desc], [catid], [amount] AS [budget], 0 AS [actual], 0 AS [pct] " _
& "FROM [catbud] " _
& "WHERE [year] = " & intYear & " AND [depid] = " & intDepID & " " _
& "ORDER BY [desc]"

rs1.Open strSQL1, gcon, adOpenKeyset, adLockBatchOptimistic

Here is the statement that I believe is generating the error:

rs1.Fields("actual&quot.Value = IIf(IsNull(rs2.Fields("cost&quot), 0, rs2.Fields("cost&quot)

The error occurs regardless of the way I try to assign it to rs1.fields("actual&quot.

I'm sure that the problem is "right in front of me" and I just can't see it.

I would greatly appreciate any & all help.

Thanks

Patrick

 

[MarkSweetland]

Assuming there is a column [actual] in the table [catbud],
in your select statement you've aliased a value (0) to the column name [actual] in the return recordset. When you try to set the value of the recordset column, there is no correlation to the column name [actual] in the underlying table [catbud]. It only exists in the returned recordset. Since your updating the value in the [actual] column based on the presence of a NULL in the [cost] column, just return it (probable the [pct] column as well)the column value it contains.

strSQL1 = "SELECT [desc], [catid], [amount] AS [budget], [red]0 AS [/red][actual], [red]0 AS[/red] [pct] " _
& "FROM [catbud] " _
& "WHERE [year] = " & intYear & " AND [depid] = " & intDepID & " " _
& "ORDER BY [desc]"




Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[BlackBearDude]

Mark,

Thanks. I can't believe I wasn't seeing the obvious.

My question now is, how do I update a so-called temporary recordset??

I simply want to start with the basic RS as described with strSQL1 and then nav thru and update fields along the way, report on it, then eliminate it.

Any thoughts?

 

[MarkSweetland]

I haven't tried the code depicted on this link, but I imagine it will allow you to accomplish your goal:

http://authors.aspalliance.com/nothingmn/view.asp?aid=28

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach