none
Dataset Question RRS feed

  • Question

  •  

    I have a question related to datasets.

     

    I have a visual basic .net 2008 project with datasets. I fill a datagrid with a query that only shows a few of the records from a table. When I add a new record in a datagrid it increments the primary key based on either the seed value if there is no record in the query for the datagrid, or the +1 increment from the last primary key in the query. The problem is it increments based only on the primary keys listed in the datagrid, not the whole table so I get errors on duplicate keys.

     

    How do I increment the primary key based on the largest primary in the table?
    Thanks

    Thursday, October 2, 2008 1:05 PM

Answers

  •  

    The DataTable should automatically track the largest value for the autoincrement column.

    The only place I see this break down is if you set the AutoIncrement = true after adding data.

     

     

     

    Code Snippet

       DataTable dt = new DataTable("t1");
       DataColumn dc = dt.Columns.Add("ID", typeof(int));
       dt.Columns.Add("Value", typeof(string));

     

       dt.Rows.Add(new object[] { 21, "3" });

     

       // if you set the AutoIncrement true after adding data

       // then you do need to set the AutoIncrementSeed, the Min and Max functions are usefull here
       dc.AutoIncrement = true;

       dc.AutoIncrementSeed = 1 + (int)dt.Compute("Max(ID)", "");

     

       dt.Rows.Add(new object[] { 5, "1" });
       dt.Rows.Add(new object[] { 2, "2" });

     

       // if you set AutoIncrement=true before adding any data, the generated value should be 22

       // if you set AutoIncrment after and didn't set the AutoIncrementSeed, the value would be 6

       dt.Rows.Add(new object[] { null, "4" });


       foreach(DataRow row in dt.Rows) {
         Console.WriteLine(row[0]);
       }

     

     

    Friday, October 3, 2008 1:21 AM
    Moderator

All replies

  • Increment primary key by -1 starting from 0. This way you will not get errors. The actual values of primary key will be retrieved when the record is inserted.
    Thursday, October 2, 2008 1:53 PM
  • I already have records in the table. When I press the add record ('+') it bases the decrement based only on the records(primary keys) that are showing in the grid, not the most negative primary key in the table. Even if I had no records already in the table it would act the same. Any ideas?

    Thanks

    Thursday, October 2, 2008 2:02 PM
  •  

    The DataTable should automatically track the largest value for the autoincrement column.

    The only place I see this break down is if you set the AutoIncrement = true after adding data.

     

     

     

    Code Snippet

       DataTable dt = new DataTable("t1");
       DataColumn dc = dt.Columns.Add("ID", typeof(int));
       dt.Columns.Add("Value", typeof(string));

     

       dt.Rows.Add(new object[] { 21, "3" });

     

       // if you set the AutoIncrement true after adding data

       // then you do need to set the AutoIncrementSeed, the Min and Max functions are usefull here
       dc.AutoIncrement = true;

       dc.AutoIncrementSeed = 1 + (int)dt.Compute("Max(ID)", "");

     

       dt.Rows.Add(new object[] { 5, "1" });
       dt.Rows.Add(new object[] { 2, "2" });

     

       // if you set AutoIncrement=true before adding any data, the generated value should be 22

       // if you set AutoIncrment after and didn't set the AutoIncrementSeed, the value would be 6

       dt.Rows.Add(new object[] { null, "4" });


       foreach(DataRow row in dt.Rows) {
         Console.WriteLine(row[0]);
       }

     

     

    Friday, October 3, 2008 1:21 AM
    Moderator