How to add row to sql datatable with primary key autoincreasement

Question

How can I add a row to a sql datable with a autoincreasement primary key?

        private void AddBtn_Click(object sender, EventArgs e)
        {
            this.UseWaitCursor = true;
            DataRow newCallRow = customerDataDataSet.Tables["CallCenter"].NewRow();
            newCallRow["ID_Key"] = null;
            newCallRow["ID"] = AreaCodeTxtBx.Text + "-" + PrefixTxtBx.Text + "-" + NumberTxtBx.Text;
            newCallRow["Request"] = RequestCmboBx.SelectedText;
            newCallRow["Statius"] = StatiusCmboBx.SelectedText;
            newCallRow["Message"] = MessageTxtBx.Text;
            newCallRow["Time_Called"] = TimeCalledPDTicker.Value;
            newCallRow["Time_Log"] = System.DateTime.Now;
            customerDataDataSet.Tables["CallCenter"].Rows.Add(newCallRow);
            callCenterTableAdapter.Update(newCallRow);
            this.UseWaitCursor = false;
            this.Hide();
            this.Dispose();
        }

I get this error;

Cannot set Column 'ID_Key' to be null. Please use DBNull instead.

---

why767

Answer 1

Hi,

check the below link for Autoincrement in a DataTable.

http://www.dotnetfunda.com/interview/exam545-how-to-add-auto-increment-column-in-the-datatable.aspx

---

PS.Shakeer Hussain

Answer 2

Thanks for the reply.

I already have created the table with SQL Server Management Studio and it has the autoincreasement feild set in it. The trouble I have is I cannot find a way to add a new row from c# and get SQL to give the value for the autoincreasement feild. In the code above its the "ID_Key" field that is set to null. In SSMS a priamary key with autoincreasement needs to be set to not null. So what value do you pass in that field to get SQL to autoincrease it?

---

why767

Answer 3

Hi

Your issue is not really clear because if your primary key is auto incremented so you can not putting it in your code and your row will be created with generated id_key.A primary key should not be set to null .so drop the :

     newCallRow["ID_Key"] = null;

Feel free to ask more questions 

Regards

---

Best Regards...Please mark as answer if my post is helpful http://yosr-jemili.blogspot.com

Answer 4

Hey,

When I try the code without that line I get

{"Column 'ID_Key' does not allow nulls."} Column 'ID_Key' does not allow nulls.

Thanks

---

why767

Answer 5

newCallRow["ID_Key"] = null;

Cannot set Column 'ID_Key' to be null. Please use DBNull instead.

Sure not, if you set the "ID_Key" column as primary key.

So make this column auto-incremented, then you have to define properties when creating datatable:

- AutoIncrement (true)

- AutoIncrementSeed  (1) and

- AutoIncrementStep (1)

----

Then do not define as set this primary key column in anyway, becuase when adding new row, the new ID will automarically addes (and will be for +1 higher then in the previous row).

Hope it helps,

bye

---

Mitja

Answer 6

Yes I have it all set that way.

I think I found my problem. I had added the data source to my project and then went back to the datatable and set the "ID_Key" as PK with auto-increasement. So my project still defined it as it originally was. I removed the table form the project and then add it back with the Data Source Wizard. It works now.

Thanks everyone for the replies. <o:p></o:p>

---

why767