Jump to content
Xtreme .Net Talk

Recommended Posts

Posted

Hello,

 

The details that are entered in a form in VB.NET are inserted in 3 different tables when "OK" button is clicked. I would like to either commit the transactions or rollback. Meaning if there any errors occurring while inserting into table2 during runtime then it shouldn't insert the values in table1 and table3 too.

 

Can the above mentioned problem be acheived in MS-ACCESS.

 

Please do help me by providing a sample.

  • Moderators
Posted

As Derek mentioned it wouldn't be practical in Access.

 

But you can can still create/use Stored Proc in Access....

 

   Private Sub ProductsProcs()
       Dim sSQL As String

       ' procProductsList - Retrieves entire table
       sSQL = "CREATE PROC procProductsList AS SELECT * FROM Products;"
       CreateStoredProc(sSQL)

       ' procProductsDeleteItem - Returns the details (one record) from the JobTitle table
       sSQL = "CREATE PROC procProductsDeleteItem(inProductID LONG) " & _
              "AS DELETE FROM Products WHERE ProductID = inProductID;"
       CreateStoredProc(sSQL)

       ' procProductsAddItem - Add one record to the JobTitle table
       sSQL = "CREATE PROC procProductsAddItem(inProductName VARCHAR(40), inSupplierID LONG, inCategoryID LONG) " & _
              "AS INSERT INTO Products (ProductName, SupplierID, CategoryID) Values (inProductName, inSupplierID, inCategoryID);"
       CreateStoredProc(sSQL)

       ' procProductsUpdateItem - Update one record on the JobTitle table
       sSQL = "CREATE PROC procProductsUpdateItem(inProductID LONG, inProductName VARCHAR(40)) " & _
              "AS UPDATE Products SET ProductName = inProductName WHERE ProductID = inProductID;"
       CreateStoredProc(sSQL)


   End Sub


   Private Sub CreateStoredProc(ByVal sSQL As String)
       Dim con As OleDbConnection
       Dim cmd As OleDbCommand = New OleDbCommand()
       Dim da As OleDbDataAdapter

       Dim sConStr As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Northwind.mdb"

       con = New OleDbConnection(sConStr)

       cmd.Connection = con
       cmd.CommandText = sSQL

       con.Open()
       cmd.ExecuteNonQuery()
       con.Close()

   End Sub

Visit...Bassic Software
  • *Experts*
Posted

I can't say if Access supports them or not, but a connection can call BeginTransaction, which returns a Transaction object that will do what you want. You must assign the connection's returned Transaction object to the Command object for each Command to be part of the same transaction.

 

Check out this link for more info.

 

-Nerseus

"I want to stand as close to the edge as I can without going over. Out on the edge you see all the kinds of things you can't see from the center." - Kurt Vonnegut
  • 3 weeks later...
  • Leaders
Posted
The same way you'd execute an sql statement except set the CommandType to CommandType.StoredProcedure. If the stored procedure accepts parameters then you'll need to add those to the Command object's parameters collection.
--tim

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...