08-Retrieve and Update Academic Year

This tutorial is about Retrieve and Update Academic Year.
This time, I’m going to retrieve and update all the Academic Year in Ms Access Database and there are some changes to the design of the Form.

Let’s begin:

Open the project which is “StudentInformation“. After that, add two Buttons and DataGridView inside the GroupBox in the Form. It will look like this.

Now, double click the “Load” Button inside the academic year GroupBox and do this method for retrieving the academic year in Ms Access Database that will display in the DatagridView.
[vbnet]

Private Sub dtnsyload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtnsyload.Click
Try
‘opening the connection
con.Open()
‘store a query to a variable that you have declared.
query = “SELECT * FROM tblsy”
‘set a query and a connection string to a class that you have declared.
da = New OleDb.OleDbDataAdapter(query, con)
dt = New DataTable
‘Filling the data in the datatable
da.Fill(dt)
‘Get or set the datasource of a datagridview to display the data that came from the database.
dtgsylist.DataSource = dt

Catch ex As Exception
‘catching error
MsgBox(ex.Message)
End Try
con.Close()
End Sub
[/vbnet]

After that, go back to the Form Design and click the DataGridView. After clicking it, go to the properties, click the event that looks like a lightning and scroll down to find the “DoubleClick” event handler.

In the event handler of the DataGridView, do this following code for passing the data from a DataGridView to the TextBox.
[vbnet]
Dim syid As Integer = 0
Private Sub dtgsylist_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtgsylist.DoubleClick
Try
‘passing the value to a variable
syid = dtgsylist.CurrentRow.Cells(0).Value
‘passing the value to a textbox
txtyl.Text = dtgsylist.CurrentRow.Cells(1).Value
Catch ex As Exception
‘catching error
MsgBox(ex.Message)
End Try
End Sub
[/vbnet]

And Lastly, these are the codes for updating the “Academic Year” in Ms Access Database.
[vbnet]
Private Sub btnsyupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsyupdate.Click
Try
‘opening the connection
con.Open()
‘store a query to a variable that you have declared.
query = “UPDATE tblsy SET SY = ‘” & txtyl.Text & “‘ WHERE SYID =” & levelid & “”
‘set a query and a connection string to a class that you have declared.
da = New OleDb.OleDbDataAdapter(query, con)
dt = New DataTable
‘Filling the data in the datatable
da.Fill(dt)

MsgBox(“School Year has been updated.”)
txtsy.Clear()
syid = 0
Catch ex As Exception
‘catching error
MsgBox(ex.Message)
End Try
con.Close()
End Sub
[/vbnet]

Readers might read also:

• 09-Removing Academic Year
• 109 – Update Statement Using Visual Basic Application for Managing Payroll System Module