[RESOLVED] [2005] Connection

[shakti5385]

Hi all
If we are using more then one table in my code for saving the data. If both table contain different data then what I want
I want to delete data from first table, if I get error when I am saving then data in the second table.
any property we have directly

[jmcilhinney]

You need to wrap both operations in a transaction, so either everything completes or nothing does. What database are you using and are you using TableAdapters or DataAdapters?

[shakti5385]

Presently I am using the Access but I want this things on the SQL Server also,I am using the datatable.

[jmcilhinney]

If you want to use SQL Server then you should use SQL Server because you'll have to change your code to switch. You can use MSDE or SQL Server Express to develop even if you intend to use a full version of SQL Server when you deploy. If you want to code transactions then it will be different in each case so you should make up your mind now.

[shakti5385]

Presently I am using the access database , so you guide me about the access.
Thanks

[shakti5385]

Finally Got the solution
While a transaction is in progress parts of the database will get locked out
from other processes that are trying to access it. This is to ensure the safety of the transaction and other processes that are accessing the database. When you start a transaction perform the queries you need quickly so that the locks don't last long. And do not wait for user input inside a transaction.

myConnection.BeginTransaction()
'do stuff with the database here
If someErrorHappened Then 
   myConnection.RollbackTransaction()  
  Return
End If
myConnection.CommitTransaction()

[shakti5385]

This is the advance function for connection transaction,commit and the rollback!!
While a transaction is in progress parts of the database will get locked out
from other processes that are trying to access it. This is to ensure the safety of the transaction and other processes that are accessing the database. When you start a transaction perform the queries you need quickly so that the locks don't last long. And do not wait for user input inside a transaction.

tell me if any suggestion!!

#Region "Advance Function for connection rollback And Transaction By Shakti"
    ':Author ::::Shakti Singh Dulawat
    Public Function ExecuteMultipleQuery(ByVal QueryString() As String) As Boolean
        Dim myConnection As SqlClient.SqlConnection = Nothing
        Dim transaction As System.Data.SqlClient.SqlTransaction
        Dim command As DbCommand
        myConnection = OpenNewConnection() 'Open The SQL connection Here
        command = myConnection.CreateCommand()
        Try
            transaction = myConnection.BeginTransaction()
            command.CommandType = CommandType.Text
            command.Transaction = transaction
            command.Connection = myConnection
            For Each query As String In QueryString
                command.CommandText = query
                command.ExecuteNonQuery()
            Next
            transaction.Commit()
            Return True
        Catch ex As SqlClient.SqlException
            Throw New Exception("SQL Exception ", ex)
            transaction.Rollback()
            Return False
        Catch exx As Exception
            Throw New Exception("Other ", exx)
            transaction.Rollback()
            Return False
        Finally
            myConnection.Close()
            command.Dispose()
        End Try
    End Function
#End Region