How to access columns having same name in a datarow

[vish]

Hi all,

I know this might look strange at first look, but i am looping thru a
dataset created using to different tables
from database.These two tables have some fields with the same names.

Now i want to use those columns from datarow. How can i get exactly
the same column i want not the othere duplicate one residing in the
datarow?

If you understand it pls reply me.

Thanks in advance,
Vish

 

[Guest]

Vish,

Why not use an alias in the Select statement that retrieves the data from
the database?

Kerry Moorman

 

[vish]

Thanks Kerry,

But actually the requirement is such that i can not use the alias in
the select statement. As i want to use both columns ( that are with
same name ) for different purpose in a code block.

Is it still possible in the programmers world?

Thanks very much for your reply.

Vish

 

[RobinS]

Yes, I posted the answer to this in the other newsgroup where you posted
this question.

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.

 

[punjab_tom]

i wouldn't listen to this dyke she doesn't know what she is talking
about

 

[Bruce W. Darby]

And folks should listen to you because..... your a low-life malcontent whose
only goal in life is to rant about how YOU were taken advantage of because
you couldn't follow company policy and keep yer code tight and fast? Give it
up Aaron, you stuck your own foot in your mouth when you signed your name to
your first punjab_tom post. Can't you just keep ONE of your personalities
in control of your miserable life?

 

[RobinS]

LOL. You're still trying to figure out if I'm female or male! What a
goober! You just absolutely crack me up.

Robin S.
King of Russia

 

[Cor Ligthert [MVP]]

Punjab,

i wouldn't listen to this dyke she doesn't know what she is talking
about

I don't know it, but what if RobinS is a dyke.

Maybe in your country it is normal to judge people about their sexual
interest, in my country it is the same as doing that because sombodies
politic, religion or race and is seen as a crime. Therefore please we are a
international newsgroup where there was alomost never any discussion about
religion or politic, we are just busy with our job.

And whatever the sexual interest of RobinS is, he/she does a good job here
to help everybody.

Cor

....

 

[Bruce W. Darby]

Here, here, Cor. Could not have said it better myself. Robin has helped a
lot of folks here and I do appreciate that, cause I'm one of them. And not
just because he's appreciated my posts to pujab_aaron. You yourself have
been extremely active in helping folks here and I'm learning a lot about the
language from articles on your website.

Thanks,
Bruce

 

[Kat]

I am still looking for an answer to this question, as I do not know what
other newsgroup the question was asked & answered in. Can someone just
answer the question, please?

 

[RobinS]

Thanks, Cor and Bruce. I appreciate that!

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.

 

[RobinS]

My understanding is the OP had two data tables in one dataset, and had the
same column name in both data tables.

So what?

When you access the columns, you have to specify which data table you're
getting it out of, so it doesn't matter that you have the same column name
in two tables.

Now, if he has two columns of the same name in one dataset, that's a
different problem. Then, the question is, does he know which one is which?
For example, the first one belongs with the customer table, and the second
one belongs with the product table.

In that case, I would loop through the field names and set two indeces to
the appropriate columns, and access the data that way.

Dim idxOne as integer
Dim idxTwo as integer
For each i as Integer = 0 to myDataSet.Tables(0).Columns.Count - 1
If dc.ColumnName = "customerid" then
If idxOne > 0 Then
idxTwo = i
Else
idxOne = i
End If
End If
If idxOne > 0 and IdxTwo > 0 Then Exit For
Next i

Then in your code, when you want the first one,

For each dr as DataRow in myDataSet.Tables(0).Rows
debug.print "First customer id is " & _
dr.Items(idxOne).ToString
debug.Print "Second customer id is " & _
dr.Items(idxTwo).ToString
Next dr

If you're the OP, don't multi-post. If you want to post in multiple
newsgroups, post them all at once, so a solution someone provides in one
shows up in all of them. If you're *not* the OP, this is *why* he shouldn't
have multi-posted. :-D

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------

 

[Cor Ligthert [MVP]]

Kat,

As I understood the question well and there are conflicting names in the
solution, than I probably would use the numbered indexer.

Which I use by the way mostly if are functions which I use for more
datatables.

Cor

 

[Tom Shelton]

Hi all,

I know this might look strange at first look, but i am looping thru a
dataset created using to different tables
from database.These two tables have some fields with the same names.

Now i want to use those columns from datarow. How can i get exactly
the same column i want not the othere duplicate one residing in the
datarow?

If you understand it pls reply me.

Thanks in advance,
Vish

Vish...

I believe that in the case you describe, the dataset will name the duplicate
columns with a numeric suffix. For example, if I have two columns called
"Bling" in my dataset, the dataset will name them "Bling" and "Bling1".

Anway, HTH

 

[vish]

My understanding is the OP had two data tables in one dataset, and had the
same column name in both data tables.

So what?

When you access the columns, you have to specify which data table you're
getting it out of, so it doesn't matter that you have the same column name
in two tables.

Now, if he has two columns of the same name in one dataset, that's a
different problem. Then, the question is, does he know which one is which?
For example, the first one belongs with the customer table, and the second
one belongs with the product table.

In that case, I would loop through the field names and set two indeces to
the appropriate columns, and access the data that way.

Dim idxOne as integer
Dim idxTwo as integer
For each i as Integer = 0 to myDataSet.Tables(0).Columns.Count - 1
If dc.ColumnName = "customerid" then
If idxOne > 0 Then
idxTwo = i
Else
idxOne = i
End If
End If
If idxOne > 0 and IdxTwo > 0 Then Exit For
Next i

Then in your code, when you want the first one,

For each dr as DataRow in myDataSet.Tables(0).Rows
debug.print "First customer id is " & _
dr.Items(idxOne).ToString
debug.Print "Second customer id is " & _
dr.Items(idxTwo).ToString
Next dr

If you're the OP, don't multi-post. If you want to post in multiple
newsgroups, post them all at once, so a solution someone provides in one
shows up in all of them. If you're *not* the OP, this is *why* he shouldn't
have multi-posted. :-D

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.






- Show quoted text -

Thanks all for your answers,

I think Robin has come the closest to solution. But still i am asking
more than that which may not be possible. As we may use the same query
for those table that have
many columns with same name and they will be residing in a single
datatable of the dataset. So in that case it could be more difficult
to handle. I am still hoping for its solution if its possible
anyways.

Thanks again to you all,
Vish

 

[RobinS]

Thanks all for your answers,

I think Robin has come the closest to solution. But still i am asking
more than that which may not be possible. As we may use the same query
for those table that have
many columns with same name and they will be residing in a single
datatable of the dataset. So in that case it could be more difficult
to handle. I am still hoping for its solution if its possible
anyways.

Thanks again to you all,
Vish

I don't really understand what your problem is. Are you saying you query
the database and join two tables and get the same column from both tables?
Just change your query and add aliases.

Select a.CustomerID as c_CustomerID, b.CustomerID as o_CustomerID FROM
Customer a, Orders b where a.whatever = b.whatever.

If you still need more help, be more specific as to exactly what you're
trying to do, because it's not clear.

Robin S.

 

[vish]

Hi Robin,

Thanks but I am clerifying my requirement here:

Select a.CustomerID as c_CustomerID, b.CustomerID as o_CustomerID
FROM
Customer a, Orders b where a.whatever = b.whatever.

Here is wht you have written in your example. That's really working
normally. But i was asking that is it possible to trace the
same named columns ( customerID in your example above) without giving
them any aliases. ( i dont want to access them using index of them
too.)

e.g. ( query which i use)
-----------------------------------------------------------------------------------------------------------------------------------------
Select a.CustomerID , b.CustomerID FROM Customer a, Orders b where
a.whatever = b.whatever

and in code,

[language c#]

DataSet dsCustomer; // this contains the single datatable from above
query
string str1,str2;
foreach( DataRow dr in dsCustomer.Table[0].Rows)
{
str1 = (string) dr["CustomerID"]; // This needs to be from table a
str2 = (string) dr["CustomerID"]; // This needs to be from table b
}

-----------------------------------------------------------------------------------------------------------------------------------------
Now the problem I am facing here is str2 always contains the value of
customerID from table a as its first in sequence. I have separate uses
of str1 and str2 values in my code.

This really one of my requirements in my project. I could have gone to
some of the solution given by you people in this post but it may not
be proper for future in my coding and thats why i am looking for the
way to do it.

I hope this will be clearer to you. Thanks to you all who have given
their precious time guiding me.

Vish

 

[RobinS]

I do understand what you're getting at. As several people have pointed out,
there is no way to do that. Also, I believe at least one person put forth
the fact that if you do this:

select a.Customer_ID, b.Customer_ID from ...

The query results will have Customer_ID and Customer_ID1 as the column
names, because a datatable can not have duplicates in the columnnames. I
have also found this to be true. Here's a test to show you how to prove
that.

Public Sub TestColumnsWithSameName()
Dim dt As DataTable
'open the connection
Using cnn As New SqlConnection(My.Settings.myConnectionString)
cnn.Open()
'define the command
Dim cmd As New SqlCommand
cmd.Connection = cnn
cmd.CommandText = "SELECT ProductID, ProductID FROM Product"
'define the data adapter and fill the data table
Dim da As New SqlDataAdapter(cmd)
dt = New DataTable()
da.Fill(dt)
End Using
For Each col As DataColumn In dt.Columns
Debug.Print("column header = " & col.ColumnName)
Next
End Sub

Gives you this output:

column header = ProductID
column header = ProductID1

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------

Hi Robin,

Thanks but I am clerifying my requirement here:

Select a.CustomerID as c_CustomerID, b.CustomerID as o_CustomerID
FROM
Customer a, Orders b where a.whatever = b.whatever.

Here is wht you have written in your example. That's really working
normally. But i was asking that is it possible to trace the
same named columns ( customerID in your example above) without giving
them any aliases. ( i dont want to access them using index of them
too.)

e.g. ( query which i use)
-----------------------------------------------------------------------------------------------------------------------------------------
Select a.CustomerID , b.CustomerID FROM Customer a, Orders b where
a.whatever = b.whatever

and in code,

[language c#]

DataSet dsCustomer; // this contains the single datatable from above
query
string str1,str2;
foreach( DataRow dr in dsCustomer.Table[0].Rows)
{
str1 = (string) dr["CustomerID"]; // This needs to be from table a
str2 = (string) dr["CustomerID"]; // This needs to be from table b
}

-----------------------------------------------------------------------------------------------------------------------------------------
Now the problem I am facing here is str2 always contains the value of
customerID from table a as its first in sequence. I have separate uses
of str1 and str2 values in my code.

This really one of my requirements in my project. I could have gone to
some of the solution given by you people in this post but it may not
be proper for future in my coding and thats why i am looking for the
way to do it.

I hope this will be clearer to you. Thanks to you all who have given
their precious time guiding me.

Vish