The document describes the design of a database prototype to track inventory and sales transactions for a supermarket chain. The prototype consists of 3 tables - Product, Sales, and Sales Details. The Product table stores product information. The Sales table stores transaction-level data. The Sales Details table stores the individual products purchased for each transaction. The document also provides code for functions like connecting to the database, navigating records, and adding/updating records in the Product table.
The document describes the design of a database prototype to track inventory and sales transactions for a supermarket chain. The prototype consists of 3 tables - Product, Sales, and Sales Details. The Product table stores product information. The Sales table stores transaction-level data. The Sales Details table stores the individual products purchased for each transaction. The document also provides code for functions like connecting to the database, navigating records, and adding/updating records in the Product table.
The document describes the design of a database prototype to track inventory and sales transactions for a supermarket chain. The prototype consists of 3 tables - Product, Sales, and Sales Details. The Product table stores product information. The Sales table stores transaction-level data. The Sales Details table stores the individual products purchased for each transaction. The document also provides code for functions like connecting to the database, navigating records, and adding/updating records in the Product table.
The document describes the design of a database prototype to track inventory and sales transactions for a supermarket chain. The prototype consists of 3 tables - Product, Sales, and Sales Details. The Product table stores product information. The Sales table stores transaction-level data. The Sales Details table stores the individual products purchased for each transaction. The document also provides code for functions like connecting to the database, navigating records, and adding/updating records in the Product table.
Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt
You are on page 1/ 9
Visual Basic .
Net database programming -
1 Practical
Case Study Food world is one of country’s leading supermarkets. You are asked to develop a prototype to keep track of the company’s inventory and sales transactions. You are given with the table structure for the prototype. Note that the requirement is to develop an initial prototype and not necessary the implementation of a complete system.
Table design The prototype consists of 3 tables including Product table, Sales table and Sales Details table. Table structure and necessary relationships are given as follows:
Product Table
Product table consist of a primary column ProdID, which records the primary key of the table the ID of the Product. Note that ProdID should be an auto generated number where the identity increment is 1 and the seed is 1000.
Other columns specified in the table contain information about Product description, vendor or manufacturer of the product, unit price of a product, unit which product is measured and the current (hand-in) stock.
Sales table records information of each transaction. The bill no will be the primary key again it should be auto generated at the run time. Let the identity increment be 1 and the seed is 10000. Note that sales table contains only summarized information of a transaction that is date of billing, total of transaction, the amount of tax, discount if any and the net total of each transaction.
A customer should be able to purchase multiple products in one bill. Product details of each purchase are recorded in the Sales Details table.
Sales Details Table
Sales details table is related to Product table and sales table. BillNo and ProdID fields are served as composite primary keys where all the products purchased will be listed with the associated BillNo. Then Product description and unit price is filtered from the product table by ProdID and the purchased quantity is recorded in the sales details table.
This form is the user interface offered to warehouse manager or the stock keeper to keep track of the product stock in the supermarket. Allow functions to add new products to the stock, edit them or delete them from the stock. This form also provide functionalities to record navigation, display data in data grid view, search by various parameters.
Configure the data connection; create a new dataset & data adapter and bind data into textboxes and data grid view
Imports System.Data.SqlClient
Public Class frmInventory
Dim ds As New DataSet Dim da As SqlDataAdapter Dim str As String Dim ptr As Integer Dim RowCount As Integer Dim connectionObject As New SqlConnection(“Data Source=ServerName; Initial Catalog=Database Name; Integrated Security=True”)
Private Sub frmInventory_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load Try con.Open() 'MessageBox.Show("Successfull connection") str = "Select * from Product" da = New SqlDataAdapter(str, con) da.Fill(ds, "Items") RowCount = ds.Tables("Items").Rows.Count RecordNavigator() DataGridView1.DataSource = ds.Tables("Items") Catch ex As Exception MsgBox(ex.ToString) End Try
Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnFirst.Click Try If ptr <> 0 Then ptr = 0 Call RecordNavigator() End If Catch ex As Exception MsgBox(ex.ToString) End Try
End Sub
Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnPrevious.Click Try If ptr > 0 Then ptr -= 1 Call RecordNavigator() Else MessageBox.Show("That's the first record") End If Catch ex As Exception MsgBox(ex.ToString) End Try
End Sub Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click Try If ptr <> RowCount - 1 Then ptr += 1 Call RecordNavigator() Else MessageBox.Show("That's the last record") End If Catch ex As Exception MsgBox(ex.ToString) End Try
End Sub Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click Try If ptr <> RowCount - 1 Then ptr = RowCount - 1 Call RecordNavigator() End If Catch ex As Exception
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click Try Dim sq As String sq = "Update Product set ProdDesc='" & txtProdDesc.Text & "', ProdVendor='" & txtVendor.Text & "', ProdUnitPrice=" & txtUnitPrice.Text & ", ProdUnit='" & txtUnit.Text & "', CurrentStock=" & txtStock.Text & " where ProdID=" & txtProdID.Text Dim com As New SqlCommand(sq, con)
Dim x As Integer x = MessageBox.Show("Are you sure to update this record? Hit NO if you want to cancel modifications", "Are you sure?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) If x = System.Windows.Forms.DialogResult.Yes Then com.ExecuteNonQuery() DataGridView1.DataSource = Nothing DataGridView1.Refresh() ds.Clear() str = "Select * from Product" da = New SqlDataAdapter(str, con) da.Fill(ds, "Items") DataGridView1.DataSource = ds.Tables("Items")
'con.Close() Else MsgBox("Tables are not updated", MsgBoxStyle.OkOnly, "Cancellation") End If
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnDelete.Click Try Dim sq As String sq = "DELETE FROM Product WHERE ProdID=" & txtProdID.Text
Dim com As New SqlCommand(sq, con)
Dim x As Integer x = MessageBox.Show("Are you sure to Delete item " & txtProdDesc.Text & " from database? Hit NO if no to cancel deletions", "Are you sure?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) If x = System.Windows.Forms.DialogResult.Yes Then com.ExecuteNonQuery() RowCount -= 1 DataGridView1.DataSource = Nothing DataGridView1.Refresh() ds.Clear() str = "Select * from Product" da = New SqlDataAdapter(str, con) da.Fill(ds, "Items") DataGridView1.DataSource = ds.Tables("Items")
txtProdID.Text = "" txtProdDesc.Text = "" txtVendor.Text = "" txtUnitPrice.Text = "" txtUnit.Text = "" txtStock.Text = "" Else MsgBox("Deletion is cancelled", MsgBoxStyle.OkOnly, "Cancellation") End If Catch ex As Exception MsgBox(ex.ToString) End Try
Load datagridview data into a set of textboxes on click of row header
Private Sub DataGridView1_RowHeaderMouseClick(ByVal sender As Object, ByVal e
As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.RowHeaderMouseClick Try ptr = DataGridView1.CurrentRow.Cells(0).Value - 1 If Not IsDBNull(DataGridView1.CurrentRow.Cells(0).Value) Then txtProdID.Text = DataGridView1.CurrentRow.Cells(0).Value End If If Not IsDBNull(DataGridView1.CurrentRow.Cells(1).Value) Then txtProdDesc.Text = DataGridView1.CurrentRow.Cells(1).Value End If If Not IsDBNull(DataGridView1.CurrentRow.Cells(2).Value) Then txtVendor.Text = DataGridView1.CurrentRow.Cells(2).Value End If
If Not IsDBNull(DataGridView1.CurrentRow.Cells(3).Value) Then
txtUnitPrice.Text = DataGridView1.CurrentRow.Cells(3).Value End If If Not IsDBNull(DataGridView1.CurrentRow.Cells(4).Value) Then txtUnit.Text = DataGridView1.CurrentRow.Cells(4).Value End If If Not IsDBNull(DataGridView1.CurrentRow.Cells(5).Value) Then txtStock.Text = DataGridView1.CurrentRow.Cells(5).Value End If Catch ex As Exception MsgBox(ex.ToString) End Try
Python Advanced Programming: The Guide to Learn Python Programming. Reference with Exercises and Samples About Dynamical Programming, Multithreading, Multiprocessing, Debugging, Testing and More
Python Advanced Programming: The Guide to Learn Python Programming. Reference with Exercises and Samples About Dynamical Programming, Multithreading, Multiprocessing, Debugging, Testing and More
