none
System.InvalidOperationException: 'ExecuteReader: Connection property has not been initialized.' HELP ME GUYS FOR MY SCHOOL RESEARCH PROJECT RRS feed

  • Question

  • 'Import System
    Imports System.Data.OleDb
    Public Class Form1

    Public DNS As OleDbConnection
    Public yify As New OleDbCommand
    Public rd As OleDbDataReader
    Public QRS As New OleDbDataAdapter
    Public Sy As New DataSet

    Public RTX As String

    Public Sub Opendatabase()
    Try
    DNS = New OleDbConnection("Microsoft.Jet.OLEDB.4.0;  " & "Data Source=" & Application.StartupPath & "\\BOOKING.mdb")
    If DNS.State = ConnectionState.Open Then DNS.Close()
    DNS.Open()
    ' MsgBox("ALRIGHT")
    Catch ex As Exception
    MessageBox.Show("Cannot Reserve!")
    End Try
    End Sub
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    opendatabase()
    RTX = "Insert into tbl_name (NumberOfCustomers,Lastname,Firstname,Middlename,Age,DateOfBirth,Contactnumber,Address,OtherContacts,Relationship,Telephone,Mobilenumber,NumberOfGuests,Pincode) Values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & TextBox6.Text & "','" & TextBox7.Text & "','" & TextBox8.Text & "','" & TextBox9.Text & "','" & TextBox10.Text & "','" & TextBox11.Text & "','" & TextBox12.Text & "','" & TextBox13.Text & "','" & TextBox14.Text & "')"
    yify = New OleDbCommand(RTX, DNS)
    rd = yify.ExecuteReader

    MsgBox("Reserve!")

    loadlist()
    Txtbehave()
    Button1.Enabled = True
    Button2.Enabled = False
    Button3.Enabled = False
    Button4.Enabled = True
    Button5.Enabled = False
    Button6.Enabled = True
    End Sub
    Public Sub loadlist()
    opendatabase()
    RTX = "Select * from tbl_name"
    yify = New OleDbCommand(RTX, DNS)
    rd = yify.ExecuteReader

    ListView1.Items.Clear()

    While rd.Read

    Dim lv As ListViewItem = ListView1.Items.Add(rd.Item("NumberOfCustomers").ToString)
    lv.SubItems.Add(rd.Item("Lastname").ToString)
    lv.SubItems.Add(rd.Item("Firstname").ToString)
    lv.SubItems.Add(rd.Item("Middlename").ToString)
    lv.SubItems.Add(rd.Item("Age").ToString)
    lv.SubItems.Add(rd.Item("DateOfBirth").ToString)
    lv.SubItems.Add(rd.Item("Contactnumber").ToString)
    lv.SubItems.Add(rd.Item("Address").ToString)
    lv.SubItems.Add(rd.Item("OtherContact").ToString)
    lv.SubItems.Add(rd.Item("Relationship").ToString)
    lv.SubItems.Add(rd.Item("Telephone").ToString)
    lv.SubItems.Add(rd.Item("Mobilenumber").ToString)
    lv.SubItems.Add(rd.Item("NumberOfGuests").ToString)
    lv.SubItems.Add(rd.Item("Pincode").ToString)
    lv.SubItems.Add(rd.Item("NumberOfGuests").ToString)
    End While
    End Sub
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Me.BOOKINGTableAdapter3.Fill(Me.BOOKINGDataSetlast.BOOKING)
    loadlist()
    txtbehave()
    Button2.Enabled = False
    Button3.Enabled = False
    Button5.Enabled = False
    End Sub
    Private Sub ListView1_SelectedIndexChanged(sender As Object, e As EventArgs)
    TextBox1.Text = ListView1.FocusedItem.SubItems(0).Text
    TextBox2.Text = ListView1.FocusedItem.SubItems(1).Text
    TextBox3.Text = ListView1.FocusedItem.SubItems(2).Text
    TextBox4.Text = ListView1.FocusedItem.SubItems(3).Text
    TextBox5.Text = ListView1.FocusedItem.SubItems(4).Text
    TextBox6.Text = ListView1.FocusedItem.SubItems(5).Text
    TextBox7.Text = ListView1.FocusedItem.SubItems(6).Text
    TextBox8.Text = ListView1.FocusedItem.SubItems(7).Text
    TextBox9.Text = ListView1.FocusedItem.SubItems(8).Text
    TextBox10.Text = ListView1.FocusedItem.SubItems(9).Text
    TextBox11.Text = ListView1.FocusedItem.SubItems(10).Text
    TextBox12.Text = ListView1.FocusedItem.SubItems(11).Text
    TextBox13.Text = ListView1.FocusedItem.SubItems(12).Text
    TextBox14.Text = ListView1.FocusedItem.SubItems(13).Text
    End Sub



    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
    opendatabase()
    RTX = "Update tbl_name set NumberOfCustomers='" & TextBox2.Text & "',Lastname='" & TextBox3.Text & "',Firstname='" & TextBox4.Text & "',Middlename='" & TextBox5.Text & "',Age='" & TextBox6.Text & "',DateOfBirth='" & TextBox7.Text & "',Contactnumber='" & TextBox8.Text & "',Address='" & TextBox9.Text & "',OtherContacts='" & TextBox10.Text & "',Relationship='" & TextBox11.Text & "',Telephone='" & TextBox12.Text & ",Mobilenumber='" & TextBox13.Text & "',NumberOfGuests='" & TextBox14.Text & "',Pincode='" & "' where NumberOfCustomers='" & TextBox1.Text & "'"
    yify = New OleDbCommand(RTX, DNS)
    rd = yify.ExecuteReader

    MsgBox("Record Update!")
    loadlist()
    txtbehave()
    Button3.Enabled = False
    Button4.Enabled = True
    Button5.Enabled = True
    End Sub

    Public Sub txtbehave()
    TextBox1.Text = ""
    TextBox2.Text = ""
    TextBox3.Text = ""
    TextBox4.Text = ""
    TextBox5.Text = ""
    TextBox6.Text = ""
    TextBox7.Text = ""
    TextBox8.Text = ""
    TextBox9.Text = ""
    TextBox10.Text = ""
    TextBox11.Text = ""
    TextBox12.Text = ""
    TextBox13.Text = ""
    TextBox14.Text = ""

    TextBox1.Enabled = False
    TextBox2.Enabled = False
    TextBox3.Enabled = False
    TextBox4.Enabled = False
    TextBox5.Enabled = False
    TextBox6.Enabled = False
    TextBox7.Enabled = False
    TextBox8.Enabled = False
    TextBox9.Enabled = False
    TextBox10.Enabled = False
    TextBox11.Enabled = False
    TextBox12.Enabled = False
    TextBox13.Enabled = False
    TextBox14.Enabled = False
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    txtbehave()
    TextBox1.Text = ""
    TextBox2.Text = ""
    TextBox3.Text = ""
    TextBox4.Text = ""
    TextBox5.Text = ""
    TextBox6.Text = ""
    TextBox7.Text = ""
    TextBox8.Text = ""
    TextBox9.Text = ""
    TextBox10.Text = ""
    TextBox11.Text = ""
    TextBox12.Text = ""
    TextBox13.Text = ""
    TextBox14.Text = ""

    TextBox1.Enabled = True
    TextBox2.Enabled = True
    TextBox3.Enabled = True
    TextBox4.Enabled = True
    TextBox5.Enabled = True
    TextBox6.Enabled = True
    TextBox7.Enabled = True
    TextBox8.Enabled = True
    TextBox9.Enabled = True
    TextBox10.Enabled = True
    TextBox11.Enabled = True
    TextBox12.Enabled = True
    TextBox13.Enabled = True
    TextBox14.Enabled = True
    TextBox1.Focus()

    Button1.Enabled = True
    Button2.Enabled = False
    Button5.Enabled = False
    Button6.Enabled = False

    End Sub

    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
    If TextBox1.Text = "" Then
    MsgBox("Select Data First!")
    Else
    Dim result = MessageBox.Show("Are you sure you want to Delete?", "System", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
    If DialogResult.Yes = result Then
    Opendatabase()
    RTX = "delete * from tbl_name where NumberOfCustomers='" & TextBox1.Text & "'"
    yify = New OleDbCommand(RTX, DNS)
    rd = yify.ExecuteReader

    MsgBox("Record Deleted!")
    loadlist()
    txtbehave()
    Else

    End If

    End If
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    If TextBox1.Text = "" Then
    MsgBox("Select Data First!")
    Else

    TextBox2.Enabled = True
    TextBox3.Enabled = True
    TextBox4.Enabled = True
    TextBox5.Enabled = True
    TextBox6.Enabled = True
    TextBox2.Focus()

    Button1.Enabled = False
    Button2.Enabled = True
    Button5.Enabled = True
    Button6.Enabled = False
    End If

    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
    Button7.Enabled = True
    Button6.Enabled = True
    Button4.Enabled = True
    Button5.Enabled = True
    Button1.Enabled = False
    End Sub

    Private Sub Button8_Click(sender As Object, e As EventArgs)

    If ComboBox1.Text = "NumberOfCustomers" Then
    opendatabase()
    RTX = "delete * from tbl_name where NumberOfCustomers='" & TextBox1.Text & "'"
    yify = New OleDbCommand(RTX, DNS)
    rd = yify.ExecuteReader

    ListView1.Items.Clear()

    While rd.Read

    Dim lv As ListViewItem = ListView1.Items.Add(rd.Item("NumberOfCustomers").ToString)
    lv.SubItems.Add(rd.Item("Lastname").ToString)
    lv.SubItems.Add(rd.Item("Firstname").ToString)
    lv.SubItems.Add(rd.Item("Middlename").ToString)
    lv.SubItems.Add(rd.Item("Age").ToString)
    lv.SubItems.Add(rd.Item("DateOfBirth").ToString)
    lv.SubItems.Add(rd.Item("Contactnumber").ToString)
    lv.SubItems.Add(rd.Item("Address").ToString)
    lv.SubItems.Add(rd.Item("OtherContact").ToString)
    lv.SubItems.Add(rd.Item("Relationship").ToString)
    lv.SubItems.Add(rd.Item("Telephone").ToString)
    lv.SubItems.Add(rd.Item("Mobilenumber").ToString)
    lv.SubItems.Add(rd.Item("NumberOfGuests").ToString)
    lv.SubItems.Add(rd.Item("Pincode").ToString)
    lv.SubItems.Add(rd.Item("NumberOfGuests").ToString)

    End While
    ElseIf ComboBox1.Text = "Lastname" Then

    opendatabase()
    RTX = "select * from tbl_name where studname='" & TextBox7.Text & "'"
    yify = New OleDbCommand(RTX, DNS)
    rd = yify.ExecuteReader
    ListView1.Items.Clear()

    While rd.Read

    Dim lv As ListViewItem = ListView1.Items.Add(rd.Item("NumberOfCustomers").ToString)
    lv.SubItems.Add(rd.Item("Lastname").ToString)
    lv.SubItems.Add(rd.Item("Firstname").ToString)
    lv.SubItems.Add(rd.Item("Middlename").ToString)
    lv.SubItems.Add(rd.Item("Age").ToString)
    lv.SubItems.Add(rd.Item("DateOfBirth").ToString)
    lv.SubItems.Add(rd.Item("Contactnumber").ToString)
    lv.SubItems.Add(rd.Item("Address").ToString)
    lv.SubItems.Add(rd.Item("OtherContact").ToString)
    lv.SubItems.Add(rd.Item("Relationship").ToString)
    lv.SubItems.Add(rd.Item("Telephone").ToString)
    lv.SubItems.Add(rd.Item("Mobilenumber").ToString)
    lv.SubItems.Add(rd.Item("NumberOfGuests").ToString)
    lv.SubItems.Add(rd.Item("Pincode").ToString)
    lv.SubItems.Add(rd.Item("NumberOfGuests").ToString)

    End While
    Else
    MsgBox("Select Search Criteria First!!")
    End If
    End Sub

    Private Sub Button9_Click(sender As Object, e As EventArgs)
    loadlist()
    End Sub

    Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
    ' Reset or clear any controls (recommended)
    TextBox1.Clear()
    ' NOT recommended:
    ' Application.Restart()
    End Sub
    End Class


    • Edited by NicoMigs Saturday, March 2, 2019 7:12 AM
    Saturday, March 2, 2019 7:11 AM

All replies

  • Hello,

    • You have not indicated which line this issue occurs on
    • It's incredibly difficult to read your code because you have not place the code into a code block, please edit your post and place code into a code block where the code is properly formatted and indented.

    Here is a solid piece of advice, DO NOT use form level variables for  your connection, command, reader, adapter and data set.

    These need to be scoped to where they will be used e.g. if you have five places to open a connection will mean you need five connection objects. I have a GitHub repository that shows this, the following link shows a pattern for connecting to a database, read data and returning data. Note that when working with data your code for this should not reside directly in a form but instead be in a class.

    Public DNS As OleDbConnection
    Public yify As New OleDbCommand
    Public rd As OleDbDataReader
    Public QRS As New OleDbDataAdapter
    Public Sy As New DataSet

    Here is a sample of a read operation. Note I changed from looking for a student by name to student by id which in this case would be the primary key (if you don't have a primary key create one).

    In this case the function is not in the form but if you are set on that so be it.

    IMPORTANT In the code below change SqlConnection to OleDbConnection, change SqlCommand to OleDbCommand.

    First a class that represents a record in the database table, of course there would be more fields.

    Public Class Student
        Public Property Id() As Integer
        Public Property FirstName() As String
        Public Property LastName() As String
    End Class

    Here the only globally scoped variable is the connection string. Everything else if locally scoped and destroyed when done.

    Private databaseConnectionString As String =
                $"Microsoft.Jet.OLEDB.4.0;Data Source={Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "BOOKING.mdb")}"
    ''' <summary>
    ''' Get student by name
    ''' </summary>
    ''' <param name="pStudentName"></param>
    ''' <returns>If student found a valid student object, if not found the return value is Nothing</returns>
    Public Function DemoRead(pStudentIdentifier As Integer) As Student
        Dim student As Student
    
        Dim selectStatement = "select id, FirstName, LastName from tbl_name where id= ?"
    
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With {.Connection = cn, .CommandText = selectStatement}
                cmd.Parameters.AddWithValue("?", pStudentIdentifier)
                cn.Open()
                Dim reader = cmd.ExecuteReader()
                If reader.HasRows Then
                    reader.Read()
                    student = New Student With {.Id = reader.GetInt32(0), .FirstName = reader.GetString(1), .LastName = reader.GetString(2)}
                End If
    
            End Using
        End Using
    
        Return student
    
    End Function
    I'd bet if use go down the path I recommended you will a) learn more b) be better off with whomever evaluates the project. Do look at my repository and learn from it.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Saturday, March 2, 2019 12:01 PM
    Moderator
  • The Code Block to format the code so that it is readable is the icon next to the HTML Icon on the toolbar. You should Edit your post and past your original code from the project, not the code you have already posted and repost your code.
    Sunday, March 3, 2019 2:13 AM