I solved the problem. Here is what you have to do if your table has an
autonumber field (like mine):
1. Make a new row with the NewRow Method:
DataRow dr = dataTable.NewRow();
2. Add the new row to the Rows collection of the dataTable:
dataTable.Rows.Add(dr);
3. Open the SqlCeConnection:
conn.Open();
4. Use the DataAdapter's Update method
dataAdapter.Update(dataTable);
5. Select the identity (autonumber generated in the database) with the
following SqlCeCommand:
SqlCeCommand cmd = new SqlCeCommand(@"SELECT @@IDENTITY FROM " +
dataTable.TableName, conn);
6. Use the ExecuteScalar method of the SqlCeCommand like this:
object ident = cmd.ExecuteScalar();
7. Close the connection (optional):
conn.Close();
8. If your autonumber column is the first column in the table (like in most
cases) then that column is read only. You must set the ReadOnly property to
false like this:
dataTable.COlumns[0].ReadOnly = false;
and then do the following:
dr[0] = Int32.Parse(ident.ToString());
dr.AcceptChanges();
9. Now set the ReadOnly property back to true like this:
dataTable.Columns[0].ReadOnly = true;
That's it!!!
If you wonder why you have to do all that to insert a new row into the
database, this is why (that's what I think):
I found out that the database has it's own counter for the autonumber field,
and the dataTable has it's own counter aswell (it depends how you set it).
Imagine that you have one database with one table named TEMP, and 2 users.
Say that you have 1 record in the TEMP table with the autonumber field set
to 100. If the first user enters a new record into TEMP, that record will
have the autonumber field set to 101 in the database, aswell as the
dataTable. Now if the second user enters a new record into TEMP, that record
will have the autonumber filed set to 102 in the database, but 101 in the
dataTable. I think you see the picture.
My problem was a bit different to this one. I had only one user (me). When I
enered the first record into TEMP the autonumber field was set to 101 in
both the database, and the dataTable. But when I enered the second record
into TEMP the autonumber filed was set to 102 in the database, and for some
reason to 103 in the dataTable. When I tryed to update the record with the
autonumber 103, I got a Concurrency violation because there was no record
103 in the database. Weird!!!
