Recordset doesn't return basecolumn attribute for a column

OK, here is the situation.  We have a stored proc that goes and collects a list of column names that the user has saved off as a personal view, then, it constructs a SQL statement to select those columns from the underlying table - only, in this case the underlying table is actually a view.

This works fine, all of the columns come back, but if you save the recordset as XML and you look at the attributes, some of the columns have rs:basetable and rs:basecolumn and some of the fields don't. - though, if you go through the fields collection, all of the fields do have proper names

This is a problem, as we're loading the recordset into the datasource of an Infragistics Ultragrid, and later we are trying to use the basecolumn which the Ultragrid keeps a reference to.  So, the basecolumn ends up being '' for some of the columns.

My question is, what would influence whether these rs:basecolumn or basetable attributes are returned for a column?  

There appear to be no differences between some fields that do return a basecolumn and some that do not (as far as how they are defined in the view, or referenced in the stored proc).
LVL 18
Who is Participating?
AzraSoundConnect With a Mentor Commented:
Very odd...definitely sounds like some sort of bug.  I'm all out of ideas but do keep me posted on what you find.

I did a quick search on the web to see what I could find, and found a related post referring to a similar problem:

Though he discovered his error b/c he was unable to update his database, perhaps his fix of releasing any extra recordsets connected to the DB may be an issue?
In the meantime, are you able to just create these attributes manually prior to loading the recordset into the Ultragrid datasource?

Are there any possible issues on these columns not appearing?  I am just taking stabs in the dark, but maybe things like same column names from different tables, odd column names, possible xml reserved words as column names, etc.
mdouganAuthor Commented:
Some of these ideas are possible.  I decided to put together a quick routine so I could see the effects of different cursor types on the recordset properties.

For i = 0 To RS.Fields.Count - 1
  sTemp = sTemp & RS.Fields(i).Name & " = " & RS.Fields(i).Properties(1).Name & " = " & RS.Fields(i).Properties(1).Value & vbCrLf

The second property is the BASECOLUMNNAME property. And what I found is that if you open the recordset using adUseServer and adOpenKeyset then you get the BASECOLUMNNAME every time.  However, I never seem to get it using adUseClient.

This recordset property may or may not be where XML is getting the data from and/or where the Ultragrid is getting the data from, but it's the only thing I can think of.

Now, if I could just change my recordsets to adUseServer and adOpenKeyset, I'd be all set, but the rest of our code is depending on a disconnected client-side recordset.  

What is a little strange is that I seem to be getting the BASECOLUMNNAME for some of the columns in our actual program, and, I was getting them all of the time, as far as I know, when we were going against the base tables, before we switched to using views.  But, in my test program I never get the basecolumn name for either going against the view or base table directly if I use the same adUseClient and adOpenStatic that our real program uses...
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

mdouganAuthor Commented:
Sigh... I did some more tests, and the appearance of a value in the BASECOLUMNNAME property of the recordset field's properties seems to be unrelated to the appearance of the attribute in XML when I dump the RS to XML.  However, the appearance of the attribute in XML matches exactly the appearance of the attribute when I put the RS into the Ultragrid.  So, if I could figure out what causes the basecolumnname to appear when the RS is dumped to XML I'd be in good shape.

Unfortunately, nothing that I've done in my test program so far, gets any of the basecolumnnames to appear in the XML
Article shows appending theses attributes manually to the XML:

"Connect a stand-alone Recordset to a database using XML"

I got a bit lost in your last post, however.  The appearance being unrelated when you dump RS to XML how?  So in the RS, its not showing up, but when dumped to XML, it is?
>>what causes the basecolumnname to appear when the RS is dumped to XML

I am confused b/c I thought the XML wasnt grabbing it either, per your original post:
>>but if you save the recordset as XML and you look at the attributes, some of the columns have rs:basetable and rs:basecolumn and some of the fields don't
mdouganAuthor Commented:
OK, let me start over.

Our stored procs return recordsets opened with adUseClient and adOpenStatic, we disconnect them and then set the DataSource of an UltraGrid to the recordset.  If you display these properties of the Ultragrid which are something like:


We were always getting the actual column name of the column in the query regardless of any alias that might have been on the column.  So:

Select loan_no as loan_number from loans

would give us an RS.Fields(0).Name = loan_number and the basecolumnname.value as described above would be loan_no.

As I said, I'm pretty sure it was all working fine until we switched to accessing views in the stored proc instead of the base tables.  Now, we've switched the proc to going against Views instead of the base table.

When we run the queries we get the RS.Fields(0).Name = loan_number but the basecolumnname.value ends up as ""

This causes a problem as we were using the base column name in our code.

While trying to debug the problem, I would dump the contents of the recordset to an XML file, because when you do this, there is a section of the XML file that contains all of the schema meta-data from the recordset.  You can do this simply by saying:

RS.Save "C:\Temp.xml, adPersistXML

I found that whenever a column did have a value in the Ultragrid's basecolumnname.value it also showed up as an attribute when I dumped the recordset to XML.  And, whenever it was missing in the basecolumnname.value, it was also missing in the XML.

So, I wrote a sample program, thinking that I could look at the values in the recordset directly, instead of resorting to putting the recordset into the Ultragrid and looking at the basecolumnname.value there, or dumping to XML.  So, I looked at the field properties and found a property called basecolumnname.  But, it turns out that if this field basecolumnname property gets set or not doesn't affect whether the basecolumn names shows up in the XML.  So, I can't use my test code which checks the recordset directly.

Using my test code and dumping to XML and then looking at the XML for the basecolumnname attribute will allow me to test, because if I get the basecolumnname to show up consistently in the XML, then I think it will show up in the ultragrid.  However, now, no matter what I do, I can't get any basecolumnnames to appear in the XML of my test program, though they do show up for some of the fields in the real production program if I put in the line to dump that recordset to xml, and I'm calling the same procs with the same ado commands.... weird huh?
mdouganAuthor Commented:
It is a possibility that there is an open recordset or two.  However, I suspect that it has more to do with the stored procs accessing a View rather than the base tables, as I think that our code was working before we switched (though I can't be sure, as I'm sure I didn't test every column).  I'm still looking into it, and will let you know what I find.
mdouganAuthor Commented:
I think I've discovered the operative factors.

The stored proc was doing a Union on selects from several tables.  The DBA changed the proc for other reasons to insert the values from each of the selects in the union to a temp table, and then the proc returns a single Select from the temp table and suddenly all of the meta data appears in the recordset.  Unfortunately for me, the base table name is the name of the temp table, not the original table, and the basecolumnname is the name of the column in the temp table, which may or may not be the same as the original column from the database that was selected...

However, it does make sense.  If you are selecting values for the same column from multiple tables via a union, then there is no value that can be set for basetablename, for a column, because there could be several basetablenames.  Likewise, the basecolumnname could be different in each of the unions, as the field "name" is taken from the first select of the union, but unions don't care if the second column in the second select of the union has a different column name from the second column in the first select of the union -- as long as they are the same datatype.

So, I think that the key to the missing meta data is that Unions will make it difficult for the recordset to say which basetable or basecolumn the data in the resultset came from.

I've accepted your comment as answer to move this into the PAQ in case anyone else runs into this problem!

mdouganAuthor Commented:
Oh, and one other point, if I used a Command object, and set the recordset's Source = CMD and then opened the Recordset, I didn't get the meta-data.  But, if I simply opened the recordset giving the connection object, then I get the meta data.....   So, additional point, if you want the meta data, don't use the Command Object.  Now that sounds like a bug :)
Very interesting findings mdougan...good research, and thanks for the brief tutorial.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.