Friday, January 23, 2015

Selecting Recordset from SQL Server with Same Name Columns

This applies to SQL Server 2008 R2 and VB.Net.
Thought I'd share what I discovered inside a VB.Net project converted from old VB6 code. We have a recordset that used to be brought in from SQL Server 2008 R2 via ADODB that had 2 fields from different tables with same name.
The old VB6 project apparently ignored the one from the first table and used the second, as the program was designed to do.
I wasn't aware of this right away. Following the .Net conversion I changed this database call to use System.Data.SqlClient.SQlCommand, replacing the original recordset with a datatable fill.
Suddenly, I wasn't getting the value I expected when this field was read for each row.
After going back and seeing what the stored procedure was set up to do (SIGH!!!) I learned that the insert into my datatable appended a "1" to the name of the second of the same-named fields.
The first SAMEFIELDNAME was left as is, the second became SAMEFIELDNAME1.
Not able for various reasons to alias the column names in the proc, which needed to remain as is for other users, I just recoded the program to read field SAMEFIELDNAME1.

I also posted this on VB Forum at:
http://www.vbforums.com/showthread.php?627485-How-to-distinguish-between-2-fields-with-the-same-name-(after-a-table-join)-in-vb6