Solved

Recordset doesn't return basecolumn attribute for a column

Posted on 2002-07-11
10
600 Views
Last Modified: 2013-11-19
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).
0
Comment
Question by:mdougan
  • 6
  • 4
10 Comments
 
LVL 28

Expert Comment

by:AzraSound
ID: 7149016
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.
0
 
LVL 18

Author Comment

by:mdougan
ID: 7149168
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
Next

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...
0
 
LVL 18

Author Comment

by:mdougan
ID: 7149257
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
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 7149316
Article shows appending theses attributes manually to the XML:

"Connect a stand-alone Recordset to a database using XML"
http://www.vb2themax.com/Item.asp?PageID=TipBank&ID=58


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
0
 
LVL 18

Author Comment

by:mdougan
ID: 7149488
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:

oGrid.Band(0).Columns(i).basecolumnname.value

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?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 28

Accepted Solution

by:
AzraSound earned 200 total points
ID: 7149548
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:

http://p2p.wrox.com/archive/activex_data_objects/2002-05/0.asp

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?
0
 
LVL 18

Author Comment

by:mdougan
ID: 7157038
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.
0
 
LVL 18

Author Comment

by:mdougan
ID: 7157223
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!

Thanks!
0
 
LVL 18

Author Comment

by:mdougan
ID: 7157247
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 :)
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 7157254
Very interesting findings mdougan...good research, and thanks for the brief tutorial.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

706 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

20 Experts available now in Live!

Get 1:1 Help Now