joe joe - 2 months ago 8 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


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


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


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


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


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


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


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


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


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


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


MsgBox("Import Completed!")

Catch ex As Exception


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)
sql = New OleDbCommand(sqlct, importsCon)
End If

da = New OleDbDataAdapter(sql)

Return dt

End Function


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


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

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