none
Problem with multiple datatables and the key columns RRS feed

  • Question

  •  

    Hello,

     

    I hope someone can help me with this problem.

    I'm making a program for a PPC using VS2005 and the languages C# and SQL

    I have 3 output datatables which are connected to eachother.

    The first table has 1 datacolumn which is the key of this table.

    The second table has 2 datacolumns that form the key, one with the same name as the key of the first column and one to make the new key unique.

     

    To explain the problem, a visual example.

     

    table one:

    oneNr

    oneName

    oneStartDate

    oneEndDate

    oneStartTime

    oneEndTime

     

    table two:

    oneNr

    twoNr

    twoName

    twoDate

    twoTime

     

    when I create a new row for table one, the oneNr starts at 1.

    then when I create a new row for table two which is linked to table one the oneNr is 0 (this is the problem, this should be 1) and the twoNr starts at 1

    I save the values of the numbers in a extra dataclass so I don't need to read the datatable everytime I need to get information out of the datatable. And when I need to update a datarow I can easily get the numbers out of the dataclass. BUT the value af the parameters in the dataclass don't get the value of the oneNr and twoNr and they only get 0. I know the value they get at first is good because when I check the datatable with a Query Analyzer I see the value is 1, 2, 3, 4 etc. but in table two the oneNr stays 0 and the twoNr goes from 1, 2, 3, 4 etc.

    Maybe the problem is because I autogenerate the unique numbers (in table one the oneNr and in table two the twoNr)

     

    It isn't easy to explain I hope this is clear enough for someone to help me, if it isn't I'll be glad to give some more/better information but I need to know what you want to know.

     

    Kind regards

     

    Korsten

    Monday, August 20, 2007 11:27 AM

All replies

  • Hi,

     

    Ah I see whats happening... although oneNr is part of a composite key it is really a foreign key also so you cannot automatically generate it, because you can have oneNr = 1, twoNr = 1 and oneNr =1, twoNr = 2. If you did autogenerate oneNr's value it would always be different and thats not correct. OneNr cannot be generated it has to be specified, or in other words to create an entry in table two the user/system must specify a entry in table one.

     

    What you need to do is set the oneNr value. Thats doesn't mean that you need to create a new entry in table one, but there needs to be something indicating with oneNr to link on (i.e. the user select the oneName to store table two entry with).

     

    Hope that makes sense. Nothing is really wrong here, you just cannot auto generate oneNr in table two. Instead you need to specify, either in code (from user input), or some other way what oneNr to use.

     

    Wednesday, August 22, 2007 5:05 PM
  • Hi Derek (and everyone else),

     

    Thanks for the reply. But what you are telling me about the autogeneration of oneNr in table two. I'm already doing that. oneNr isn't autogenerated. When I create a new row in table one I save all the values (even the oneNr) in a data class. When I create a new row in table two. I copy the table one, oneNr value into the table two oneNr value.

    Problem is it gives a 0.

     

    I think that when I don't auto generate oneNr it will work. This way I can also give it a value I would like and I need to increase the value when needed.

     

    Please give your thougts on this.

     

    Korsten

     

    Thursday, August 23, 2007 6:19 AM
  • Hi,

     

    I understand. Thats odd, in a recent project i did there was an autogenerated key column and it was set when a call to NewRow() was made, no actually it was a call to NewTableRow (the new strongly typed equivalent to the NewRow method)..... is that the problem? are you using a strongly typed dataset and are you calling the NewRow() method rather than the NewMyTableNameRow() method ?

     

    When you debug your application what is the value of oneNr in a newly created row?

    basically is the autogenerated number set when the row is created or when the row is stored?

     

    Think it best you step through your application, esp. when a new row in table one is created and just determine when the autogenerated ID for oneNr is set... as far I can tell your doing everything right.... it does sound trivial though, something simple.

     

     

    *edit: can you post some code, might help in identifying the problem

    Thursday, August 23, 2007 10:44 AM