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.

Autogenerate reply or ServerProc generated message............

Hello,

I got my sample application to work that I am building my proof of concept out of. I need to be able to auto generate a reply message that would normally be in the run routine. The only way I see to do this is pull it from a table but I do not want to do that. I have tried tests where I change the code to see if I can send a message back but I have to reinstall the assembly into the database which is not what I am looking for. I am looking for a way to change the message by either accessing the GUI and getting the string, calling another function to do this, or something like that. I want it so I can change the code in the run routine in VS 2005 but this does not work. I am sure their is a trick to do this but am not sure what that trick is. What is a good way to do this other than accessing a table in the database?

Thanks,

Scott Allison...

What do you mean by auto-generate a reply? A reply could either be static (i.e. independent of the request and state), a stateless (i.e. dependent only on the request) or stateful (i.e. dependent on both the request as well as some state... the state would normally be stored in the database, but may also be stored separately). I cannot see why you need to recompile and redeploy your app to meet any of the above patterns.

Rushi

|||

Hello Rushi,

It calls the ServiceProc in my code and that is loaded into the database. I am writing this for applications that will need to report the status of a current event. If this is not stored in the database then they would not want to write anything to the database. This means they will get the request then need to reply to that request with a text message that they generate. In the worst case situation it can come from the database but would rather just be retrieved from a function call. It seems as though from your message this can be done so I am sure I have a setting incorrect. What I have is currently static and I want the response dependent on the request. What I meant from Autogenerated is retrieved from another assembly or another part of that service.

Thanks,

Scott Allison...

|||If the function that generates a response from request is a method in a different assembly, you could deploy that assembly into the database and simply invoke the method from ServiceProc.|||

Hello Rushi,

This is a remote system and will not be able to deploy all of the assemblies to the database. It consists of several services and needs dynamic access to what the message. Are you telling me that the only way to get a dynamic message is to write it to a database and have the ServiceProc read it? This is a draw back beacuse it will slow things down but I want to make sure before I tell people that they have to do that.

Thanks,

Scott Allison...