Solved

Adding and updating column in disconnected recordset

Posted on 2004-10-08
6
241 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:planza
  • 5
6 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
ID: 12264083
Your field appended as this is being appended  (NULL in the query, or a constant value in the query) is automatically being defined as a read-only field.

What is probably the simplest method to handle htis would be to build a second recordset after the original SELECT.  Since this will not be open yet, APPENDing new field defs will work.

So, you would spin through the FieldDefs in the original RS, adding identical defs to the new RS.  Once that is done, then you will add one more field for your PointValue, and open the new RS.

Then, in your UpdatePoints routine, you would:

Add a new record to the new RS
Copy the existing values from the original RS
Add the PointValue to the new record.
Update the new RS.

contiune until done...
0
 
LVL 1

Author Comment

by:planza
ID: 12276351
Cool, thanks, I will try this and come back and award you points if it works...

thx again,
P
0
 
LVL 1

Author Comment

by:planza
ID: 12277673
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
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 1

Author Comment

by:planza
ID: 12278088
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
0
 
LVL 1

Author Comment

by:planza
ID: 12279875
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 & "<br>"
                        'response.write "<br>NULL<br>"
                  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) & "<br>"
                                                                  'response.write TypeName(strField.Name) & " " & TypeName(strField.Type) & "<br>"
                                                                  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
0
 
LVL 1

Author Comment

by:planza
ID: 12280560
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
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question