Step 3 – Coding Clinical Lab Test Mini Project in VB.NET
After you have designed the database the third step is to code the forms for saving data in the respective tables. We begin with defining the Module for the Clinical Lab Test Mini Project in VB.NET
Module Module1 for Clinical lab Test Mini Project in VB.NET
‘Declare the connection object
Public connDB As New OleDb.OleDbConnection
‘Function to pass a table name and field name for unique ID and get the next ID that can be used while inserting the record
Function getNewID(tblName As String, fldName As String) As String
Dim strVal, sql As String
Dim cmd As OleDb.OleDbCommand
connectdB()
Sql = "select max(" & fldName & ") from " & tblName
cmd = New OleDb.OleDbCommand(sql, connDB)
strVal = Convert.ToString(cmd.ExecuteScalar())
If strVal = "" Then
strVal = "1"
Else
strVal = Convert.ToString(CInt(strVal) + 1)
End If
Return strVal
End Function
‘Procedure to connect with the database LabReportDB.accdb Access file) which is stored in the “\bin\Debug” startup project path of the current project
Public Sub connectdB()
If connDB.State = ConnectionState.Closed Then
connDB.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Application.StartupPath & "\LabReportDB.accdb"
connDB.Open()
End If
End Sub
Form- Test Master
Global Variables for Form
Dim cmd As OleDb.OleDbCommand Dim datrd As OleDb.OleDbDataReader
Form load Event
txtTestID.Text = getNewID("TestMaster", "TestID")’getting new TestID by calling getNewID function defined in module
connectdB()’ calling the database connection procedure from module
fillList()’ calling the procedure to fill the listbox control with testID and TestName from TestMaster Table
Procedure fillList
Private Sub fillList()
Dim strSQL As String
strSQL = "select testID, TestDesc from TestMaster"
cmd = New OleDb.OleDbCommand(strSQL, connDB)
datrd = cmd.ExecuteReader()
lstTest.Items.Clear()
While datrd.Read()
lstTest.Items.Add(datrd.Item(0).ToString.PadRight(10) & datrd.Item(1))
End While
End Sub
btnSave Click Event
Dim strSQL As String
strSQL = "Insert into TestMaster values (" & CInt(txtTestID.Text) & ",'" & txtTestDesc.Text & "','" & cboTestCat.Text & "'," & CDbl(txtCharges.Text) & ",'" & txtTestNV.Text & "'" & ")"
cmd = New OleDb.OleDbCommand(strSQL, connDB)
cmd.ExecuteNonQuery()
fillList()
txtTestID.Text = getNewID("TestMaster", "TestID")
txtTestDesc.Clear()
txtCharges.Clear()
cboTestCat.Text = ""
txtTestNV.Clear()
btnUpdate Click Event
Dim strSQL As String
strSQL = "update TestMaster set testDesc='" & txtTestDesc.Text & "',TestCategory='" & cboTestCat.Text & "',testCharges=" & CDbl(txtCharges.Text) & ", NormalVal='" & txtTestNV.Text & "' where testid=" & CInt(txtTestID.Text)
cmd = New OleDb.OleDbCommand(strSQL, connDB)
cmd.ExecuteNonQuery()
fillList()
txtTestID.Text = getNewID("TestMaster", "TestID")
txtTestDesc.Clear()
txtCharges.Clear()
cboTestCat.Text = ""
txtTestNV.Clear()
btnDelete Click Event
Dim strSQL As String
strSQL = "delete * from TestMaster where testid=" & CInt(txtTestID.Text)
cmd = New OleDb.OleDbCommand(strSQL, connDB)
cmd.ExecuteNonQuery()
fillList()
txtTestID.Text = getNewID("TestMaster", "TestID")
txtTestDesc.Clear()
txtCharges.Clear()
cboTestCat.Text = ""
txtTestNV.Clear()
lstTest Click event – to click a record in listbox and display on screen
Dim strSQL As String
strSQL = "select * from TestMaster where testID=" & CInt(Mid(lstTest.Items(lstTest.SelectedIndex), 1, 9))
cmd = New OleDb.OleDbCommand(strSQL, connDB)
datrd = cmd.ExecuteReader()
txtTestID.Text = Mid(lstTest.Items(lstTest.SelectedIndex), 1, 9)
datrd.Read()
txtTestDesc.Text = datrd.Item(1)
cboTestCat.Text = datrd.Item(2)
txtCharges.Text = Convert.ToString(datrd.Item(3))
txtTestNV.Text = datrd.Item(4)
btnNew Click Event
txtTestID.Text = getNewID("TestMaster", "TestID")
Form- Patient Master
Global Variables for Form
Dim cmd As OleDb.OleDbCommand Dim datrd As OleDb.OleDbDataReader
Form Load Event
txtPID.Text = getNewID("PatientMaster", "PID")
connectdB()
fillList()
txtRegDate.Text = DateAndTime.Today()
btnSave Click Event
Dim strSQL As String
strSQL = "Insert into PatientMaster values (" & CInt(txtPID.Text) & ",'" & txtPName.Text & "','" & txtAddress.Text & "','" & txtEmail.Text & "','" & txtPhone.Text & "',#" & txtRegDate.Text & "#)"
cmd = New OleDb.OleDbCommand(strSQL, connDB)
cmd.ExecuteNonQuery()
fillList()
txtPID.Text = getNewID("PatientMaster", "PID")
txtPName.Clear()
txtAddress.Clear()
txtPhone.Text = ""
txtEmail.Clear()
btnUpdate Click Event
Dim strSQL As String
strSQL = "update PatientMaster set pname='" & txtPName.Text & "',paddress='" & txtAddress.Text & "',pemail='" & txtEmail.Text & "', pphone='" & txtPhone.Text & "' where pid=" & CInt(txtPID.Text)
cmd = New OleDb.OleDbCommand(strSQL, connDB)
cmd.ExecuteNonQuery()
fillList()
txtPID.Text = getNewID("PatientMaster", "PID")
txtPName.Clear()
txtAddress.Clear()
txtEmail.Clear()
txtPhone.Clear()
btnSave.Enabled = True
btnUpdate.Enabled = False
btnDelete Click Event
Dim strSQL As String
strSQL = "delete * from PatientMaster where pid=" & CInt(txtPID.Text)
cmd = New OleDb.OleDbCommand(strSQL, connDB)
cmd.ExecuteNonQuery()
fillList()
txtPID.Text = getNewID("PatientMaster", "PID")
txtPName.Clear()
txtAddress.Clear()
txtEmail.Clear()
txtPhone.Clear()
lstPatient Click Event
Dim strSQL As String
strSQL = "select * from PatientMaster where PID=" & CInt(Mid(lstPatient.Items(lstPatient.SelectedIndex), 1, 9))
cmd = New OleDb.OleDbCommand(strSQL, connDB)
datrd = cmd.ExecuteReader()
txtPID.Text = Mid(lstPatient.Items(lstPatient.SelectedIndex), 1, 9)
datrd.Read()
txtPName.Text = datrd.Item(1)
txtAddress.Text = datrd.Item(2)
txtEmail.Text = datrd.Item(3)
txtPhone.Text = datrd.Item(4)
btnSave.Enabled = False
btnUpdate.Enabled = True
btnNew Click Event
txtPID.Text = getNewID("PatientMaster", "PID")
btnSave.Enabled = True
btnUpdate.Enabled = False
lstPatient Click Event
Dim strSQL As String
strSQL = "select pID, PName from PatientMaster"
cmd = New OleDb.OleDbCommand(strSQL, connDB)
datrd = cmd.ExecuteReader()
lstPatient.Items.Clear()
While datrd.Read()
lstPatient.Items.Add(datrd.Item(0).ToString.PadRight(10) & datrd.Item(1))
End While
Form – Patient Test Details
Global Variable for form
Dim cmd As OleDb.OleDbCommand
Dim datrd As OleDb.OleDbDataReader
Dim i As Integer
Dim totalamt As Decimal
Form Load Event
connectdB()
fillcombo()
i = 0
totalamt = 0
txtTDate.Text = DateAndTime.Today()
txtTID.Text = getNewID("PatientTests", "TID")
Local Procedure fillcombo
Dim strSQL As String
strSQL = "select testID, TestDesc from TestMaster"
cmd = New OleDb.OleDbCommand(strSQL, connDB)
datrd = cmd.ExecuteReader()
cboTest.Items.Clear()
While datrd.Read()
cboTest.Items.Add(datrd.Item(0).ToString.PadRight(10) & datrd.Item(1))
End While
Patient ID LostFocus Event- Fill the name of the patient for verification
Dim strVal, sql As String
Dim cmd As OleDb.OleDbCommand
connectdB()
sql = "select pName from PatientMaster where PID=" & CInt(txtPID.Text)
cmd = New OleDb.OleDbCommand(sql, connDB)
strVal = cmd.ExecuteScalar()
If strVal = "" Then
MsgBox(" No patient exists for this Patient ID")
txtPID.Focus()
Else
Label10.Text = strVal
End If
cboTest SelectedIndexChanged Event- To fill the selected test in the datagrid
Dim strSQL As String
strSQL = "select * from TestMaster where testID=" & CInt(Mid(cboTest.Items(cboTest.SelectedIndex), 1, 9))
cmd = New OleDb.OleDbCommand(strSQL, connDB)
datrd = cmd.ExecuteReader()
dtgTests.Rows.Add()
datrd.Read()
dtgTests.Item(0, i).Value = Mid(cboTest.Items(cboTest.SelectedIndex), 1, 9)
dtgTests.Item(1, i).Value = Convert.ToString(datrd.Item(1))
dtgTests.Item(2, i).Value = datrd.Item(3)
totalamt = totalamt + CDec(dtgTests.Item(2, i).Value)
txtTotAmt.Text = Convert.ToString(totalamt)
i = i + 1
txtAdv LostFocus EVent to calculate balance money to be paid
txtBal.Text = CInt(txtTotAmt.Text) - CInt(txtAdv.Text)
btnSave Click event- to save data in tables patienttestdetail and patienttest
Dim strSQL As String
Dim j As Integer
strSQL = "Insert into PatientTests values (" & CInt(txtTID.Text) _
& ",#" & txtTDate.Text & "#," & CInt(txtPID.Text) & "," & txtTotAmt.Text &
"," & txtAdv.Text & "," & txtBal.Text & ",'" & txtDName.Text & "','" &
txtHospital.Text & "','" & txtPhone.Text & "')"
cmd = New OleDb.OleDbCommand(strSQL, connDB)
cmd.ExecuteNonQuery()
For j = 0 To i - 1
strSQL = "Insert into PatientTestDetail (TID, TestID, Charges) values (" & CInt(txtTID.Text) _
& "," & dtgTests.Item(0, j).Value & "," & dtgTests.Item(2, j).Value & ")"
cmd = New OleDb.OleDbCommand(strSQL, connDB)
cmd.ExecuteNonQuery()
Next
txtPID.Text = getNewID("Patienttests", "TID")
txtPID.Clear()
Label10.Text = ""
txtDName.Clear()
txtPhone.Clear()
txtHospital.Clear()
rdbTestResult CheckedChanged Event when the user has to fill the test results and the TestID textbox in right top corner is enabled
If rdbTestResult.Checked = True Then
btnSave.Enabled = False
txtTID.Enabled = True
txtTID.Focus()
End If
txtTID LostFocus Event- to load the test conducted data in controls so that user can fill the results in grid against each test and update the PatientTestDetail table
Dim strSQL As String
Dim k As Integer
k = 0
strSQL = "select * from PatientTests where TID=" & CInt(txtTID.Text)
cmd = New OleDb.OleDbCommand(strSQL, connDB)
datrd = cmd.ExecuteReader()
If Not datrd.HasRows Then
MsgBox("No records of this patient's Tests")
txtTID.Focus()
Exit Sub
End If
datrd.Read()
txtPID.Text = datrd.Item(2)
txtPID_LostFocus(sender, e)
txtDName.Text = datrd.Item(6)
txtHospital.Text = datrd.Item(7)
txtPhone.Text = datrd.Item(8)
txtTotAmt.Text = datrd.Item(3)
txtAdv.Text = datrd.Item(4)
txtBal.Text = datrd.Item(5)
strSQL = "select PD.TestID,TM.TestDesc, TM.TestCharges from patientTestDetail PD, TestMaster TM where PD.TestID=TM.TestID and TID=" & CInt(txtTID.Text)
cmd = New OleDb.OleDbCommand(strSQL, connDB)
datrd = cmd.ExecuteReader()
dtgTests.RowCount = 1
k = 0
While datrd.Read()
dtgTests.Rows.Add()
dtgTests.Item(0, k).Value = datrd.Item(0)
dtgTests.Item(1, k).Value = Convert.ToString(datrd.Item(1))
dtgTests.Item(2, k).Value = datrd.Item(2)
k = k + 1
End While
btnUpdate Click Event to update the patientTestDetail table
Dim x As Integer
Dim strSQL As String
For x = 0 To dtgTests.RowCount - 1 And dtgTests.Item(1, x).Value = Nothing
strSQL = "update PatientTestDetail set TestRes='" & dtgTests.Item(3, x).Value & "' WHERE TID= " & CInt(txtTID.Text) & " and TestID=" & dtgTests.Item(0, x).Value
cmd = New OleDb.OleDbCommand(strSQL, connDB)
cmd.ExecuteNonQuery()
Next