Retrieving from a datareader by column Name with multiple columns same name

J

Jamin Mace

I am trying to pull data out of a data reader by column name from a cmd that
joins to tables. Both tables have an "ID" column. How do I access by column
name and include the table to access from? All of my code and sql is below

I want to access the data this way to that I don't have to alias the columns
in all of my queries.


Thanks in advance

Jamin Mace
Code:
Dim cn As SqlConnection

Dim dr As SqlDataReader

Dim cmd As SqlCommand

cn = New SqlConnection("ConnectionString")

cn.Open()

cmd = New SqlCommand("SELECT dbo.temp1.ID, " & _

"dbo.temp1.temp1Data, " & _

"dbo.temp2.ID, " & _

"dbo.temp2.temp2data," & _

"FROM(dbo.temp1) " & _

"LEFT JOIN dbo.temp2 on dbo.temp1.temp2id = dbo.temp2.id ")

cmd.Connection = cn

dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

'try to extract data using columnname

Debug.WriteLine(dr.Item("ID")) 'This prints the temp1.ID How do I print the
temp2.ID

Debug.WriteLine(dr.Item("dbo.temp2.ID")) 'This gives an index out of range
property

'How to I access the temp.ID and temp2.ID using the column name since the
columns have the same name

Here is my test SQL Scripts:

/*Table scripts */
CREATE TABLE [dbo].[temp1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[temp1Data] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[temp2ID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[temp2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[temp2data] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[temp1] WITH NOCHECK ADD
CONSTRAINT [PK_temp1] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[temp2] WITH NOCHECK ADD
CONSTRAINT [PK_temp2] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[temp1] ADD
CONSTRAINT [FK_temp1_temp2] FOREIGN KEY
(
[temp2ID]
) REFERENCES [dbo].[temp2] (
[ID]
)
GO


/*Insert some test data */
INSERT dbo.temp2
(temp2Data)
Values ('TestTemp2Data')

INSERT dbo.temp2
(temp2Data)
Values ('TestTemp2Data1')

INSERT dbo.temp1
(temp1data,
temp2ID)
Values ('testTemp1Data',
1)

/* now select the data */
SELECT dbo.temp1.ID,
dbo.temp1.temp1Data,
dbo.temp2.ID,
dbo.temp2.temp2data
FROM dbo.temp1
LEFT JOIN dbo.temp2 on dbo.temp1.temp2id = dbo.temp2.id
 
M

Miha Markic

Hi Jamin,

Modify in your query one of IDs :dbo.temp2.ID as secondId
In this way it will apear under alias and you'll access it by ["secondId"]
 
J

Jamin Mace

Thanks, I am aware that aliasing the column will work. But, I'm am trying
to keep a common routine that unloads the data in the reader into a class
object. Aliasing the column would mean that I would either have to break
away from the common routine or modify about 20 stored procedures.

Thanks for the help

Jamin
Miha Markic said:
Hi Jamin,

Modify in your query one of IDs :dbo.temp2.ID as secondId
In this way it will apear under alias and you'll access it by ["secondId"]

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

Jamin Mace said:
I am trying to pull data out of a data reader by column name from a cmd that
joins to tables. Both tables have an "ID" column. How do I access by column
name and include the table to access from? All of my code and sql is below

I want to access the data this way to that I don't have to alias the columns
in all of my queries.


Thanks in advance

Jamin Mace
Code:
Dim cn As SqlConnection

Dim dr As SqlDataReader

Dim cmd As SqlCommand

cn = New SqlConnection("ConnectionString")

cn.Open()

cmd = New SqlCommand("SELECT dbo.temp1.ID, " & _

"dbo.temp1.temp1Data, " & _

"dbo.temp2.ID, " & _

"dbo.temp2.temp2data," & _

"FROM(dbo.temp1) " & _

"LEFT JOIN dbo.temp2 on dbo.temp1.temp2id = dbo.temp2.id ")

cmd.Connection = cn

dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

'try to extract data using columnname

Debug.WriteLine(dr.Item("ID")) 'This prints the temp1.ID How do I print the
temp2.ID

Debug.WriteLine(dr.Item("dbo.temp2.ID")) 'This gives an index out of range
property

'How to I access the temp.ID and temp2.ID using the column name since the
columns have the same name

Here is my test SQL Scripts:

/*Table scripts */
CREATE TABLE [dbo].[temp1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[temp1Data] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[temp2ID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[temp2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[temp2data] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[temp1] WITH NOCHECK ADD
CONSTRAINT [PK_temp1] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[temp2] WITH NOCHECK ADD
CONSTRAINT [PK_temp2] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[temp1] ADD
CONSTRAINT [FK_temp1_temp2] FOREIGN KEY
(
[temp2ID]
) REFERENCES [dbo].[temp2] (
[ID]
)
GO


/*Insert some test data */
INSERT dbo.temp2
(temp2Data)
Values ('TestTemp2Data')

INSERT dbo.temp2
(temp2Data)
Values ('TestTemp2Data1')

INSERT dbo.temp1
(temp1data,
temp2ID)
Values ('testTemp1Data',
1)

/* now select the data */
SELECT dbo.temp1.ID,
dbo.temp1.temp1Data,
dbo.temp2.ID,
dbo.temp2.temp2data
FROM dbo.temp1
LEFT JOIN dbo.temp2 on dbo.temp1.temp2id = dbo.temp2.id
Click to expand...
Click to expand...
 
M

Miha Markic

Hi Jamin,

Either way you won't get two ID fields (the second one will apear under
random alias set by provider)

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

Jamin Mace said:
Thanks, I am aware that aliasing the column will work. But, I'm am trying
to keep a common routine that unloads the data in the reader into a class
object. Aliasing the column would mean that I would either have to break
away from the common routine or modify about 20 stored procedures.

Thanks for the help

Jamin
Miha Markic said:
Hi Jamin,

Modify in your query one of IDs :dbo.temp2.ID as secondId
In this way it will apear under alias and you'll access it by ["secondId"]

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

Jamin Mace said:
I am trying to pull data out of a data reader by column name from a
Click to expand...
cmd
that
joins to tables. Both tables have an "ID" column. How do I access by column
name and include the table to access from? All of my code and sql is below

I want to access the data this way to that I don't have to alias the columns
in all of my queries.


Thanks in advance

Jamin Mace
Code:
Dim cn As SqlConnection

Dim dr As SqlDataReader

Dim cmd As SqlCommand

cn = New SqlConnection("ConnectionString")

cn.Open()

cmd = New SqlCommand("SELECT dbo.temp1.ID, " & _

"dbo.temp1.temp1Data, " & _

"dbo.temp2.ID, " & _

"dbo.temp2.temp2data," & _

"FROM(dbo.temp1) " & _

"LEFT JOIN dbo.temp2 on dbo.temp1.temp2id = dbo.temp2.id ")

cmd.Connection = cn

dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

'try to extract data using columnname

Debug.WriteLine(dr.Item("ID")) 'This prints the temp1.ID How do I
Click to expand...
print
the
temp2.ID

Debug.WriteLine(dr.Item("dbo.temp2.ID")) 'This gives an index out of range
property

'How to I access the temp.ID and temp2.ID using the column name since the
columns have the same name

Here is my test SQL Scripts:

/*Table scripts */
CREATE TABLE [dbo].[temp1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[temp1Data] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[temp2ID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[temp2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[temp2data] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[temp1] WITH NOCHECK ADD
CONSTRAINT [PK_temp1] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[temp2] WITH NOCHECK ADD
CONSTRAINT [PK_temp2] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[temp1] ADD
CONSTRAINT [FK_temp1_temp2] FOREIGN KEY
(
[temp2ID]
) REFERENCES [dbo].[temp2] (
[ID]
)
GO


/*Insert some test data */
INSERT dbo.temp2
(temp2Data)
Values ('TestTemp2Data')

INSERT dbo.temp2
(temp2Data)
Values ('TestTemp2Data1')

INSERT dbo.temp1
(temp1data,
temp2ID)
Values ('testTemp1Data',
1)

/* now select the data */
SELECT dbo.temp1.ID,
dbo.temp1.temp1Data,
dbo.temp2.ID,
dbo.temp2.temp2data
FROM dbo.temp1
LEFT JOIN dbo.temp2 on dbo.temp1.temp2id = dbo.temp2.id
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL Server Error: 8629 2
"IF" test in SP evals to True and False at same time using DataAdapter.FillSchema() 7
Parent -Child relationship 15
how to retrieve ddl script from a db object..? 5
binding tables in many-to-many relationships 4
GetChildRows() Fails. 3
6 digits Unique Number 6
Fast SQL Server Stored Procedure runs slow in VB.NET 4

Top