problems adding a second table to a dataset

Discussion in 'ASP .Net' started by TB, Jan 24, 2006.

  1. TB

    TB Guest

    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(""; 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) & " " &
    result2 = myDataSet.Tables("tbl2").Rows(0).Item(0) & " " &

    or this

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

    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.


    TB, Jan 24, 2006
    1. Advertisements

  2. TB

    Guest Guest

    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.
    Guest, Jan 24, 2006
    1. Advertisements

  3. TB

    TB Guest

    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, Jan 24, 2006
  4. 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

    MydataAdapter= New MysqlDataAdapter(StrSQL,MyConnection)
    Kenneth Windish, Jan 25, 2006
  5. TB

    TB Guest

    You got it right. Thanks a lot.


    TB, Jan 25, 2006
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.