Module1.vb codes
Option Explicit On
Imports System.Data.OleDb
Imports System.Data
Module Module1
Public con As String = "Provider=SQLOLEDB;Data Source=315mis\wdc1;Initial Catalog=sample;User Id=sa;Password=P@ssw0rd;"
Public cnSQL As New OleDbConnection
Public cmSQL As New OleDbCommand
Public strSQL As String
Public objRead As OleDbDataReader
Public currenttab As Integer
Public ds As New DataSet
Public lv As ListViewItem
Public Sub connection_open()
cnSQL = New OleDbConnection(con)
cnSQL.Open()
End Sub
Public Sub connection_close()
cnSQL.Close()
cnSQL.Dispose()
End Sub
Public Sub SaveEntry(ByVal id As String, ByVal name As String, ByVal course As String)
Try
connection_open()
strSQL = "insert into tbl_stud(id,name,course) values('" & id & "','" & name & "','" & course & "')"
cmSQL = New OleDbCommand(strSQL, cnSQL)
cmSQL.ExecuteNonQuery()
cmSQL.Dispose()
connection_close()
Catch Exp As OleDbException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
End Try
End Sub
Public Sub updateEntry(ByVal id As String, ByVal name As String, ByVal course As String)
Try
connection_open()
strSQL = "UPDATE tbl_stud SET name='" & name & "',course='" & course & "'where id='" & id & "'"
cmSQL = New OleDbCommand(strSQL, cnSQL)
cmSQL.ExecuteNonQuery()
cmSQL.Dispose()
connection_close()
Catch Exp As OleDbException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
End Try
End Sub
Public Sub deleteEntry(ByVal id As String, ByVal name As String, ByVal course As String)
Try
connection_open()
strSQL = "delete from tbl_stud where id='" & id & "'"
cmSQL = New OleDbCommand(strSQL, cnSQL)
cmSQL.ExecuteNonQuery()
cmSQL.Dispose()
connection_close()
Catch Exp As OleDbException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
End Try
End Sub
Public Function Getid(ByVal tablename As String, ByVal fieldname As String, Optional ByVal cond As String = "") As Double
Dim cmd2 As New OleDbCommand
cnSQL = New OleDbConnection(con)
cnSQL.Open()
Try
cmd2.Connection = cnSQL
cmd2.CommandText = "select top 1 " & fieldname & " from " & tablename & IIf(cond <> "", " where " & cond, "") & " order by 1 desc"
objRead = cmd2.ExecuteReader
If objRead.Read = True Then
Getid = objRead(0) + 1
Else
Getid = 1
End If
objRead.Close()
Catch ex As Exception
MsgBox(Err.Description)
End Try
cnSQL.Close()
End Function
Function AutoID(ByVal Tablename As String, ByVal Fieldname As String) As Int32
cnSQL = New OleDbConnection(con)
cnSQL.Open()
Dim cmd1 As New OleDbCommand
Dim dr1 As OleDbDataReader
cmd1.Connection = cnSQL
cmd1.CommandType = CommandType.Text
cmd1.CommandText = "select top 1 " & Fieldname & " from " & Tablename & " Order by 1 Desc "
dr1 = cmd1.ExecuteReader
If dr1.Read = True Then
AutoID = Microsoft.VisualBasic.Right(dr1(0), 4) + 1
Else
AutoID = 1
End If
dr1.Close()
cnSQL.Close()
End Function
End Module
Form1.vb codes
Option Explicit On
Imports System.Data.OleDb
Imports System.Data
Public Class Form1
Dim myDA As New OleDbDataAdapter
Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
If btnsave.Text = "New" Then
btnsave.Text = "Save"
tab1()
GroupBox1.Enabled = True
txtname.Focus()
Stud_autonum()
ElseIf btnsave.Text = "Save" And txtname.Text <> "" And txtcourse.Text <> "" Then
' txtname.Text = txtname.Text & "-" & txtid.Text
SaveEntry(txtid.Text, txtname.Text, txtcourse.Text)
MsgBox("Student has been Successfully Added.", MsgBoxStyle.Information)
btnsave.Text = "New"
PopulateListView()
clear()
tab2()
GroupBox1.Enabled = False
ElseIf txtname.Text = "" Then
MsgBox("Pleas Input Student Name.", MsgBoxStyle.Critical)
txtname.Focus()
ElseIf txtcourse.Text = "" Then
MsgBox("Pleas Input Course.", MsgBoxStyle.Critical)
txtcourse.Focus()
End If
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
GroupBox1.Enabled = False
currenttab = 0
PopulateListView()
ListView1.Items(0).Selected = True
ListView1.Select()
txtid.Enabled = False
btnupdate.Enabled = False
btndelete.Enabled = False
End Sub
Private Sub tab1()
currenttab = TabControl1.SelectedIndex
currenttab = currenttab + 1
If (currenttab < TabControl1.TabCount) Then
TabControl1.SelectedIndex = currenttab
End If
End Sub
Private Sub tab2()
currenttab = TabControl1.SelectedIndex
currenttab = currenttab - 1
If (currenttab > -1) Then
TabControl1.SelectedIndex = currenttab
End If
End Sub
Private Sub PopulateListView()
ListView1.Items.Clear()
connection_open()
Try
strSQL = "SELECT * FROM tbl_stud ORDER BY id Asc"
cmSQL = New OleDbCommand(strSQL, cnSQL)
objRead = cmSQL.ExecuteReader
While objRead.Read
lv = Me.ListView1.Items.Add(objRead("id") & "")
lv.SubItems.Add(objRead("name") & "")
lv.SubItems.Add(objRead("course") & "")
End While
objRead.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
'---------inserted code to count the number of columns
ds = New DataSet
myDA = New OleDbDataAdapter(strSQL, cnSQL)
myDA.Fill(ds, "MyData")
Dim kRow1 As Integer = ds.Tables("MyData").Rows.Count 'TOTAL ROW
lblTotal.Text = "Total No. of Students : " + kRow1.ToString
'------------------------end
ListView1.Items.Clear()
Dim i As Integer 'COLUMNS
Dim iCol As Integer = ds.Tables("MyData").Columns.Count 'TOTAL COLUMN
Dim k As Integer 'ROWS
Dim kRow As Integer = ds.Tables("MyData").Rows.Count 'TOTAL ROW
Dim ThisRow(4) As String '//=For each Loop of ROW
For k = 0 To kRow - 1
For i = 0 To iCol - 1
ThisRow(i) = ds.Tables("MyData").Rows(k).Item(i).ToString
Next
Dim lsvi As New ListViewItem(ThisRow, 2)
'//=Start Alternate Color
If k / 2 <> Int(k / 2) Then '//=Determine Alternate Row(odd or not)
lsvi.BackColor = Color.SkyBlue
Else
lsvi.BackColor = Color.LightCyan
End If
'//=End Alternate Color
ListView1.Items.Add(lsvi)
Next
connection_close()
End Try
End Sub
Private Sub RefreshForm()
ListView1.Items.Clear()
Call searchListView()
End Sub
Private Sub searchListView()
connection_open()
Try
strSQL = "SELECT * FROM tbl_stud where id like '" & txtsearch.Text & "%'"
cmSQL = New OleDbCommand(strSQL, cnSQL)
objRead = cmSQL.ExecuteReader
While objRead.Read
lv = Me.ListView1.Items.Add(objRead("id") & "")
lv.SubItems.Add(objRead("name") & "")
lv.SubItems.Add(objRead("course") & "")
End While
objRead.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
connection_close()
End Try
End Sub
Private Sub ListView1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles ListView1.KeyPress
If e.KeyChar = Chr(13) Then
GroupBox1.Enabled = True
btnupdate.Enabled = True
btndelete.Enabled = True
btnsave.Enabled = False
tab1()
With ListView1.SelectedItems(0)
txtid.Text = .SubItems(0).Text
txtname.Text = .SubItems(1).Text
txtcourse.Text = .SubItems(2).Text
End With
End If
End Sub
Private Sub ListView1_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseClick
GroupBox1.Enabled = True
btnsave.Enabled = False
tab1()
ListView1_MouseDoubleClick(sender, e)
End Sub
Private Sub ListView1_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseDoubleClick
GroupBox1.Enabled = True
btnupdate.Enabled = True
btndelete.Enabled = True
btnsave.Enabled = False
tab1()
With ListView1.SelectedItems(0)
txtid.Text = .SubItems(0).Text
txtname.Text = .SubItems(1).Text
txtcourse.Text = .SubItems(2).Text
End With
End Sub
Private Sub txtsearch_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtsearch.KeyPress
If e.KeyChar = Chr(13) Then
ListView1.Items(0).Selected = True
ListView1.Select()
End If
End Sub
Private Sub txtsearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtsearch.TextChanged
RefreshForm()
End Sub
Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
updateEntry(txtid.Text, txtname.Text, txtcourse.Text)
MsgBox("Student has been Successfully Udated.", MsgBoxStyle.Information)
PopulateListView()
clear()
tab2()
GroupBox1.Enabled = False
btnupdate.Enabled = False
btndelete.Enabled = False
btnsave.Enabled = True
End Sub
Private Sub clear()
txtid.Text = ""
txtname.Text = ""
txtcourse.Text = ""
End Sub
Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
deleteEntry(txtid.Text, txtname.Text, txtcourse.Text)
MsgBox("Student has been Successfully Deleted.", MsgBoxStyle.Information)
PopulateListView()
clear()
tab2()
GroupBox1.Enabled = False
btnupdate.Enabled = False
btndelete.Enabled = False
btnsave.Enabled = True
End Sub
Private Sub Stud_autonum()
Dim year As String
Dim studid As String
year = Now.ToString("yyyy")
studid = "" & Format(AutoID("tbl_stud", "id"), "0000")
txtid.Text = year & studid
End Sub
Private Sub txtname_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtname.KeyPress
If e.KeyChar = Chr(13) Then
txtcourse.Focus()
End If
End Sub
Private Sub btncancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncancel.Click
clear()
tab2()
GroupBox1.Enabled = False
btnsave.Enabled = True
btnupdate.Enabled = False
btndelete.Enabled = False
btnsave.Text = "New"
txtsearch.Text = ""
End Sub
End Class
