SQL Join and ASP.NET dataset - newbie

[Sebastian Kenny]

I'm new to VB.NET. I'm usging an SQL select join in the following way:

----------------
strConn = Application.Get("DB_CONNECTION_STRING")
Dim MySQL As String
MySQL = "Select * from Users u, Banks b where u.UserID=? and b.BankID =
u.BankID"
Dim MyConn As New Odbc.OdbcConnection(strConn)

Dim Cmd As New Odbc.OdbcDataAdapter(MySQL, MyConn)
Cmd.SelectCommand.Parameters.Add("MemberID",System.Data.Odbc.OdbcType.Int).Value
= iUserID

Dim ds As DataSet = New DataSet
Cmd.Fill(ds)

Dim dr As DataRow
dr = ds.Tables(0).Rows(0)

----------------

It works fine, but I have a problem. If both tables have a field of the
same name (e.g. DATECREATED), I assumed that I could use (eg):

dr("u.DATECREATED")

to access a specific column, but it isn't working.

Can anyone tell me how to do this?

Thanks

Seb

 

[Chris R. Timmons]

I'm new to VB.NET. I'm usging an SQL select join in the
following way:

----------------
strConn = Application.Get("DB_CONNECTION_STRING")
Dim MySQL As String
MySQL = "Select * from Users u, Banks b where u.UserID=? and
b.BankID = u.BankID"
Dim MyConn As New Odbc.OdbcConnection(strConn)

Dim Cmd As New Odbc.OdbcDataAdapter(MySQL, MyConn)
Cmd.SelectCommand.Parameters.Add("MemberID",System.Data.Odbc.Odbc
Type.Int).Value
= iUserID

Dim ds As DataSet = New DataSet
Cmd.Fill(ds)

Dim dr As DataRow
dr = ds.Tables(0).Rows(0)

----------------

It works fine, but I have a problem. If both tables have a field
of the same name (e.g. DATECREATED), I assumed that I could use
(eg):

dr("u.DATECREATED")

to access a specific column, but it isn't working.

Can anyone tell me how to do this?

Seb,

If you want to access columns by name like that, you cannot use
"select *". You must explicitly specify all of the column names
instead of using *.

 

[W.G. Ryan eMVP]

Select * usually isn't a good idea unless you definitely want all of the
Columns returned. I'd also recommend not doing the join and using two
separate queries with a DataRelation between the tables. However if you
need to use SELECT * and use a JOIN, just name the columns when you get the
data back - you can name just the ones you want to reference but you might
as well name all of them.. The ColumnName property will do it for you
http://msdn.microsoft.com/library/d.../html/frlrfsystemdatadatacolumnclasstopic.asp