SQL Join and ASP.NET dataset - newbie

  • Thread starter Thread starter Sebastian Kenny
  • Start date Start date
S

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
 
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?
Click to expand...

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 *.
 
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
 
Back
Top