ADO.NET Transaction Fails to update Database after .Commit()

SQL Server 2000, C#, ASP.NET and ADO.NET. I have searched for 3 days trying to figure out why my ADO.NET Transaction executes my stored procedures and runs the .COMMIT() on the server, figured this out by using the SQL Profiler, but the data doesn't show up in the database tables and I recieve no error from SQL or ASP.NET. What gives?? Anyone else ever heard of such a thing. Please help, before I loose my sanity.I may need to be more specific about the problem, so here goes. I am running an ADO.NET SQLTransaction that is calling 3 different stored procedures. The first proc Inserts data into a MailMessage Table and returns the @.@.IDENTITY of the Insert. The next set of stored procedures are called in a loop to Insert into a MailboxMessageAddressees table that has a foreign key of the MailMessages table and the ID of the Addressee. This loop can run 1-50+ times to insert all the Addressees. In SQL Profiler, I can trace the connection I made to a database and it starts the transaction, executes all stored procedures, and then COMMITS. I recieve no errors in my transaction and no errors occur on SQLServer. But the data is never inserted into the database. Its almost like the ADO.NET Transaction didn't take place, or SQL Server is rolling back the transaction on its own without throwing any errors that I can see.

Needless to say, I have closed the connection and disposed of the command and connection. Is there something else I need to send to SQL Server other than COMMIT to let it know that I have completed my transaction. This is really perplexing because I can manually execute all the stored procedures in Entrerprise Manager. I get every statement executed from the SQL Profiler when I trace the Transaction and put it in Query Analyzer and it works fine, and all the data shows up in SQL Server. Is there something I am missing?

Here is the actual code, maybe someone can clue me into what I might be missing.

/// <summary>
/// Mail_DAL.AddNewMessage(int Mailbox_ID,string subject,string body,ArrayList recipientTo,ArrayList recipientCc,ArrayList groupTo,ArrayList groupCc,ArrayList files,Wargame_ID)
/// ->Executes a ExecuteNonQuery using the Stored Procedure Mail_DeleteGroupAddressees and an ExecuteNonQuery using stored
/// procedure Mail_AddGroupAddressee to insert all addressees not in the Group.
/// </summary>
/// <param name="Mailbox_ID">Int: The Mailbox associated with the MailGroup.</param>
/// <param name="subject">String: The GroupName of the Group the addressees are associated with.</param>
/// <param name="body">String: The GroupName of the Group the addressees are associated with.</param>
/// <param name="recipientTo">ArrayList: An ArrayList of the To addresses Mailbox_IDs for the group.</param>
/// <param name="recipientCc">ArrayList: An ArrayList of the CC addresses Mailbox_IDs for the group.</param>
/// <param name="groupTo">ArrayList: An ArrayList of the To group addresses Mailbox_IDs for the group.</param>
/// <param name="groupCc">ArrayList: An ArrayList of the CC group addresses Mailbox_IDs for the group.</param>
/// <param name="files">ArrayList: An ArrayList of Files if there are any.</param>
/// <param name="Wargame_ID">Int: The Wargame_ID of any file to be put in Files</param>
public void AddNewMessage(int Mailbox_ID,string subject,string body,ArrayList recipientTo,ArrayList recipientCc,ArrayList groupTo,ArrayList groupCc,ArrayList files,int Wargame_ID)
{
//Get To Addresses from Groups
OpenProcCommand(Conn,"Mail_GetCurrentGroupAddressees");
SqlCommand cmd = ProcCommand;
param = new SqlParameter();
SqlDataReader dr;
Int32 holder = 0;
if(groupTo.Count > 0)
{
foreach(Object item in groupTo)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MailGroup_ID",SqlDbType.Int);
param.Value = item;
dr = cmd.ExecuteReader();
//Add any Group Recipients to Recipients ArrayList
while(dr.Read())
{
holder = Convert.ToInt32(dr["Mailbox_ID"].ToString());
if(!recipientTo.Contains(holder))
{
recipientTo.Add(holder);
}
}
dr.Close();
}
}

//Get CC Addresses from Groups
if(groupCc.Count > 0)
{
foreach(Object item in groupCc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MailGroup_ID",SqlDbType.Int);
param.Value = item;
dr = cmd.ExecuteReader();
//Add any Group Recipients to Recipients ArrayList
while(dr.Read())
{
holder = Convert.ToInt32(dr["Mailbox_ID"].ToString());
if(!recipientCc.Contains(holder))
{
recipientCc.Add(holder);
}
}
dr.Close();
}
}

//Get BCC Recipients for this Maillbox
ArrayList recipientAll = new ArrayList();
recipientAll.Add(Convert.ToInt32(Mailbox_ID));
foreach(Object item in recipientTo)
{
recipientAll.Add(item);
}
foreach(Object item in recipientCc)
{
if(!recipientAll.Contains(item))
{
recipientAll.Add(item);
}
}
string allRecipients = "";
foreach(Object item in recipientAll)
{
allRecipients += item + ",";
}
allRecipients = allRecipients.Substring(0,allRecipients.Length-1);
ArrayList recipientBcc = new ArrayList();
cmd.CommandText = "Mail_GetBccAddressees";
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Addressees",SqlDbType.VarChar,1000);
param.Value = allRecipients;
dr = cmd.ExecuteReader();
//Add any Bcc Recipients to Bcc Recipients ArrayList
while(dr.Read())
{
recipientBcc.Add(Convert.ToInt32(dr["Addressee"].ToString()));
}
dr.Close();

// Start a local transaction.
SqlTransaction myTrans = Conn.BeginTransaction();
// Enlist the command in the current transaction.
cmd.Transaction = myTrans;

try
{
//Insert Message into Database
cmd.CommandText = "Mail_AddNewMessage";
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MsgFrom",SqlDbType.Int);
param.Value = Mailbox_ID;
param = cmd.Parameters.Add("@.MsgSubject",SqlDbType.VarChar,100);
param.Value = subject;
param = cmd.Parameters.Add("@.MsgBody",SqlDbType.Text);
param.Value = body;
int msg_ID = Convert.ToInt32(cmd.ExecuteScalar());

//Add To Recipients
cmd.CommandText = "Mail_AddMessageAddressees";
foreach(Object item in recipientTo)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 1;
cmd.ExecuteNonQuery();
}

//Add CC Recipients
foreach(Object item in recipientCc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 2;
cmd.ExecuteNonQuery();
}

//Add BCC Recipients
foreach(Object item in recipientBcc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 3;
cmd.ExecuteNonQuery();
}

//Add message to senders Mailbox in SentItems
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = Mailbox_ID;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 4;
cmd.ExecuteNonQuery();

//Add files if there are any.
foreach(HttpPostedFile myfile in files)
{
//Get the file name
string FileName = myfile.FileName;
int strLoc = FileName.LastIndexOf("\\");
FileName = FileName.Remove(0,strLoc+1);

// Get size of uploaded file
int nFileLen = myfile.ContentLength;

// Allocate a buffer for reading of the file
byte[] myData = new byte[nFileLen];

// Read uploaded file from the Stream
myfile.InputStream.Read(myData, 0, nFileLen);
File file = new File();
int file_ID = file.UploadFile(cmd,3,FileName,nFileLen,myfile.GetType().ToString(),Wargame_ID,myData);

//Add the file message association
cmd.Parameters.Clear();
cmd.CommandText = "Mail_AddMessageFile";
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.File_ID",SqlDbType.Int);
param.Value = file_ID;
cmd.ExecuteNonQuery();
}
//Commit Transaction
myTrans.Commit();
}
catch (SqlException sqlex)
{
// Specific catch for deadlock
if (sqlex.Number != 1205)
{
myTrans.Rollback();
}
throw(sqlex);
}
catch(InvalidOperationException ex1){
string ex2 = ex1.ToString();
}
catch (Exception ex)
{
myTrans.Rollback();
throw (ex);
}
finally
{
myTrans.Dispose();
Conn.Close();
Conn.Dispose();
}

}|||First try to run a simple transaction with one stored proc and minimal .net code. This will make it easier to pinpoint your problem. Could be memory problem on server or some other potential problem you are not aware of.

This may not be a problem but I have encountered this one before. Are you returning more than one id with the @.@.IDENTITY call (i.e more than one insert command in a single transaction)? If you are you need to reset the sql connection or the same id is returned each time you call it if my memory serves me correctly.

Also, you can check the error status within a stored proc and return it to .net. I have used this before to pinpoint a problem.

Cheers

Mo|||Thanks Mo for the insight, unfortunatly I still have the same problems. Funny thing is I created a single stored procedure to do all the inserts, thinking that would solve the problem, but alas nothing. Getting different results, but same outcome. The database executes the procedure, which inserts data into the database, but for some reason after about 3 min or so the rows are rolled back. Running sp_lock I can see that the process that executed the command has a lock on all the tables, but it never gives them up. After that timeout, they are all rolled back. Is there some reason anyone can think of where this would happen. I can execute the stored procedure on the database using query analyzer and the data goes in fine and stays in there. Another thing to note, after the web application executes the stored procedure I cannot view the data in the tables through Enterprise manager. I can't even view the current activity, it sits there for a while then returns a 1222 Error. This has become a nightmare. Thanks

Chris|||Could be a bug between sql server and .net ?? Are both upto date with service packs?

Have you looked on http://support.microsoft.com

Very good area for problem solving, used it many times.