SQL Transaction - Changing the connection being used

Question

I have a vb.net program where there are 2 databases - The live one and a test one. When saving a new user to the system, I need to insert the values into both databases (unless one fails, in which case insert into neither), so I am using an OleDbTransaction to handle this.

To connect to the test database, a checkbox is set to Checked = True state, and this is being stored as a Global variable, frmLogOn.isTest.

I have the following code to handle this. The first INSERT statement works totally fine, however, I then am unable to insert the user data into the other database (which is currently closed, so the newcon variable is where it gets opened). The connection opens fine, I've stepped through to check it, but I get an error message when executing the query, saying

The transaction is either not associated with the current connection or has been completed.

So, I feel that I'm not changing the database connection correctly - How do I change the code below to insert the data into two separate databases?

  Dim tr As OleDb.OleDbTransaction = Nothing

    If frmLogOn.isTest = False Then

        Try
            tr = con.BeginTransaction()
            Dim sql As String
            Dim admin As Boolean
            Dim userID As String
            Dim pword As String
            Dim UName As String

            admin = ugUsers.Rows(ugUsers.Rows.Count - 1).Cells("Admin").Value
            userID = ugUsers.Rows(ugUsers.Rows.Count - 1).Cells("User_ID").Value
            pword = ugUsers.Rows(ugUsers.Rows.Count - 1).Cells("Password").Value
            UName = ugUsers.Rows(ugUsers.Rows.Count - 1).Cells("UserName").Value


            sql = "INSERT INTO [Users]([Admin],[User_ID],[Password],[UserName]) VALUES(@admin, @uID, @pw, @un)"
            cmd = New OleDb.OleDbCommand(sql, con)
            cmd.Transaction = tr

            With cmd.Parameters
                .AddWithValue("@admin", admin)
                .AddWithValue("@uID", userID)
                .AddWithValue("@pw", pword)
                .AddWithValue("@un", UName)
            End With

            cmd.ExecuteNonQuery()

            Dim newcon As New OleDb.OleDbConnection
            Dim dbProvider As String
            Dim dbSource As String
            Dim testsql As String

            dbProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=myPassword;Data Source="
            dbSource = readIni("DATABASE", directorypath & "settings.ini")
            dbSource = dbSource.Substring(0, dbSource.Length - 4) & "-TEST.mdb"

            newcon.ConnectionString = dbProvider & dbSource
            newcon.Open()

            testsql = "INSERT INTO [Users]([Admin],[User_ID],[Password],[UserName]) VALUES(@admin, @uID, @pw, @un)"
            cmd = New OleDb.OleDbCommand(testsql, newcon)
            cmd.Transaction = tr

            With cmd.Parameters
                .AddWithValue("@admin", admin)
                .AddWithValue("@uID", userID)
                .AddWithValue("@pw", pword)
                .AddWithValue("@un", UName)
            End With

            cmd.ExecuteNonQuery()
            tr.Commit()

            MsgBox("Users saved")

        Catch ex As Exception
            tr.Rollback()
            errorLog(ex)

        End Try

Answer 1

Transactions are obviously bound to a Connection: tr = con.BeginTransaction(), so you cant bind one Transaction to 2 connections.

Given the description though, you can link tables from one DB to another. The linked (in older versions it may be called "attached") table from the other DB will appear in the linked db as if it was actually there instead.

So, you can attach/link the live db Users table to the dev DB as Users1 or UsersA. The oldest version I have is Access 2003. In that, tables are attached via:
File -> Get External Data -> Link Tables.
A dialog opens where you select the other/live DB. Click Link and a list of Tables comes up. Select the desired table.

You still (sort of) have to use 2 commands in your code. For simplicity and to reduce scrolling, I skipped the Try/Catch RollBack element:

Dim SQL = "INSERT INTO {0} (Name, Color, Fish, LastUpdatedDate, Active) VALUES (?,?,?,?,?)"
Dim T As OleDbTransaction

Using dbcon As New OleDbConnection(ACEConnStr)
    dbcon.Open()
    T = dbcon.BeginTransaction

    Using cmd As New OleDbCommand(String.Format(SQL, "MergeB"), dbcon),
        cmd2 As New OleDbCommand(String.Format(SQL, "MergeB1"), dbcon)

        cmd.Transaction = T
        cmd2.Transaction = T

        cmd.Parameters.Add("?", OleDbType.VarChar).Value = "Ziggy"
        cmd.Parameters.Add("?", OleDbType.VarChar).Value = RD.GetColor()
        cmd.Parameters.Add("?", OleDbType.VarChar).Value = RD.GetFish()
        cmd.Parameters.Add("?", OleDbType.DBDate).Value = DateTime.Now.Date
        cmd.Parameters.Add("?", OleDbType.Boolean).Value = RNG.NextBoolean()
        cmd.ExecuteNonQuery()

        ' see note
        cmd2.Parameters.Add("?", OleDbType.VarChar).Value = cmd.Parameters(0).Value
        cmd2.Parameters.Add("?", OleDbType.VarChar).Value = cmd.Parameters(1).Value
        cmd2.Parameters.Add("?", OleDbType.VarChar).Value = cmd.Parameters(2).Value
        cmd2.Parameters.Add("?", OleDbType.DBDate).Value = cmd.Parameters(3).Value
        cmd2.Parameters.Add("?", OleDbType.Boolean).Value = cmd.Parameters(4).Value
        cmd2.ExecuteNonQuery()

        T.Commit()
    End Using
End Using

The code shown copies the values from one to the other just because my data is coming from a random data generator, but it does assure that the same data is used for both Inserts.

It turns out that this works as well, but I am not sure if it is a good idea:

...
cmd.Parameters.Add("?", OleDbType.Boolean).Value = RNG.NextBoolean()
cmd.ExecuteNonQuery()

' change the SQL, use the existing param collection:
cmd.CommandText = String.Format(SQL, "MergeB1")
cmd.ExecuteNonQuery()

This skips the second command object entirely. Other:

• Passwords should never, ever be stored as plain text. They should be hashed and salted.
• You should turn on Option Strict. ugUsers.Rows(x).Cells(y).Value returns Object and you are assigning them to various variables using implicit conversion.
• Parameters.Add() should be used rather than AddWithValue so that the exact datatype can be specified rather than leaving OleDB to guess. Especially with Option Strict off, you could easily encounter data type mismatches.