dataAdapter InsertCommand problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J - 28 Feb 2008 06:00 GMT

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

Reply to this Message

J - 28 Feb 2008 15:11 GMT

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

>I previously used the following which works fine:
>
[quoted text clipped - 18 lines]
>
> Many thanks

Reply to this Message

viepia@nospam.com - 05 Mar 2008 14:05 GMT

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

>I previously used the following which works fine:
>
[quoted text clipped - 18 lines]
>
>Many thanks

Reply to this Message

dataAdapter InsertCommand problems

Free Magazines