automatic transaction - Stored procedure - @@identity

Hi, i am having a hard time understanding the concept maybe .

i am using automatic transaction as such:

[AutoComplete]

public void SaveInvoiceAndCharges()

{

//try

//{

DSPinvoiceandcharges.Tainvoice.Update(DSinvoiceAndcharges.Invoice);

// DSPinvoiceandcharges.Tainvoicecharge.Update(DSinvoiceAndcharges.InvoiceCharge);

//}

//catch (Exception e)

//{

// throw e;

//}

}

and my sp :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[Invoice_New]

(

@.DateIssued datetime,

@.Reference nvarchar(50),

@.Note nvarchar(50),

@.DocumentType int,

@.CompanyCode int,

@.Valid nchar(1),

@.Fees int,

@.isDistributed nchar(1)

)

AS

SET NOCOUNT OFF;

Begin try

INSERT INTO [Invoice].[Invoice] ([DateIssued], [Reference], [Note],

[DocumentType], [CompanyCode], [Valid], [Fees], [isDistributed])

VALUES (@.DateIssued, @.Reference, @.Note, @.DocumentType, @.CompanyCode,

@.Valid, @.Fees, @.isDistributed);

return @.@.identity

End try

begin catch

raiserror('Test - Error', 16, 1 )

End catch

.. the code being run is not actually throwing any error.

DSPinvoiceandcharges.Tainvoice.Update(DSinvoiceAndcharges.Invoice);

updates but actually just does nothing and i lose the invalid row.

what is happening here please?

thank you ,

hrub

hrubesh:

I am not sure what else is going on but most likely you should be returning SCOPE_IDENTITY() instead of @.@.identity. Also, what are you getting for a return code?

|||

Hi, thanks for your reply,

i did not understand what u mean by what am i getting for a return code.

|||

Hrubesh:

You have this line in your code:

Code Snippet

return @.@.identity

How are you invoking the stored procedure? And after the execution of the stored procedure completes, what is the value returned by the stored procedure? Or are you just ignoring the return value? Finally, what do you mean by "it just does nothing." And if you don't know what the return value is, how do you know that "it is just doing nothing."

|||

hi,

i have been searching on the issue, and this is what i believe now it is not an issue with the sql part of it.

exception unhandled by user code in serviced components is what i am looking into now.

thanks for your help,

btw why the difference between @.@.identity and SCOPE_IDENTITY() ..

i have been using @.@.identity so that it automatically updates my parent row and child row with the new id , i am using

DSPinvoiceandcharges.Tainvoice.Update(DSinvoiceAndcharges.Invoice);

DSPinvoiceandcharges.Tainvoicecharge.Update(DSinvoiceAndcharges.InvoiceCharge);

invoice is the parent table and invoicecharge the child, and it works.

i will try the scope_identity asa i get the exception unhandled workaround. ..

yep so how i am invoking the sp is that Tainvoice is the sql data adapter that i generated using the wizard in my dataset, that created stored procedures on an sql command, and it automatically calls the corresponding insert/upd/del sp.

thanks a lot.

|||

@.@.identity will frequently work but it is not technically the correct choice. I gave an example of how @.@.identity can go wrong in this thread last year:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=880725&SiteID=1

Another thread in which I discussed with my friend Craig was here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=793185&SiteID=1

|||

thanks kent.