Solved

objConnection.Close() Error

Posted on 2008-10-02
7
640 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 143

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 143

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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 Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

752 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