Solved

Adding and updating column in disconnected recordset

Posted on 2004-10-08
6
227 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now