L. Moronvalle L. Moronvalle - 3 months ago 17
Vb.net Question

VB.Net OLEDB ExecuteNonQuery INSERT INTO - No effect on Database

Here is my issue:
I have the code below.
If I debug my code and copy the queries directly into MS Access the Queries work perfectly fine but if I execute it from my application no change are made to the table.

Note that the connection to the DB is OK as I am doing several Select before that are working perfectly well.

I am probably making something stupid but it is so big I cannot see it after many hours working on it.

I know I should use parameters in my query and I did originally but I change it within my many try to make it work and I suppose this should not change much anyway.

Dim cmd As New OleDbCommand
Dim sQuery As String = String.Empty

Try
cmd.CommandText = "DELETE * FROM tbl_Invoices"
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox("PrepareInvoicing Delete" & vbCrLf & ErrorToString())
Exit Sub
End Try

'Insert into the Invoice table the fleet info with Usage for the invoicing period selected
Try
sQuery = String.Empty
sQuery = sQuery & "INSERT INTO tbl_Invoices "
sQuery = sQuery & "SELECT tbl_Fleet.CustomerName AS CustomerName, "
sQuery = sQuery & "tbl_Fleet.CountryCode AS CountryCode, "
sQuery = sQuery & "#" & DateSerial(InvoicingYear, InvoicingMonth + 1, 0) & "# AS InvoiceDate, "
sQuery = sQuery & "tbl_Fleet.Area AS Area, "
sQuery = sQuery & "tbl_Fleet.Group AS [Group], "
sQuery = sQuery & "tbl_Fleet.Site_nm AS SiteName, "
sQuery = sQuery & "tbl_Sites.RCS AS CustomerPO, "
sQuery = sQuery & "tbl_Fleet.Site_Addr_1 AS SiteAddress1, "
sQuery = sQuery & "tbl_Fleet.Site_Addr_2 AS SiteAddress2, "
sQuery = sQuery & "tbl_Fleet.Site_Addr_ZIP AS ZIP, "
sQuery = sQuery & "tbl_Fleet.Site_Addr_cty AS City, "
sQuery = sQuery & "tbl_Fleet.model_nm AS ProductDescription, "
sQuery = sQuery & "tbl_Fleet.product_no AS ProductNumber, "
sQuery = sQuery & "tbl_Fleet.serial_no AS SerialNumber, "
sQuery = sQuery & "tbl_Fleet.hostname AS hostname, "
sQuery = sQuery & "tbl_Fleet.asset_no AS AssetNumber, "
sQuery = sQuery & "tbl_Fleet.Grid AS Grid, "
sQuery = sQuery & "tbl_Fleet.ChangeOrderID AS ChangeOrderID, "
sQuery = sQuery & "tbl_Fleet.install_date AS InstalledDate, "
sQuery = sQuery & "INT(((tbl_Fleet.install_date - temptbl_CO.ChangeOrderStartDate)/365.25)+1) AS YearInContract, "
sQuery = sQuery & "(tbl_RM.BlackClicks + tbl_RM.AccentClicks) AS BlackPages, "
sQuery = sQuery & "(tbl_RM.ColorClicks + tbl_RM.ProfessionalColorClicks) AS ColorPages "
sQuery = sQuery & "FROM tbl_Fleet, "
sQuery = sQuery & "tbl_Sites, "
sQuery = sQuery & "tbl_RM, "
sQuery = sQuery & "(SELECT DISTINCT tbl_Bases.ProductNumber, tbl_Bases.ChangeOrderID, tbl_Bases.ChangeOrderStartDate FROM tbl_Bases WHERE tbl_Bases.CustomerName = '" & mdlGlobalStuff.SelectedCustomerName & "' AND tbl_Bases.CountryCode = '" & mdlGlobalStuff.SelectedCountryCode & "') AS temptbl_CO "
sQuery = sQuery & "WHERE tbl_Fleet.CustomerName = '" & mdlGlobalStuff.SelectedCustomerName & "' "
sQuery = sQuery & "AND tbl_Fleet.CountryCode = '" & mdlGlobalStuff.SelectedCountryCode & "' "
sQuery = sQuery & "AND tbl_Fleet.LoadDate = #" & LoadFleetDate & "# "
sQuery = sQuery & "AND MONTH(tbl_RM.RMDate) = " & Month(LoadUsageDate) & " "
sQuery = sQuery & "AND YEAR(tbl_RM.RMDate) = " & Year(LoadUsageDate) & " "
sQuery = sQuery & "AND tbl_Fleet.CustomerName = tbl_Sites.CustomerName "
sQuery = sQuery & "AND tbl_Fleet.CountryCode = tbl_Sites.CountryCode "
sQuery = sQuery & "AND tbl_Fleet.Site_nm = tbl_Sites.Site_nm "
sQuery = sQuery & "AND tbl_Fleet.CustomerName = tbl_RM.CustomerName "
sQuery = sQuery & "AND tbl_Fleet.CountryCode = tbl_RM.CountryCode "
sQuery = sQuery & "AND tbl_Fleet.serial_no = tbl_RM.SerialNumber "
sQuery = sQuery & "AND tbl_Fleet.product_no = temptbl_CO.ProductNumber "
sQuery = sQuery & "AND tbl_Fleet.ChangeOrderID = temptbl_CO.ChangeOrderID "
cmd.CommandText = sQuery
cmd.ExecuteNonQuery()
MsgBox("Step 1")
Catch ex As Exception
MsgBox("PrepareInvoicing: Invoicing step 1" & vbCrLf & ErrorToString())
Exit Sub
End Try

'Update the "non aging" Bases (if Base.contractYear = 0) in the Invoice table
Try
sQuery = String.Empty
sQuery = sQuery & "UPDATE tbl_Invoices "
sQuery = sQuery & "INNER JOIN tbl_Bases ON tbl_Bases.ProductNumber = tbl_Invoices.ProductNumber "
sQuery = sQuery & "AND tbl_Bases.ChangeOrderID = tbl_Invoices.ChangeOrderID "
sQuery = sQuery & "SET tbl_Invoices.Base = tbl_Bases.BasePrice "
sQuery = sQuery & "WHERE tbl_Bases.CustomerName = '" & mdlGlobalStuff.SelectedCustomerName & "' "
sQuery = sQuery & "AND tbl_Bases.CountryCode = '" & mdlGlobalStuff.SelectedCountryCode & "' "
sQuery = sQuery & "AND tbl_Bases.ContractYear = 0 "
cmd.CommandText = sQuery
cmd.ExecuteNonQuery()
MsgBox("Step 2")
Catch ex As Exception
MsgBox("PrepareInvoicing: Invoicing step 2" & vbCrLf & ErrorToString())
Exit Sub
End Try

'Update the "aging" Bases (if Base.contractYear <> 0) in the Invoice table
Try
sQuery = String.Empty
sQuery = sQuery & "UPDATE tbl_Invoices "
sQuery = sQuery & "INNER JOIN tbl_Bases ON tbl_Bases.ProductNumber = tbl_Invoices.ProductNumber "
sQuery = sQuery & "AND tbl_Bases.ChangeOrderID = tbl_Invoices.ChangeOrderID "
sQuery = sQuery & "AND tbl_Bases.ContractYear = tbl_Invoices.YearInContract "
sQuery = sQuery & "SET tbl_Invoices.Base = tbl_Bases.BasePrice "
sQuery = sQuery & "WHERE tbl_Bases.CustomerName = '" & mdlGlobalStuff.SelectedCustomerName & "' "
sQuery = sQuery & "AND tbl_Bases.CountryCode = '" & mdlGlobalStuff.SelectedCountryCode & "' "
cmd.CommandText = sQuery
cmd.ExecuteNonQuery()
MsgBox("Done")
Catch ex As Exception
MsgBox("PrepareInvoicing: Invoicing step 3" & vbCrLf & ErrorToString())
Exit Sub
End Try

Answer

I'm going to go ahead and post this as an answer because, based on my experience, it's at least 95% likely to be applicable.

When you add a local data file, e.g. MDB or ACCDB file, to your project, it gets copied to the project folder along with all the other source files. That file is part of your project, not part of your application. Any schema changes or default data gets added to that file but it does NOT get touched at run time while testing.

When you build your project, that source file gets copied to the output folder along with your EXE. It's that copy that your application works with at run time. Any data you save gets saved to that working copy, NOT the source file.

By default, a new copy of the source file is made and the working copy overwritten every time you build. That means that, if you run your app in the debugger, save some data, stop the app, make a code change and then run the app again, the data you saved will disappear.

So, the mistake that you're probably making, like so many, is that you're either looking in the source file for the data you saved at run time or you're looking in the working copy after it has been overwritten by the next build. The solution to this "issue" is simple. Select your data file in the Solution Explorer, open the Properties window and set the Copy to Output Directory property to Copy if Newer. That means that when you build, the working copy will only ever be overwritten if the source file is newer, which will be the case if you've modified the schema or edited the default data. If you ever need to refresh your working database you simply delete it manually from the output folder or temporarily set the Copy to Output Directory back to Copy Always.

You may wonder why they use multiple files in the first place but it's perfectly logical and actually a very good thing. If you only had one file and you used it for testing then what happens when it comes time to deploy? You'd have to waste time cleaning up that one file and then you might miss something anyway. This way, you just keep on using your Debug copy for testing and you'll always get a nice clean data file when you do a Release build.

Comments