Visual Studio Professional 2012 says dataset is not a member of my form

Question

I have been working on a vb program in Visual Studio that begins with a login form. I have stored the usernames and passwords for 22 people into an Access database. My plan was, once the user enters their username and password into the respective textboxes, they would click the "Login" button and that would initiate the code needed to check the text in the textbox with the information in the database.

I loaded the database into the Solution Explorer using the Data Source Configuration Wizard and it created a dataset as far as I can tell. When I click on the dataset in the Solution Explorer, it shows the appropriate query. When I click to preview the data, it even shows the correct information.

The problem comes in the code itself on the login form. I entered the following code into the "Login" button event handler:

Private Sub btnLoginSubmit_Click(sender As Object, e As EventArgs) Handles btnLoginSubmit.Click
    Dim row As TutorAccountDataSet.TutorsRow 'Declares the row variable
    Dim strUsername(21) As String 'Declares the username array
    Dim strPassword(21) As String 'Declares the password array
    Dim intLoginCounter As Integer = 0 'Declares the variable for counting the loop cycles

    For Each row In Me.TutorAccountDataSet.Tutors.Rows 'Loop goes through each row in the dataset and loads the username column into the array
        strUsername(intLoginCounter) = row.Username
        intLoginCounter += 1
    Next
End Sub

In the 7th line (where I start the loop), Visual Studio gives me the following error:

TutorAccountDataSet' is not a member of 'Tutor_Training.frmLogin'.

I did do some research but I wasn't exactly sure if the suggestions given to people with similar (yet very different) problems would apply to this. I thought I might need to call the Fill method for this, but that also results in the same error when it is included in the code.

Does anyone know why this error is occurring and how to fix it?

Answer 1

First of all, as you stated that you only need the content of a single table, I would recommend to fetch a DataTable instead of a DataSet from the DataBase.

I would also suggest to create and populate this DataTable programatically instead of using the VS-wizard. By doing so, you gain full control of where and when your DataTable will be created and disposed again.

Below you'll find a heavily commented method you can add to your program. Adjust the connectionstring to fit your connection (see connectionstrings.com for reference) and call the method to create your DataTable which can be used as an object for iteration and vice versa then.

Imports System.Data
Imports System.Data.OleDb

Private Function GetDataTableFromAccess(query As String) As DataTable
    '   local variables
    Dim Con As OleDbConnection
    Dim DAdapter As OleDbDataAdapter
    Dim Command As OleDbCommand
    Dim ResultTable As DataTable

    '   initialize the connection
    Con = New OleDbConnection()
    Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\_sandbox\Northwind.mdb"

    '   initialize the command
    Command = New System.Data.OleDb.OleDbCommand()
    Command.Connection = Con
    Command.CommandText = query

    '   initialize the DataAdapter (only the Select command, as we're only reading data at this time)
    DAdapter = New System.Data.OleDb.OleDbDataAdapter()
    DAdapter.SelectCommand = Command

    '   initialize the DataTable
    ResultTable = New DataTable()

    '   get data from the dataBase and then get rid of the DataAdapter as it has done it's duty)
    DAdapter.Fill(ResultTable)
    DAdapter.Dispose()

    '   return the populated DataTable to the calling code
    Return ResultTable
End Function

After this method has been added to your code, you would be able to get any query result from your DataBase by adding just one line of code to your main program:

'   adjust the query to your needs
Dim UsrTbl As DataTable = GetDataTableFromAccess("SELECT * FROM UserTable")

Oh, and please notice that the above solution aims for a .mdb-Database file. If you're using the .accdb-Database format, you might have to install dedicated drivers on every computer your program is intended to run from, because the .accdb-Database drivers are not native part of Windows 7, as far as I know (again, see connectionstrings.com for reference). Having that in mind I would recommend to use the .mdb-Database format for your purposes.