Solved

Adding and updating column in disconnected recordset

Posted on 2004-10-08
6
236 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Count where two id types exist in column 8 27
SSRS 2013 - Creating a summarized report 19 32
2016 SQL Licensing 7 40
How to search for strings inside db views 4 25
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

786 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