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.

APIIT City Campus - Colombo Page 1

 Visual Basic .Net database programming -
2
Practical

Sales Table

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.

APIIT City Campus - Colombo Page 2

 Visual Basic .Net database programming -
3
Practical

Inventory form

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.

APIIT City Campus - Colombo Page 3

 Visual Basic .Net database programming -
4
Practical

Configure the data connection; create a new dataset & data adapter and bind
data into textboxes and data grid view

Imports [Link]

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 [Link], ByVal e As

[Link]) Handles [Link]
Try
[Link]()
'[Link]("Successfull connection")
str = "Select * from Product"
da = New SqlDataAdapter(str, con)
[Link](ds, "Items")
RowCount = [Link]("Items").[Link]
RecordNavigator()
[Link] = [Link]("Items")
Catch ex As Exception
MsgBox([Link])
End Try

End Sub

Private Sub RecordNavigator()

Try
[Link] = [Link]("Items").Rows(ptr).Item(0)
[Link] = [Link]("Items").Rows(ptr).Item(1)
[Link] = [Link]("Items").Rows(ptr).Item(2)
[Link] = [Link]("Items").Rows(ptr).Item(3)
[Link] = [Link]("Items").Rows(ptr).Item(4)
[Link] = [Link]("Items").Rows(ptr).Item(5)

Catch ex As Exception
MsgBox([Link])
End Try

End Sub

APIIT City Campus - Colombo Page 4

 Visual Basic .Net database programming -
5
Practical

Navigation of records bound to textboxes

Private Sub btnFirst_Click(ByVal sender As [Link], ByVal e As

[Link]) Handles [Link]
Try
If ptr <> 0 Then
ptr = 0
Call RecordNavigator()
End If
Catch ex As Exception
MsgBox([Link])
End Try

End Sub

Private Sub btnPrevious_Click(ByVal sender As [Link], ByVal e As

[Link]) Handles [Link]
Try
If ptr > 0 Then
ptr -= 1
Call RecordNavigator()
Else
[Link]("That's the first record")
End If
Catch ex As Exception
MsgBox([Link])
End Try

End Sub
Private Sub btnNext_Click(ByVal sender As [Link], ByVal e As
[Link]) Handles [Link]
Try
If ptr <> RowCount - 1 Then
ptr += 1
Call RecordNavigator()
Else
[Link]("That's the last record")
End If
Catch ex As Exception
MsgBox([Link])
End Try

End Sub
Private Sub btnLast_Click(ByVal sender As [Link], ByVal e As
[Link]) Handles [Link]
Try
If ptr <> RowCount - 1 Then
ptr = RowCount - 1
Call RecordNavigator()
End If
Catch ex As Exception

APIIT City Campus - Colombo Page 5

 Visual Basic .Net database programming -
6
Practical

MsgBox([Link])
End Try
End Sub

To add a new record to Product table

Private Sub btnAdd_Click(ByVal sender As [Link], ByVal e As

[Link]) Handles [Link]
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = True
[Link] = False
End Sub

Private Sub btnSave_Click(ByVal sender As [Link], ByVal e As

[Link]) Handles [Link]

Try
Dim sq As String

sq = "Insert into Product(ProdDesc, ProdVendor, ProdUnitPrice,

ProdUnit,CurrentStock) values(' " & [Link] & "', '" & [Link]
& "', " & [Link] & ", '" & [Link] & "' , " & [Link] &
")"
Dim com As New SqlCommand(sq, con)

[Link]()
[Link]()
[Link]()
[Link]()
str = "Select * from Product"
da = New SqlDataAdapter(str, con)
[Link](ds, "Items")
[Link] = Nothing
[Link]()
[Link] = [Link]("Items")
[Link] = False
RowCount += 1
Catch ex As Exception
MsgBox("Invalid Input")
End Try

End Sub

APIIT City Campus - Colombo Page 6

 Visual Basic .Net database programming -
7
Practical

To update/ modify a record in product table

Private Sub btnUpdate_Click(ByVal sender As [Link], ByVal e As

[Link]) Handles [Link]
Try
Dim sq As String
sq = "Update Product set ProdDesc='" & [Link] & "',
ProdVendor='" & [Link] & "', ProdUnitPrice=" & [Link] & ",
ProdUnit='" & [Link] & "', CurrentStock=" & [Link] & " where
ProdID=" & [Link]
Dim com As New SqlCommand(sq, con)

Dim x As Integer
x = [Link]("Are you sure to update this record? Hit NO if
you want to cancel modifications", "Are you sure?", [Link],
[Link])
If x = [Link] Then
[Link]()
[Link] = Nothing
[Link]()
[Link]()
str = "Select * from Product"
da = New SqlDataAdapter(str, con)
[Link](ds, "Items")
[Link] = [Link]("Items")

'[Link]()
Else
MsgBox("Tables are not updated", [Link],
"Cancellation")
End If

Catch ex As Exception
MsgBox([Link])
End Try

End Sub

APIIT City Campus - Colombo Page 7

 Visual Basic .Net database programming -
8
Practical

To delete a record from Product table

Private Sub btnDelete_Click(ByVal sender As [Link], ByVal e As

[Link]) Handles [Link]
Try
Dim sq As String
sq = "DELETE FROM Product WHERE ProdID=" & [Link]

Dim com As New SqlCommand(sq, con)

Dim x As Integer
x = [Link]("Are you sure to Delete item " &
[Link] & " from database? Hit NO if no to cancel deletions", "Are
you sure?", [Link], [Link])
If x = [Link] Then
[Link]()
RowCount -= 1
[Link] = Nothing
[Link]()
[Link]()
str = "Select * from Product"
da = New SqlDataAdapter(str, con)
[Link](ds, "Items")
[Link] = [Link]("Items")

[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
[Link] = ""
Else
MsgBox("Deletion is cancelled", [Link],
"Cancellation")
End If
Catch ex As Exception
MsgBox([Link])
End Try

End Sub

APIIT City Campus - Colombo Page 8

 Visual Basic .Net database programming -
9
Practical

Load datagridview data into a set of textboxes on click of row header

Private Sub DataGridView1_RowHeaderMouseClick(ByVal sender As Object, ByVal e

As [Link]) Handles
[Link]
Try
ptr = [Link](0).Value - 1
If Not IsDBNull([Link](0).Value) Then
[Link] = [Link](0).Value
End If
If Not IsDBNull([Link](1).Value) Then
[Link] = [Link](1).Value
End If
If Not IsDBNull([Link](2).Value) Then
[Link] = [Link](2).Value
End If

If Not IsDBNull([Link](3).Value) Then

[Link] = [Link](3).Value
End If
If Not IsDBNull([Link](4).Value) Then
[Link] = [Link](4).Value
End If
If Not IsDBNull([Link](5).Value) Then
[Link] = [Link](5).Value
End If
Catch ex As Exception
MsgBox([Link])
End Try

End Sub

APIIT City Campus - Colombo Page 9