Solved

objConnection.Close() Error

Posted on 2008-10-02
7
621 Views
Last Modified: 2013-12-25
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.
0
Comment
Question by:Buck_Beasom
  • 3
  • 2
  • 2
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22628196
can you show more of the relevant code?
ie, where is the assignment of the variable:
objConnection = new OleDbConnection
0
 

Author Comment

by:Buck_Beasom
ID: 22628396
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22628502
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Buck_Beasom
ID: 22633055
I'm getting the error even when I have already established a successful connection.
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 250 total points
ID: 22633150
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
 

Author Comment

by:Buck_Beasom
ID: 22681390
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 22702884
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now