Welcome to Osama M.Yaser Site

This site is under construction

Forums

Post Reply
Forum Home > VB.Net > The use of SQL transaction in VB.Net

Osama M.Yaser Dweik
Site Owner
Posts: 9

When we need to use SQL Database Transaction inside our code we shoud do it like:

 

 

1- We need a connection string to be defined and ready.

2- Open the database connection.

3- We will use a command which we will assign our sql statment to created from the connection.

4- Define the transaction from the connection.

5- Link the command to the transaction defined.

6- Define the parameters we will use.

7- Execute the sql statment.

8- Clear the parameters.

9- Commit the transaction.

10- Close the database connection.

 

 

We may use a Rollback if the transaction process didn't complete successfully or an error happend.

 

 

Here is a code example of how it is done:

---------------------------------

Dim Conn As New Data.SqlClient.SqlConnection

Dim Comm As Data.SqlClient.SqlCommand = Conn.CreateCommand()

Dim SQL As String

Conn.ConnectionString = CONN_STR ' CONN_STR is the connection string

Comm.Connection = Conn

Conn.Open()

Dim Trans As Data.SqlClient.SqlTransaction = Conn.BeginTransaction

Comm.Transaction = Trans

SQL = "UPDATE EMPLOYEE SET SSNO = @SSNO WHERE EMPNO = @EMPNO"

Comm.CommandText = SQL

Comm.Parameters.AddWithValue("EMPNO", TXT_EMPNO.TEXT)

Comm.Parameters.AddWithValue("SSNO", TXT_EMPSSNO.TEXT)

Comm.ExecuteNoneQuery

Comm.Parameters.Clear()

'Commit Process

 

Try

Trans.Commit()

Conn.Close()

Catch ex As Exception

Msgbox "Transaction is not commited"

Trans.Rollback()

End Try

 

 

-----------------------------------

 

Osama Dweik

October 1, 2010 at 11:49 AM Flag Quote & Reply

You must login to post.