How to property increment UniqueID

programming
1

In MS C# Visual Studio Express 2008 I am trying to figure out the proper way to increment the “unique id” field in a SQL Server Database Table when adding a new record. My table is called “metadata” and the fields are id, name, and description. Of course the “id” is the primary key and the one I wish to increment automatically.

I know that you do not want to rely on users to properly handle this field. I know how to set the identity specification, increment, and seed in the table definition. Is this the method I want to use? If so, how do I implement this in my form?

Right now my form simply has a combobox for selecting the “metadatatype” to edit, add or delete. It also has a grid to view, edit, and add records, and two textboxes - “Name” and “Description” giving the user the option to simply type the data in these two boxes and click “Add Record.”

How do I properly add a record with these two columns of information while at the same time avoiding the “Null not allowed” constraint on the “id” field? Do I actually have to get the last id value from the table and increment by 1 in code myself, before updating the table / database?

I’ve read so many different articles on this. Some say handle it at the database level, some say in the application, some say build a “data access layer.” Do I use the tableadapter’s update method, direct SQL commands? What?

This will be a small desktop application with maybe ten tables.

Is there a video or tutorial anyone can recommend?

thanx

Slider16

2

Well, I may have answered my own question. I think I’m going to use the “Insert” method of the TableAdapter as shown below. But please tell me if I’m on the right track, as I plan to use this method for adding records to all my various tables in my application. I have the primary key set as “Identifier” in the SQL Table. I’m open for suggestions.

    private void btnInsert_Click(object sender, EventArgs e)
    {
        try
        {
            int typeSelection;              // Variable used to cast the user's selection to int
            typeSelection = (int)cboLists.SelectedValue;      // Cast user's selection to int type
            this.Validate();
            this.metadataBindingSource.EndEdit();
            this.metadataTableAdapter.Insert(typeSelection, cboLists.Text, txtName.Text, txtDescription.Text);
            MessageBox.Show("Update Successful");
        }
        catch (System.Exception ex)
        {
            MessageBox.Show("Update failed " + ex);
        }
                        
    }

Thanx for any comments or suggestions.

3

The table adapter allows basic manipulation of the underlying data - so all it’s doing is an INSERT in the background anyway.

In SQL you can perform an insert with an autoincrement PK by using the insert statement and leaving out the PK field in the statement

i.e.

for a table with the columns

id, description

where id is a autoincrement PK

INSERT INTO someTable (description) VALUES (‘test’)

The tableadapter is simply doing the same thing in the background. It’s perfectly fine to use it for this