Problem with Scope Identity()

  • Thread starter Thread starter Ruffeo
  • Start date Start date
R

Ruffeo

Hi all:

I am trying to add a record into my datagrid. I call a getchanges()
on the dataset. Then I call the DA.Update() method and that works (I
think). Next I merge my dataset with changes with the my original
dataset and call the acceptchanges(). That is when I get duplicate
records. I am doing the "negative ID" thing and I set up a delagated
function call RowUpdated which links to the RowUpdated event on the
DataAdapter and skips the row for inserts. I have a stored procedure
that return the Scope Indentity() of the row just inserted. Here some
of my code. I have the data stuff encapsolated in a different class so
bear with me here.

I hope someone can help me with this. If you need any more info please
let me know.



---Here is my form load
//Customer Data Access is the Class to Retreive Data
CustomerDataSet = CustomerDataAccess.SelectData("Select * from
Customer","CustomerData","Customers");


CustomerDataTable = CustomerDataSet.Tables["Customers"];
CustomerDataTable.Columns["Cus_id"].AutoIncrement = true;
CustomerDataTable.Columns["Cus_id"].AutoIncrementStep = -1;
CustomerDataTable.Columns["Cus_id"].AutoIncrementSeed = 0;
CustomerDataTable.Columns["Cus_id"].Unique = true;
dataGridCustomer.DataSource = CustomerDataSet;
dataGridCustomer.DataMember = CustomerDataSet.Tables[0].TableName;



--Here is my update button click event
DataSet DS;
string TableName;
DS = CustomerDataSet.GetChanges();
TableName = DS.Tables[0].TableName;
if( DS != null )
{
CustomerDataSet.Merge( CustomerDataAccess.UpdateData(DS,TableName) );
CustomerDataSet.AcceptChanges();
}


--Here is my DataAdapter Stuff
public DataSet UpdateData(DataSet myDataSet,string TableName )
{
SqlDataAdapter DA = new SqlDataAdapter();
DA.RowUpdated += new SqlRowUpdatedEventHandler(Update_RowUpdated);
return this.SqlUpdate(DA,myDataSet,TableName);
}


DataSet SqlUpdate(SqlDataAdapter DA,DataSet DS, string TableName)
{
DA.DeleteCommand = this._DeleteCommand;
DA.UpdateCommand = this._UpdateCommand;
DA.InsertCommand = this._InsertCommand;
DA.Update(DS,TableName);
return DS;
}




--Here is my insert command
//Insert Command Setup
_InsertCommand.CommandType = CommandType.StoredProcedure;
_InsertCommand.CommandText = "Training.dbo.usp_ADO_Customer_Insert";
_InsertCommand.Parameters.Add("@iCTP_ID",SqlDbType.SmallInt,4,"CUS_CTP_ID");
_InsertCommand.Parameters.Add("@bActive",SqlDbType.Bit,1,"CUS_Active");
_InsertCommand.Parameters.Add("@sFirstName",SqlDbType.VarChar,50,"CUS_First_Name");
_InsertCommand.Parameters.Add("@sMiddleName",SqlDbType.VarChar,50,"CUS_Middle_Name");

this._InsertCommand.Parameters.Add("@sLastName",SqlDbType.VarChar,50,"CUS_Last_Name");

this._InsertCommand.Parameters.Add("@sNickName",SqlDbType.VarChar,50,"CUS_Nick_Name");




--RowUpdated Delegate
private void Update_RowUpdated(object sender, SqlRowUpdatedEventArgs
e)
{
if (e.StatementType == StatementType.Insert)
e.Status = UpdateStatus.SkipCurrentRow;
}



--Here is the Stored Procedure
LTER PROCEDURE dbo.usp_ADO_Customer_Insert
@iCTP_ID int = NULL,
@bActive bit = 1,
@sFirstName varchar(50) = NULL,
@sMiddleName varchar(50) = NULL,
@sLastName varchar(50) = NULL,
@sNickName varchar(50) = NULL
AS
SET NOCOUNT ON

INSERT INTO dbo.Customer
(CUS_CTP_ID,
CUS_Active,
CUS_First_Name,
CUS_Middle_Name,
CUS_Last_Name,
CUS_Nick_Name)
VALUES
(@iCTP_ID,
@bActive,
@sFirstName,
@sMiddleName,
@sLastName,
@sNickName)

SELECT SCOPE_IDENTITY() AS CUS_ID





GO
 
See the thread above.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

Ruffeo said:
Hi all:

I am trying to add a record into my datagrid. I call a getchanges()
on the dataset. Then I call the DA.Update() method and that works (I
think). Next I merge my dataset with changes with the my original
dataset and call the acceptchanges(). That is when I get duplicate
records. I am doing the "negative ID" thing and I set up a delagated
function call RowUpdated which links to the RowUpdated event on the
DataAdapter and skips the row for inserts. I have a stored procedure
that return the Scope Indentity() of the row just inserted. Here some
of my code. I have the data stuff encapsolated in a different class so
bear with me here.

I hope someone can help me with this. If you need any more info please
let me know.



---Here is my form load
//Customer Data Access is the Class to Retreive Data
CustomerDataSet = CustomerDataAccess.SelectData("Select * from
Customer","CustomerData","Customers");


CustomerDataTable = CustomerDataSet.Tables["Customers"];
CustomerDataTable.Columns["Cus_id"].AutoIncrement = true;
CustomerDataTable.Columns["Cus_id"].AutoIncrementStep = -1;
CustomerDataTable.Columns["Cus_id"].AutoIncrementSeed = 0;
CustomerDataTable.Columns["Cus_id"].Unique = true;
dataGridCustomer.DataSource = CustomerDataSet;
dataGridCustomer.DataMember = CustomerDataSet.Tables[0].TableName;



--Here is my update button click event
DataSet DS;
string TableName;
DS = CustomerDataSet.GetChanges();
TableName = DS.Tables[0].TableName;
if( DS != null )
{
CustomerDataSet.Merge( CustomerDataAccess.UpdateData(DS,TableName) );
CustomerDataSet.AcceptChanges();
}


--Here is my DataAdapter Stuff
public DataSet UpdateData(DataSet myDataSet,string TableName )
{
SqlDataAdapter DA = new SqlDataAdapter();
DA.RowUpdated += new SqlRowUpdatedEventHandler(Update_RowUpdated);
return this.SqlUpdate(DA,myDataSet,TableName);
}


DataSet SqlUpdate(SqlDataAdapter DA,DataSet DS, string TableName)
{
DA.DeleteCommand = this._DeleteCommand;
DA.UpdateCommand = this._UpdateCommand;
DA.InsertCommand = this._InsertCommand;
DA.Update(DS,TableName);
return DS;
}




--Here is my insert command
//Insert Command Setup
_InsertCommand.CommandType = CommandType.StoredProcedure;
_InsertCommand.CommandText = "Training.dbo.usp_ADO_Customer_Insert";
_InsertCommand.Parameters.Add("@iCTP_ID",SqlDbType.SmallInt,4,"CUS_CTP_ID");
_InsertCommand.Parameters.Add("@bActive",SqlDbType.Bit,1,"CUS_Active");
Click to expand...
_InsertCommand.Parameters.Add("@sFirstName",SqlDbType.VarChar,50,"CUS_First_
Name");_InsertCommand.Parameters.Add("@sMiddleName",SqlDbType.VarChar,50,"CUS_Middl
e_Name");this._InsertCommand.Parameters.Add("@sLastName",SqlDbType.VarChar,50,"CUS_La
st_Name");this._InsertCommand.Parameters.Add("@sNickName",SqlDbType.VarChar,50,"CUS_Ni
ck_Name");




--RowUpdated Delegate
private void Update_RowUpdated(object sender, SqlRowUpdatedEventArgs
e)
{
if (e.StatementType == StatementType.Insert)
e.Status = UpdateStatus.SkipCurrentRow;
}



--Here is the Stored Procedure
LTER PROCEDURE dbo.usp_ADO_Customer_Insert
@iCTP_ID int = NULL,
@bActive bit = 1,
@sFirstName varchar(50) = NULL,
@sMiddleName varchar(50) = NULL,
@sLastName varchar(50) = NULL,
@sNickName varchar(50) = NULL
AS
SET NOCOUNT ON

INSERT INTO dbo.Customer
(CUS_CTP_ID,
CUS_Active,
CUS_First_Name,
CUS_Middle_Name,
CUS_Last_Name,
CUS_Nick_Name)
VALUES
(@iCTP_ID,
@bActive,
@sFirstName,
@sMiddleName,
@sLastName,
@sNickName)

SELECT SCOPE_IDENTITY() AS CUS_ID





GO
Click to expand...
 
Back
Top