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