Adding a Row to a DataGrid

bmains · December 16th, 2003, 04:22 PM

Hello,

I'm trying to add a row of data to the data grid, by updating the data source. The data source is a DataTable object. How I do this is:

Code:

objTable.Rows.Clear()

dim objRow as DataRow = objTable.NewRow()
objRow("Name") = "New Name"
objRow("Text") = "New Text"
objTable.Rows.Add(objRow)

DataGrid1.DataSource = objTable
DataGrid1.DataBind()
DataGrid1.EditItemIndex = 0

However, I'm getting an error when I add the data row. There is another field called ID, which is an identity in SQL Server. It is erroring because it isn't provided. I thought that it would automatically increment the value. How do I specify the ID field to be an autonumber, and how do I specify the auto numbering to start where the data left off?

Thanks,

Brian Mains

Imar · December 16th, 2003, 04:52 PM

In the Enterprise Manager, open your table in Design View and then click the ID column. In the properties grid for the column, you'll see Identity. Set it to Yes. You can also set the Identity Seed (the start) and the Identity Increment.

HtH,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

bmains · December 17th, 2003, 09:19 AM

Hello,

The ID field is already an identity. When I do a Tables.Rows.Add(objRow), an error occurs, because there is no value for ID. I figured it is due to the fact that I have to recreate the primary key identity field; however, how do I ensure that the identity will leave off where the numbering system last was? For example,

the last row of data is:
6 Test The testing environment

How do I ensure that when I insert a new row, that the numbering will start at 7? I want to avoid requerying SQL Server for the last ID because I am required to use stored procedures and I don't want to have to maintain more SP's (already have too many).

Thanks.

Imar · December 17th, 2003, 11:53 AM

First of all, don't confuse Identity with the Primary key. When a field is a primary key, it's not necessary an identity and vice versa.

When you have set the Identity and then set the Increment to 1, SQL Server will automatically assign 7 when to a record, when the previous record was 6. IMO, it's dangerous to assume the next number is going to be 7. What about two users adding a record at the same time. One gets 6, the other gets 7. However, the first will then assume 7 is the next record, while in fact that number has already been taken.

You can use @@IDENTITY to return the ID of the record that was last inserted from a sproc. But, for the reasons mentioned, don't use that information to assume the ID of the next record. Have the database handle that number.

What exactly are you doing? Maybe there are other ways to do the same thing....

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

bmains · December 17th, 2003, 12:50 PM

Hello,

I'm using a data grid to do adds, updates, and deletes. I have the ID field specified as:

[ID] [int] IDENTITY (1, 1) NOT NULL

However, I'm editing the data in a disconnected environment, which could the problem be is that it's not connecting back to SQL Server before I try and get the ID number? Or does ADO.NET increment the ID field?

Thanks,

Brian Mains

stu9820 · December 17th, 2003, 03:03 PM

Are you using the EditCommandColumn?

bmains · December 17th, 2003, 04:21 PM

No. I didn't do it that way, because I didn't know that you could add a column through the data grid functionality. I thought you had to add it to the data source. Is this correct?

stu9820 · December 17th, 2003, 04:27 PM

I use the EditCommandColumn and when "edit" is clicked it adds textbox controls to the columns that aren't read only. It also replaces "edit" with "update" and "cancel". When "update" is clicked it takes what is in the textbox controls as parameters and sends the updated data to the database. "cancel" takes away any item for editing in the DataGrid.

stu9820 · December 17th, 2003, 04:28 PM

Here is what the HTML looks like:

<asp:DataGrid id="dgDates" CellPadding="2" runat="server" AutoGenerateColumns="False" DataKeyField="id" OnUpdateCommand="dgDates_UpdateCommand" OnEditCommand="dgDates_EditCommand" OnDeleteCommand="dgDates_Delete"
                    OnCancelCommand="dgDates_CancelCommand">
                    <Columns>
                        <asp:BoundColumn DataField="id" ReadOnly="True" Visible="False" />
                        <asp:BoundColumn DataField="event_date" HeaderText="Event Date" />
                        <asp:BoundColumn DataField="event" HeaderText="Event" />
                        <asp:BoundColumn DataField="event_time" HeaderText="Time" />
                        <asp:BoundColumn DataField="location" HeaderText="Location" />
                        <asp:ButtonColumn Text="Delete" CommandName="Delete" />
                        <asp:EditCommandColumn EditText="Edit" UpdateText="Update" CancelText="Cancel"></asp:EditCommandColumn>
                    </Columns>
                </asp:DataGrid>

alyeng2000 · December 17th, 2003, 10:35 PM

hi Brian Mains,

do you mean that current identity value for a table is increased without doing somthing.

Ahmed Ali
Software Developer