Working with databases (OleDb, MS Access 2007) code-wise (SELECT COMMAND)

When learning how to work with databases in college / highschool they often teach people how to do it with the wizard from Visual Studio x

I always found that this method doesn’t give me the desired amount of control i’d like to have over my application, so i’ve always done it code wise.
Yes, it’s a bit of extra work, but it’s worth it.

If you plan on doing this, there will always be a set of objects you’ll need in order to work with a database:

1. A database connection
2. A SQL command
3. For reading data (SELECT) a DataReader

I’m not going to handle working with datasets & gridviews in this post, you can view a small example of that here: Fill a DataGridView from a DataSet (code-wise)

We’ll start off by defining the objects we need:

Dim oCon As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MyDatabase.accdb;Persist Security Info=False;")
Dim oCommand As New OleDb.OleDbCommand("", con)
Dim oDR As OleDb.OleDbDataReader

There we go, we got our connection, command and datareader.
We’ll start off with selecting data from our database using the SELECT command:

'Code example to read data from a table and place it inside textboxes
'Let's say we have a form with 2 textfields txtName and txtFirstName, and our table has 2 columns Name and FirstName
Try
	'Set the query
	oCommand.CommandText = "SELECT * FROM MyTable WHERE Name = 'MyName'"

	'Open connection
	oCon.Open()

	'Set the datareader
	oDR = oCommand.ExecuteReader

	'Get items while reading
	While oDR.Read
		txtName.text = oDR("Name").ToString			'You can also work with column indexes -> oDR(0).ToString
		txtFirstName.text = oDR("FirstName").ToString		'You can also work with column indexes -> oDR(1).ToString
	End While

	'Close connection
	oCon.Close()

Catch ex As OleDb.OleDbException
	'Show oledb exception
	MessageBox.Show(ex.ToString)
End Try

What this little snippet will do is select the row of where Name = ‘MyName’ and display the data for that row in 2 textfields (Ofcourse, if there are multiple people with the same name in your database you’ll prefer to be working with unique ID’s etc because you’ll get data from the last row read by the datareader)

There we go, that’s pretty much it.