problems adding a second table to a dataset

[TB]

Hi All:

As a newbie to ASP.NET, I think I have understood a dataset as a kind
of container where I can store several datatables to used during the
lifetime of a page. With that in mind I written the following code for
a page (simplified here):

Dim myConnection As MySqlConnection = New
MySQLConnection(""server=myserver.com; user id=myself; password=myself;
database=mydatabase; pooling=false;"
Dim myDataAdapter As MySqlDataAdapter
Dim myDataSet As DataSet = New dataset
Dim Row As DataRow
Dim strSQL as String
Dim irecord as integer
Dim result1 as String
Dim result2 as String

'Loading the first table into the dataset
irecord = 3 'For the sake of this example
strSQL = "Select firstname, lastname from table1 where ID = " & irecord
MydataAdapter = New MysqlDataAdapter(StrSQL, MyConnection)
MyDataAdapter.Fill(MyDataSet, "tbl1")

'Loading the second table into the dataset
irecord = 28 'For the sake of this example
strSQL = "Select firstname, lastname from table2 where ID = " & irecord
MyDataAdapter.Fill(MyDataSet, "tbl2")

Debugging, I can see that after executing the above, MyDataSet contains
two tables (MyDataSet.Tables.Count), however they appear to contain the
same data because

if do this:
result1 = myDataSet.Tables("tbl1").Rows(0).Item(0) & " " &
myDataSet.Tables("tbl1").Rows(0).Item(1)
result2 = myDataSet.Tables("tbl2").Rows(0).Item(0) & " " &
myDataSet.Tables("tbl2").Rows(0).Item(1)

or this

result1 = myDataSet.Tables(0).Rows(0).Item(0) & " " &
myDataSet.Tables(0).Rows(0).Item(1)
result2 = myDataSet.Tables(1).Rows(0).Item(0) & " " &
myDataSet.Tables(1).Rows(0).Item(1)

it turns out that result1 and result2 contain the same data, namely
that of tbl1.

What am I doing wrong here?

Any advice will be highly appreciated.

Thanks,

TB

[Peter Bromberg [C# MVP]]

Call the Fill method one time, with both SQL Statements as the CommandText,
one right after the other, and separated with a semicolon. Better yet, use a
Stored procedure that has two separate select statements.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"TB" wrote:

Hi All:

As a newbie to ASP.NET, I think I have understood a dataset as a kind
of container where I can store several datatables to used during the
lifetime of a page. With that in mind I written the following code for
a page (simplified here):

Dim myConnection As MySqlConnection = New
MySQLConnection(""server=myserver.com; user id=myself; password=myself;
database=mydatabase; pooling=false;"
Dim myDataAdapter As MySqlDataAdapter
Dim myDataSet As DataSet = New dataset
Dim Row As DataRow
Dim strSQL as String
Dim irecord as integer
Dim result1 as String
Dim result2 as String

'Loading the first table into the dataset
irecord = 3 'For the sake of this example
strSQL = "Select firstname, lastname from table1 where ID = " & irecord
MydataAdapter = New MysqlDataAdapter(StrSQL, MyConnection)
MyDataAdapter.Fill(MyDataSet, "tbl1")

'Loading the second table into the dataset
irecord = 28 'For the sake of this example
strSQL = "Select firstname, lastname from table2 where ID = " & irecord
MyDataAdapter.Fill(MyDataSet, "tbl2")

Debugging, I can see that after executing the above, MyDataSet contains
two tables (MyDataSet.Tables.Count), however they appear to contain the
same data because

if do this:
result1 = myDataSet.Tables("tbl1").Rows(0).Item(0) & " " &
myDataSet.Tables("tbl1").Rows(0).Item(1)
result2 = myDataSet.Tables("tbl2").Rows(0).Item(0) & " " &
myDataSet.Tables("tbl2").Rows(0).Item(1)

or this

result1 = myDataSet.Tables(0).Rows(0).Item(0) & " " &
myDataSet.Tables(0).Rows(0).Item(1)
result2 = myDataSet.Tables(1).Rows(0).Item(0) & " " &
myDataSet.Tables(1).Rows(0).Item(1)

it turns out that result1 and result2 contain the same data, namely
that of tbl1.

What am I doing wrong here?

Any advice will be highly appreciated.

Thanks,

TB

[TB]

How exactly (using my sample code as a reference)?

Does that mean that I cannot use the Fill method more than once for
each dataset? (not very practical, because one may not know both SQL
statements at the same time - the second statement may depend on
additional code to be correctly generated.)

TB

[Kenneth Windish]

Hi,

I am no expert but I think that you need to reload MydataAdapter with
contents of 2nd SQL query before adding it to the dataset

TABLE1:

strSQL = "Select firstname, lastname from table1 where ID = " & irecord
MydataAdapter = New MysqlDataAdapter(StrSQL, MyConnection)
MyDataAdapter.Fill(MyDataSet, "tbl1")
TABLE2: strSQL = "Select firstname, lastname from table2 where ID = " & irecord
(ADD THIS LINE HERE)
MydataAdapter= New MysqlDataAdapter(StrSQL,MyConnection)
MyDataAdapter.Fill(MyDataSet, "tbl2")

HTH
Ken

[TB]

You got it right. Thanks a lot.

Cheers,

TB