Reading ambiguous name columns from a SQLDataReader

ikantspelwurdz

Well-known member
Joined
Dec 8, 2009
Messages
49
Programming Experience
1-3
Suppose a database with two tables, "Main" and "Users."

Main:
IDUser1IDUser2ID
112

Users:
UserIDName
1Alice
2Bob


I've got a SQL statement that looks something like this:
select User1.Name, User2.Name from main
inner join Users as User1 on main.User1ID = User1.UserID
inner join Users as User2 on main.User2ID = User2.UserID

The SQL itself works fine. But I want to read this data using a SQLDataReader, and I want to reference by column name, not column index.

This code:
Code:
Dim reader As SqlDataReader = ExecuteReader(sql)
reader.Read()
Dim alice as String = reader.Item("User1.Name")
Dim bob as String = reader.Item("User2.Name")
...does not work. It crashes with IndexOutOfRangeException. These columns do not exist. A look at the metadata shows both columns are called "Name."

This code:
Code:
Dim reader As SqlDataReader = ExecuteReader(sql)
reader.Read()
Dim alice as String = reader.Item("Name")
Dim bob as String = reader.Item("Name")
...runs, but both strings are going to be "Alice."

How can I best solve this problem? Going by column index isn't a good solution; the indices could (and almost certainly will) change. Using multiple SQLDataReaders would work, but I'd prefer a more elegant solution if there is one.
 

Herman

Well-known member
Joined
Oct 18, 2011
Messages
882
Location
Montreal, QC, CA
Programming Experience
10+
Use aliases in your SQL query:

select User1.Name AS User1Name, User2.Name AS User2Name from main
 

ikantspelwurdz

Well-known member
Joined
Dec 8, 2009
Messages
49
Programming Experience
1-3
That won't work in the long run, unfortunately. The real "Users" table has 160 fields in it, and they can change. I don't mind updating the VB code when the table fields change, but also having to maintain a SQL query with 320 aliased columns is too much work.

I did find some other threads on this subject on Google, and the best suggestion I've seen is to build the SQL dynamically. That would still take two trips to the DB, one to get the schema (so you can build the dynamic SQL query), and another to execute the dynamic query.
 
Top Bottom