problems adding a second table to a dataset

  • Thread starter Thread starter TB
  • Start date Start date
T

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

(ADD THIS LINE HERE)
MydataAdapter= New MysqlDataAdapter(StrSQL,MyConnection)
MyDataAdapter.Fill(MyDataSet, "tbl2")
Click to expand...

HTH
Ken
 
Back
Top