How do I make xp_cmdshell transactions run on the client instead of the server

Post title says it all. Any ideas? I asked this earlier but it seems to
have gotten lost in the shuffle.
Randall Arnoldxp_cmdshell runs OS level commands on the server -not the client. Using
xp_cmdshell to run executables on the client is not, in my experience,
something that you even want to attempt. (I'm not saying it can't be
done -just that it is too risky and very troublesome.)
However, if you are trying to have xp_cmdshell read the client computer's
file system, you could take the following steps.
Put xp_cmdshell into a Stored Procedure, use host_name to determine the
client computer and build the unc filepath.
You will be able to read the directory, and read files and write files to
the client computer. (Assuming you have appropriate permissions on the
client computer. Of course, you would NEVER be executing xp_cmdshell with
network admin priviledges, would you?)
While this can be done, the better question is why would you want to do it,
and what are the security ramifications?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:GGaog.32766$Nb2.601914@.news1.nokia.com...
> Post title says it all. Any ideas? I asked this earlier but it seems to
> have gotten lost in the shuffle.
> Randall Arnold
>|||I want to be able to periodically launch a VB script on the client PC.
Security is not an issue at all in this environment.
Randall
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:Ohd5kvfmGHA.4700@.TK2MSFTNGP02.phx.gbl...
> xp_cmdshell runs OS level commands on the server -not the client. Using
> xp_cmdshell to run executables on the client is not, in my experience,
> something that you even want to attempt. (I'm not saying it can't be
> done -just that it is too risky and very troublesome.)
> However, if you are trying to have xp_cmdshell read the client computer's
> file system, you could take the following steps.
> Put xp_cmdshell into a Stored Procedure, use host_name to determine the
> client computer and build the unc filepath.
> You will be able to read the directory, and read files and write files to
> the client computer. (Assuming you have appropriate permissions on the
> client computer. Of course, you would NEVER be executing xp_cmdshell with
> network admin priviledges, would you?)
> While this can be done, the better question is why would you want to do
> it, and what are the security ramifications?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
> news:GGaog.32766$Nb2.601914@.news1.nokia.com...
>|||Security is always an issue :) My question would be do you absolutely have
to do this from within SQL Server? If not, I would create a Windows Service
to launch your script on a timer, or something to that effect.
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:fObog.32769$Nb2.601990@.news1.nokia.com...
>I want to be able to periodically launch a VB script on the client PC.
>Security is not an issue at all in this environment.
> Randall
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:Ohd5kvfmGHA.4700@.TK2MSFTNGP02.phx.gbl...
>|||I'm looking at any and all reasonable options to solving this need, working
several threads in parallel. One idea similar to this one but it requires
the symin role to have write/execute privileges on another server in a
certain folder but the IT guys here can't figure out how to give symin
that ability.
*sigh*
Randall
"Mike C#" <xyz@.xyz.com> wrote in message
news:OzgeLjgmGHA.3600@.TK2MSFTNGP02.phx.gbl...
> Security is always an issue :) My question would be do you absolutely
> have to do this from within SQL Server? If not, I would create a Windows
> Service to launch your script on a timer, or something to that effect.
> "Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
> news:fObog.32769$Nb2.601990@.news1.nokia.com...
>|||You are using SQL 2000 right? Honestly this task doesn't belong on SQL
Server. You can probably force the issue, but you'd be better off overall
if you made this a separate application that operated independently of SQL
Server. Is there some particular reason you feel you need to have it kick
off from inside SQL Server?
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:MsAog.33256$_k2.585855@.news2.nokia.com...
> I'm looking at any and all reasonable options to solving this need,
> working several threads in parallel. One idea similar to this one but it
> requires the symin role to have write/execute privileges on another
> server in a certain folder but the IT guys here can't figure out how to
> give symin that ability.
> *sigh*
>|||Well there's always VBScript, .BAT files and the dos command shell "at"
command :) I think someone else already mentioned sharing a directory on
the client and mapping a drive to it from the server. Another possibility
(note that I haven't actually tried this...) might be to install MSDE on the
client and run an SP via linked server that runs xp_cmdshell on the client.
Note again that I'm not even sure this would work, as I haven't tried it,
but it might be worth a try... I'd still recommend using a scripting
language of some sort to do the job, but if xp_cmdshell is what you want,
then you might give this a try.
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:ldDog.33266$_k2.585753@.news2.nokia.com...
> I'm trying to do as much as possible via SQL server because that's what we
> have and I lack the tools to "do it right" otherwise. I'd much rather be
> doing this in ASP.NET and deploying everything on the intranet the way it
> SHOULD be done. This facility will be closed by this time next year so
> I'm not exactly seeing people jump all over my resource requests. ; )
> But in the meantime, I still have this demand to deal with...
> Randall|||I've tried scripting, but can't get our IT guys to figure out how to give
symin write/execute privileges on a protected share (where the work needs
to take place and results stored).
Randall
"Mike C#" <xxx@.yyy.com> wrote in message news:jNEog.63$Ur7.47@.fe09.lga...
> Well there's always VBScript, .BAT files and the dos command shell "at"
> command :) I think someone else already mentioned sharing a directory on
> the client and mapping a drive to it from the server. Another possibility
> (note that I haven't actually tried this...) might be to install MSDE on
> the client and run an SP via linked server that runs xp_cmdshell on the
> client. Note again that I'm not even sure this would work, as I haven't
> tried it, but it might be worth a try... I'd still recommend using a
> scripting language of some sort to do the job, but if xp_cmdshell is what
> you want, then you might give this a try.
> "Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
> news:ldDog.33266$_k2.585753@.news2.nokia.com...
>|||If you don't mind, can I ask specifically what you're trying to accomplish?
Someone might be able to give you a better solution if we knew exactly what
you were trying to do. So far it sounds like you might be trying to read
some file(s) in, do some processing on them and write them back out? Or are
you trying to import or export data from SQL Server? Or maybe some
combination?
As for periodically executing a VBScript on a timer, the "at" command could
probably do the trick for you. As for setting share permissions, it all
depends on your network -- you might want to try one of the .networking, .vb
or .vbscript newsgroups (if you haven't already)
"Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
news:nyUog.32924$Nb2.605940@.news1.nokia.com...
> I've tried scripting, but can't get our IT guys to figure out how to give
> symin write/execute privileges on a protected share (where the work
> needs to take place and results stored).
> Randall|||I'm trying to take daily SQL queries and automatically build Powerpoint
presentations containing charts and tables representing data from those
queries (production performance/yield metrics). It's too much work for
people do be constantly burdened with, but I lack the proper tools and have
had trouble getting expenditures approved. This facility will be shuttered
by this time next year... but meanwhile work has to get out and we're told
we have to solve everything for the folks in Mexico who will be taking our
jobs.
Anyway, one poster here showed me how to get SQL Server 2000 Reporting
Services for free, so I've ordered that. I'll try to put management off
until it comes in.
As for the folder access, I have found that simply granting the
Adminstrators group on server A write/execute privileges on a folder on
server B solves my scripting problem. Just having a hard time getting IT
guys to do it.
Thanks for your interest.
Randall
"Mike C#" <xyz@.xyz.com> wrote in message
news:utHt9nEnGHA.3656@.TK2MSFTNGP03.phx.gbl...
> If you don't mind, can I ask specifically what you're trying to
> accomplish? Someone might be able to give you a better solution if we knew
> exactly what you were trying to do. So far it sounds like you might be
> trying to read some file(s) in, do some processing on them and write them
> back out? Or are you trying to import or export data from SQL Server? Or
> maybe some combination?
> As for periodically executing a VBScript on a timer, the "at" command
> could probably do the trick for you. As for setting share permissions, it
> all depends on your network -- you might want to try one of the
> .networking, .vb or .vbscript newsgroups (if you haven't already)
> "Randall Arnold" <randall.nospam.arnold@.nospamnokia.com.> wrote in message
> news:nyUog.32924$Nb2.605940@.news1.nokia.com...
>sql

How do I encapsulate Transactions in a data entry form with multiple insert and update statement

I have 3 tables - Sales, Purchases, and PurchasesAppropriation

Now, I have a data entry form for Sales. In this form, I first enter all the details for the item to be sold (single item per sale record, there is no master-detail relationship). Then, based on the item name, it pulls a few records from the purchases table, and populates a dataset in memory. It then compares the quantities of the item being sold, i.e. the sale quantity with the purchase quantity of each record in the dataset, and based on preset criteria, selects one (or more, it is set to compare in a loop) record(s) and passes all the info from that record into the PurchasesAppropriation table. At the same time, it makes updates to a couple of fields in the Purchases table for that particular record being passed to the appropriation table.

I want to encapsulate this whole thing into a transaction, so if a problem occurs somewhere (the most problematic is the PurchaseAppropriation insert statement), parts of the transaction (i.e. the insert for the sale table, and the update for the Purchase table) are not saved to the database. I want it to enter either the whole thing at once, or throw an error and change nothing in the db at all. How do I go about doing that?

(Note, this whole thing takes place in the form's button's click event)
(Also note, all insert and update statements are in the form - Dim Statement as String, they are all accurate, all the data entry works fine, and there is nothing wrong with my connection string either. The only issues I face which give errors while entering data are the datetime fields, which is already in discussion in another thread. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1679851&SiteID=1)

I have an insert statement passed in the beginning of the form's button's click event (to the Sale table)

Code Snippet

connstr.Open()
Dim cmd As New SqlClient.SqlCommand(InsertSales, connstr)
cmd.ExecuteNonQuery()
connstr.Close()

Then I have it populate the dataset (with the data from the Purchase table)

Code Snippet

Dim dataAdapter As New SqlClient.SqlDataAdapter(SelectPurchases, connstr)
dataAdapter.Fill(dt)
DataGridView1.DataSource = dt
dataAdapter.Dispose()

Then I have a Loop FOR NEXT statement with a nested IF statement. If certain criteria is met in the IF statement, then another insert statement is passed to the db (to the PurchaseAppropriation table), and the loop is repeated.

Code Snippet

connstr.Open()
Dim cmd2 As New SqlClient.SqlCommand(InsertPurAppropriation1, connstr)
cmd2.ExecuteNonQuery()
connstr.Close()

If it is not met, then the IF statement exits the Loop and continues with the statements after it. There are 3 conditions within the IF statement. I have created 2 extra subprocedures within the form class, which pass one update statement to the Purchase table each. The contents of both differ, that's why there are 2. Before passing any Insert Statement to the PurchaseAppropriation (even for the third condition within the loop and nested if statement), the sub procedures are called from within the IF statements, and the appropriate records are updated in the Purchases table.

Code Snippet

Sub MarkBalanceEmpty()
connstr.Open()
Dim cmd4 As New SqlClient.SqlCommand(UpdatePurchases1, connstr)
cmd4.ExecuteNonQuery()
connstr.Close()
End Sub

Code Snippet

Sub MarkBalancePartial()
connstr.Open()
Dim cmd5 As New SqlClient.SqlCommand(UpdatePurchases2, connstr)
cmd5.ExecuteNonQuery()
connstr.Close()
End Sub

(Statement after the 'Next' in the Loop statement. The sub procedures are actually at the bottom of the page, they are not within the For Next statement, they are just called from there, before any Insert statements are passed, or the loop is exited with a 'Exit For' statement.)

Thereafter there is another Insert Statement to be passed to the PurchasesAppropriation table.

Code Snippet

connstr.Open()
Dim cmd3 As New SqlClient.SqlCommand(InsertPurAppropriation2, connstr)
cmd3.ExecuteNonQuery()
connstr.Close()

And the whole thing is done.

Now what I did was, add a Begin Transaction before the record in the sale table is inserted

Code Snippet

'Dim TransStart as String = "Begin Transaction"
connstr.Open()
Dim cmd0 As New SqlClient.SqlCommand(TransStart, connstr)
cmd0.ExecuteNonQuery()
connstr.Close()

and a Commit at the end, when everything is done (i.e after the cmd3 is executed)

Code Snippet

'Dim TransEnd as String = "Commit Transaction Go"
connstr.Open()
Dim cmd6 As New SqlClient.SqlCommand(TransEnd, connstr)
cmd6.ExecuteNonQuery()
connstr.Close()

I deliberately intoduced an error in the Insert Statement for the PurchaseAppropriation table (for testing) and checked to see if the other records were inserted/updted, and they were. Where am I going wrong? Why can't I get the transaction to behave as it should?

I am using SQL Server 2005 Express SP2 and VBExpress 2005 (no SP).

You can manage transactions at the ADO.NET level, using the Transaction and Connection objects.

Here's the basic approach:

Code Snippet

Dim cn as New SqlConnection(cnstring)

Dim tr As SqlTransaction

cn.Open()

Try

tr = cn.BeginTransaction(IsolationLevel.Serializable)

'do your work here

tr.Commit()

Catch as Exception

tr.Rollback()

End Try

|||

Okay, I did as you said, and it seems to work fine, atleast it didn't make any changes to the database, as it is supposed to do (or not do, depends on the way you look at it). But, it did throw an error on the rollback statement, as is shown here -

http://img502.imageshack.us/img502/818/rollbackerror1hs2.jpg

Is it supposed to do that? I mean, shouldn't it rollback the changes and continue with the statements after the rollback, without throwing an error? Where am I going wrong?

|||The connection object needs to remain open throughout the whole transaction. You shouldnt close it until all of your statements have finished executing.|||

Oh, you can do that? I did think that was the problem when I got this issue the first time, but I'm just a beginner, and I had just copy-pasted the code for passing SQL commands in VB from another thread. So I wasn't sure what the results would be. I didn't know I could keep the connection open throughout and not have to open and close it for every SQL command that I have to pass from that subprocedure/eventprocedure.

I'll try that and let you know what happens.

|||

Nope, gives the same results. It still throws an error on the rollback statement as shown in the screenshot above.

Other than that, the db doesn't get unnecesarily updated any more, just like I wanted. It gives me the same results, whether I open and close the connection string for every SQL command or not.

Oh, and I just checked - I tried entering the data without the deliberately intrduced error, and it doesn't enter the data in the db at all, it still gives me the same error with the rollback statement.

|||

You need to keep the connection open for the duration of the transaction, ie. you need to open before or at the top of the try and close after you've done a commit or rollback.

I don't see where you are initiating the transaction. You open the connection, create a command and then execute it. The transaction is never being created.

|||

If you're referring to my screenshot, obviously you won't see where I'm opening the connection, because, as you mentioned, it has to be at the top. The portion you're seeing only pertains to where in the code the error appears, and that is at the rollback, which obviously will be at the bottom of the code. You're seeing lines 91 - 119, whereas the transaction is explicitly started at line 15 and the connection is opened on line 46.

I see now where I'm going wrong, I closed the connection on line 97 before the rollback is being executed on line 104. Even when I tried to keep the connection open throughout, I made the mistake of closing the connection before the rollback statement. I'll put the close connection after the rollback, and see what happens.

Nope, no luck, tried putting the close connection string statement after the rollback, but still get the same error.

I think there's something wrong with the try catch block you gave me, maybe a line of code or more are missing.

I don't get any squigly lines underneath any text in my entire code window, meaning everything seems to be fine so far, no syntax errors, but I think something else might be missing.

(e.g. in your code, you said -

Code Snippet

Catch As Exception

you forgot the 'ex' and that gave me a blue squigly line underneath the 'As Exception'. Also you had said -

Code Snippet

Dim cn as New SqlConnection(cnstring)

whereas it should have been -

Code Snippet

Dim cn as New SqlClient.SqlConnection

).

Mind you, I am still a beginner, so most of the times I don't know if the code samples you guys give are incomplete/incorrect or not. So of there are any other such mistakes, I might not be able to find them and correct them.

|||

No problem.

We all make boo-boo's.

My post was meant as an outline to the approach.

Can you post that entire section of your code so I can see what all is going on?
Kinda hard to troubleshoot in the dark

|||

No problem. It's a little big, but here's the entire 150 lines of code for my form -

The form designer itself has 9 textboxes with corresponding labels, and a datagridview and a button.

Code Snippet

Public Class frmSales

Dim dt As New DataTable()

Dim connstr As New SqlClient.SqlConnection

Dim RowIndex As Integer = 0

Dim PurNo As Integer

Dim NewBalPurQty As Integer

Dim tr As SqlClient.SqlTransaction

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Try

tr = connstr.BeginTransaction(IsolationLevel.Serializable)

Dim InsertSales As String

Dim SelectPurchases As String

Dim InsertPurAppropriation1 As String

Dim InsertPurAppropriation2 As String

Dim SaleNo As String = TextBox1.Text

Dim BillNo As String = TextBox2.Text

Dim SaleDate As String = TextBox3.Text

Dim Broker As String = TextBox4.Text

Dim Share As String = TextBox5.Text

Dim Quantity As String = TextBox6.Text

Dim Rate As String = TextBox7.Text

Dim Amount As String = TextBox8.Text

Dim BalPurQty As String

Dim FullPurQty As Integer

Dim PurDate As Date

Dim PurRate As Double

Dim PurAmount As Double

InsertSales = "Insert Into Sales Values (" _

& SaleNo & "," & "'" & BillNo & "'" & "," & "'" & SaleDate & "'" & "," & "'" & Broker & "'" & "," & _

"'" & Share & "'" & "," & Quantity & "," & Rate & "," & Amount & ");"

SelectPurchases = "Select PurchaseNo, BillNo, Date, Broker, ShareName, Quantity, Rate, Amount, Old, Balance, BalanceQty From Purchases Where (ShareName = " & "'" & Share & "')" & " And (Balance <> 'e') Order By Date;"

connstr.ConnectionString = "Data Source=.\SQLEXPRESS; initial catalog=MyDatabase.MDF; Integrated Security = SSPI"

connstr.Open()

Dim cmd As New SqlClient.SqlCommand(InsertSales, connstr)

cmd.ExecuteNonQuery()

'connstr.Close()

Dim dataAdapter2 As New SqlClient.SqlDataAdapter(SelectPurchases, connstr)

dataAdapter2.Fill(dt)

DataGridView1.DataSource = dt

dataAdapter2.Dispose()

For i As Integer = 0 To (dt.Rows.Count - 1)

RowIndex = i

BalPurQty = dt.Rows(RowIndex)("BalanceQty")

PurDate = dt.Rows(RowIndex)("Date")

PurRate = CStr(dt.Rows(RowIndex)("Rate"))

If CInt(BalPurQty) = CInt(Quantity) Then

FullPurQty = CInt(Quantity)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkEmpty()

Exit For

ElseIf CInt(BalPurQty) > CInt(Quantity) Then

FullPurQty = CInt(Quantity)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

NewBalPurQty = CInt(BalPurQty) - CInt(Quantity)

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkPartial()

Exit For

ElseIf CInt(BalPurQty) < CInt(Quantity) Then

FullPurQty = CInt(BalPurQty)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

Quantity = CInt(Quantity) - CInt(BalPurQty)

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkEmpty()

InsertPurAppropriation1 = "Insert Into PurchaseAppropriation Values (" _

& SaleNo & "," & PurNo & "," & "'" & PurDate & "'" & "," & "'" & Share & _

"'" & "," & FullPurQty & "," & PurRate & "," & PurAmount & ");"

'connstr.Open()

Dim cmd2 As New SqlClient.SqlCommand(InsertPurAppropriation1, connstr)

cmd2.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Appropriated)")

End If

Next

InsertPurAppropriation2 = "Insert Into PurchaseAppropriation Values (" & SaleNo & "," & _

PurNo & "," & "'" & PurDate & "'" & "," & "'" & Share & "'" & "," & FullPurQty & "," & _

PurRate & "," & PurAmount & ");"

'connstr.Open()

Dim cmd3 As New SqlClient.SqlCommand(InsertPurAppropriation2, connstr)

cmd3.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Appropriated)")

tr.Commit()

MsgBox("Record Inserted")

Catch ex As Exception

tr.Rollback()

connstr.Close()

End Try

ClearTextBoxes()

End Sub

Sub ClearTextBoxes()

TextBox1.Clear()

TextBox2.Clear()

TextBox3.Clear()

TextBox4.Clear()

TextBox5.Clear()

TextBox6.Clear()

TextBox7.Clear()

TextBox8.Clear()

End Sub

Sub MarkEmpty()

Dim UpdatePurchases1 As String

UpdatePurchases1 = "Update Purchases Set Balance = 'e', BalanceQty = 0 Where PurchaseNo = " & PurNo & ";"

'connstr.Open()

Dim cmd4 As New SqlClient.SqlCommand(UpdatePurchases1, connstr)

cmd4.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Updated)")

End Sub

Sub MarkPartial()

Dim UpdatePurchases2 As String

UpdatePurchases2 = "Update Purchases Set Balance = 'p', BalanceQty = " & NewBalPurQty & " Where PurchaseNo = " & PurNo & ";"

'connstr.Open()

Dim cmd5 As New SqlClient.SqlCommand(UpdatePurchases2, connstr)

cmd5.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Updated)")

End Sub

End Class

|||

Ah, the problem is that your trying to initiate the transaction before you open the connection.

The transaction needs a connection in place.

You need this basic structure:

Code Snippet

connstr.ConnectionString = "Data Source=.\SQLEXPRESS; initial catalog=MyDatabase.MDF; Integrated Security = SSPI"

connstr.Open() 'Open Connection

Dim tr As SqlClient.SqlTransaction = connstr.BeginTransaction(IsolationLevel.Serializable) 'Start transaction

Try

... do stuff

tr.Commit() 'Save our work

Catch ex as Exception

tr.Rollback() 'remove our work

...handle exception, throw error, etc.

Finally

connstr.Close() 'close the transaction

End Try

...continue processing

|||

My form's code is exactly as I've posted above, except, now I've modifed the order of the connection string's opening and the transaction starting, as you've suggested in the post above, and I have added this line to the catch block

Code Snippet

Dim ex as Exception

tr.Rollback()

MsgBox("Database Error!" & vbCrLf & ex.Message)

So after I ran the form, this is what I got. There still seems to be something missing.

http://img141.imageshack.us/img141/8656/transactionerror1yc0.jpg

On a bright note, I don't get that above error anymore. The transaction's rollback feature seems to be working fine, and it also processes the lines after the try catch block as it should, that is it clears the textboxes fine.

|||

Progress!!

ExecuteNonQuery's apparently need to be manually enlisted into the transaction.

See if this fixes you up:

After you create each command, add a line:

Code Snippet

cmd.Transaction = tr

Before you execute the command.

Since you have multiple command objects with different names, you'll need to adjust the object name for each occurance.

|||

Nope, only this time, I get a new error.

http://img510.imageshack.us/img510/5476/transactionerror2ds3.jpg

|||DaleJ, I'm still stuck with this thing, and need your help. Waiting for your reply.

How do I encapsulate Transactions in a data entry form with multiple insert and update statement

I have 3 tables - Sales, Purchases, and PurchasesAppropriation

Now, I have a data entry form for Sales. In this form, I first enter all the details for the item to be sold (single item per sale record, there is no master-detail relationship). Then, based on the item name, it pulls a few records from the purchases table, and populates a dataset in memory. It then compares the quantities of the item being sold, i.e. the sale quantity with the purchase quantity of each record in the dataset, and based on preset criteria, selects one (or more, it is set to compare in a loop) record(s) and passes all the info from that record into the PurchasesAppropriation table. At the same time, it makes updates to a couple of fields in the Purchases table for that particular record being passed to the appropriation table.

I want to encapsulate this whole thing into a transaction, so if a problem occurs somewhere (the most problematic is the PurchaseAppropriation insert statement), parts of the transaction (i.e. the insert for the sale table, and the update for the Purchase table) are not saved to the database. I want it to enter either the whole thing at once, or throw an error and change nothing in the db at all. How do I go about doing that?

(Note, this whole thing takes place in the form's button's click event)
(Also note, all insert and update statements are in the form - Dim Statement as String, they are all accurate, all the data entry works fine, and there is nothing wrong with my connection string either. The only issues I face which give errors while entering data are the datetime fields, which is already in discussion in another thread. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1679851&SiteID=1)

I have an insert statement passed in the beginning of the form's button's click event (to the Sale table)

Code Snippet

connstr.Open()
Dim cmd As New SqlClient.SqlCommand(InsertSales, connstr)
cmd.ExecuteNonQuery()
connstr.Close()

Then I have it populate the dataset (with the data from the Purchase table)

Code Snippet

Dim dataAdapter As New SqlClient.SqlDataAdapter(SelectPurchases, connstr)
dataAdapter.Fill(dt)
DataGridView1.DataSource = dt
dataAdapter.Dispose()

Then I have a Loop FOR NEXT statement with a nested IF statement. If certain criteria is met in the IF statement, then another insert statement is passed to the db (to the PurchaseAppropriation table), and the loop is repeated.

Code Snippet

connstr.Open()
Dim cmd2 As New SqlClient.SqlCommand(InsertPurAppropriation1, connstr)
cmd2.ExecuteNonQuery()
connstr.Close()

If it is not met, then the IF statement exits the Loop and continues with the statements after it. There are 3 conditions within the IF statement. I have created 2 extra subprocedures within the form class, which pass one update statement to the Purchase table each. The contents of both differ, that's why there are 2. Before passing any Insert Statement to the PurchaseAppropriation (even for the third condition within the loop and nested if statement), the sub procedures are called from within the IF statements, and the appropriate records are updated in the Purchases table.

Code Snippet

Sub MarkBalanceEmpty()
connstr.Open()
Dim cmd4 As New SqlClient.SqlCommand(UpdatePurchases1, connstr)
cmd4.ExecuteNonQuery()
connstr.Close()
End Sub

Code Snippet

Sub MarkBalancePartial()
connstr.Open()
Dim cmd5 As New SqlClient.SqlCommand(UpdatePurchases2, connstr)
cmd5.ExecuteNonQuery()
connstr.Close()
End Sub

(Statement after the 'Next' in the Loop statement. The sub procedures are actually at the bottom of the page, they are not within the For Next statement, they are just called from there, before any Insert statements are passed, or the loop is exited with a 'Exit For' statement.)

Thereafter there is another Insert Statement to be passed to the PurchasesAppropriation table.

Code Snippet

connstr.Open()
Dim cmd3 As New SqlClient.SqlCommand(InsertPurAppropriation2, connstr)
cmd3.ExecuteNonQuery()
connstr.Close()

And the whole thing is done.

Now what I did was, add a Begin Transaction before the record in the sale table is inserted

Code Snippet

'Dim TransStart as String = "Begin Transaction"
connstr.Open()
Dim cmd0 As New SqlClient.SqlCommand(TransStart, connstr)
cmd0.ExecuteNonQuery()
connstr.Close()

and a Commit at the end, when everything is done (i.e after the cmd3 is executed)

Code Snippet

'Dim TransEnd as String = "Commit Transaction Go"
connstr.Open()
Dim cmd6 As New SqlClient.SqlCommand(TransEnd, connstr)
cmd6.ExecuteNonQuery()
connstr.Close()

I deliberately intoduced an error in the Insert Statement for the PurchaseAppropriation table (for testing) and checked to see if the other records were inserted/updted, and they were. Where am I going wrong? Why can't I get the transaction to behave as it should?

I am using SQL Server 2005 Express SP2 and VBExpress 2005 (no SP).

You can manage transactions at the ADO.NET level, using the Transaction and Connection objects.

Here's the basic approach:

Code Snippet

Dim cn as New SqlConnection(cnstring)

Dim tr As SqlTransaction

cn.Open()

Try

tr = cn.BeginTransaction(IsolationLevel.Serializable)

'do your work here

tr.Commit()

Catch as Exception

tr.Rollback()

End Try

|||

Okay, I did as you said, and it seems to work fine, atleast it didn't make any changes to the database, as it is supposed to do (or not do, depends on the way you look at it). But, it did throw an error on the rollback statement, as is shown here -

http://img502.imageshack.us/img502/818/rollbackerror1hs2.jpg

Is it supposed to do that? I mean, shouldn't it rollback the changes and continue with the statements after the rollback, without throwing an error? Where am I going wrong?

|||The connection object needs to remain open throughout the whole transaction. You shouldnt close it until all of your statements have finished executing.|||

Oh, you can do that? I did think that was the problem when I got this issue the first time, but I'm just a beginner, and I had just copy-pasted the code for passing SQL commands in VB from another thread. So I wasn't sure what the results would be. I didn't know I could keep the connection open throughout and not have to open and close it for every SQL command that I have to pass from that subprocedure/eventprocedure.

I'll try that and let you know what happens.

|||

Nope, gives the same results. It still throws an error on the rollback statement as shown in the screenshot above.

Other than that, the db doesn't get unnecesarily updated any more, just like I wanted. It gives me the same results, whether I open and close the connection string for every SQL command or not.

Oh, and I just checked - I tried entering the data without the deliberately intrduced error, and it doesn't enter the data in the db at all, it still gives me the same error with the rollback statement.

|||

You need to keep the connection open for the duration of the transaction, ie. you need to open before or at the top of the try and close after you've done a commit or rollback.

I don't see where you are initiating the transaction. You open the connection, create a command and then execute it. The transaction is never being created.

|||

If you're referring to my screenshot, obviously you won't see where I'm opening the connection, because, as you mentioned, it has to be at the top. The portion you're seeing only pertains to where in the code the error appears, and that is at the rollback, which obviously will be at the bottom of the code. You're seeing lines 91 - 119, whereas the transaction is explicitly started at line 15 and the connection is opened on line 46.

I see now where I'm going wrong, I closed the connection on line 97 before the rollback is being executed on line 104. Even when I tried to keep the connection open throughout, I made the mistake of closing the connection before the rollback statement. I'll put the close connection after the rollback, and see what happens.

Nope, no luck, tried putting the close connection string statement after the rollback, but still get the same error.

I think there's something wrong with the try catch block you gave me, maybe a line of code or more are missing.

I don't get any squigly lines underneath any text in my entire code window, meaning everything seems to be fine so far, no syntax errors, but I think something else might be missing.

(e.g. in your code, you said -

Code Snippet

Catch As Exception

you forgot the 'ex' and that gave me a blue squigly line underneath the 'As Exception'. Also you had said -

Code Snippet

Dim cn as New SqlConnection(cnstring)

whereas it should have been -

Code Snippet

Dim cn as New SqlClient.SqlConnection

).

Mind you, I am still a beginner, so most of the times I don't know if the code samples you guys give are incomplete/incorrect or not. So of there are any other such mistakes, I might not be able to find them and correct them.

|||

No problem.

We all make boo-boo's.

My post was meant as an outline to the approach.

Can you post that entire section of your code so I can see what all is going on?
Kinda hard to troubleshoot in the dark

|||

No problem. It's a little big, but here's the entire 150 lines of code for my form -

The form designer itself has 9 textboxes with corresponding labels, and a datagridview and a button.

Code Snippet

PublicClass frmSales

Dim dt AsNew DataTable()

Dim connstr AsNew SqlClient.SqlConnection

Dim RowIndex AsInteger = 0

Dim PurNo AsInteger

Dim NewBalPurQty AsInteger

Dim tr As SqlClient.SqlTransaction

PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Try

tr = connstr.BeginTransaction(IsolationLevel.Serializable)

Dim InsertSales AsString

Dim SelectPurchases AsString

Dim InsertPurAppropriation1 AsString

Dim InsertPurAppropriation2 AsString

Dim SaleNo AsString = TextBox1.Text

Dim BillNo AsString = TextBox2.Text

Dim SaleDate AsString = TextBox3.Text

Dim Broker AsString = TextBox4.Text

Dim Share AsString = TextBox5.Text

Dim Quantity AsString = TextBox6.Text

Dim Rate AsString = TextBox7.Text

Dim Amount AsString = TextBox8.Text

Dim BalPurQty AsString

Dim FullPurQty AsInteger

Dim PurDate AsDate

Dim PurRate AsDouble

Dim PurAmount AsDouble

InsertSales = "Insert Into Sales Values (" _

& SaleNo & "," & "'" & BillNo & "'" & "," & "'" & SaleDate & "'" & "," & "'" & Broker & "'" & "," & _

"'" & Share & "'" & "," & Quantity & "," & Rate & "," & Amount & ");"

SelectPurchases = "Select PurchaseNo, BillNo, Date, Broker, ShareName, Quantity, Rate, Amount, Old, Balance, BalanceQty From Purchases Where (ShareName = " & "'" & Share & "')" & " And (Balance <> 'e') Order By Date;"

connstr.ConnectionString = "Data Source=.\SQLEXPRESS; initial catalog=MyDatabase.MDF; Integrated Security = SSPI"

connstr.Open()

Dim cmd AsNew SqlClient.SqlCommand(InsertSales, connstr)

cmd.ExecuteNonQuery()

'connstr.Close()

Dim dataAdapter2 AsNew SqlClient.SqlDataAdapter(SelectPurchases, connstr)

dataAdapter2.Fill(dt)

DataGridView1.DataSource = dt

dataAdapter2.Dispose()

For i AsInteger = 0 To (dt.Rows.Count - 1)

RowIndex = i

BalPurQty = dt.Rows(RowIndex)("BalanceQty")

PurDate = dt.Rows(RowIndex)("Date")

PurRate = CStr(dt.Rows(RowIndex)("Rate"))

IfCInt(BalPurQty) = CInt(Quantity) Then

FullPurQty = CInt(Quantity)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkEmpty()

ExitFor

ElseIfCInt(BalPurQty) > CInt(Quantity) Then

FullPurQty = CInt(Quantity)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

NewBalPurQty = CInt(BalPurQty) - CInt(Quantity)

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkPartial()

ExitFor

ElseIfCInt(BalPurQty) < CInt(Quantity) Then

FullPurQty = CInt(BalPurQty)

PurNo = CInt(dt.Rows(RowIndex)("PurchaseNo"))

Quantity = CInt(Quantity) - CInt(BalPurQty)

PurAmount = CInt(FullPurQty) * CDbl(PurRate)

MarkEmpty()

InsertPurAppropriation1 = "Insert Into PurchaseAppropriation Values (" _

& SaleNo & "," & PurNo & "," & "'" & PurDate & "'" & "," & "'" & Share & _

"'" & "," & FullPurQty & "," & PurRate & "," & PurAmount & ");"

'connstr.Open()

Dim cmd2 AsNew SqlClient.SqlCommand(InsertPurAppropriation1, connstr)

cmd2.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Appropriated)")

EndIf

Next

InsertPurAppropriation2 = "Insert Into PurchaseAppropriation Values (" & SaleNo & "," & _

PurNo & "," & "'" & PurDate & "'" & "," & "'" & Share & "'" & "," & FullPurQty & "," & _

PurRate & "," & PurAmount & ");"

'connstr.Open()

Dim cmd3 AsNew SqlClient.SqlCommand(InsertPurAppropriation2, connstr)

cmd3.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Appropriated)")

tr.Commit()

MsgBox("Record Inserted")

Catch ex As Exception

tr.Rollback()

connstr.Close()

EndTry

ClearTextBoxes()

EndSub

Sub ClearTextBoxes()

TextBox1.Clear()

TextBox2.Clear()

TextBox3.Clear()

TextBox4.Clear()

TextBox5.Clear()

TextBox6.Clear()

TextBox7.Clear()

TextBox8.Clear()

EndSub

Sub MarkEmpty()

Dim UpdatePurchases1 AsString

UpdatePurchases1 = "Update Purchases Set Balance = 'e', BalanceQty = 0 Where PurchaseNo = " & PurNo & ";"

'connstr.Open()

Dim cmd4 AsNew SqlClient.SqlCommand(UpdatePurchases1, connstr)

cmd4.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Updated)")

EndSub

Sub MarkPartial()

Dim UpdatePurchases2 AsString

UpdatePurchases2 = "Update Purchases Set Balance = 'p', BalanceQty = " & NewBalPurQty & " Where PurchaseNo = " & PurNo & ";"

'connstr.Open()

Dim cmd5 AsNew SqlClient.SqlCommand(UpdatePurchases2, connstr)

cmd5.ExecuteNonQuery()

'connstr.Close()

'MsgBox("Purchase(s) Updated)")

EndSub

EndClass

|||

Ah, the problem is that your trying to initiate the transaction before you open the connection.

The transaction needs a connection in place.

You need this basic structure:

Code Snippet

connstr.ConnectionString = "Data Source=.\SQLEXPRESS; initial catalog=MyDatabase.MDF; Integrated Security = SSPI"

connstr.Open() 'Open Connection

Dim tr As SqlClient.SqlTransaction = connstr.BeginTransaction(IsolationLevel.Serializable) 'Start transaction

Try

... do stuff

tr.Commit() 'Save our work

Catch ex as Exception

tr.Rollback() 'remove our work

...handle exception, throw error, etc.

Finally

connstr.Close() 'close the transaction

End Try

...continue processing

|||

My form's code is exactly as I've posted above, except, now I've modifed the order of the connection string's opening and the transaction starting, as you've suggested in the post above, and I have added this line to the catch block

Code Snippet

Dim ex as Exception

tr.Rollback()

MsgBox("Database Error!" & vbCrLf & ex.Message)

So after I ran the form, this is what I got. There still seems to be something missing.

http://img141.imageshack.us/img141/8656/transactionerror1yc0.jpg

On a bright note, I don't get that above error anymore. The transaction's rollback feature seems to be working fine, and it also processes the lines after the try catch block as it should, that is it clears the textboxes fine.

|||

Progress!!

ExecuteNonQuery's apparently need to be manually enlisted into the transaction.

See if this fixes you up:

After you create each command, add a line:

Code Snippet

cmd.Transaction = tr

Before you execute the command.

Since you have multiple command objects with different names, you'll need to adjust the object name for each occurance.

|||

Nope, only this time, I get a new error.

http://img510.imageshack.us/img510/5476/transactionerror2ds3.jpg

|||DaleJ, I'm still stuck with this thing, and need your help. Waiting for your reply.