ADO recordset and adding fields 1

[AdaHacker]

This is probably either really easy or impossible, but I can't figure out which it is.

I have an ADO recordset with some employee information from a database; ID numbers, names, etc. I need to combine that with some monthly and year-to-date fuel usage totals that I'm calculating in code and storing in an array (not in order). I was ordering the recordset in SQL and using a hash to get the totals for my report as I went through it, but due to a change my boss has requested, I now need to order the data by monthly totals.

In order to save myself a lot of rewrite time, I'd like to add fields to the existing recordset and stick the totals into those fields. Then I could just do a sort on the recordset. I haven't been able to add fields to the recordset (it gives me a message about not being able to append them in this context). Can I do this? And, if so, how?

 

[gharabed]

I am relatively new to VB but here's an idea and hopefully I understand your problem correctly. I don't know of any way to add the fields after the recordset has been created. Intead of trying to add fields to the existing recordset why don't you just add "dummy" total fields to the SQL statement (query) so that the recordset has empty total fields in it when it is created. Then you can add the totals as you wish and resort the record set. This way you don't have to do a major rewrite of the query. What do you think?

Greg

 

[neilkonitzer]

You cannot add fields to an open recordset in ADO Neil Konitzer, President
Freisoft

http://www.freisoft.com

 

[AdaHacker]

Thanks for the input guys!

I was afraid you couldn't append fields to an open recordset, but I figured it might be worth asking.

Excellent suggestion Greg! I checked the database, and there just happened to be a field with the data type I needed. I just added two aliases for that field in my SQL (changed 2 lines in one file) and changed the lock type of the recordset through an optional parameter (3 lines this time), comment out a couple things and now my failed field updating code works perfectly! Very simple fix with a minimal performance hit. Thanks again!