Joining two tables with fields that have the same names

[post re-directed here from sqlserver.programming]

Hi all,

I'm joining two tables that have some of the same field names, like so:

ParentTable
ID
Note
DateCreated

ChildTable
ID
ParentID
Note
DateCreated

I then retrieve this data in a SQLDataReader (Using VB2005, .Net 2.0, MS
SQL 2000) using a statement like this:
SELECT C.*, P.* FROM ChildTable
INNER JOIN ParentTable P ON C.ParentID = P.ID

In ADO.Classic, I could specify which table and field I wanted in a
recordset like so:
oRecordSet("P.Note")

However in the .Net world with a SQLDataReader (at least when I tried last
time), it would not let me access the field like so:
oDataReader("P.Note")

I could only specify the field name, not the table name. It seems we are
taking a step back with ADO.Net in this particular case, unless I'm wrong. I
know I can specify a field name in my SQL like so "SELECT C.Note as
ChildNote", but I have some other considerations that make that impractical
in this case. I also know I can specify the index of the columns, but I
anticipate there will be changes in my table structure in the future and
don't want to have to re-work this part each time. I also am aware that
typically one would want to restrict your query results to the smallest
group of fields necessary, but in this case I need pretty much all of them
and the other considerations I mentioned above make it difficult to
specify all of them. I'm really hoping someone will point out that I'm wrong
and that it is possible to specify "TableName.FieldName".

Thanks in advance!


.

Relevant Pages

  • Re: Dont want to wrap long text - just truncate
    ... document then postprocess the results using VBA, ... All you can do in the standard user interface is specify a table of view, ... you have to specify the SQL in an OpenDataSource method call in VBA. ... via MS Query, MS Query has to be installed, and you will need an ODBC DSN ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Dont want to wrap long text - just truncate
    ... >new document then postprocess the results using VBA, ... > and specify a bit of sorting and filtering (go into the Edit Recipients ... To do more, you either have to go through MS Query, ... > or you have to specify the SQL in an OpenDataSource method call in VBA. ...
    (microsoft.public.word.mailmerge.fields)
  • RE: problems with visual studio 2008 on vista
    ... Administrative utility to install and uninstall ASP.NET features on a SQL ... -d <database> Database name for use with application services. ... specify a database and ensure that the web.config file is modified ...
    (microsoft.public.dotnet.framework.aspnet)
  • group by errors
    ... Creating a view in SQL and use Access to open the view ... >specify ALL with the CUBE or ROLLUP operators. ... the query. ... >summary rows are introduced into the result set. ...
    (microsoft.public.access.queries)
  • Re: sliced find in Linq
    ... by over all of the returned columns. ... Since the original query was unordered, I'd like the above SQL to ... (Perhaps your table doesn't have a primary key though?) ... didn't specify the ordering, so any ordering could have been used. ...
    (microsoft.public.dotnet.languages.csharp)