Can you do this in a SQL Server transaction? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Can you do this in a SQL Server transaction?

Author

Topic

Scott Mitchell
Starting Member

5 Posts

Posted - 2005-03-31 : 11:48:52

I have two tables, A and B, with a foreign key relationship between
them... so one row in A can have many related rows in B.

Now, what I want to do is create a row in A then create a number of
related rows in B. I want this all wrapped in a transaction so that
if any insert fails, the whole mess is rolled back.

So, I have this, essentially, in .NET code:

Create Transaction (using SqlConnection.BeginTransaction)
Insert new record into A (calls a sproc that does the INSERT and
returns the ID of the newly inserted row - one issue might be that
this sproc has a transaction within it as well)
Loop through related data, adding rows to B
Commit Trans if success, rollback if exception is raised anywhere

This bombs out when trying to add a row to B, as the foreign key
constraint complains. When I step through the db code I see the sproc
that inserts A coming back with an ID, but of course going to SQL
Query Analyzer and running SELECT * FROM A returns nothing... stepping
into the insertion into B, and -boom- the FK error creeps up.

I could have sworn that it was possible to do what I'm after... what's
going on here?

Here's a more detailed look at my code (I am using the Data Access Application Block (DAAB)):

Dim myConnection As SqlConnection
Dim myTrans As SqlTransaction

Try
'Create a connection object
myConnection = New SqlConnection(...connection string...)
myConnection.Open()

'Create a transaction object and start it
myTrans = myConnection.BeginTransaction()

'Insert the "master" record (for table A) and get back newly inserted ID
Dim ID as Integer =
Convert.ToInt32(SqlHelper.ExecuteScalar(myTrans,
CommandType.StoredProcedure, "Insert_A_Record", params...))

'Loop through a collection, inserting a record into B for each
For Each blah in Foo
SqlHelper.ExecuteNonQuery(myTrans,
CommandType.StoredProcedure, "Insert_B_Record", new
SqlParameter("@A_ID", ID), ...other params....)
Next

'Everything succeeded
myTrans.Commit
Catch e as Exception
myTrans.Rollback
Finally
myConnection.Close
End Try

I get an exception when inserting the first record into table B:
"INSERT statement conflicted with COLUMN FOREIGN KEY constraint ...
The statement has been terminated"

As I said, if I step through the code in the debugger, I can see a
value coming back in the ID variable, which gets passed into the
second sproc to insert a record into B.

Scott Mitchell
Starting Member

5 Posts

Posted - 2005-03-31 : 12:38:43

Answered my own question - YES YOU CAN! (Unless you are a programmer who suffers from a brain fart, like yours truly.) My problem was that I wasn't doing:

Dim ID as Integer =
Convert.ToInt32(SqlHelper.ExecuteScalar(myTrans,
CommandType.StoredProcedure, "Insert_A_Record", params...))

As I wrote in my pseudo-code. I was doing:

Dim ID as Integer =
Convert.ToInt32(SqlHelper.ExecuteNonQuery(myTrans,
CommandType.StoredProcedure, "Insert_A_Record", params...))

ExecuteNonQuery returns the # of rows affected, NOT the scalar result returned by the sproc! Eep. So that's why my code was throwing an FK contraint exception - the #s didn't match up. Meh.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources