i have here below codes of my maintenance module..my problem is that when i click the edit button and search for student number it makes error in saving but when i use the listview to edit it saves successfully...please help me i'll be attaching a sample screenshot
here's my code
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.OleDb
Public Class maintenance
Dim con As OleDbConnection
Dim cmd As OleDbCommandBuilder
Dim ds As DataSet
Dim da As New OleDbDataAdapter
Dim lastrow As Integer
Dim myrow As Data.DataRow
Dim com As OleDbCommand
Dim rid As OleDbDataReader
Private Sub maintenance_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Activated
DisplayList()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim dbload As String = Application.StartupPath + "\Enrollment System.accdb"
Dim backupdb As String = "G:{0:yyyyMMdd}" + ".accdb"
System.IO.File.Copy(dbload, String.Format(backupdb, Date.Today), True)
MsgBox("DB Backup Successful")
End Sub
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
ListView1.Enabled = False
LblAdd_Edit.Text = "Add"
btnAdd.Enabled = False
BtnCancel.Enabled = True
btnSave.Enabled = True
BtnEdit.Enabled = False
GroupBox1.Enabled = True
TextBox11.Enabled = True
LinkLabel1.Visible = False
ComboBox2.SelectedIndex = -1
DateTimePicker1.Value = Date.Now
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
ComboBox2.Text = ""
DateTimePicker1.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox11.Text = ""
TextBox11.Focus()
con = New OleDbConnection("Provider= Microsoft.ACE.oledb.12.0; Data Source=C:\Users\edenzam\Desktop\CBFMNHS Enrollment System\CBFMNHS Enrollment System\bin\Debug\Enrollment System.accdb")
con.Open()
Dim x As String
x = "select count (*) from RegAndVer"
com = New OleDbCommand(x, con)
com.ExecuteNonQuery()
rid = com.ExecuteReader
rid.Read()
Dim y As String
y = rid(0)
TextBox11.Text = "MP" + Format(y + 3, Date.Today.Year - 2000 & "00000")
TextBox11.Enabled = False
LinkLabel1.Visible = False
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim Age As Double = Math.Floor(DateDiff(DateInterval.Month, DateTimePicker1.Value, System.DateTime.Now) / 12)
If TextBox1.Text = "" Then : MsgBox("Complete the form!", MsgBoxStyle.Exclamation) : TextBox1.Focus() : Exit Sub : End If
If TextBox2.Text = "" Then : MsgBox("Complete the form!", MsgBoxStyle.Exclamation) : TextBox2.Focus() : Exit Sub : End If
If TextBox3.Text = "" Then : MsgBox("Complete the form!", MsgBoxStyle.Exclamation) : TextBox3.Focus() : Exit Sub : End If
If ComboBox2.Text = "" Then : MsgBox("Complete the form!", MsgBoxStyle.Exclamation) : ComboBox2.Focus() : Exit Sub : End If
If DateTimePicker1.Text = "" Then : MsgBox("Complete the form!", MsgBoxStyle.Exclamation) : ElseIf Age <= 10 Then
MsgBox("Please input your birthday correctly") : DateTimePicker1.Focus() : Exit Sub : End If
If TextBox4.Text = "" Then : MsgBox("Complete the form!", MsgBoxStyle.Exclamation) : TextBox4.Focus() : Exit Sub : End If
If TextBox5.Text = "" Then : MsgBox("Complete the form!", MsgBoxStyle.Exclamation) : TextBox5.Focus() : Exit Sub : End If
If TextBox6.Text = "" Then : MsgBox("Complete the form!", MsgBoxStyle.Exclamation) : TextBox6.Focus() : Exit Sub : End If
If TextBox7.Text = "" Then : MsgBox("Complete the form!", MsgBoxStyle.Exclamation) : TextBox7.Focus() : Exit Sub : End If
If TextBox8.Text = "" Then : MsgBox("Complete the form!", MsgBoxStyle.Exclamation) : TextBox8.Focus() : Exit Sub : End If
If TextBox11.Text = "" Then : MsgBox("Complete the form!", MsgBoxStyle.Exclamation) : TextBox11.Focus() : Exit Sub : End If
If MsgBox("Do you want to save this record ?", MsgBoxStyle.YesNo, "Message") = MsgBoxResult.No Then : Exit Sub : End If
LinkLabel1.Visible = False
rs = New ADODB.Recordset
With rs
If LblAdd_Edit.Text = "Add" Then
.Open("Select * from RegAndVer", cn, 2, 3)
.AddNew()
Else
.Open("Select * from RegAndVer where StudentNumber='" & Me.Text & "'", cn, 2, 3)
End If
.Fields("StudentNumber").Value = TextBox11.Text
.Fields("FirstName").Value = TextBox1.Text
.Fields("LastName").Value = TextBox2.Text
.Fields("MiddleName").Value = TextBox3.Text
.Fields("Gender").Value = ComboBox2.Text
.Fields("Birthdate").Value = DateTimePicker1.Text
.Fields("Nationality").Value = TextBox4.Text
.Fields("Address").Value = TextBox5.Text
.Fields("ContactNumStudent").Value = TextBox6.Text
.Fields("GuardianName").Value = TextBox7.Text
.Fields("ContactNumGuardian").Value = TextBox8.Text
If LinkLabel1.Enabled = False Then
Dim cn1 As OleDbConnection
Dim cmd As OleDbCommand
cn1 = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\edenzam\Desktop\CBFMNHS Enrollment System\CBFMNHS Enrollment System\bin\Debug\Enrollment System.accdb;Persist Security Info=False;")
cn1.Open()
cmd = New OleDbCommand("UPDATE RegAndVer SET FirstName = '" & TextBox1.Text & "', LastName = '" & TextBox2.Text & "', MiddleName = '" & TextBox3.Text & "', Gender = '" & ComboBox2.Text & "', Birthdate = '" & DateTimePicker1.Text & "', Nationality = '" & TextBox4.Text & "', Address = '" & TextBox5.Text & "', ContactNumStudent = '" & TextBox6.Text & "', GuardianName = '" & TextBox7.Text & "', ContactNumGuardian = '" & TextBox8.Text & "', where StudentNumber ='" & TextBox11.Text & "'", cn)
cmd.ExecuteNonQuery()
cn1.Close()
End If
.Update()
Clear()
End With
DisplayList()
LinkLabel1.Visible = False
ComboBox2.SelectedIndex = -1
DateTimePicker1.Value = Date.Now
End Sub
Private Sub BtnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnEdit.Click
ListView1.Enabled = True
TextBox11.Enabled = True
TextBox11.ReadOnly = False
btnAdd.Enabled = False
btnSave.Enabled = True
BtnCancel.Enabled = True
BtnEdit.Enabled = False
LinkLabel1.Visible = True
If TextBox11.Text.Length > 0 Then
If BtnEdit.Text.ToLower() = "edit" Then
Me.Text = ListView1.FocusedItem.Text
LblAdd_Edit.Text = "Edit"
btnAdd.Enabled = False
btnSave.Enabled = True
BtnCancel.Enabled = True
BtnEdit.Enabled = False
GroupBox1.Enabled = True
TextBox11.Focus()
ElseIf LinkLabel1.Enabled = True Then
Dim cn1 As OleDbConnection
Dim cmd As OleDbCommand
cn1 = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\edenzam\Desktop\CBFMNHS Enrollment System\CBFMNHS Enrollment System\bin\Debug\Enrollment System.accdb;Persist Security Info=False;")
cn1.Open()
cmd = New OleDbCommand("UPDATE RegAndVer SET FirstName = '" & TextBox1.Text & "', LastName = '" & TextBox2.Text & "', MiddleName = '" & TextBox3.Text & "', Gender = '" & ComboBox2.Text & "', Birthdate = '" & DateTimePicker1.Text & "', Nationality = '" & TextBox4.Text & "', Address = '" & TextBox5.Text & "', ContactNumStudent = '" & TextBox6.Text & "', GuardianName = '" & TextBox7.Text & "', ContactNumGuardian = '" & TextBox8.Text & "', where StudentNumber ='" & TextBox11.Text & "'", cn)
cmd.ExecuteNonQuery()
cn1.Close()
End If
Else : MsgBox("Please select record or click search student number to edit.", MsgBoxStyle.Information, "Message")
BtnEdit.Enabled = True
End If
End Sub
Sub Clear()
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
ComboBox2.Text = ""
DateTimePicker1.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox11.Text = ""
btnAdd.Enabled = True
btnSave.Enabled = False
BtnCancel.Enabled = False
BtnEdit.Enabled = True
GroupBox1.Enabled = False
End Sub
Sub DisplayList()
Dim lst
ListView1.Items.Clear()
rs = New ADODB.Recordset
With rs
.Open("select * from RegAndVer", cn, 2, 3)
Do While Not .EOF
lst = New ListViewItem
lst = ListView1.Items.Add(.Fields("StudentNumber").Value)
lst.SubItems.Add(.Fields("FirstName").Value)
lst.SubItems.Add(.Fields("LastName").Value)
lst.SubItems.Add(.Fields("MiddleName").Value)
lst.SubItems.Add(.Fields("Gender").Value)
lst.SubItems.Add(.Fields("Birthdate").Value)
lst.SubItems.Add(.Fields("Nationality").Value)
lst.SubItems.Add(.Fields("Address").Value)
lst.SubItems.Add(.Fields("ContactNumStudent").Value)
lst.SubItems.Add(.Fields("GuardianName").Value)
lst.SubItems.Add(.Fields("ContactNumGuardian").Value)
.MoveNext()
Loop
End With
End Sub
Private Sub ListView1_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseClick
rs = New ADODB.Recordset
With rs
.Open("Select * from RegAndVer where StudentNumber like '" & ListView1.FocusedItem.Text & "'", cn, 2, 3)
TextBox11.Text = .Fields("StudentNumber").Value
TextBox1.Text = .Fields("FirstName").Value
TextBox2.Text = .Fields("LastName").Value
TextBox3.Text = .Fields("MiddleName").Value
ComboBox2.Text = .Fields("Gender").Value
DateTimePicker1.Text = .Fields("Birthdate").Value
TextBox4.Text = .Fields("Nationality").Value
TextBox5.Text = .Fields("Address").Value
TextBox6.Text = .Fields("ContactNumStudent").Value
TextBox7.Text = .Fields("GuardianName").Value
TextBox8.Text = .Fields("ContactNumGuardian").Value
GroupBox1.Enabled = True
TextBox11.Enabled = False
End With
End Sub
Private Sub maintenance_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
connection()
btnAdd.Enabled = True
btnSave.Enabled = True
BtnCancel.Enabled = True
BtnEdit.Enabled = True
ListView1.Enabled = False
End Sub
Private Sub BtnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCancel.Click
Clear()
LinkLabel1.Visible = False
ComboBox2.SelectedIndex = -1
DateTimePicker1.Value = Date.Now
End Sub
Private Sub LinkLabel1_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles LinkLabel1.LinkClicked
BtnCancel.Enabled = True
btnAdd.Enabled = False
BtnEdit.Enabled = False
TextBox11.Enabled = True
TextBox11.ReadOnly = False
ListView1.Enabled = False
If TextBox11.TextLength = 9 Then
GroupBox1.Enabled = True
con = New OleDbConnection("Provider= Microsoft.ACE.oledb.12.0; Data Source=C:\Users\edenzam\Desktop\CBFMNHS Enrollment System\CBFMNHS Enrollment System\bin\Debug\Enrollment System.accdb")
Dim sql As String = "Select * from RegAndVer where StudentNumber = '" & TextBox11.Text & "'"
com = New OleDbCommand(sql, con)
con.Open()
com.ExecuteNonQuery()
rid = com.ExecuteReader
rid.Read()
If (rid.HasRows) Then
TextBox1.Text = rid(3)
TextBox2.Text = rid(4)
TextBox3.Text = rid(5)
ComboBox2.Text = rid(6)
DateTimePicker1.Text = rid(7)
TextBox4.Text = rid(8)
TextBox5.Text = rid(9)
TextBox6.Text = rid(10)
TextBox7.Text = rid(11)
TextBox8.Text = rid(12)
TextBox11.Enabled = False
ListView1.Enabled = False
btnSave.Enabled = True
If LinkLabel1.Enabled = True Then
Dim cn As OleDbConnection
Dim cmd As OleDbCommand
cn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\edenzam\Desktop\CBFMNHS Enrollment System\CBFMNHS Enrollment System\bin\Debug\Enrollment System.accdb;Persist Security Info=False;")
cn.Open()
cmd = New OleDbCommand("UPDATE RegAndVer SET FirstName = '" & TextBox1.Text & "', LastName = '" & TextBox2.Text & "', MiddleName = '" & TextBox3.Text & "', Gender = '" & ComboBox2.Text & "', Birthdate = '" & DateTimePicker1.Text & "', Nationality = '" & TextBox4.Text & "', Address = '" & TextBox5.Text & "', ContactNumStudent = '" & TextBox6.Text & "', GuardianName = '" & TextBox7.Text & "', ContactNumGuardian = '" & TextBox8.Text & "', where StudentNumber ='" & TextBox11.Text & "'", cn)
'cmd.ExecuteNonQuery()
cn.Close()
btnAdd.Enabled = False
btnSave.Enabled = True
BtnCancel.Enabled = True
BtnEdit.Enabled = False
GroupBox1.Enabled = True
End If
End If
ElseIf TextBox11.Text = "" Then
MsgBox("Input Student Number", MsgBoxStyle.Critical, "Error")
Else
MsgBox("Incorrect Student Number", MsgBoxStyle.Critical, "Error")
TextBox11.Clear()
End If
End Sub
Private Sub ListView1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListView1.SelectedIndexChanged
Me.Text = ListView1.FocusedItem.Text
LinkLabel1.Visible = False
BtnEdit.Enabled = False
End Sub
Attached