How to make a simple access connection?

[clockwork3orange]

All I am trying to do here is make a connection to an access database to retrieve a few records and populate a list box. Below you will see the code I am using. When I run the program and hit connect it says the connection is still open and cannot be closed. Then it jumps to this statement [FONT=&quot]RecordCount = MyDataAdapter.Fill(MyDataSet, "user")[/FONT] and says there is an error in the FROM clause. Any idea what is wrong?

Imports System.Data
Imports System.Data.OleDb

Public Class frmDBRead




    Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click
        Dim sqlString As SqlTypes.SqlString
        sqlString = "SELECT * FROM user;"

        Dim MyCon As OleDb.OleDbConnection
        MyCon = New OleDbConnection()
        MyCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\UNH\CS 623\projects\ASAP.mdb"
        MyCon.Open()

        Try
            MyCon.Open()
        Catch OleDbEXceptionerr As OleDbException
            MessageBox.Show(OleDbEXceptionerr.Message, "Access SQL")
        Catch invalidoperationexeceptionerr As InvalidOperationException
            MessageBox.Show(invalidoperationexeceptionerr.Message, "Access SQL")
        End Try


        Dim MyDataAdapter As OleDb.OleDbDataAdapter
        MyDataAdapter = New OleDbDataAdapter(sqlString, MyCon)
        Dim MyDataSet As DataSet
        MyDataSet = New DataSet()
        Dim RecordCount As Integer
        RecordCount = MyDataAdapter.Fill(MyDataSet, "user")

        Dim N As Integer

        Try
            For N = 0 To RecordCount - 1
                lstDBRead.Items.Add(MyDataSet.Tables("user").Rows(N).Item("userLastName"))
            Next N

        Catch ex As Exception
            MessageBox.Show("Error - " & ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try



    End Sub
End Class

 

[jmcilhinney]

May I suggest reading your code CAREFULLY before posting a question? As you read each line, ignore all the rest of the code and just ask yourself what that line does. Then look at the line again in context and see if what it does makes sense. Your code opens, or at least tries to open, the same connection twice and never closes it. The second attempt fails becasue the connection is already open. The exception is caught and execution continues. You should only be opening the connection once, plus you should be closing it when you're done. Having said that, if you're only calling Fill on one DataAdapter there's no need to open or close the connection as Fill will do it implicitly. Calling Fill more than once will happen faster if you explicitly open and close the connection and any other use requires it. The general structure of your database access should be:

'Create connection object and set appropriate properties.

'Execute any other required code, like creatin other data access objects.

Try
    'Open connection.

    'Use connection.
Catch
    'Handle exception here.
Finally
    'Close connection so it will happen whether an exception is thrown or not.
End Try

 

[cjard]

All I am trying to do here is make a connection to an access database to retrieve a few records

You are using .NET 2.0 - For advice on the microsoft recommended way to do data access in 2.0, consult the link in my signature called Data Walkthroughs 2.0