Insert failed, though returning value to asp.net page

[wrbodine]

Hi,

I have an .aspx page (asp.net) that calls a COM+ component, which performs an insert into a SQL Server 2000 database (3 Servers, all Windows 2000). When the code in the component successfully works (no errors) it returns the ID of the row just entered into the database (this id was an identity field, generated in the insert). Currently, the confirmation page comes up, with the ID field displayed, but when I go to the SQL table there's no new row in the database. Also, the next time I attempt this process it returns what would be the next ID, and same thing, no new row. Any ideas how to resolve this, or where the problem even lies?

Thanks,
Ray

 

[tlbroadbent]

SQL Server increments the identiy column whenever a row is inserted. If the insertion is rolled back, SQL server does not rollback the identiy. It appears that an error is occuring that cauases a rollback after the application retrieves the identity value. Perhaps the error handling in the component is catching the error condition or rollback.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[SQLSister]

Definitely something is happening to cause a rollback. Hopefully your component has error trapping. At the very minimum the code should be modified so that the statement to return the identity code (you did you scope_identity and not @@identity , didn;t you? ) would only execute if the transaction was not rolled back.

When checking for why the transaction is rolling back, don;t forget that it could be caused by a trigger error not just an error in your component code which would also cause a rollback.

 

[wrbodine]

Hi,

Thanks much for the input. I am returning the ID from the component (which has error trapping), and it returns fine to the page, which also means I'm getting no errors from the component. I added a trasaction to the component, and am committing it right after the Insert. When I've made changes like this to the component, and re-created it, every time after I do, the first time I run the pages the insert takes into the database, and the second time and following it returns the new ID to the page, but when I run a select query in Query Analyzer it takes a long time (30, 40 seconds) to return the rows (there's about 30) from the table and the new row isn't there.

I don't have any triggers, but could that still be an issue? Or is there more cleanup code needed? Right now I'm closing the Connection object, but maybe something more?
Maybe something in the .aspx page that calls the component?
Thanks for your help,
Ray

 

[wrbodine]

Well, this is resolved now! It was an .aspx issue, that I found while debugging through the code.

I needed to add the dispose method call to the component, and this fixed it.

I guess it was using a cached copy of the component, so it was calling the code, but it hung in the .aspx page at the line that called the component.