Autonumber value - synch datatable with database value RRS feed

  • Question

  • I have been reading a lot about this topic, and still cannot find appropriate solution. I am using RowUpdate event for retrieving autoincrement value from database

    Code Snippet

    void da_RowUpdated(object sender, OleDbRowUpdatedEventArgs e)
    OleDbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT @@IDENTITY";
    int recordID = 0;

    Int32.TryParse(cmd.ExecuteScalar().ToString(), out recordID);
    e.Row["id"] = recordID;

    In next scenario everything works fine:

    Code Snippet


    dt.Rows.Add(dr) // implementation of dr is not shown fot simplicity matters


    However, this is not a common scenario. The common scenario is to pass datatable with changes only to Update method. Now, in this scenario, above code doesn't work.

    Code Snippet

    DataTable dtChanged = dt.GetChanges();


    Merge method will Add another row, not update the existing row, since rows cannot be matched due to change of primary key (autonumber field) in dtChanged. The finall result is two rows in dt datatable with same data except for the autonumber field.

    Has anyone any idea how to solve this problem?

    Thanks, Goran
    Saturday, June 16, 2007 3:46 PM

All replies

  • I cant believe that noone has had the same problem? Smile
    Monday, June 18, 2007 11:52 AM
  • You can make a SP in SQL that recive parameters.. 1 of the parameters is for your field ID, delcare de parameter like this:
    @Field_Id bigint output,

    after the inserte statement this:

    SET @Field_Id = @@identity.

    But is very important that in the code when tou call de SP, create the command with a parameter direction "inputOutpu"

    SqlCommand cmdINSERT = new SqlCommand("SP_Name", conn);
                cmdINSERT.CommandType = CommandType.StoredProcedure;
                cmdINSERT.UpdatedRowSource = UpdateRowSource.OutputParameters;

                cmdINSERT.Parameters.Add("@Field_Id", SqlDbType.BigInt, 0, "Field_Id").Direction = ParameterDirection.Output;
    Monday, June 18, 2007 12:29 PM
  • I am using MS Access database. I didn't specify this, my mistake, but from RowUpdated signature you can see I am using OleDB provider, not Sql (OleDbRowUpdatedEventArgs).

    Second, I dont see how would your method solve my problem, if I was indeed using SqlServer database. Rec
    ordID variable holds value returned by SELECT @@IDENTITY query and this value is correct one. The problem lies in passing that value from RecordID to dataTable, as I have shown is 2nd example.

    Monday, June 18, 2007 3:19 PM