473,289 Members | 2,040 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Problem with SqlCommand and T-SQL transaction

I'm having trouble with what should be a simple task; beginning and
committing T-SQL transactions using the SQLClient. I'm using a SqlCommand
(cmd) to begin the transaction and delete records from two tables. if both
records are successfully deleted, I'd like to commit the transaction.
Everything works fine until I attempt to commit the first time the foreach
loop runs, at this point I get the following exception. I haven't been able
to make sense of what I've seen on msdn or other newsgroups; this seems like
it should be an easy process since I'm creating a new SqlCommand every time
the foreach loop runs.

Any help or suggestions would be much appreciated.

Thanks, Andre
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count = 0.
foreach (DataRow dsRow in Class1.myDS.MasterAccounts.Rows)
{
//begin SQL Transaction
sSQL = "BEGIN TRAN;";
Class1.cmd = new SqlCommand(sSQL, Class1.cn);
Class1.cmd.CommandTimeout = 0;
Class1.cmd.ExecuteNonQuery();

//declare @ServiceID param.
Class1.cmd.Parameters.Add("@ServiceID", SqlDbType.Int, 4).Value =
Convert.ToInt32(dsRow["ServiceID"]);

sSQL = "DELETE FROM tblMainServices " +
"WHERE ServiceID = @ServiceID AND Service LIKE '920%';";
Class1.cmd.CommandText = sSQL;
Class1.cmd.ExecuteNonQuery();

sSQL = "DELETE FROM tblMainServices_B " +
"WHERE ServiceID = @ServiceID;";
Class1.cmd.CommandText = sSQL;
Class1.cmd.ExecuteNonQuery();

//Commit SQL Transaction
sSQL = "COMMIT TRAN;";
Class1.cmd.CommandText = sSQL;
Class1.cmd.ExecuteNonQuery();

iCount ++;

int iProgValue = Convert.ToInt16(20 * Convert.ToSingle(iCount / iRecords));
if (iProgValue != progBar.Value)
{
progBar.Value = iProgValue;
this.Update();}
}
Nov 16 '05 #1
2 8303

"Andre Ranieri" <An**********@discussions.microsoft.com> wrote in message
news:F5**********************************@microsof t.com...
I'm having trouble with what should be a simple task; beginning and
committing T-SQL transactions using the SQLClient. I'm using a SqlCommand
(cmd) to begin the transaction and delete records from two tables. if
both
records are successfully deleted, I'd like to commit the transaction.
Everything works fine until I attempt to commit the first time the foreach
loop runs, at this point I get the following exception. I haven't been
able
to make sense of what I've seen on msdn or other newsgroups; this seems
like
it should be an easy process since I'm creating a new SqlCommand every
time
the foreach loop runs.

Any help or suggestions would be much appreciated.

Thanks, Andre


Try this instead:

public static SqlConnection Connect()
{
SqlConnection con = new SqlConnection("..");
con.Open();
return con;
}

......
using (SqlConnection con = Connect())
foreach (DataRow dsRow in Class1.myDS.MasterAccounts.Rows)
{
using (SqlTransaction tran = con.BeginTransaction() )
{
SqlCommand cmd1 = new SqlCommand( "DELETE FROM tblMainServices " +
"WHERE ServiceID = @ServiceID AND Service LIKE '920%';",con,tran);
cmd1.Parameters.Add("@ServiceID", SqlDbType.Int, 4).Value =
Convert.ToInt32(dsRow["ServiceID"]);

SqlCommand cmd2 = new SqlCommand( "DELETE FROM tblMainServices_B " +
"WHERE ServiceID = @ServiceID AND Service LIKE '920%';",con,tran);
cmd2.Parameters.Add("@ServiceID", SqlDbType.Int, 4).Value =
Convert.ToInt32(dsRow["ServiceID"]);

cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
tran.Commit();
iCount ++;
//...
}
}
David
Nov 16 '05 #2
David,

I wanted to thank you for your reply, I was able to get the code working
based on your feedback.

I'm still not sure why my T-SQL transactions didn't work and why the ADO.NET
tran did.
Nov 16 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Bob | last post by:
I have an ASP.NET web application that has been running without any problems for a while. I recently transferred the site to shared hosting and had multiple users start to use the site. The problem...
3
by: mattgcon | last post by:
When I use a stored procedure to insert data into a table and when the Datagrid refreshes two new columns are added. The dataSet is set up with one table with NO columns defined. Please help me on...
4
by: mattgcon | last post by:
When I use a stored procedure to insert data into a table and when the Datagrid refreshes two new columns are added. The dataSet is set up with one table with NO columns defined. Please help me on...
2
by: Mike Hutton | last post by:
I have a rather odd problem. I have a SP which uses temp. tables along the way, and then returns a table of results: CREATE PROCEDURE dbo.usp_myproc( @pNameList VARCHAR(6000) ) AS
1
by: Sunil Sabir | last post by:
Dear All, I have a search form which has 3 drop down boxes dropID ,dropPostCode, dropName. when I select some thing in the dropID name lets say 0001 I press go button.It displays a Record of...
8
by: I am Sam | last post by:
Hi everyone, This problem is making me old. I don't want to get any older. I have a multi-nested repeater control as follows: <asp:Repeater ID="clubRep1" Runat="server">...
7
by: Dabbler | last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the following error when trying to update (ExecuteNonQuery): System.Data.SqlClient.SqlException: Procedure or Function...
2
by: Constantine | last post by:
Hi, I have developed one class called CProductInfo providing == and != operator features. I have one problem. When I use this class in my program, say CProductInfo product = null; and...
3
by: Constantine | last post by:
Hi, I have developed one class called CProductInfo providing == and != operator features. I have one problem. When I use this class in my program, say CProductInfo product = null; and...
3
by: kpeeroo | last post by:
Private Function AddCompanyOvertime() As Integer Dim companyID As Integer = GetCompanyID() Console.WriteLine(companyID) Dim paramCompanyID As New SqlParameter("@CompanyID",...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.