Board index » Visual Studio » Transactions

Visual Studio379
Hey Guys, how I can use the BEGIN TRANSACTION and the COMMIT statements in

my VB code??


-

Transactions are handled in VB code that connects to a database using ADO using the following:



Connection.BeginTrans

Connection.CommitTrans

Connection.RollbackTrans



If you want more, please post a more specific question.



--

Al Reid



"It ain't what you don't know that gets you into trouble. It's what you know

for sure that just ain't so." --- Mark Twain



"André Almeida Maldonado" <deweb@bol.com.br>wrote in message news:uOYJVhUqDHA.2804@TK2MSFTNGP09.phx.gbl...



-

Ok, but between the Conn.BeginTrans and Conn.CommitTrans, I need to put my

SQL statement, right? How I can do it?







Connection.BeginTrans



????



Connection.CommitTrans







"Al Reid" <areidjr@reidDASHhome.com>escreveu na mensagem



-

André,



Right, sort of. You need to build your SQL statements then execute them using the

Connection.Execute, Command.Execute.



Connection.BeginTrans



Connection.Execute "UPDATE tblTest SET...."



Connection.CommitTrans





--

Al Reid



"It ain't what you don't know that gets you into trouble. It's what you know

for sure that just ain't so." --- Mark Twain



"André Almeida Maldonado" <deweb@bol.com.br>wrote in message news:#exLg#UqDHA.3180@TK2MSFTNGP11.phx.gbl...



-

This example shows how to use the ADO transaction methods=20

BeginTrans, CommitTrans, and RollbackTrans to manage a=20

transaction.=20



Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset



. . .=20

' Open connection.

cn.Open



' Open titles table.

rs.Open "SELECT * FROM titles", Cn, adOpenDynamic,=20

adLockPessimistic

. . .

' Begin the transaction.

rs.MoveFirst

cn.BeginTrans



' User loops through the recordset making changes.

. . .=20

' Ask if the user wants to commit all the changes made.

If MsgBox("Save all changes?", vbYesNo) =3D vbYes Then

cn.CommitTrans

Else

cn.RollbackTrans

End If

Sorry I forgot something important, here goes the example:





Set rstTitles =3D New ADODB.Recordset

strSQLTitles =3D "Titles"

rstTitles.Open strSQLTitles, Cnxn, adOpenDynamic,=20

adLockPessimistic, adCmdTable

=20

Cnxn.BeginTrans

=20

' Loop through recordset and prompt user

' to change the type for a specified title

=20

rstTitles.MoveFirst

=20

Do Until rstTitles.EOF

If Trim(rstTitles!Type) =3D "psychology" Then

strTitle =3D rstTitles!Title

strMessage =3D "Title: " & strTitle & vbCr & _

"Change type to self help?"



' If yes, change type for the specified title

If MsgBox(strMessage, vbYesNo) =3D vbYes Then

rstTitles!Type =3D "self_help"

rstTitles.Update

End If

End If

rstTitles.MoveNext

Loop



' Prompt user to commit all changes made

If MsgBox("Save all changes?", vbYesNo) =3D vbYes Then

Cnxn.CommitTrans

Else

Cnxn.RollbackTrans

End If



Keep in mind this note: "The BeginTrans, CommitTrans, and=20

RollbackTrans methods are not available on a client-side=20

Connection object"

Villaco

In article <01fc01c3a953$9a268020$a401280a@phx.gbl>, LibertadV

<anonymous@discussions.microsoft.com>writes

MDAC 2.8 is no longer supporting Jet and ODBC am getting a bit

depressed:( ), so can someone give me advice?



You access the data in the underlying database -

by means of a record set and can traverse the set of data returned

several ways depending upon the cursor type in the open statement.



You can update the underlying data -

using the record set update method



*or*



you can use your own sql statement to update the underlying database.



If you have a program that is dealing mainly with queries with just an

occasional update, what is the 'accepted' method for doing the

updating and associated locking?

I don't want to lock out a whole record set just because a user *may*

want to update one of the items in it.

Is it possible to open a record set as adLockReadOnly (ie underlying

database records not locked) then later, if a user picks out 'a' record

to update, change the lock for a selected record to adLockPesimistic for

the duration of the update and then back to adLockReadOnly? Will it

involve opening another record set for that one item with

adLockPessimistic - a shame to hit the database twice for the same

record - and doing all the transaction stuff at that time?



--

Jane Ransom in Lancaster.

If you need to email me for any other reason, put ransoms

at jandg dot demon dot co dot uk where you see ransom@deadspam.com





-