Stored Procedure Update not sticking

[Lisa]

I have a stored procedure in my database which looks up a number in a table, increments it by one, and updates the table value with the new value - a classic get me the next available number scenario.  The stored procedure works perfectly executed through SQL management console. The new value is an output parameter.

However when I execute this stored procedure from my asp.net code using Telerik open access, it does return to me the new value correctly but the update of the old value with the new value doesn't stick.  The database still shows the old value after the sp executes through data access.

I've used stored procedures to do database updates and I've used stored procedures to return values successfully - I guess this is the first time I've tried to do both. Is there something about Data Access that prevents this from working.  It is the last statement of the sp which does the table update.

 Thanks

[George]

Disclaimer: Not telerik, but use data access and call procs that make changes.

 

Who is managing the transaction, the proc or data access?  It works both ways, but if the sp does a rollback, nothing will be commited.  Additionally, you still need to call savechanges from the context.  Eg:

 

using(var db = new FluentContext())
{
  //Call the procedure
  db.SaveChanges();
}

 Without save changes the transaction is never committed to the db.

 

[Yavor Slavchev]

Hi Lisa,
As George noted, you should make sure that SaveChanges() is called in the end when the stored procedure is executed. This is necessary, because Telerik DataAccess is responsible for managing the underlying transaction, since it is executed in its context. Look at this article for more information about this.


Regards,
Yavor Slavchev
Telerik

 

[Lisa]

Thanks George. You were exactly right!