DbDataReadet.GetOrdinal() - duplicate column name

  • Thread starter Thread starter Guest
  • Start date Start date
Dave,

If you look at the columns in you DataReader you will see two columns called
"Name". So DbDataReader("Name") is always going to return the first Field
called "Name".

The best way to fix this is not to use "*" on your select statement and to
alias the column names so that they are uniquely named. Try something like
"select c.Name as CompanyName, p.Name as PersonName, c.col1, c.col2 ... from
Company as c, Person as p where ...". Then you can use
DbDataReader("CompanyName") and DbDataReader("PersonName").

The other thing you can do is to reference the columns in you dataReader by
ordinal position instead of the field name (DbDataReader(0), DbDataReader(1),
etc).

Hope this helps,
Jason Vermillion
 
Hi Dave,
Thanks for Jason's reply.

I agree with Jason. We can't get the second column called "Name" by
DbDataReader("Name"). DataReader is always going to return the first Field
called "Name".
You can get the Schema of DataReader by "DataReader.GetSchemaTable();".
There are two columns called "Name" in table and we can't read the second
one by column name. If you want to get this value, you should have to
reference the column id (for example: DbDataReader[0], DbDataReader[1]...)
as Jason said.

The way to achieve this is to use "as" in query command "select c.Name as
CompanyName, p.Name as PersonName, c.col1, c.col2 ... from Company as c,
Person as p where ..."

Additional, in Dataset, the first column will be named "Name", but the
second one will be named "Name1". For this reason, you can get it by
dataset.table.rows[0]["Name"]. This maybe another way to achieve this.
But I'm afraid the best way is, as Jason said, using "as" in query command.

Hope this helps,
Sincerely,
Wen Yuan
 
Hi Dave,

Just want to check if the issue has been resolved.
Please feel free to reply me if you have any further issue on this. I'm
very glad to assist you.:)

Have a great day!
Wen Yuan
 
Hi;

We need to handle this for any database as our product (reporting) is used
by customers to hit their databases. So we don't know up front what they will
do, what order columns are in, etc.

Is there a way in the metadata - for any database (Oracle, MySql, DB2, Sql
Server) - to determine the table name for a column and build it up from that?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Hi Dave,
Thanks for your reply.

DBConnection.GetSchema("Columns") will show you the relationship between
Tables and Columns. For different database, I think you should create
different connections (SQLConnection, OLEDBConnection,ODBCConnetion) and
abstract them as DBConnection.
If there is anything unclear, please feel free to let me know. I'm glad to
assist you.

I hope this helps.
Best regards,
Wen Yuan
 
Could you ask the ADO.NET development team the following question:

For a commercial library where user's can pass in any kind of select, that
select could be of the form "select * from customers, companies where..." and
there is both a customers.name and companies.name column in the returned data.

If this commercial library then allows the caller to identify columns by
name to get data, how can we return customers.name and companies.name
distinctly?

As a commercial library that is seperate from the database, we have no
knowledge of the database schema except for what we request. We tried
DbDataReader.GetOrdinal("customers.name") but that didn't work.

What metadata information is returned by DbDataReader that would let us
determine this? This information must exist, but we cannot find it.

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Use DbDataReader.GetSchemaTable() to fetch a DataTable with detailed schema
information on the column(s) involved in the active query. There will be a
row in the returned DataTable for each column in the datareader.

Robert
 
With datasets, it will not name both columns the same, and I assume that
DataReaders are the same way. I When it hits a column with a duplicate
name, it adds a 1 to it, then if it hits another one, it adds a 2.

So try looking for a column called [Name] and a column called [Name1]. I
believe the second one will be from the second table.

Robin S.
 
Hi Dave,
Thanks for your reply.

I think the "BaseTableName" column returned by
DbDataReader.GetSchemaTable() is what you need.
Additionally, if the table name entries come back null, please add
"CommandBehavior.KeyInfo" into your DBCommand.
For example: SqlDataReader sr = scd.ExecuteReader(CommandBehavior.KeyInfo);

But I'm afraid we still can't identify the column by
DbDataReader.GetOrdinal("customers.name") so far. We should have to create
a function to return the Ordinal from the SchemaTable by ourselves.
For Example:
private int getIndexByTable(string tablenName, string columnName, DataTable
schemaTable)
{
foreach (DataRow dr in schemaTable.Rows)
{
if (dr["BaseTableName"].Equals(tablenName) &&
dr["BaseColumnName"].Equals(columnName))
return System.Convert.ToInt16(dr["ColumnOrdinal"]);
}
return -1;
}

Hope this helps. Please feel free to let me know if you have anything
unclear. I'm very glad to work with you.

Have a great day!
Sincerely.
Wen Yuan
 
Hello everybody,

Very interesting discussion. And very useful to me. But I have a problem
one step further, maybe any of you also do: what if you include a table
more than one time? You or your user, as it is my case. Proposed
function was great but not with this new requisite, so I'm going to
preprocess the query before pass it to the datareader to include
necessary aliases.

Sorry for my English.

Pablo Núñez
Málaga - Spain
 
Back
Top