ADO.NET Increment ID on add new row

Question

I'm trying to add new values to my GridView, that are later passed to Cache and DataSet and underlying SQL Database.

Here is my code, but I can't figure out what to type on the line "dataRow["ID"]=" as you can see. Everything else works fine and the other values are added to the database if I just give "ID" any number that doesn't exist.

protected void insertStudent_Click(object sender, EventArgs e)
    {
        DataSet dataSet = (DataSet)Cache["DATASET"];

        //DataRow dataRow = dataSet.Tables["Students"].Rows.Find(e.Keys["ID"]);

        dataSet.Tables["Students"].PrimaryKey = new DataColumn[] { dataSet.Tables["Students"].Columns["ID"] };

        DataRow dataRow = dataSet.Tables["Students"].NewRow();

        dataRow["ID"]           =

        dataRow["FirstName"]    = ((TextBox)GridView1.FooterRow.FindControl("txtFirstName")).Text;
        dataRow["LastName"]     = ((TextBox)GridView1.FooterRow.FindControl("txtLastName")).Text;
        dataRow["Gender"]       = ((DropDownList)GridView1.FooterRow.FindControl("DropDownListGender")).SelectedValue;
        dataRow["Course"]       = ((DropDownList)GridView1.FooterRow.FindControl("DropDownListCourse")).SelectedValue;
        dataRow["Grade"]        = ((DropDownList)GridView1.FooterRow.FindControl("DropDownListGrade")).SelectedValue;

        Cache.Insert("DATASET", dataSet, null, DateTime.Now.AddHours(24), System.Web.Caching.Cache.NoSlidingExpiration);

        dataSet.Tables["Students"].Rows.Add(dataRow);

        GridView1.DataSource = (DataSet)Cache["DATASET"];
        GridView1.DataBind();
    }

You should not type anything. Usually database has auto increment setup for ID field, so new entry has a unique id = last used id + 1 — Andrei, Oct 06 '15 at 16:31
If I don't add anything, I get an exception that "Column ID cannot be null". Any ideas on how to increment ID using SQL on each new entry? Thanks! — Erik Lydecker, Oct 06 '15 at 16:34
That is exacly what I am talking about, you should setup database to handle this for you. If you are using SQL Server, take a look at [this thread](http://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012) for example — Andrei, Oct 06 '15 at 16:35
Sorry but I already have it to increment by 1, just as they say on that thread you posted. Still no ideas on how to add a new ID to ID column and increment by 1? — Erik Lydecker, Oct 06 '15 at 16:47
Oh, all right, then it must be DataTable who throws it. For the ID DatColumn, try either setting `AutoIncrement = true` or `AllowDBNull = true`. The former looks more promising — Andrei, Oct 06 '15 at 16:57
The DataSet does not increment ID, its the DataSet that throws an exception... I think. Since it gets the "column rules" from the underlying DB, and the DB column ID is "int not null". But happens when we add ID to DataSet, then send data from DataSet to DB? It wont work... since the DB is adding ID automatically. This is crazy =) — Erik Lydecker, Oct 06 '15 at 17:34

singhsac · Answer 1 · 2015-10-06T17:00:25.760

0

As per Andrei in the comment above, set up your ID column in the table as:

CREATE TABLE sample( ID INT IDENTITY(1,1) NOT NULL,
                     FirstName VARCHAR(50) )) -- And your rest of the details

No need to add a value to ID, it will increment by itself. Insert other values and when you read from the database, you have ID column incremented.

P.S. Do not include ID column while inserting other values to the table.

Google 'SQL INCREMENT' for more information.

edited Oct 06 '15 at 17:00

answered Oct 06 '15 at 16:54

singhsac

401
1
8
17

What I'm doing is getting data from database into a dataset, cache the dataset. So when I add new values I add them to the dataset, store in the cache, then send new values to database. This allows me to work with data offline in my application. If I don't provide an ID it will throw an exception, and the primary key cannot be Null. The "insertStudent_Click" button is only adding values to the DataSet, when the data is sent back to the database the ID will be incrementing automatically. So I need to add ID to the DataSet. – Erik Lydecker Oct 06 '15 at 17:13
This doesn't work: dataSet.Tables["Student"].Columns["ID"].AllowDBNull == true; any ideas on how to make that column in the DataSet to allo null value? – Erik Lydecker Oct 06 '15 at 17:48
Ohh Gotcha! So how about this: Drop the ID column from the cached dataTable, add ID column again with INCREMENT property. I'm sure this will work in the dataTable. Later use the dataTable to update your database table. You can find info on how to remove a column [LINK](http://stackoverflow.com/questions/673616/how-can-i-delete-a-column-of-a-dataset) And, here you can find how to add a column to a dataTable[LINK](http://stackoverflow.com/questions/23410046/c-sharp-adding-row-to-datatable-which-has-an-auto-increment-column) – singhsac Oct 06 '15 at 18:05
This solved the problem: dataSet.Tables["Students"].Columns["ID"].AutoIncrement = true; – Erik Lydecker Oct 06 '15 at 18:15

score 0 · Answer 2 · answered Oct 06 '15 at 18:16

0

The answer to this question is to use AutoIncrement on the ID Column in your Cached DataSet. Then when you save to DB, the added rows will get their correct ID in the DB.

dataSet.Tables["Students"].Columns["ID"].AutoIncrement = true;

answered Oct 06 '15 at 18:16

Erik Lydecker

719
3
12
29

So it was DataSet throwing an exception, and suggested autoincrement property worked. Good to know – Andrei Oct 07 '15 at 09:36
Yes. But the problem now, is that AutoIncrement can add an ID value that is already present and eventually throw an exception. So we need to add unique ID values, maybe by looking at the highest ID value in the DataSet now? – Erik Lydecker Oct 07 '15 at 10:59

ADO.NET Increment ID on add new row

2 Answers2