G
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
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