Imports System.Data.OleDb

' Zombie Database Example
'   ZombieNum (INT)     ZombieType (String)     ZombieHealth (INT)      ZombieDescription (String)
'   301                   Zombie                  10                        This zombie loves brains. Can't get...
'   807                   Bucket Head Zombie      30                        Bucket Head Zombie always wore a bucket...
'   22                    Cone Head Zombie        15                        Cone Head Zombie shuffled mindlessly forward...
'   9000                  Imp                     5                         Imp may be small, but he's wiry. He's ...
' ... etc

Public Class Form1
    ' Here we declare some object-level variables for use throughout out program. Much easier than re-typing things out.

    Dim dataBaseName As String = "ZombieList.mdb" ' The name of our database file.
    Dim tableName As String = "Zombies" ' The name of the table to read from.

    ' The connection string for accessing the database file, used in the OleDbConnection constructor.
    Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dataBaseName

    Private Sub btnSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelect.Click
        Try

            ' Creates a connection to the specified database specified earlier in the code. ZombieList.mdb in this case
            Dim connection As New OleDbConnection(strConnection)

            ' Here we need to use a SELECT statement to grab all rows in the table that match the ZombieNum.
            ' The syntax for the SELECT statement is
            ' SELECT fields FROM table_name WHERE field = value
            '
            ' SELECT * selects all fields, normally you could limit it to certain data using a list, such as (ZombieType, ZombieHealth)
            ' But for a small table like this, it's not needed for efficiency.
            '
            ' The WHERE clause tells the database to only return the rows in which certain values are matched.
            ' In this case, only rows where the ZombieNum matches the one on the form is matched.
            ' If the database is properly managed, this should return one row.
            Dim command As New OleDbCommand("SELECT * FROM " & tableName & " WHERE ZombieNum = " & nudZombieNum.Value, connection)

            ' The .Open() method of connection actually opens the stream to the database file to read and write data.
            connection.Open()

            ' The .ExecuteReader property of the OleDbCommand class both writes the command to the file, and gets a reader object.
            ' The reader object can be used to read information about the returned rows from the command.
            Dim reader As OleDbDataReader = command.ExecuteReader

            ' The .Read() method moves the row pointer down one (to the first result in this case), to grab values from each row.
            reader.Read()

            ' The .Item(String column) method simply gets the value for a specific column in the table for this row.
            ' Which we can use to set the value. Remembering to parse the Health (an INT) to an Integer, Val() can also be used here.
            txtZombieType.Text = reader.Item("ZombieType")
            nudZombieHealth.Value = Integer.Parse(reader.Item("ZombieHealth"))
            lblZombieDescription.Text = reader.Item("ZombieDescription")

            ' It is important that we use the .Close() method on the connection, as VB.NET does not automatically cut off the stream.
            ' Leaving it open could lead to data corruption or a lack of efficiency (excess memory used in our program for no reason).
            connection.Close()
        Catch ex As Exception
            ' If we encounter any errors accessing the table or database, they will be displayed in a MessageBox.
            ' The .Message() property of an exception contains the text that describes the problem.
            MessageBox.Show(ex.Message)
        End Try
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Try
            ' Similar to the btnSelect_Click() method, we need to open a connection to the database.
            Dim connection As New OleDbConnection(strConnection)

            ' This time, we are using an UPDATE command, which has a syntax of
            ' UPDATE table_name SET field = new_value WHERE other_field = value
            '
            ' Like the WHERE clause in the SELECT statement, this one tells the Command to only affect rows with the specific ZombieNum.
            '
            ' The SET statement sets the value of a field, ZombieHealth here, to a new value, such as the one in our NumericUpDown.
            Dim command As New OleDbCommand("UPDATE " & tableName & " SET ZombieHealth = " & nudZombieHealth.Value & " WHERE ZombieNum = " & nudZombieNum.Value, connection)

            ' Once again, we need to Open() the connection to the database file for access.
            connection.Open()

            ' We are going to retrieve the reader once more, but in this case, that is all we will do.
            ' UPDATE returns no rows of significant importance for the purpose of this program.
            Dim reader As OleDbDataReader = command.ExecuteReader

            ' Once again, .Close() the connection for safety purposes.
            connection.Close()
        Catch ex As Exception
            ' Display any errors with our SQL connection.
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class