none
Copying From Two Different Data Tables...With a Twist!! RRS feed

  • Question

  • Hi All,

     

    I have a question about accessing datatable columns, for an application I am attempting to create.  The issue I am facing at the moment is that I have two DataTable's, dtUploadedFile (3 columns, 'Number', 'Date Joined', 'Publication'), dtMagCodes (8 Columns).

     

    What I was looking to do is return the data from dtMagCodes where it matches a row in dtUploadedFile, i.e. Search in dtMagCodes for a Code where the Description in dtMagCodes matches the description ('Publication') for the current row in dtUploadedFile.

     

    Where there is a match, I wanted to add a new column to dtUploadedFile called MagCode, and populate it with the code found in dtMagCodes?

     

    Please let me know if this is not clear.

     

    Thanks in Advance!!

    Wednesday, January 2, 2008 1:54 PM

Answers

  • Using the designer to create relations is good for quickly setting up something simple but when it comes to doing specifics you need to start using code.  I admit that there are many methods that are already pre-made and many of them i didn't even know could do certain things but it seems that many times you have to juggle methods to make things work when you could just use the same basic code that is behind many of these methods and eliminate a lot of waste.

     

    Your situation is a good example because in order for you to make this work you have gone through the process of setting up a dataset, tables, relations, objects, forms and methods.  You have all this in the background that doesn't really do anything for you.  some of these things you need obviously such as a form to view the data but all you really need is a select statement, datareader, update statement and your command objects.  you can read the values from one table with the datareader and then control the values as you see fit with varibles and add them to the other table.  The code behind all these methods and relations are all handled with basic sql commands so why not eliminate all the excess and get straight to the code.  I wrote some simple code to handle relationships between tables just by using a datareader to check the parent and child records.

     

    I don't know if you have any experience with datareaders or sql statements but if you do a little searching you will find what you need.
    Thursday, January 3, 2008 5:59 PM

All replies

  • You could set relations between the DataTables. In this case parent table will be dtUploadedFile and child table will be dtMagCodes. To be able to create relation first you need to add both datatables to same DataSet. Then you create DataRelation where you will have description column on left (dtUploadedFile) side will match description column on a right (dtMagCodes) side. Make sure that description column in dtUploadedFile DataTable is unique and does not contain duplicates, otherwise relation cannot be created. After relation created you could get all related children for specific row in dtUploadedFile DataTable calling GetChildRows method for the row.

    Thursday, January 3, 2008 11:24 AM
    Moderator
  • H VMazur,

     

    Thanks for that information, it helped, but now I am getting another exception.  It basically states that "This constraint cannot be enabled as not all values have corresponding parent values.", which I understand is due to the parent table not containing all the values for the child table. 

     

    So my question is, is there a way so that when a child row does not have a parent value, it returns null or 0 or blank?

     

    Thanks

    Thursday, January 3, 2008 4:20 PM
  • Using the designer to create relations is good for quickly setting up something simple but when it comes to doing specifics you need to start using code.  I admit that there are many methods that are already pre-made and many of them i didn't even know could do certain things but it seems that many times you have to juggle methods to make things work when you could just use the same basic code that is behind many of these methods and eliminate a lot of waste.

     

    Your situation is a good example because in order for you to make this work you have gone through the process of setting up a dataset, tables, relations, objects, forms and methods.  You have all this in the background that doesn't really do anything for you.  some of these things you need obviously such as a form to view the data but all you really need is a select statement, datareader, update statement and your command objects.  you can read the values from one table with the datareader and then control the values as you see fit with varibles and add them to the other table.  The code behind all these methods and relations are all handled with basic sql commands so why not eliminate all the excess and get straight to the code.  I wrote some simple code to handle relationships between tables just by using a datareader to check the parent and child records.

     

    I don't know if you have any experience with datareaders or sql statements but if you do a little searching you will find what you need.
    Thursday, January 3, 2008 5:59 PM
  • Hi JS06,

     

    Thanks for that information, went down a different route in the end, whereby I used a few filters and datatables to get the required info.

     

    Thanks

    Friday, January 4, 2008 3:50 PM