Solved

objConnection.Close() Error

Posted on 2008-10-02
7
614 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

746 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

11 Experts available now in Live!

Get 1:1 Help Now