dataAdapter InsertCommand problems

[J]

I previously used the following which works fine:

SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(daAccount);
daAccount.Update(dsAccount, "Account");

But now I also want to retrieve the resulting identity column value from an
INSERT. So I tried the following:

daAccount.InsertCommand.CommandText = "INSERT into account (userid,
organization, groupdesc, stage, sizepotential) VALUES (@userid,
@organization, @groupdesc, @stage, @sizepotential); Select accountid =
SCOPE_IDENTITY()";
daAccount.Update(dsAccount, "Account");

But the InsertCommand returns a "NullReferenceException...Object reference
not set to an instance of an object". The dataAdapter is not null as it
worked in the first case (also "? daAccount == null" returns false). It's
not clear what the exception is telling me. Secondly, how does the
dataAdapter.Update know to plug in the correct dataSet columns into the
corresponding @valuename's?

Many thanks

 

[J]

Sorry, I'd mistakenly posted in c# where I wanted it in asp.net.

 

[viepia]

I use this SPROC
ALTER PROCEDURE [dbo].[GetSysIdentity]
@TableName VARCHAR(64),
@LastIdentity BIGINT OUTPUT
AS
BEGIN
SELECT @LastIdentity = IDENT_CURRENT(@TableName)
DECLARE @TestIdentiy BIGINT
SELECT @TestIdentiy = scope_identity()
IF @TestIdentiy <> @LastIdentity
RETURN 1
RETURN 0
END

this way
Trace.Assert(db.GetSysIdentity("MyTable", ref lastIdentity) == 0, "Bad SPROC");

so far it has always returned 0 and set LastIdentity to the correct value. I think
scope_identity works because all LINQ requests in SQL Server Profiler show the same SPID,
ClientProcessID, and ApplicationName: ".Net SqlClient Data Provider".

In your example "userid" is the _columnName of the "userid" column of your dataset.
Viepia