I have had my head spinning for two days on this one.  I have what i
thought was a simple enough locking issue, but havnt been able to get
it working.

Here is my setup:
App Server is a .net 2.0
dB is Sql Server 2005

I have a aspx page that creates an object, lets call it an asset
object for now.  Each time this page is viewed, i create the object.
I then call a method of that object.  In that method, i create a new
object (a data access layer object).  I call a method in that new data
object.  In that data object, i have two stored procs being fired:

SqlConnection thisConn = new
SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ConnectionString);

                SqlCommand thisCmd3;

                //get a new id for the table
                thisCmd3 = new SqlCommand("uspIdentityIncrement",
thisConn);
                thisCmd3.CommandType = CommandType.StoredProcedure;
                thisCmd3.Parameters.Add("@tableName",
SqlDbType.VarChar, 255);
                thisCmd3.Parameters["@tableName"].Value =
"uAssetView";
                thisCmd3.Parameters.Add("@newKey", SqlDbType.Int);
                thisCmd3.Parameters["@newKey"].Direction =
ParameterDirection.Output;

                thisConn.Open();
                int newKey;
                thisCmd3.ExecuteNonQuery();
                newKey =
Convert.ToInt32(thisCmd3.Parameters["@newKey"].Value);
                //thisConn.Close();

                SqlCommand thisCmd = new
SqlCommand("uspAssetViewRecord", thisConn);
                thisCmd.CommandType = CommandType.StoredProcedure;

                thisCmd.Parameters.Add("@assetViewId", SqlDbType.Int);
                thisCmd.Parameters["@assetViewId"].Value = newKey;
                thisCmd.Parameters.Add("@assetId", SqlDbType.Int);
                thisCmd.Parameters["@assetId"].Value = assetId;
                thisCmd.Parameters.Add("@userId", SqlDbType.Int);
                thisCmd.Parameters["@userId"].Value = userId;

                //thisConn.Open();
                thisCmd.ExecuteNonQuery();
                thisConn.Close();

The first sp does an insert, and the second does another insert based
on the first ones results.  They have to stay seperate like that, i
cannot combine the stored procs.

The issue i am having is threads are crossing, resulting in the first
sp to get called two times in a row, before the second has a chance to
fire.

I have tried all that I can think of.  I have placed locks in every
place I can think of, i have tried transactions, transactionScopes.  I
am completely stumped.  Any ideas would be great!

Thanks,
Brian

When you say "they have to stay separate" is this because of some decree from 
the heavens above, or just that you aren't sure how to get what you need all 
in one stored proc? Certainly if you put all the logic in a single stored 
proc, wrap it in a transaction, and use the ROWLOCK or table locking hints in 
your sproc, you can prevent multiple access to the sproc until the two - 
table operation is complete. Your sproc can also return the identity value as 
either an output parameter or via a scalar. In fact, if you generate a GUID, 
you can supply the identity value from the outside and not have to rely on 
getting it out of the database at all.
--Peter 
Recursion: see Recursion
site:  http://www.eggheadcafe.com
unBlog:  http://petesbloggerama.blogspot.com
BlogMetaFinder:    http://www.blogmetafinder.com



"BEwebdev@gmail.com" wrote:


> I have had my head spinning for two days on this one.  I have what i
> thought was a simple enough locking issue, but havnt been able to get
> it working.
> 
> Here is my setup:
> App Server is a .net 2.0
> dB is Sql Server 2005
> 
> I have a aspx page that creates an object, lets call it an asset
> object for now.  Each time this page is viewed, i create the object.
> I then call a method of that object.  In that method, i create a new
> object (a data access layer object).  I call a method in that new data
> object.  In that data object, i have two stored procs being fired:
> 
> SqlConnection thisConn = new
> SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ConnectionString);
> 
>                 SqlCommand thisCmd3;
> 
>                 //get a new id for the table
>                 thisCmd3 = new SqlCommand("uspIdentityIncrement",
> thisConn);
>                 thisCmd3.CommandType = CommandType.StoredProcedure;
>                 thisCmd3.Parameters.Add("@tableName",
> SqlDbType.VarChar, 255);
>                 thisCmd3.Parameters["@tableName"].Value =
> "uAssetView";
>                 thisCmd3.Parameters.Add("@newKey", SqlDbType.Int);
>                 thisCmd3.Parameters["@newKey"].Direction =
> ParameterDirection.Output;
> 
>                 thisConn.Open();
>                 int newKey;
>                 thisCmd3.ExecuteNonQuery();
>                 newKey =
> Convert.ToInt32(thisCmd3.Parameters["@newKey"].Value);
>                 //thisConn.Close();
> 
>                 SqlCommand thisCmd = new
> SqlCommand("uspAssetViewRecord", thisConn);
>                 thisCmd.CommandType = CommandType.StoredProcedure;
> 
>                 thisCmd.Parameters.Add("@assetViewId", SqlDbType.Int);
>                 thisCmd.Parameters["@assetViewId"].Value = newKey;
>                 thisCmd.Parameters.Add("@assetId", SqlDbType.Int);
>                 thisCmd.Parameters["@assetId"].Value = assetId;
>                 thisCmd.Parameters.Add("@userId", SqlDbType.Int);
>                 thisCmd.Parameters["@userId"].Value = userId;
> 
>                 //thisConn.Open();
>                 thisCmd.ExecuteNonQuery();
>                 thisConn.Close();
> 
> The first sp does an insert, and the second does another insert based
> on the first ones results.  They have to stay seperate like that, i
> cannot combine the stored procs.
> 
> The issue i am having is threads are crossing, resulting in the first
> sp to get called two times in a row, before the second has a chance to
> fire.
> 
> I have tried all that I can think of.  I have placed locks in every
> place I can think of, i have tried transactions, transactionScopes.  I
> am completely stumped.  Any ideas would be great!
> 
> Thanks,
> Brian
> 
>