Question maintenance

[jhedonghae]

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[h=4][/h]

 

[jmcilhinney]

First up, the chances of anyone reading all that code, especially unformatted, is very, very small. Secondly, anyone doing so really doesn't know what they're looking for. You need to put a lot more thought into your posts. For a start, if an exception is thrown then the IDE shows you where and tells you what happened as an aid to fixing the problem. You have done neither for us. Please post only the relevant code, format it and tell us at least what the error message was. That will give you a much better chance of getting the help you want.

 

[jhedonghae]

First up, the chances of anyone reading all that code, especially unformatted, is very, very small. Secondly, anyone doing so really doesn't know what they're looking for. You need to put a lot more thought into your posts. For a start, if an exception is thrown then the IDE shows you where and tells you what happened as an aid to fixing the problem. You have done neither for us. Please post only the relevant code, format it and tell us at least what the error message was. That will give you a much better chance of getting the help you want.

sorry about that..i have attach an image of the error...

 

[jmcilhinney]

Urch! Why are you using ADO in VB.NET? If you're using .NET then you should use .NET, which means ADO.NET for data access. Some people use ADO because they are comfortable with it but that error tells me that you're no ADO expert, so there's no reason to be using it in preference to ADO.NET. I've never used VB6 or a Recordset in my life and I know what that error means.

BOF is "beginning of file" and EOF is "end of file", so that's telling you that the cursor is either located before the first record or after the last. For it to be after the last there would have to be no records in the result set. I'm guessing that there are records and you haven't actually advanced to the first yet. I don't know for sure but I'm guessing that you have to call Read or something like that on the Recordset first in order to advance to the first record. You shouldn't even bother finding out though. You should switch to ADO.NET and either use a data adapter or a data reader.

 

[jhedonghae]

well i just found some of this codes in the net and adopt it to my system.. and yes i'm really not expert with ADO and don't have an idea about it that's why i'm having problems

 

[jmcilhinney]

Dump ADO and rewrite your data access code using ADO.NET. You should start by following the Data Walkthroughs link in my signature.

 

[jhedonghae]

ok i'll try..tnx for the advice

 

[jhedonghae]

well i don't need to dumb ADOD..my problem is solved..

 

[jmcilhinney]

I assume that you mean that you don't need to dump ADO. That is certainly true but you absolutely should. You're doing yourself a big disservice if you don't. Your choice though. I certainly won't be wasting my time helping people who insist on using technologies that have been obsolete for 10 years though.

 

[jhedonghae]

I assume that you mean that you don't need to dump ADO. That is certainly true but you absolutely should. You're doing yourself a big disservice if you don't. Your choice though. I certainly won't be wasting my time helping people who insist on using technologies that have been obsolete for 10 years though.

thank you for your concern but someone help me solved this problem already