I'm buiding a VB.Net client-server application using VS2017 with an SQL back end. The application includes a form which will allow the user to insert a new record or to display (and optionally update or delete) an existing record. I need to use a transaction to prevent concurrency issues. Because the table has dozens of columns, I also want to have the data adapter autogenerate the appropriate insert, update and delete commands, rather than manually coding the SQL. However, I can't figure out how to make transactions work with autogenerated commands - no matter how I re-arrange my code I get some sort of run-time error. For example, the following code produces the error "Object reference not set to an instance of an object." when it executes the last line shown:

Public Function InitializedSQLDataAdapter(SelectStatement As String, SQLConn As SqlConnection,
Optional UseCommandBuilder As Boolean = False) As SqlDataAdapter
'returns an SQLDataAdapter object, using SelectStatement and SQLConn, and including INSERT,
'UPDATE and DELETE commands if UseCommandBuilder is True
Dim DAdapter As SqlDataAdapter = New SqlDataAdapter(SelectStatement, SQLConn)
Try
If UseCommandBuilder Then
Dim CmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(DAdapter)
CmdBuilder.GetInsertCommand()
CmdBuilder.GetUpdateCommand()
CmdBuilder.GetDeleteCommand()
End If
Return DAdapter
Catch ex As SystemException
DisplayException(ex, "Utility.InitializedSQLDataAdapter")
End Try
End Function
...
Conn.Open()
SQL = "SELECT * FROM Consent WHERE ConsentID = " & ConsentID.ToString
DAdapter = InitializedSQLDataAdapter(SQL, Conn, True)
trans = Conn.BeginTransaction(IsolationLevel.Serializable)
DAdapter.UpdateCommand.Transaction = trans

If I use the following code instead, I get the same error in the same place:

Conn.Open()
trans = Conn.BeginTransaction(IsolationLevel.Serializable)
SQL = "SELECT * FROM Consent WHERE ConsentID = " & ConsentID.ToString
DAdapter = New SqlDataAdapter(SQL, Conn)
DAdapter.UpdateCommand.Transaction = trans

If I use the following code instead, I get a different error ("ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.") on the last line shown:

Conn.Open()
trans = Conn.BeginTransaction(IsolationLevel.Serializable)
SQL = "SELECT * FROM Consent WHERE ConsentID = " & ConsentID.ToString
DAdapter = New SqlDataAdapter(SQL, Conn)
DAdapter.Fill(DTable)

So, it seems that the command itself has to have a transaction, but when I try to assign one to it I always get one of these errors. How can I make this work?