How to refresh record after an update with a SP

Question

Hello Everybody,

I am dealing with the following problem: I use a MSSQL Stored Procedure for displaying my data in a DataGridView. The Update and Insert Commands work, but there is one problem:

On inserting a new Row, the auto-numbered primary key isn't send back to my DataAdaptar. So the insertion is successfull in the database, but the PK is left blank in the DataGridView. I allready tried some codes like:

    private void _rowUpdated(object sender, SqlRowUpdatedEventArgs e)
    {
        if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert)
        {
            cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT @@IDENTITY FROM " + e.Row.Table.TableName;

            DataRow r = dt.Rows[dt.Rows.Count - 1];
            r.ItemArray[0] = cmd.ExecuteScalar();
            //r.SetModified(); --> err?
            r.AcceptChanges();
        }
    }

on the DataAdapter, but nothing seems to work. All the SQL commands work fine.

When I refresh the data in the DataGridView, everyting is perfect. But the problem with this is, that the sort order and column width are adjusted. And that isn't what I want.

Can someone help me with this problem?

Looking forward for the solutions!

Thanks!

Answer 1

Since the connection to sql server has terminated so @@identity will become null and hence you are getting null value.

You cannot use scope_identity() here since its scope is limited to the place i.e. procedure in your case where it is called.

ident_current() always returns last identity value for the table that you specified. It might not work correct in case of replication

Update your commandText to ="Select ident_current("+e.Row.Table.TableName+")"

Here are the results of using various techniques of retrieving identity value. Replace dbo.M_PatientEntry with your table name

select ident_current('dbo.M_PatientEntry')
---------------------------------------
13

select @@identity from dbo.M_PatientEntry    
---------------------------------------
NULL
NULL

select scope_identity()    
---------------------------------------
NULL

select scope_identity() from dbo.M_PatientEntry    
---------------------------------------
NULL
NULL

Also try avoiding @@Identity rather use scope_identity() or ident_current @@identity will give you incremented value of trigger table result if you are using trigger on the same table where insertion is going on.

see this documentation

Answer 2

Hello All,

Finally found the answer and wanted to share it:

dt.RowChanged += new DataRowChangeEventHandler(_update_fields);


    private void _update_fields(object sender, DataRowChangeEventArgs e)
    {
        try
        {
            if (e.Action == DataRowAction.Add)
            {
                conn.Open();
                cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT IDENT_CURRENT('" + e.Row.Table.TableName + "')";
                dt.Rows[dt.Rows.Count - 1][0] = int.Parse(cmd.ExecuteScalar().ToString()) + 1;
                dt.AcceptChanges();
                conn.Close();
            }
            adapt.Update(dt);
        }
        catch (SqlException ex)
        {
            Debug.WriteLine(ex.Message);
        }
        catch (Exception ex)
        {
            Debug.WriteLine(ex.Message);
        }
    }

Hope it will save you some time! :)

Gr VeeWee