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(""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, Jan 24, 2006
    #1
    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.
    Peter
     
    Guest, Jan 24, 2006
    #2
    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
     
    TB, Jan 24, 2006
    #3
  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

    TABLE1:
    (ADD THIS LINE HERE)
    MydataAdapter= New MysqlDataAdapter(StrSQL,MyConnection)
    HTH
    Ken
     
    Kenneth Windish, Jan 25, 2006
    #4
  5. TB

    TB Guest

    You got it right. Thanks a lot.

    Cheers,

    TB
     
    TB, Jan 25, 2006
    #5
    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.