Imports System.Data.OleDb
Public Class Form1
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As New OleDb.OleDbDataAdapter
Dim sql As String
Dim conString As String
Dim path As String
Dim inc As Integer
Dim maxrows As Integer
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Try
path = "C:\Users\JoshuaNieves\Desktop\Sample\WindowsApplication1\WindowsApplication1\bin\Debug\studentsgrades.accdb"
conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path
con = New OleDbConnection(conString)
con.Open()
sql = "select * from grades"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "studentsgrades")
con.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
NavigateRecords()
DataGridView1.DataSource = ds.Tables("studentsgrades")
End Sub
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
savegrades()
loadGridView()
End Sub
Private Sub savegrades()
Try
If con.State = ConnectionState.Closed Then
con.Open()
End If
If txtFullName.Text <> "" And txtCourseYear.Text <> "" And txtSubject.Text <> "" And txtPrelimExam.Text <> "" And txtPrelimExamRating.Text <> "" And txtClassStanding.Text <> "" And txtPrelimGrade.Text <> "" And txtRemarks.Text <> "" Then
sql = "insert into grades([Full_Name], [Course_Year], [Subject], [PrelimExam], [Prelim_ExamRating], [ClassStanding], [PrelimGrade], [Remarks]" & _
"values(?,?,?,?,?,?,?,?)"
Dim cmd As OleDbCommand = New OleDbCommand(sql, con)
cmd.Parameters.Add(New OleDbParameter("Full_Name", CType(txtFullName.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Course_Year", CType(txtCourseYear.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Subject", CType(txtSubject.Text, String)))
cmd.Parameters.Add(New OleDbParameter("PrelimExam", CType(txtPrelimExam.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Prelim_ExamRating", CType(txtPrelimExamRating.Text, String)))
cmd.Parameters.Add(New OleDbParameter("ClassStanding", CType(txtClassStanding.Text, String)))
cmd.Parameters.Add(New OleDbParameter("PrelimGrade", CType(txtPrelimGrade.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Remarks", CType(txtRemarks.Text, String)))
Try
cmd.ExecuteNonQuery()
cmd.Dispose()
con.Close()
MessageBox.Show("Data Inserted")
clearFields()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Else
MessageBox.Show("Fill All Fields")
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
btnAddNew.Enabled = True
btnDelete.Enabled = True
btnEdit.Enabled = True
End Sub
Private Sub clearFields()
txtID.Text = ""
txtFullName.Text = ""
txtCourseYear.Text = ""
txtSubject.Text = ""
txtPrelimExam.Text = ""
txtPrelimExamRating.Text = ""
txtClassStanding.Text = ""
txtPrelimGrade.Text = ""
txtRemarks.Text = ""
End Sub
Private Sub editGrades()
Try
If con.State = ConnectionState.Closed Then
con.Open()
End If
If txtID.Text <> "" And txtFullName.Text <> "" And txtCourseYear.Text <> "" And txtSubject.Text <> "" And txtPrelimExam.Text <> "" And txtPrelimExamRating.Text <> "" And txtClassStanding.Text <> "" And txtPrelimGrade.Text <> "" And txtRemarks.Text <> "" Then
sql = "update grades set Full_Name = '" & txtFullName.Text & "'," & _
"Course_Year = '" & txtCourseYear.Text & "', " & "Subject = '" & txtSubject.Text & "', " & _
"PrelimExam = '" & txtPrelimExam.Text & "', " & "Prelim_ExamRating = '" & txtPrelimExamRating.Text & "'. " & _
"ClassStanding = '" & txtClassStanding.Text & "', " & "PrelimGrade = '" & txtPrelimGrade.Text & "', " & _
"Remarks = '" & txtRemarks.Text & "'" & " where ID = " & CInt(txtID.Text) & ""
Dim cmd As OleDbCommand = New OleDbCommand(sql, con)
Try
cmd.ExecuteNonQuery()
cmd.Dispose()
con.Close()
MessageBox.Show("Update Successful")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Else
MessageBox.Show("One or more fields are left blank")
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Private Sub loadGridview()
DataGridView1.DataSource.Clear()
Try
If con.State = ConnectionState.Closed Then
con.Open()
End If
sql = "select * from grades"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "studentsgrades")
con.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
DataGridView1.DataSource = ds.Tables("studentsgrades")
End Sub
Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
editGrades()
loadGridview()
End Sub
Private Sub deleteGrades()
Try
If con.State - ConnectionState.Closed Then
con.Open()
End If
If MessageBox.Show("Do you want to delete this record?", "Delete",
MessageBoxButtons.YesNoCancel, MessageBoxIcon.Warning) = DialogResult.No Then
MessageBox.Show("Operation Cancelled")
Exit Sub
ElseIf txtID.Text <> "" Then
sql = "delete from grades where ID = " & CInt(txtID.Text)
Dim cmd As OleDbCommand = New OleDbCommand(sql, con)
Try
cmd.ExecuteNonQuery()
cmd.Dispose()
con.Close()
MessageBox.Show("Delete Successful")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
deleteGrades()
loadGridview()
clearFields()
End Sub
Private Sub btnAddNew_Click(sender As Object, e As EventArgs) Handles btnAddNew.Click
clearFields()
btnAddNew.Enabled = False
btnDelete.Enabled = False
btnEdit.Enabled = False
End Sub
Private Sub NavigateRecords()
txtID.Text = ds.Tables("studentsgrades").Rows(inc).Item(0) <--- Ayan po yung problema
txtFullName.Text = ds.Tables("studentsgrades").Rows(inc).Item(1)
txtCourseYear.Text = ds.Tables("studentsgrades").Rows(inc).Item(2)
txtSubject.Text = ds.Tables("studentsgrades").Rows(inc).Item(3)
txtPrelimExam.Text = ds.Tables("studentsgrades").Rows(inc).Item(4)
txtPrelimExamRating.Text = ds.Tables("studentsgrades").Rows(inc).Item(5)
txtClassStanding.Text = ds.Tables("studentsgrades").Rows(inc).Item(6)
txtPrelimGrade.Text = ds.Tables("studentsgrades").Rows(inc).Item(7)
txtRemarks.Text = ds.Tables("studentsgrades").Rows(inc).Item(8)
maxrows = ds.Tables("studentsgrades").Rows.Count
End Sub
Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click
If inc <> 0 Then
inc = 0
NavigateRecords()
End If
End Sub
Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
If inc <> maxrows - 1 Then
inc = inc + 1
NavigateRecords()
Else
MessageBox.Show("No Records")
End If
End Sub
Private Sub btnPrev_Click(sender As Object, e As EventArgs) Handles btnPrev.Click
If inc <> 0 Then
inc = inc - 1
NavigateRecords()
Else
MessageBox.Show("No Records")
End If
End Sub
Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click
Try
If inc <> maxrows - 1 Then
inc = maxrows - 1
NavigateRecords()
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
End Class
Public Class Form1
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As New OleDb.OleDbDataAdapter
Dim sql As String
Dim conString As String
Dim path As String
Dim inc As Integer
Dim maxrows As Integer
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Try
path = "C:\Users\JoshuaNieves\Desktop\Sample\WindowsApplication1\WindowsApplication1\bin\Debug\studentsgrades.accdb"
conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path
con = New OleDbConnection(conString)
con.Open()
sql = "select * from grades"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "studentsgrades")
con.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
NavigateRecords()
DataGridView1.DataSource = ds.Tables("studentsgrades")
End Sub
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
savegrades()
loadGridView()
End Sub
Private Sub savegrades()
Try
If con.State = ConnectionState.Closed Then
con.Open()
End If
If txtFullName.Text <> "" And txtCourseYear.Text <> "" And txtSubject.Text <> "" And txtPrelimExam.Text <> "" And txtPrelimExamRating.Text <> "" And txtClassStanding.Text <> "" And txtPrelimGrade.Text <> "" And txtRemarks.Text <> "" Then
sql = "insert into grades([Full_Name], [Course_Year], [Subject], [PrelimExam], [Prelim_ExamRating], [ClassStanding], [PrelimGrade], [Remarks]" & _
"values(?,?,?,?,?,?,?,?)"
Dim cmd As OleDbCommand = New OleDbCommand(sql, con)
cmd.Parameters.Add(New OleDbParameter("Full_Name", CType(txtFullName.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Course_Year", CType(txtCourseYear.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Subject", CType(txtSubject.Text, String)))
cmd.Parameters.Add(New OleDbParameter("PrelimExam", CType(txtPrelimExam.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Prelim_ExamRating", CType(txtPrelimExamRating.Text, String)))
cmd.Parameters.Add(New OleDbParameter("ClassStanding", CType(txtClassStanding.Text, String)))
cmd.Parameters.Add(New OleDbParameter("PrelimGrade", CType(txtPrelimGrade.Text, String)))
cmd.Parameters.Add(New OleDbParameter("Remarks", CType(txtRemarks.Text, String)))
Try
cmd.ExecuteNonQuery()
cmd.Dispose()
con.Close()
MessageBox.Show("Data Inserted")
clearFields()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Else
MessageBox.Show("Fill All Fields")
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
btnAddNew.Enabled = True
btnDelete.Enabled = True
btnEdit.Enabled = True
End Sub
Private Sub clearFields()
txtID.Text = ""
txtFullName.Text = ""
txtCourseYear.Text = ""
txtSubject.Text = ""
txtPrelimExam.Text = ""
txtPrelimExamRating.Text = ""
txtClassStanding.Text = ""
txtPrelimGrade.Text = ""
txtRemarks.Text = ""
End Sub
Private Sub editGrades()
Try
If con.State = ConnectionState.Closed Then
con.Open()
End If
If txtID.Text <> "" And txtFullName.Text <> "" And txtCourseYear.Text <> "" And txtSubject.Text <> "" And txtPrelimExam.Text <> "" And txtPrelimExamRating.Text <> "" And txtClassStanding.Text <> "" And txtPrelimGrade.Text <> "" And txtRemarks.Text <> "" Then
sql = "update grades set Full_Name = '" & txtFullName.Text & "'," & _
"Course_Year = '" & txtCourseYear.Text & "', " & "Subject = '" & txtSubject.Text & "', " & _
"PrelimExam = '" & txtPrelimExam.Text & "', " & "Prelim_ExamRating = '" & txtPrelimExamRating.Text & "'. " & _
"ClassStanding = '" & txtClassStanding.Text & "', " & "PrelimGrade = '" & txtPrelimGrade.Text & "', " & _
"Remarks = '" & txtRemarks.Text & "'" & " where ID = " & CInt(txtID.Text) & ""
Dim cmd As OleDbCommand = New OleDbCommand(sql, con)
Try
cmd.ExecuteNonQuery()
cmd.Dispose()
con.Close()
MessageBox.Show("Update Successful")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Else
MessageBox.Show("One or more fields are left blank")
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Private Sub loadGridview()
DataGridView1.DataSource.Clear()
Try
If con.State = ConnectionState.Closed Then
con.Open()
End If
sql = "select * from grades"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "studentsgrades")
con.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
DataGridView1.DataSource = ds.Tables("studentsgrades")
End Sub
Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
editGrades()
loadGridview()
End Sub
Private Sub deleteGrades()
Try
If con.State - ConnectionState.Closed Then
con.Open()
End If
If MessageBox.Show("Do you want to delete this record?", "Delete",
MessageBoxButtons.YesNoCancel, MessageBoxIcon.Warning) = DialogResult.No Then
MessageBox.Show("Operation Cancelled")
Exit Sub
ElseIf txtID.Text <> "" Then
sql = "delete from grades where ID = " & CInt(txtID.Text)
Dim cmd As OleDbCommand = New OleDbCommand(sql, con)
Try
cmd.ExecuteNonQuery()
cmd.Dispose()
con.Close()
MessageBox.Show("Delete Successful")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
deleteGrades()
loadGridview()
clearFields()
End Sub
Private Sub btnAddNew_Click(sender As Object, e As EventArgs) Handles btnAddNew.Click
clearFields()
btnAddNew.Enabled = False
btnDelete.Enabled = False
btnEdit.Enabled = False
End Sub
Private Sub NavigateRecords()
txtID.Text = ds.Tables("studentsgrades").Rows(inc).Item(0) <--- Ayan po yung problema
txtFullName.Text = ds.Tables("studentsgrades").Rows(inc).Item(1)
txtCourseYear.Text = ds.Tables("studentsgrades").Rows(inc).Item(2)
txtSubject.Text = ds.Tables("studentsgrades").Rows(inc).Item(3)
txtPrelimExam.Text = ds.Tables("studentsgrades").Rows(inc).Item(4)
txtPrelimExamRating.Text = ds.Tables("studentsgrades").Rows(inc).Item(5)
txtClassStanding.Text = ds.Tables("studentsgrades").Rows(inc).Item(6)
txtPrelimGrade.Text = ds.Tables("studentsgrades").Rows(inc).Item(7)
txtRemarks.Text = ds.Tables("studentsgrades").Rows(inc).Item(8)
maxrows = ds.Tables("studentsgrades").Rows.Count
End Sub
Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click
If inc <> 0 Then
inc = 0
NavigateRecords()
End If
End Sub
Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
If inc <> maxrows - 1 Then
inc = inc + 1
NavigateRecords()
Else
MessageBox.Show("No Records")
End If
End Sub
Private Sub btnPrev_Click(sender As Object, e As EventArgs) Handles btnPrev.Click
If inc <> 0 Then
inc = inc - 1
NavigateRecords()
Else
MessageBox.Show("No Records")
End If
End Sub
Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click
Try
If inc <> maxrows - 1 Then
inc = maxrows - 1
NavigateRecords()
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
End Class