Why is my form not loading data from .mdb file?

Question

This is a homework assignment that I am working on. I have an .mdb file that is supposed to connect to my application and then I should be able to navigate among the records. The database is connected as a data source. But, when I run the application no data is populated and I get a IndexOutOfRangeException was unhandled error when I press a button on my toolstrip. I have tried to ask for assistance from the professor but she has been non-existent all semester. What am I doing wrong here? I'm only looking for help for where to focus my attention so that I can figure this out on my own.

Public Class Form1

Dim strMemoryConnection As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " &
    Application.StartupPath & "\memory.mdb"
Dim strSQLMem As String
Dim dtMem As New DataTable()
Dim intTotalRows As Integer
Dim intCurrentRow As Integer

Private Sub displayRecord()
    Me.txtTitle.Text = CStr(dtMem.Rows(intCurrentRow)("title"))
    Me.txtAuthor.Text = CStr(dtMem.Rows(intCurrentRow)("author"))
    Me.txtPublisher.Text = CStr(dtMem.Rows(intCurrentRow)("publisher"))
    Me.txtStuff.Text = CStr(dtMem.Rows(intCurrentRow)("stuff"))
End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventHandler)
    dtMem.Clear()
    strSQLMem = "SELECT * FROM Memory"
    Dim dataAdapter As New OleDb.OleDbDataAdapter(strSQLMem, strMemoryConnection)
    dataAdapter.Fill(dtMem)
    dataAdapter.Dispose()
    intTotalRows = dtMem.Rows.Count
    intCurrentRow = 0
    displayRecord()
End Sub

#Region "Tool Strip Button Clicks"
Private Sub btnTop_Click(sender As Object, e As EventArgs) Handles btnTop.Click
    intCurrentRow = 1
    displayRecord()
End Sub

Private Sub btnPrev_Click(sender As Object, e As EventArgs) Handles btnPrev.Click
    intCurrentRow = intCurrentRow - 1
    If intCurrentRow < 0 Then
        intCurrentRow = 1
    End If
    displayRecord()
End Sub

Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
    intCurrentRow = intTotalRows + 1
    If intCurrentRow = intTotalRows Then
        intCurrentRow = intTotalRows - 1
    End If
    displayRecord()
End Sub

Private Sub btnBot_Click(sender As Object, e As EventArgs) Handles btnBot.Click
    intCurrentRow = intTotalRows - 1
    displayRecord()
End Sub
#End Region
End Class

Make sure you are compiling 32 bit. Move that Load event code to the form's constructor. The load event can hide exceptions sometimes. — LarsTech, Apr 09 '17 at 14:10
@SLaks I have ran the debugger. It says that there is no row at position 1. This makes me think that the database is not loaded properly. — T4RH33L, Apr 09 '17 at 14:20
I'm guessing `btnTop` should also be setting `intCurrentRow` to 0 not 1. 0 will be the first record. As for the issue, is there actually any data in `Memory`? — Bugs, Apr 09 '17 at 14:24
@Bugs Yes, there is data in the database. I used the New Data Source wizard to connect and I can view the data. Could this cause a conflict because I'm calling the data source within my code? — T4RH33L, Apr 09 '17 at 14:34
Are there no errors? I mean an alternative way instead of using a `OleDbDataAdapter` is to use an `OleDbCommand`. You could then do `dt.Load(command.ExecuteReader())`. This is how I read data from a database into a `DataTable`. You should also look at implementing `Using`...`Using con As New OleDbConnection(strMemoryConnection), Using command As New OleDbCommand(strSQLMem, con)...End Using` something like that. — Bugs, Apr 09 '17 at 14:37
@T4RH33L I've an answer [here](http://stackoverflow.com/a/42021399/6375113) which uses MySQL but same concept applies. — Bugs, Apr 09 '17 at 15:00
@bugs I set up my connection and command like you suggest. I'm getting the same problem where it says that there is no row at position 1. I really think that the problem is how I'm pointing to my database. — T4RH33L, Apr 09 '17 at 15:19
I'm not sure when it comes to the database connection. It's been a while since I've used Access but I'm sure there's a site called connectionstrings.com which may help — Bugs, Apr 09 '17 at 15:39

score 0 · Accepted Answer · answered Apr 09 '17 at 23:47

In the end, it was as I expected. The data was not loading properly. I finally realized that the Form1_Load argument was not correct.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventHandler)

Needed to be:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

I will be letting my professor and fellow classmates know that this is incorrect.

Thanks to Bugs for the troubleshooting. At the least, I now know how to create a SQL connection very easily. I also appreciate whomever downvoted my question and their help in figuring this out.

Why is my form not loading data from .mdb file?

1 Answers1