Accesing fields in a datareader

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a simple query with an inner join:
select A.Field1, B.Field1 from A inner join B on A.Field2 = B.Field2

when I read the values with an oledbdatareader accesing a SQL Server
database (reader["Field1"]) return the value from the table "A", and i can't
read the value in the table "B". I test this --> reader["A.Field1"], but
result in an exception

when I use the same query accesing a simple MS Access database I must use
the reader like this --> reader["A.Field1"] and reader["B.Field1"] (I can
access the values in all tables A and B). I test this --> reader["Field1"],
but result in an exception.

How i can access the same field name in two different tables when I'm
accessing a SQL Server???


thanks in advance
 
Hi Jordi,

Either define alias in sql statement, example: select A.Field1 as a,
B.Field1 as b
Or access reader field using int indexer.
 
For a simple join query, you should be able to dupe the work from SQL Server
in the same exact manner. If you need two separate tables in a relationship,
consider moving to a DataSet instead of a DataReader.

Not sure if this helps.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Thanks for the response, but.....

there's any one solution??

Is very hard for me add aliases in the fields, and the indexer is not secure
becuase I can add a new file in the future

Thanks a lot

Miha Markic said:
Hi Jordi,

Either define alias in sql statement, example: select A.Field1 as a,
B.Field1 as b
Or access reader field using int indexer.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Jordi Vidal said:
Hi,

I have a simple query with an inner join:
select A.Field1, B.Field1 from A inner join B on A.Field2 = B.Field2

when I read the values with an oledbdatareader accesing a SQL Server
database (reader["Field1"]) return the value from the table "A", and i
can't
read the value in the table "B". I test this --> reader["A.Field1"], but
result in an exception

when I use the same query accesing a simple MS Access database I must use
the reader like this --> reader["A.Field1"] and reader["B.Field1"] (I can
access the values in all tables A and B). I test this -->
reader["Field1"],
but result in an exception.

How i can access the same field name in two different tables when I'm
accessing a SQL Server???


thanks in advance
Click to expand...
Click to expand...
 
Jordi Vidal said:
Thanks for the response, but.....

there's any one solution??

Is very hard for me add aliases in the fields,
Click to expand...

Aliases are actually created implicitly (in your case). If you declare them
you'll give them more logical names.
And why would be hard to add aliases?

and the indexer is not secure
becuase I can add a new file in the future
Click to expand...

File? You mean you'll change the sql statement?
 
Sorry, File --> Field.

Ok, aliases are a good solution, but I think is only a patch. The real
problem is the different manner to access the data in oledbdatareader when I
use SQL Server or use MS Access, I supose that an advantatge of ADO.NET and
OLEDB is the abstraction of the SGBD (I known that isn't 100% true, always
are difference in the SQL statements, but not in the use of datareaders)

I have two tables:
A {A_Id int, A_Name varchar}
B {B_Id int, B_Name varchar, A_Id int (FK to table A)}

I think that in the definition of the FK is correct to use the same name in
the master and slave tables.

When I use Access as SGBD I access the values from datareader by this way:
reader["B.A_Id"] (for value in table B) and reader["A.A_Id"] (for value in
table A)
In this case the value are the same and is indifferent the value to read,
but when I use SQL Server as SGBD I can only access the values by this way:
reader["A_Id"] (I can't especify the table because produce an exception)

The really problem is the different sintax of retreive values when I change
the SGBD (it sames to be a bug).

The question is: Exists any property or flag to force the datareader to
always "use" the name of the table to access the values?? or the unic
solution is the use of aliases??

Thanks for help
 
Back
Top