Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
CODE PROJECT For Those Who Code
  • Home
  • Articles
  • FAQ
Community
  1. Home
  2. General Programming
  3. Visual Basic
  4. Returning a single recordset of data from two related tables in a Dataset

Returning a single recordset of data from two related tables in a Dataset

Scheduled Pinned Locked Moved Visual Basic
csharpdatabasehelptutorial
13 Posts 3 Posters 2 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • A Ashfield

    Try this:

    SELECT PLSupplierAccount.*,PLProposedPayment.*
    FROM PLSupplierAccount INNER JOIN PLProposedPayment ON PLSupplierAccount.PLSupplierAccountID = PLProposedPayment.PLSupplierAccountID

    But change PLSupplierAccount.*,PLProposedPayment.* for the columns you really need - its not good practice to do a select *, although I often use it for code examples ;)

    Bob Ashfield Consultants Ltd

    S Offline
    S Offline
    Steven J Jowett
    wrote on last edited by
    #3

    Bob Thanks for the reply, but it's not the SQL statement I am having trouble with. I need to return the data from a DataSet i.e. execute the select statement against the dataset and get the results as a single datatable.

    Steve Jowett ------------------------- It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)

    A B 2 Replies Last reply
    0
    • S Steven J Jowett

      Bob Thanks for the reply, but it's not the SQL statement I am having trouble with. I need to return the data from a DataSet i.e. execute the select statement against the dataset and get the results as a single datatable.

      Steve Jowett ------------------------- It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)

      A Offline
      A Offline
      Ashfield
      wrote on last edited by
      #4

      Sorry, I misunderstood. Personally I would be tempted to requery the database and create the new table that way. This may help, but I have not tried it myself. I'm kind of old fashioned, I like to let sql do the work for me whenever I can :) Microsoft Article

      Bob Ashfield Consultants Ltd

      1 Reply Last reply
      0
      • S Steven J Jowett

        Bob Thanks for the reply, but it's not the SQL statement I am having trouble with. I need to return the data from a DataSet i.e. execute the select statement against the dataset and get the results as a single datatable.

        Steve Jowett ------------------------- It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)

        B Offline
        B Offline
        BDEz Member 3919223
        wrote on last edited by
        #5

        so you need to drop the combined dataset into an array, then loop through the array and assign each line to a pre-built internal data table?

        A 1 Reply Last reply
        0
        • B BDEz Member 3919223

          so you need to drop the combined dataset into an array, then loop through the array and assign each line to a pre-built internal data table?

          A Offline
          A Offline
          Ashfield
          wrote on last edited by
          #6

          Thats Microsoft's solution not mine :) I would requery the database and create the new table that way

          Bob Ashfield Consultants Ltd

          S 1 Reply Last reply
          0
          • A Ashfield

            Thats Microsoft's solution not mine :) I would requery the database and create the new table that way

            Bob Ashfield Consultants Ltd

            S Offline
            S Offline
            Steven J Jowett
            wrote on last edited by
            #7

            Ideally I would also requery the database (and normally would), but in this case I need to load a subset of data from the database, make some changes to it and when the user is happy with the changes, submit them back. Thanks for the help and advise

            Steve Jowett ------------------------- It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)

            B A 2 Replies Last reply
            0
            • S Steven J Jowett

              Ideally I would also requery the database (and normally would), but in this case I need to load a subset of data from the database, make some changes to it and when the user is happy with the changes, submit them back. Thanks for the help and advise

              Steve Jowett ------------------------- It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)

              B Offline
              B Offline
              BDEz Member 3919223
              wrote on last edited by
              #8

              I failed to mention two wonderful classes that you can use to do what you want quickly, one is dataset and the other is dataview and they should not be overlooked because they can help you out. Public Class DataView Inherits System.ComponentModel.MarshalByValueComponent Member of: System.Data Summary: Represents a databindable, customized view of a System.Data.DataTable for sorting, filtering, searching, editing, and navigation. Public Class DataSet Inherits System.ComponentModel.MarshalByValueComponent Member of: System.Data Summary: Represents an in-memory cache of data.

              A 1 Reply Last reply
              0
              • B BDEz Member 3919223

                I failed to mention two wonderful classes that you can use to do what you want quickly, one is dataset and the other is dataview and they should not be overlooked because they can help you out. Public Class DataView Inherits System.ComponentModel.MarshalByValueComponent Member of: System.Data Summary: Represents a databindable, customized view of a System.Data.DataTable for sorting, filtering, searching, editing, and navigation. Public Class DataSet Inherits System.ComponentModel.MarshalByValueComponent Member of: System.Data Summary: Represents an in-memory cache of data.

                A Offline
                A Offline
                Ashfield
                wrote on last edited by
                #9

                That was (or still is) the problem, Steve has a dataset with 2 datatables that he wants to query with a join. There is no real support within the dataset/datatable/datarelation world to do this easily.

                Bob Ashfield Consultants Ltd

                B 1 Reply Last reply
                0
                • S Steven J Jowett

                  Ideally I would also requery the database (and normally would), but in this case I need to load a subset of data from the database, make some changes to it and when the user is happy with the changes, submit them back. Thanks for the help and advise

                  Steve Jowett ------------------------- It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)

                  A Offline
                  A Offline
                  Ashfield
                  wrote on last edited by
                  #10

                  Sometimes life's a bitch..... Just a thought, would linq help? I have only played with it briefly, but it might be worth a look?

                  Bob Ashfield Consultants Ltd

                  S 1 Reply Last reply
                  0
                  • A Ashfield

                    Sometimes life's a bitch..... Just a thought, would linq help? I have only played with it briefly, but it might be worth a look?

                    Bob Ashfield Consultants Ltd

                    S Offline
                    S Offline
                    Steven J Jowett
                    wrote on last edited by
                    #11

                    I've not even had time to look at LINQ, but I think I will make the time.

                    Steve Jowett ------------------------- It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)

                    B 1 Reply Last reply
                    0
                    • A Ashfield

                      That was (or still is) the problem, Steve has a dataset with 2 datatables that he wants to query with a join. There is no real support within the dataset/datatable/datarelation world to do this easily.

                      Bob Ashfield Consultants Ltd

                      B Offline
                      B Offline
                      BDEz Member 3919223
                      wrote on last edited by
                      #12

                      Lets see if I can think this through ... We point / read a dataset (call it dsOne) to a table from your SQL? server. We point / read a second dataset (call it dsTwo) to another table from your SQL server. We point a dataview (call it dvOne) to dsOne We do a sort on dvOne on PLSupplierAccountID We pull the first entry for PLSupplierAccountID in dvOne We point a second dataview (call it dvTwo) to dsTwo We use the entry from dv.One SupplierAccountID to do a dvtwo.Find If we get a match we combine the row entry from dvOne with dvTwo in a DataGrid Once you get all the matches combined in the datagrid, you can make your changes using your form. These changes will be stored on the form until you "submit" them back to your SQL server using a update query. This sound about right to you?

                      1 Reply Last reply
                      0
                      • S Steven J Jowett

                        I've not even had time to look at LINQ, but I think I will make the time.

                        Steve Jowett ------------------------- It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)

                        B Offline
                        B Offline
                        BDEz Member 3919223
                        wrote on last edited by
                        #13

                        Looks interesting! I will have to play with this!

                        1 Reply Last reply
                        0
                        Reply
                        • Reply as topic
                        Log in to reply
                        • Oldest to Newest
                        • Newest to Oldest
                        • Most Votes


                        • Login

                        • Don't have an account? Register

                        • Login or register to search.
                        • First post
                          Last post
                        0
                        • Categories
                        • Recent
                        • Tags
                        • Popular
                        • World
                        • Users
                        • Groups