Solved

objConnection.Close() Error

Posted on 2008-10-02
7
650 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
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…
Suggested Courses
Course of the Month9 days, 11 hours left to enroll

623 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