asked on

Adding and updating column in disconnected recordset

I need to append a column to a disconencted recordset and then unpate its value with an integer.

Below is the code that I have tried:
I have also tried CASTing the point value field as an integer (CAST 1 as INT) AS Pointvalue

objRS.Source = "SELECT table.*, Null AS PointValue FROM table WHERE ListingID IN (" & strComps & ")"
                                                            objRS.CursorLocation = adUseClient
       objrs.CursorType = adOpenKeySet
                                                            objRS.LockType = adLockOptimistic
                                                            objRS.Open
                                                            set objRS.ActiveConnection = Nothing
                                                            Do WHile NOT objRS.EOF
                                                                  updatePoints(trim(objRS.Fields("ListingID")))

                                                                  objrs.MoveNext
                                                            Loop
                                                                                                            objRS.Sort = "PointValue DESC"

Below is the update Points function (this is what gives an error as detailed below):

d is a data dictionary holding the listingID and its points

Function updatePoints(listingID)
'sets point value for listings returned to allow sorting by relevence before displaying resultgrid
      points = d.Item(listingID)
      'objrs.Fields("PointValue") = cint(trim(points))
      'objRS.Update "PointValue", cint(points)
End Function

I have tried both ways to update this newly created field, but get the same error every time.

This is the error:
Microsoft Cursor Engine (0x80040E21)
Multiple-step operation generated errors. Check each status value.

I have tried everything that I can think of including using the objRS.Fields.Append Method with no success. Help will be appreciated.

thx

ASKER CERTIFIED SOLUTION

Brendt Hess

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

planza

ASKER

Cool, thanks, I will try this and come back and award you points if it works...

thx again,
P

planza

ASKER

I am trying that, but cannot figure out how to append a new field def to the second rs Object.

I am trying to use the parameter.append stuff, but not having much success (prob because it only works with command objects).

Please let me know how to append the new field to the second rs.

thx,
P

planza

ASKER

Specifically, this is the code I am using:

                                                            For each strField in objRS.Fields
                                                                                    '****This causes and error***                                                objRS2.Fields.Append strField.Name, strField.Type
                                                                                                                              Next
                                                                        '***This works***                                                objRS2.Fields.Append "pointValue", adInteger

This is the error:

Error Type:
ADODB.Fields (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

I even treid expliticly converting the field parameters to string (name) and int (type)
I would rather not have to manually add every field in the first RS if possible.

Thx

planza

ASKER

I fixed this part, but now am getting the multi-step operation error message again. I am passing the first rs to my function. Below is the function:

Function updatePoints(rs)
'****THIS IS THE RIGHT WAY TO DO THIS, BUT KEPT GETTING ERRORS
 'sets point value for listings returned to allow sorting by relevence before displaying resultgrid

 'loop through RS and assign transfer data from it to objRS2
 Do While NOT rs.EOF
 listingID = rs("listingID")
 points = d.Item(listingID)
 objRS2.AddNew
 'loop through all fields on objRS (rs here) and add data to objRS2
 For each fld in rs.Fields
 If IsNull(fld.Value) Then
 If fld.Type = 3 Then 'int
 objRS2.Fields(fld.Name) = 0
 Else
 objRS2.Fields(fld.Name) = ""
 End If
 Else
 objRS2.Fields(fld.Name) = trim(fld.Value)
 'response.write fld.Name & " " & fld.Value & " "
 'response.write " NULL "
 End IF
 Next
 'response.End()
 'put pointvalue into objRS2
 objRS2.Fields("pointValue") = points
 objRS2.Update
 rs.MoveNext
 Loop

I append the fields to objRS2 right before calling the above function with the following code:

 For each strField in objRS.Fields
 'set prm = objRS2.CreateParameter(strField.Name, strField.Type, adParamInput)
 'response.write strField.Name & " " & cInt(strField.Type) & " "
 'response.write TypeName(strField.Name) & " " & TypeName(strField.Type) & " "
 objRS2.Fields.Append Trim(strField.Name), Trim(strField.Type), trim(strField.DefinedSize), adFldUpdatable AND adFldIsNullable AND adFldMayBeNull
 'set prm = Nothing
 Next
objRS2.Fields.Append "pointValue", adInteger

 objRS2.Open

I originally had the update points function attempting to add data to the 2nd RS a different way than it is now, but that caused an error saying that the field could not accept null values. DESPITE the code above that (allegedly) allows the objRS2 fields to accept null value..

Any help will be appreciated...

thx

planza

ASKER

ok, I finally got this crap to work. It works if I manually specify each field in my function as such:

do while not rs.EOF
objrs2.AddNew
objrs2("fieldname") = rs("fieldname")
objrs2("fieldname2") = rs("fieldname2")
objrs2.Update

I never could get it to work while looping through the rs's fields collection.

BUT, thanks for the initial help bhess1. here's the points