Transaction won't work

[Guest]

Transaction won't work. I'm trying to insert a new row in four related tables. The first uses an autoincrementing integer as it's primary key. I recover it with the standary @@IDENTITY handler. In turn, it is part of the primary key in each of the other three tables

I tried hard. I scoured ADO.NET Core Reference. I spent time with the documentation. However, neither had scenarios exactly like mine. I tried a number of variations before ending up with this

Without transaction it works fine, everytime. With my attempts to use a transaction, it produces various errors. In this variation, the error is as follows

Type = system.invalidOperationExceptio
Message = Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized

After I click OK, I get a Microsoft Development Environment message: An unhandled exception of type 'System.NullReferenceException' occurred in TFSNET2.ex

Additonal information: Object reference not set to an instance of an object

The emphasized line is txn.Rollback(

I suspect this is an easy one for some of you. However, I already have little hair to pull out

By the way, all the properties I call for values are properly set, as determined by output and message boxes

Please note that in two previous problems the solutions were traced to an obscure syntactical variation, dsqwks1.keywordsetassignments instead of DsQWKS1, "KeywordSetAssignments"). I've corrected that everywhere in my application

polynomial5

Private Sub btnSaveAndClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveAndClose.Clic
cnQWKS.Open(
cnQWKS.BeginTransaction(
Dim txn As OleDb.OleDbTransactio

Tr
daQWKS.Fill(DsQWKS1, "KeywordSets"
Dim tblKS As New dsQWKS.KeywordSetsDataTabl
Dim rowKS As dsQWKS.KeywordSetsRo
rowKS = tblKS.NewKeywordSetsRow(
rowKS.Active = Tru
rowKS.KeywordSet = txtQuickWord.Text 'sQuic
'rowKS.ID = -
tblKS.AddKeywordSetsRow(rowKS
daQWKS.InsertCommand = New OleDb.OleDbCommand("INSERT INTO KeywordSets(Active, KeywordSet) values (?,?)", cnQWKS
daQWKS.InsertCommand.Parameters.Add("@Active", OleDb.OleDbType.Boolean, 2, "Active"
daQWKS.InsertCommand.Parameters.Add("KeywordSet", OleDb.OleDbType.VarWChar, 16, "KeywordSet"
AddHandler daQWKS.RowUpdated, AddressOf OnRowUpDate
daQWK.Fill(DsQWKS1, "Keywords"
Dim tblK As New dsQWKS.KeywordsDataTabl
Dim rowK As dsQWKS.KeywordsRo
rowK = tblK.NewKeywordsRo
rowK.Active = Tru
rowK.Keyword = txtQuickWord.Text 'sQuick 'rowKS.ID = -
rowK.KeywordSetID = KSI
tblK.AddKeywordsRow(rowK
daQWK.InsertCommand = New OleDb.OleDbCommand("INSERT INTO Keywords(Active, Keyword,KeywordSetID) values (?,?,?)", cnQWKS
daQWK.InsertCommand.Parameters.Add("@Active", OleDb.OleDbType.Boolean, 2, "Active"
daQWK.InsertCommand.Parameters.Add("Keyword", OleDb.OleDbType.VarWChar, 16, "Keyword"
daQWK.InsertCommand.Parameters.Add("KeywordSetID", OleDb.OleDbType.Integer, 4, "KeywordSetID"

daQWSP.Fill(DsQWKS1, "SearchPhrase"
Dim tblSP As New dsQWKS.SearchPhraseDataTabl
Dim rowSP As dsQWKS.SearchPhraseRo
rowSP = tblSP.NewSearchPhraseRo
rowSP.Active = Tru
rowSP.SearchPhrase = txtQuickWord.Text 'sQuick 'rowKS.ID = -
rowSP.KeywordSetID = KSI
tblSP.AddSearchPhraseRow(rowSP
daQWSP.InsertCommand = New OleDb.OleDbCommand("INSERT INTO SearchPhrase(Active, SearchPhrase,KeywordSetID) values (?,?,?)", cnQWKS
daQWSP.InsertCommand.Parameters.Add("@Active", OleDb.OleDbType.Boolean, 2, "Active"
daQWSP.InsertCommand.Parameters.Add("SearchPhrase", OleDb.OleDbType.VarWChar, 16, "SearchPhrase"
daQWSP.InsertCommand.Parameters.Add("KeywordSetID", OleDb.OleDbType.Integer, 4, "KeywordSetID")

daKSTA.Fill(DsQWKS1, "KeywordSetAssignments")
Dim tblKSTA As New dsQWKS.KeywordSetAssignmentsDataTable
Dim rowKSTA As dsQWKS.KeywordSetAssignmentsRow
rowKSTA = tblKSTA.NewKeywordSetAssignmentsRow
rowKSTA.BeginText = 0
Dim frm As New frmTopicFromStart
rowKSTA.TopicID = TID
rowKSTA.KeywordSetID = KSID
tblKSTA.AddKeywordSetAssignmentsRow(rowKSTA)
daKSTA.InsertCommand = New OleDb.OleDbCommand("INSERT INTO KeywordSetAssignments(BeginText, TopicID,KeywordSetID) values (?,?,?)", cnQWKS)
daKSTA.InsertCommand.Parameters.Add("@BeginText", OleDb.OleDbType.Integer, 4, "BeginText")
daKSTA.InsertCommand.Parameters.Add("@TopicID", OleDb.OleDbType.Integer, 4, "TopicID")
daKSTA.InsertCommand.Parameters.Add("@KeywordSetID", OleDb.OleDbType.Integer, 4, "KeywordSetID")



Catch ex As Exception
MessageBox.Show("Type = " & ex.GetType.ToString & vbCr & "Message = " & ex.Message)
txn.Rollback()
End Try

txn.Commit()
cnQWKS.Close()
Me.Close()

End Sub

 

[David Browne]

Transaction won't work. I'm trying to insert a new row in four related

tables. The first uses an autoincrementing integer as it's primary key. I
recover it with the standary @@IDENTITY handler. In turn, it is part of the
primary key in each of the other three tables.

I tried hard. I scoured ADO.NET Core Reference. I spent time with the

documentation. However, neither had scenarios exactly like mine. I tried a
number of variations before ending up with this.

Without transaction it works fine, everytime. With my attempts to use a

transaction, it produces various errors. In this variation, the error is as
follows:

Type = system.invalidOperationException
Message = Execute requires the command to have a transaction object when

the connection assigned to the command is in a pending local transaction.
The Transaction property of the command has not been initialized.

First begin the transaction, assigning the resulting transaction object to a
local variable. Then assign the transaction to each Command.Transaction
(aka enlist the Commands). Then commit, or rollback on exception. Finally
close the connection.

See edits inline.

David

polynomial5d

Private Sub btnSaveAndClose_Click(ByVal sender As System.Object, ByVal

e As System.EventArgs) Handles btnSaveAndClose.Click

cnQWKS.Open()

Dim txn As OleDb.OleDbTransaction = cnQWKS.BeginTransaction()

Try
daQWKS.Fill(DsQWKS1, "KeywordSets")
Dim tblKS As New dsQWKS.KeywordSetsDataTable
Dim rowKS As dsQWKS.KeywordSetsRow
rowKS = tblKS.NewKeywordSetsRow()
rowKS.Active = True
rowKS.KeywordSet = txtQuickWord.Text 'sQuick
'rowKS.ID = -1
tblKS.AddKeywordSetsRow(rowKS)
daQWKS.InsertCommand = New OleDb.OleDbCommand("INSERT INTO

KeywordSets(Active, KeywordSet) values (?,?)", cnQWKS)
daQWKS.InsertCommand.Transaction = txn

daQWKS.InsertCommand.Parameters.Add("@Active",

OleDb.OleDbType.Boolean, 2, "Active")

daQWKS.InsertCommand.Parameters.Add("KeywordSet",

OleDb.OleDbType.VarWChar, 16, "KeywordSet")

AddHandler daQWKS.RowUpdated, AddressOf OnRowUpDated
daQWK.Fill(DsQWKS1, "Keywords")
Dim tblK As New dsQWKS.KeywordsDataTable
Dim rowK As dsQWKS.KeywordsRow
rowK = tblK.NewKeywordsRow
rowK.Active = True
rowK.Keyword = txtQuickWord.Text 'sQuick 'rowKS.ID = -1
rowK.KeywordSetID = KSID
tblK.AddKeywordsRow(rowK)
daQWK.InsertCommand = New OleDb.OleDbCommand("INSERT INTO

Keywords(Active, Keyword,KeywordSetID) values (?,?,?)", cnQWKS)
daQWK.InsertCommand.Transaction = txn

daQWK.InsertCommand.Parameters.Add("@Active",

OleDb.OleDbType.Boolean, 2, "Active")

daQWK.InsertCommand.Parameters.Add("Keyword",

OleDb.OleDbType.VarWChar, 16, "Keyword")

daQWK.InsertCommand.Parameters.Add("KeywordSetID",

OleDb.OleDbType.Integer, 4, "KeywordSetID")

daQWSP.Fill(DsQWKS1, "SearchPhrase")
Dim tblSP As New dsQWKS.SearchPhraseDataTable
Dim rowSP As dsQWKS.SearchPhraseRow
rowSP = tblSP.NewSearchPhraseRow
rowSP.Active = True
rowSP.SearchPhrase = txtQuickWord.Text 'sQuick 'rowKS.ID = -1
rowSP.KeywordSetID = KSID
tblSP.AddSearchPhraseRow(rowSP)
daQWSP.InsertCommand = New OleDb.OleDbCommand("INSERT INTO

SearchPhrase(Active, SearchPhrase,KeywordSetID) values (?,?,?)", cnQWKS)
daQWKP.InsertCommand.Transaction = txn

daQWSP.InsertCommand.Parameters.Add("@Active",

OleDb.OleDbType.Boolean, 2, "Active")

daQWSP.InsertCommand.Parameters.Add("SearchPhrase",

OleDb.OleDbType.VarWChar, 16, "SearchPhrase")

daQWSP.InsertCommand.Parameters.Add("KeywordSetID",

OleDb.OleDbType.Integer, 4, "KeywordSetID")

daKSTA.Fill(DsQWKS1, "KeywordSetAssignments")
Dim tblKSTA As New dsQWKS.KeywordSetAssignmentsDataTable
Dim rowKSTA As dsQWKS.KeywordSetAssignmentsRow
rowKSTA = tblKSTA.NewKeywordSetAssignmentsRow
rowKSTA.BeginText = 0
Dim frm As New frmTopicFromStart
rowKSTA.TopicID = TID
rowKSTA.KeywordSetID = KSID
tblKSTA.AddKeywordSetAssignmentsRow(rowKSTA)
daKSTA.InsertCommand = New OleDb.OleDbCommand("INSERT INTO

KeywordSetAssignments(BeginText, TopicID,KeywordSetID) values (?,?,?)",
cnQWKS)
daKSTA.InsertCommand.Transaction = txn

daKSTA.InsertCommand.Parameters.Add("@BeginText",

OleDb.OleDbType.Integer, 4, "BeginText")

daKSTA.InsertCommand.Parameters.Add("@TopicID",

OleDb.OleDbType.Integer, 4, "TopicID")

daKSTA.InsertCommand.Parameters.Add("@KeywordSetID",

OleDb.OleDbType.Integer, 4, "KeywordSetID")

 

[Guest]

David, thank you, but it doesn't quite work. I put in all the statements you specified, but now I get another error

Type = System.InvalidOperationExceptio
Message = Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction. The transaction property of the comand has not been initialized

So I put in all sorts of msgbox statements. Here's the first few

Private Sub btnSaveAndClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveAndClose.Clic
cnQWKS.Open(
Dim txn As OleDb.OleDbTransaction = cnQWKS.BeginTransaction(
MsgBox("dim txn"
Tr
daQWKS.Fill(DsQWKS1, "KeywordSets"
MsgBox("After daqwks fill"
Dim tblKS As New dsQWKS.KeywordSetsDataTabl

As it turns out, the first msgbox shows, the second doesn't. Now I'm more confused than ever

polynomial5

 

[Guest]

I decided to set the update and select commands transaction properties = txn for each of the four dataadapters. Here's a sample of the code

Private Sub btnSaveAndClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveAndClose.Clic
cnQWKS.Open(
Dim txn As OleDb.OleDbTransaction = cnQWKS.BeginTransaction(
Tr
daQWKS.SelectCommand.Transaction = tx
daQWKS.Fill(DsQWKS1, "KeywordSets"
Dim tblKS As New dsQWKS.KeywordSetsDataTabl
Dim rowKS As dsQWKS.KeywordSetsRo
rowKS = tblKS.NewKeywordSetsRow(
rowKS.Active = Tru
rowKS.KeywordSet = txtQuickWord.Text 'sQuic
'rowKS.ID = -
tblKS.AddKeywordSetsRow(rowKS
daQWKS.InsertCommand = New OleDb.OleDbCommand("INSERT INTO KeywordSets(Active, KeywordSet) values (?,?)", cnQWKS
daQWKS.InsertCommand.Transaction = tx
daQWKS.InsertCommand.Parameters.Add("@Active", OleDb.OleDbType.Boolean, 2, "Active"
daQWKS.InsertCommand.Parameters.Add("KeywordSet", OleDb.OleDbType.VarWChar, 16, "KeywordSet"
AddHandler daQWKS.RowUpdated, AddressOf OnRowUpDate
Dim intModified As Intege
daQWKS.UpdateCommand.Transaction = tx
intModified = daQWKS.Update(tblKS
Dim sOutput As Strin
sOutput = "Modified " & intModified & " KeywordSet(s)
MessageBox.Show(sOutput, "Update succeeded!", MessageBoxButtons.OK, MessageBoxIcon.Information

In the end that didn't work either. I still get the same exception message

polynomial5d