objConnection.Close() Error

I am using code directly out of a "Beginning Visual Basic 2005 Databases" book. I have declared OleDbConnection as "objConnection" with the following code:

Private objConnection As OleDbConnection

I am able to use the objConnection variable in code that opens the connection:
objConnection.Open()

But when I try to execute:
objConnection.Close()

I get "NullReferenceException Was Unhandled"

I'm just getting my feet wet with this stuff, and I've ground through every line of code to insure it works. Seems to me if I can open the puppy I should be able to close it!

Thanks.
Buck_BeasomDatabase DesignerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Arthur_WoodConnect With a Mentor Commented:
Try this change:

    Private Sub btnCloseConnection_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCloseConnection.Click
        Try
            'Check the state of the connection and act appropriately
            If objConnection.State = ConnectionState.Open Then
                'Close the connection
                objConnection.Close()
                'Cleanup
                 objConnection.Dispose()
                 objConnection = Nothing
            Else
                lblStatus.Text = "Connection is already closed"
           End If
        Catch ex as Exception
           MessageBox.Show("The Connection object does not exist")
        End Try
    End Sub

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show more of the relevant code?
ie, where is the assignment of the variable:
objConnection = new OleDbConnection
0
 
Buck_BeasomDatabase DesignerAuthor Commented:
Here's the whole thing:

Imports System.Data
Imports System.Data.OleDb

Public Class Form1
    'Form Level Variables
    Private objConnection As OleDbConnection

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

    End Sub

    Private Sub btnDatabase_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDatabase.Click
        'Declare OpenFileDialog Object
        Dim objOpenFileDialog As New OpenFileDialog

        'Set The OpenFileDialog Properties
        With objOpenFileDialog
            .Filter = "Access Database (*.mdb)|*.mdb|All files (*.*)|*.*"
            .FilterIndex = 1
            .Title = "Open Access Database"
        End With

        'Show the dialog
        If objOpenFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            'If the Open button was clicked, then load the file name selected
            txtDatabase.Text = objOpenFileDialog.FileName
            'Change the status
            lblStatus.Text = "Database is set"
        End If

        'Cleanup
        objOpenFileDialog.Dispose()
        objOpenFileDialog = Nothing
    End Sub


    Private Sub btnOpenConnection_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnOpenConnection.Click
        'Initialize a new instance of the OleDbConnection class
        objConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & txtDatabase.Text & ";")

        Try
            'Open the connection
            objConnection.Open()

        Catch OleDbExceptionErr As OleDbException
            'Display the error
            MessageBox.Show(OleDbExceptionErr.Message, "Access SQL")
        Catch InvalidOperationExceptionErr As InvalidOperationException
            'Display the error
            MessageBox.Show(InvalidOperationExceptionErr.Message, "Access SQL")
        End Try

        'Check the state of the connection and report appropriately
        If objConnection.State = ConnectionState.Open Then
            lblStatus.Text = "Database connection is open"
        Else
            lblStatus.Text = "Database connection failed"
        End If
    End Sub


    Private Sub btnCloseConnection_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCloseConnection.Click

        'Close the connection
        objConnection.Close()

        'Display the status of the connection
        lblStatus.Text = "Database connection is closed"

        'Cleanup
        objConnection.Dispose()
        objConnection = Nothing
    End Sub
End Class

Thanks!
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you click on Close button before the connect button, that error will indeed appear.
you have 2 methods:
§ disable the close button by default, and enable it only when the connection succeeded. disable it in the close button click event

§ check if the connection object is set (and open) before trying to close it
0
 
Buck_BeasomDatabase DesignerAuthor Commented:
I'm getting the error even when I have already established a successful connection.
0
 
Buck_BeasomDatabase DesignerAuthor Commented:
There must be something really busted in my code, because now I am getting the message from the Catch. I put a pause in at the Try to see what the "ConnectionState" was, and when I run the mouse over it I get "ConnectionState.Open1". The 1 is in a little box to the right of the ConnectionState.Open.

So now I have to figure out why the Try fails!
0
 
Arthur_WoodCommented:
What do you see when you hover the mouse over the "little box to the right of the ConnectionState.Open" with the 1 in it?  There should be some 'tool-tip' type message that is displayed.
 
AW
0
All Courses

From novice to tech pro — start learning today.