joe joe - 4 months ago 12
Vb.net Question

TSQL not committing the transaction, despite executing full code

I have an imports program that I have written to delete all the data from all datatables in a SQLServer database, and replace the data with new data from an Access database. The trouble is, is that despite it giving me the message box at the end "Import completed!", the SQLServer data seems to remain the same. I know there is a LOT of code here, apologies for that, but can anybody suggest why the data is not changing?

I thought maybe it was because my delete statements all had to be one transaction, then the inserts a separate one? Am I missing something?

Imports System.Data.OleDb

Public Class dbImports

Public Shared Function importDatabase(con As OleDbConnection, importsCon As OleDbConnection)

Dim tr As OleDbTransaction = Nothing
Dim sql As New OleDbCommand

sql.Connection = con
sql.CommandType = CommandType.Text

Try

tr = con.BeginTransaction()

sql.CommandText = "DELETE FROM tblModules"
sql.Transaction = tr

sql.CommandText = "DELETE FROM tblContracts"
sql.Transaction = tr

sql.CommandText = "DELETE FROM tblContractText"
sql.Transaction = tr

sql.CommandText = "DELETE FROM tblEmailSettings"
sql.Transaction = tr

sql.CommandText = "DELETE FROM tblIncidents"
sql.Transaction = tr

sql.CommandText = "DELETE FROM tblInvoiceLog"
sql.Transaction = tr

sql.CommandText = "DELETE FROM tblMailLog"
sql.Transaction = tr

sql.CommandText = "DELETE FROM tblModuleNames"
sql.Transaction = tr

sql.CommandText = "DELETE FROM tblRecurring"
sql.Transaction = tr

sql.CommandText = "DELETE FROM tblSystemSettings"
sql.Transaction = tr

sql.CommandText = "SELECT * FROM tblContracts ORDER BY ContractID"
sql.Transaction = tr

Dim sqlct As String
sqlct = sql.CommandText

For Each dr As DataRow In getDataTable(sqlct, importsCon, Nothing).Rows

Dim conID As Integer
Dim custacc As String
Dim datestart As Object
Dim daterenew As Object
Dim fee As Double
Dim agree As String
Dim details As String
Dim datesent As Object
Dim timesent As Object
Dim remname As String
Dim remmail As String

conID = dr("ContractID")
custacc = dbToString(dr("CustAcc"))
datestart = dbToDate(dr("DateStart"))
daterenew = dbToDate(dr("DateRenew"))
fee = dr("Fee")
agree = dbToString(dr("Agreement"))
details = dbToString(dr("OtherDetails"))
datesent = dbToDate(dr("DateSent"))
timesent = dbToTime(dr("TimeSent"))
remname = dbToString(dr("ReminderName"))
remmail = dbToString(dr("ReminderEmail"))

sql.CommandText = "INSERT INTO tblContracts(ContractID, CustAcc, DateStart, DateRenew, Fee, Agreement, OtherDetails, DateSent, TimeSent, ReminderName, ReminderEmail) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
sql.Transaction = tr
sql.Parameters.Add("@ContractID", OleDbType.Integer).Value = conID
sql.Parameters.Add("@CustAcc", OleDbType.VarChar).Value = custacc
sql.Parameters.Add("@DateStart", OleDbType.DBDate).Value = datestart
sql.Parameters.Add("@DateRenew", OleDbType.DBDate).Value = daterenew
sql.Parameters.Add("@Fee", OleDbType.Double).Value = fee
sql.Parameters.Add("@Agreement", OleDbType.VarChar).Value = agree
sql.Parameters.Add("@OtherDetails", OleDbType.VarChar).Value = details
sql.Parameters.Add("DateSent", OleDbType.DBDate).Value = datesent
sql.Parameters.Add("@TimeSent", OleDbType.DBTime).Value = timesent
sql.Parameters.Add("@ReminderName", OleDbType.VarChar).Value = remname
sql.Parameters.Add("@remmail", OleDbType.VarChar).Value = remmail

Next

sql.CommandText = "SELECT * FROM tblContractText ORDER BY pk"
sql.Transaction = tr
sqlct = sql.CommandText

For Each dr As DataRow In getDataTable(sqlct, importsCon, Nothing).Rows

Dim Para1 As String
Dim pk As Integer
Dim BillPara As String

Para1 = dbToString(dr("Para1"))
pk = dr("pk")
BillPara = dbToString(dr("BillPara"))

sql.CommandText = "INSERT INTO tblContractText(Para1, pk, BillPara) VALUES(?, ?, ?)"
sql.Transaction = tr
sql.Parameters.Add("@Para1", OleDbType.LongVarChar).Value = Para1
sql.Parameters.Add("@pk", OleDbType.Integer).Value = pk
sql.Parameters.Add("@BillPara", OleDbType.LongVarChar).Value = BillPara

Next

sql.CommandText = "SELECT * FROM tblEmailSettings ORDER BY ID"
sql.Transaction = tr
sqlct = sql.CommandText

For Each dr As DataRow In getDataTable(sqlct, importsCon, Nothing).Rows

Dim ID As String
Dim PW As String
Dim Subject As String
Dim Body As String
Dim SMTP As String
Dim conf As Boolean
Dim invSub As String
Dim invBody As String

ID = dbToString(dr("ID"))
PW = dbToString(dr("PW"))
Subject = dbToString(dr("Subject"))
Body = dbToString(dr("Body"))
SMTP = dbToString(dr("SMTPHost"))
conf = dbToBoolean(dr("Confirmation"))
invSub = dbToString(dr("invSubject"))
invBody = dbToString(dr("invBody"))

sql.CommandText = "INSERT INTO tblEmailSettings(ID, PW, Subject, Body, SMTPHost, Confirmation, invSubject, invBody) VALUES(?, ?, ?, ?, ?, ?, ?, ?)"
sql.Transaction = tr
sql.Parameters.Add("@ID", OleDbType.VarChar).Value = ID
sql.Parameters.Add("@PW", OleDbType.VarChar).Value = PW
sql.Parameters.Add("@Subject", OleDbType.VarChar).Value = Subject
sql.Parameters.Add("@Body", OleDbType.LongVarChar).Value = Body
sql.Parameters.Add("@SMTPHost", OleDbType.VarChar).Value = SMTP
sql.Parameters.Add("@Confimation", OleDbType.Boolean).Value = conf
sql.Parameters.Add("@invSubject", OleDbType.LongVarChar).Value = invSub
sql.Parameters.Add("@invBody", OleDbType.LongVarChar).Value = invBody

Next

sql.CommandText = "SELECT * FROM tblIncidents ORDER BY supportID"
sql.Transaction = tr
sqlct = sql.CommandText

For Each dr As DataRow In getDataTable(sqlct, importsCon, Nothing).Rows

Dim supportID As Integer
Dim clientName As String
Dim dateStart As Object
Dim dateEnd As Object
Dim incidentProblem As String
Dim timeStart As Object
Dim timeEnd As Object
Dim incidentSolved As Boolean
Dim incidentSolution As String

supportID = (dr("supportID"))
clientName = dbToString(dr("clientName"))
dateStart = dbToDate(dr("dateStart"))
dateEnd = dbToDate(dr("dateEnd"))
incidentProblem = dbToString(dr("incidentProblem"))
timeStart = dbToTime(dr("timeStart"))
timeEnd = dbToTime(dr("timeEnd"))
incidentSolved = dbToBoolean(dr("incidentSolved"))
incidentSolution = dbToString(dr("incidentSolution"))

sql.CommandText = "INSERT INTO tblIncidents(supportID, clientName, dateStart, dateEnd, incidentProblem, timeStart, timeEnd, incidentSolved, incidentSolution) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)"
sql.Transaction = tr
sql.Parameters.Add("@supportID", OleDbType.Integer).Value = supportID
sql.Parameters.Add("@clientName", OleDbType.VarChar).Value = clientName
sql.Parameters.Add("@dateStart", OleDbType.DBDate).Value = dateStart
sql.Parameters.Add("@dateEnd", OleDbType.DBDate).Value = dateEnd
sql.Parameters.Add("@incidentProblem", OleDbType.LongVarChar).Value = incidentProblem
sql.Parameters.Add("@timeStart", OleDbType.DBTime).Value = timeStart
sql.Parameters.Add("@timeEnd", OleDbType.DBTime).Value = timeEnd
sql.Parameters.Add("@incidentSolved", OleDbType.Boolean).Value = incidentSolved
sql.Parameters.Add("@incidentSolution", OleDbType.LongVarChar).Value = incidentSolution

Next

sql.CommandText = "SELECT * FROM tblInvoiceLog ORDER BY BatchNumber"
sql.Transaction = tr
sqlct = sql.CommandText

For Each dr As DataRow In getDataTable(sqlct, importsCon, Nothing).Rows

Dim BatchNumber As Integer
Dim BatchLine As Integer
Dim DatePosted As Object
Dim AccCode As String
Dim Description As String
Dim Period As String
Dim TotalFee As Double

BatchNumber = dr("BatchNumber")
BatchLine = dr("BatchLine")
DatePosted = dbToDate(dr("DatePosted"))
AccCode = dbToString(dr("AccCode"))
Description = dbToString(dr("Description"))
Period = dbToString(dr("Period"))
TotalFee = dr("TotalFee")

sql.CommandText = "INSERT INTO tblInvoiceLog(BatchNumber, BatchLine, DatePosted, AccCode, Description, Period, TotalFee) VALUES(?, ?, ?, ?, ?, ?, ?)"
sql.Transaction = tr
sql.Parameters.Add("@BatchNumber", OleDbType.Integer).Value = BatchNumber
sql.Parameters.Add("@BatchLine", OleDbType.Integer).Value = BatchLine
sql.Parameters.Add("@DatePosted", OleDbType.DBDate).Value = DatePosted
sql.Parameters.Add("@AccCode", OleDbType.VarChar).Value = AccCode
sql.Parameters.Add("@Description", OleDbType.LongVarChar).Value = Description
sql.Parameters.Add("@Period", OleDbType.VarChar).Value = Period
sql.Parameters.Add("@TotalFee", OleDbType.Double).Value = TotalFee

Next

sql.CommandText = "SELECT * FROM tblMailLog ORDER BY mailID"
sql.Transaction = tr
sqlct = sql.CommandText

For Each dr As DataRow In getDataTable(sqlct, importsCon, Nothing).Rows

Dim mailID As Integer
Dim Sender As String
Dim Receiver As String
Dim FileName As String
Dim DateSent As Object
Dim TimeSent As String
Dim Body As String
Dim AccCode As String

mailID = dr("mailID")
Sender = dbToString(dr("Sender"))
Receiver = dbToString(dr("Receiver"))
FileName = dbToString(dr("FileName"))
DateSent = dbToDate(dr("DateSent"))
TimeSent = dbToString(dr("TimeSent"))
Body = dbToString(dr("Body"))
AccCode = dbToString(dr("AccCode"))

sql.CommandText = "INSERT INTO tblMailLog(mailID, Sender, Receiver, FileName, DateSent, TimeSent, Body, AccCode) VALUES(?, ?, ?, ?, ?, ?, ?, ?)"
sql.Transaction = tr
sql.Parameters.Add("@mailID", OleDbType.Integer).Value = mailID
sql.Parameters.Add("Sender", OleDbType.VarChar).Value = Sender
sql.Parameters.Add("Receiver", OleDbType.VarChar).Value = Receiver
sql.Parameters.Add("@FileName", OleDbType.VarChar).Value = FileName
sql.Parameters.Add("@DateSent", OleDbType.DBDate).Value = DateSent
sql.Parameters.Add("@TimeSent", OleDbType.VarChar).Value = TimeSent
sql.Parameters.Add("@Body", OleDbType.LongVarChar).Value = Body
sql.Parameters.Add("@AccCode", OleDbType.VarChar).Value = AccCode

Next

sql.CommandText = "SELECT * FROM tblModuleNames"
sql.Transaction = tr
sqlct = sql.CommandText

For Each dr As DataRow In getDataTable(sqlct, importsCon, Nothing).Rows

Dim fModule As String

fModule = dbToString(dr("Module"))

sql.CommandText = "INSERT INTO tblModuleNames(Module) VALUES(?)"
sql.Transaction = tr
sql.Parameters.Add("@Module", OleDbType.VarChar).Value = fModule

Next

sql.CommandText = "SELECT * FROM tblModules ORDER BY ModuleID"
sql.Transaction = tr
sqlct = sql.CommandText

For Each dr As DataRow In getDataTable(sqlct, importsCon, Nothing).Rows

Dim ModuleID As Integer
Dim ModuleName As String
Dim NumberUsers As String
Dim License As String
Dim Username As String
Dim ContractID As Integer

ModuleID = dr("ModuleID")
ModuleName = dbToString(dr("ModuleName"))
NumberUsers = dbToString(dr("NumberUsers"))
License = dbToString(dr("License"))
Username = dbToString(dr("Username"))
ContractID = dr("ContractID")

sql.CommandText = "INSERT INTO tblModules(ModuleID, ModuleName, NumberUsers, License, Username, ContractID) VALUES(?, ?, ?, ?, ?, ?)"
sql.Transaction = tr
sql.Parameters.Add("@ModuleID", OleDbType.Integer).Value = ModuleID
sql.Parameters.Add("@ModuleName", OleDbType.VarChar).Value = ModuleName
sql.Parameters.Add("@NumberUsers", OleDbType.VarChar).Value = NumberUsers
sql.Parameters.Add("@License", OleDbType.VarChar).Value = License
sql.Parameters.Add("@Username", OleDbType.VarChar).Value = Username
sql.Parameters.Add("ContractID", OleDbType.Integer).Value = ContractID

Next

sql.CommandText = "SELECT * FROM tblRecurring ORDER BY billID"
sql.Transaction = tr
sqlct = sql.CommandText

For Each dr As DataRow In getDataTable(sqlct, importsCon, Nothing).Rows

Dim billID As Integer
Dim DateStart As Object
Dim DateNext As Object
Dim Fee As Double
Dim Description As String
Dim AccountCode As String
Dim Notes As String

billID = dr("billID")
DateStart = dbToDate(dr("DateStart"))
DateNext = dbToDate(dr("DateNext"))
Fee = dr("Fee")
Description = dbToString(dr("Description"))
AccountCode = dbToString(dr("AccountCode"))
Notes = dbToString(dr("Notes"))

sql.CommandText = "INSERT INTO tblRecurring(billID, DateStart, DateNext, Fee, Description, AccountCode, Notes) VALUES(?, ?, ?, ?, ?, ?, ?)"
sql.Transaction = tr
sql.Parameters.Add("@billID", OleDbType.Integer).Value = billID
sql.Parameters.Add("@DateStart", OleDbType.DBDate).Value = DateStart
sql.Parameters.Add("@DateNext", OleDbType.DBDate).Value = DateNext
sql.Parameters.Add("@Fee", OleDbType.Double).Value = Fee
sql.Parameters.Add("@Description", OleDbType.LongVarChar).Value = Description
sql.Parameters.Add("@AccountCode", OleDbType.VarChar).Value = AccountCode
sql.Parameters.Add("@Notes", OleDbType.LongVarChar).Value = Notes

Next

sql.CommandText = "SELECT * FROM tblSystemSettings ORDER BY ID"
sql.Transaction = tr
sqlct = sql.CommandText

For Each dr As DataRow In getDataTable(sqlct, importsCon, Nothing).Rows

Dim ID As Integer
Dim listContractsX As Integer
Dim listContractsY As Integer
Dim invoiceLogX As Integer
Dim invoiceLogY As Integer
Dim mailLogX As Integer
Dim mailLogY As Integer
Dim recurringX As Integer
Dim recurringY As Integer
Dim reportsX As Integer
Dim reportsY As Integer

ID = dr("ID")
listContractsX = dr("listContractsX")
listContractsY = dr("listContractsY")
invoiceLogX = dr("invoiceLogX")
invoiceLogY = dr("invoiceLogY")
mailLogX = dr("mailLogX")
mailLogY = dr("mailLogY")
recurringX = dr("recurringX")
recurringY = dr("recurringY")
reportsX = dr("reportsX")
reportsY = dr("reportsY")

sql.CommandText = "INSERT INTO tblSystemSettings(ID, listContractsX, listContractsY, invoiceLogX, invoiceLogY, mailLogX, mailLogY, recurringX, recurringY, reportsX, reportsY) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
sql.Transaction = tr
sql.Parameters.Add("@ID", OleDbType.Integer).Value = ID
sql.Parameters.Add("@listContractsX", OleDbType.Integer).Value = listContractsX
sql.Parameters.Add("@listContractsY", OleDbType.Integer).Value = listContractsY
sql.Parameters.Add("@invoiceLogX", OleDbType.Integer).Value = invoiceLogX
sql.Parameters.Add("@invoiceLogY", OleDbType.Integer).Value = invoiceLogY
sql.Parameters.Add("@mailLogX", OleDbType.Integer).Value = mailLogX
sql.Parameters.Add("@mailLogY", OleDbType.Integer).Value = mailLogY
sql.Parameters.Add("@recurringX", OleDbType.Integer).Value = recurringX
sql.Parameters.Add("@recurringY", OleDbType.Integer).Value = recurringY
sql.Parameters.Add("@reportsX", OleDbType.Integer).Value = reportsX
sql.Parameters.Add("@reportsY", OleDbType.Integer).Value = reportsY

Next

tr.Commit()
MsgBox("Import Completed!")

Catch ex As Exception

tr.Rollback()

Throw
End Try

End Function

Public Shared Function getDataTable(ByVal sqlct As String, importsCon As OleDbConnection, tr As OleDbTransaction) As DataTable

Dim sql As OleDbCommand
Dim da As OleDbDataAdapter
Dim dt As New DataTable

If tr IsNot Nothing Then
sql = New OleDbCommand(sqlct, importsCon, tr)
Else
sql = New OleDbCommand(sqlct, importsCon)
End If

da = New OleDbDataAdapter(sql)
da.Fill(dt)

Return dt

End Function

Answer

After each CommandText, you must explicitly call the ExecuteNonQuery function so that that command executes in the Database.

Example:

sql.Parameters.Clear
sql.CommandText = "DELETE FROM tblModules"
sql.Transaction = tr
sql.ExecuteNonQuery()

sql.Parameters.Clear
sql.CommandText = "DELETE FROM tblContracts"
sql.Transaction = tr
sql.ExecuteNonQuery()

...