Imports System.Data.OleDb Public Class MainBillingForm ' Define the connection string to the MS Access Database Private connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\BillingDB.accdb;" Private grandTotal As Decimal = 0.0 Private Sub MainBillingForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load LoadProducts() ClearForm() End Sub ''' ''' Fetches product list from database and binds it to the ComboBox. ''' Private Sub LoadProducts() Using conn As New OleDbConnection(connString) Dim query As String = "SELECT ProductID, ProductName FROM Products WHERE StockQuantity > 0" Using cmd As New OleDbCommand(query, conn) Dim adapter As New OleDbDataAdapter(cmd) Dim dt As New DataTable() Try conn.Open() adapter.Fill(dt) cmbProducts.DataSource = dt cmbProducts.DisplayMember = "ProductName" cmbProducts.ValueMember = "ProductID" cmbProducts.SelectedIndex = -1 Catch ex As Exception MessageBox.Show("Error loading products: " & ex.Message, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Using End Using End Sub ''' ''' Automatically updates the price field when a product is selected. ''' Private Sub cmbProducts_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbProducts.SelectedIndexChanged If cmbProducts.SelectedIndex <> -1 AndAlso TypeOf cmbProducts.SelectedValue Is Integer Then Dim prodID As Integer = Convert.ToInt32(cmbProducts.SelectedValue) Using conn As New OleDbConnection(connString) Dim query As String = "SELECT Price FROM Products WHERE ProductID = ?" Using cmd As New OleDbCommand(query, conn) cmd.Parameters.AddWithValue("?", prodID) Try conn.Open() Dim price As Object = cmd.ExecuteScalar() If price IsNot DBNull.Value Then txtPrice.Text = Convert.ToDecimal(price).ToString("F2") End If Catch ex As Exception MessageBox.Show("Error fetching price: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Using End Using End If End Sub ''' ''' Validates inputs and pushes line items into the DataGridView. ''' Private Sub btnAddToGrid_Click(sender As Object, e As EventArgs) Handles btnAddToGrid.Click If cmbProducts.SelectedIndex = -1 Then MessageBox.Show("Please select a product.", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning) Return End If Dim qty As Integer If Not Integer.TryParse(txtQuantity.Text, qty) OrElse qty <= 0 Then MessageBox.Show("Please enter a valid quantity greater than zero.", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning) Return End If Dim prodID As Integer = Convert.ToInt32(cmbProducts.SelectedValue) Dim prodName As String = cmbProducts.Text Dim price As Decimal = Convert.ToDecimal(txtPrice.Text) Dim subTotal As Decimal = price * qty ' Append item row to DataGridView dgvItems.Rows.Add(prodID, prodName, price.ToString("F2"), qty, subTotal.ToString("F2")) ' Update calculations CalculateGrandTotal() ' Reset item fields for next input cmbProducts.SelectedIndex = -1 txtQuantity.Clear() txtPrice.Clear() End Sub ''' ''' Summarizes line items inside the DataGridView. ''' Private Sub CalculateGrandTotal() grandTotal = 0 For Each row As DataGridViewRow In dgvItems.Rows If Not row.IsNewRow Then grandTotal += Convert.ToDecimal(row.Cells(4).Value) End If Next lblGrandTotal.Text = "Grand Total: $" & grandTotal.ToString("F2") End Sub ''' ''' Executes the parent-child relational database insertion within a transaction scope. ''' Private Sub btnSaveInvoice_Click(sender As Object, e As EventArgs) Handles btnSaveInvoice.Click If String.IsNullOrWhiteSpace(txtCustomerName.Text) Then MessageBox.Show("Please enter a customer name.", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning) Return End If If dgvItems.Rows.Count = 0 Then MessageBox.Show("Cart is empty. Cannot save invoice.", "Validation Error", MessageBoxButtons.OK, MessageBoxIcon.Warning) Return End If Using conn As New OleDbConnection(connString) conn.Open() ' Initialize Transaction to prevent orphaned records on execution failure Using transaction As OleDbTransaction = conn.BeginTransaction() Try ' 1. Insert Parent Invoice Record Dim insertInvoiceQuery As String = "INSERT INTO Invoices (InvoiceDate, CustomerName, GrandTotal) VALUES (?, ?, ?)" Dim newInvoiceID As Integer = 0 Using cmdInvoice As New OleDbCommand(insertInvoiceQuery, conn, transaction) cmdInvoice.Parameters.AddWithValue("?", DateTime.Now) cmdInvoice.Parameters.AddWithValue("?", txtCustomerName.Text.Trim()) cmdInvoice.Parameters.AddWithValue("?", grandTotal) cmdInvoice.ExecuteNonQuery() ' Retrieve the identity value generated for this connection cmdInvoice.CommandText = "SELECT @@IDENTITY" newInvoiceID = Convert.ToInt32(cmdInvoice.ExecuteScalar()) End Using ' 2. Loop Through Grid and Insert Details + Deduct Inventory Inventory For Each row As DataGridViewRow In dgvItems.Rows If Not row.IsNewRow Then Dim pID As Integer = Convert.ToInt32(row.Cells(0).Value) Dim pPrice As Decimal = Convert.ToDecimal(row.Cells(2).Value) Dim pQty As Integer = Convert.ToInt32(row.Cells(3).Value) Dim pSubTotal As Decimal = Convert.ToDecimal(row.Cells(4).Value) ' Insert record into InvoiceDetails Dim insertDetailQuery As String = "INSERT INTO InvoiceDetails (InvoiceID, ProductID, Quantity, UnitPrice, SubTotal) VALUES (?, ?, ?, ?, ?)" Using cmdDetail As New OleDbCommand(insertDetailQuery, conn, transaction) cmdDetail.Parameters.AddWithValue("?", newInvoiceID) cmdDetail.Parameters.AddWithValue("?", pID) cmdDetail.Parameters.AddWithValue("?", pQty) cmdDetail.Parameters.AddWithValue("?", pPrice) cmdDetail.Parameters.AddWithValue("?", pSubTotal) cmdDetail.ExecuteNonQuery() End Using ' Deduct Inventory Balance Dim updateStockQuery As String = "UPDATE Products SET StockQuantity = StockQuantity - ? WHERE ProductID = ?" Using cmdStock As New OleDbCommand(updateStockQuery, conn, transaction) cmdStock.Parameters.AddWithValue("?", pQty) cmdStock.Parameters.AddWithValue("?", pID) cmdStock.ExecuteNonQuery() End Using End If Next ' Commit all executions if no exceptions occurred transaction.Commit() MessageBox.Show("Invoice #" & newInvoiceID & " successfully saved!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information) ' Reset the workspace ClearForm() LoadProducts() Catch ex As Exception ' Rollback database state to pre-execution configurations on error transaction.Rollback() MessageBox.Show("Transaction failed: " & ex.Message, "Execution Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Using End Using End Sub Private Sub ClearForm() txtCustomerName.Clear() dgvItems.Rows.Clear() cmbProducts.SelectedIndex = -1 txtQuantity.Clear() txtPrice.Clear() grandTotal = 0 lblGrandTotal.Text = "Grand Total: $0.00" End Sub End Class Use code with caution. Code Logic Deep Dive 1. Database Transaction Isolation
When the "Finish" button is clicked, the software must loop through the grid and save each line item to the InvoiceItems table while updating the Products table to reduce stock. 5. Why VB.NET for Billing? vb.net billing software source code
Create a new project. Rename the main form to frmBilling.vb . Imports System
command.ExecuteNonQuery() connection.Close() End Sub End Class ''' Private Sub btnAddToGrid_Click(sender As Object, e As
: Use ComboBox or CheckBox to allow users to select products from a predefined list.