System.InvalidOperationException ExecuteNonQuery requires an open and available Connection

Question

The following code is supposed to display information from a database but there is an error (the title of this question) on the DBCmd.ExecuteNonQuery() line of code.

Does anyone know how I can resolve this problem?

• I am using VB.NET

• I am using an Access database

The code is:

Imports System.Data.OleDb
Public Class frmCheckAvailablity
    Private DBCon As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" &
                                         "Data Source=|DataDirectory|\NewHotel.mdb;")
    Private Access As New DBControl
    Dim QRY As String
    Private DBCmd As OleDbCommand
    Dim DBDR As OleDbDataReader
    Public DBDA As New OleDbDataAdapter("SELECT RoomType FROM tblRoomBookings", DBCon)
    Public DT As New DataTable
    Public DS As New DataSet
    Public DR As DataRow

    Private Function NotEmpty(text As String) As Boolean
        Return Not String.IsNullOrEmpty(text)
    End Function

    Private Sub frmCheckAvailability_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        'RUN QUERY
        Access.ExecQuery("SELECT * FROM tblRoomBookings ORDER BY BookingID ASC")
        If NotEmpty(Access.Exception) Then MsgBox(Access.Exception) : Exit Sub
    End Sub

    Private Sub frmCheckAvailability_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'NewHotelDataSet.tblRoomBookings' table. You can move, or remove it, as needed.
        Me.TblRoomBookingsTableAdapter.Fill(Me.NewHotelDataSet.tblRoomBookings)

        If DBCon.State = ConnectionState.Closed Then DBCon.Open() : Exit Sub
    End Sub

    Private Sub Search()
        DBDA.Fill(DT)

        txtSearch.AutoCompleteCustomSource.Clear()

        For Each DBDR In DT.Rows
            txtSearch.AutoCompleteCustomSource.Add(DBDR.Item(0).ToString)
        Next
        txtSearch.AutoCompleteMode = AutoCompleteMode.SuggestAppend
        txtSearch.AutoCompleteSource = AutoCompleteSource.CustomSource
    End Sub

    Private Sub SearchCustomers(RoomType As String)
        'ADD PARAMETERS & RUN QUERY
        Access.AddParam("@RoomType", "%" & RoomType & "%")
        Access.ExecQuery("SELECT * FROM tblRoomBookings WHERE RoomType LIKE @RoomType")

        'REPORT & ABORT ON ERRORS
        If NotEmpty(Access.Exception) Then MsgBox(Access.Exception) : Exit Sub
    End Sub

    Private Sub txtSearch_TextChanged(sender As Object, e As EventArgs) Handles txtSearch.TextChanged
        QRY = "SELECT FullName FROM tblRoomBookings WHERE RoomType'" & txtSearch.Text & "'"
        DBCmd = New OleDbCommand(QRY, DBCon)
        DBCmd.ExecuteNonQuery()
        DBDR = DBCmd.ExecuteReader

        If DBDR.Read Then
            txtRoomType.Text = DBDR("RoomType")
            txtFirstNight.Text = DBDR("FirstNight")
            txtLastNight.Text = DBDR("LastNight")
            txtNoNights.Text = DBDR("NoNights")
        End If
    End Sub

Answer 1

The only place in the code that I see DBcmd.ExecuteNonQuery is in search text changed event. Do really want to run this code every time the users types a letter?

Do not create a new connection at the class (Form) level. Every time the connection is used it needs to be disposed so it can be returned to the connection pool. Using...End Using blocks handle this for you even if there is an error.

Don't call .ExecuteNonQuery. This is not a non query; it begins with Select.

You can't execute a command without an Open connection.

Never concatenate strings for sql statments. Always use parameters.

The connection is open while the reader is active. Don't update the user interface while the connection is open.

Load a DataTable and return that to the user interface code where you update the user interface.

Private ConStr As String = "Your connection string"

Private Function GetSearchResults(Search As String) As DataTable
    Dim dt As New DataTable
    Dim QRY = "SELECT FullName FROM tblRoomBookings WHERE RoomType = @Search"
    Using DBcon As New OleDbConnection(ConStr),
            DBCmd As New OleDbCommand(QRY, DBcon)
        DBCmd.Parameters.Add("@Search", OleDbType.VarChar).Value = Search
        DBcon.Open()
        Using reader = DBCmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Return dt
End Function

Private Sub txtSearch_TextChanged(sender As Object, e As EventArgs) Handles txtSearch.TextChanged
    Dim dtSearch = GetSearchResults(txtSearch.Text)

    If dtSearch.Rows.Count > 0 Then
        txtRoomType.Text = dtSearch(0)("RoomType").ToString
        txtFirstNight.Text = dtSearch(0)("FirstNight").ToString
        txtLastNight.Text = dtSearch(0)("LastNight").ToString
        txtNoNights.Text = dtSearch(0)("NoNights").ToString
    End If
End Sub