**

Please help me debug the code, I am experiencing error in this part:

  cmd.CommandText = "select ROUND(((date_from - date_to)/30),0) as months from rental where tenant_id = '" & Me.TextBox9.Text & "'"
        cmd.CommandType = CommandType.Text

It says: Error while retriving record on the table.. There is already an open Data Reader associated with this command which muct be close first.

 Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Try

            Dim oradb As String = "Provider=OraOLEDB.Oracle; Data Source=TRAVELMATE-PC/XE;User Id=cj;Password=me;"

            Dim conn As New OleDb.OleDbConnection(oradb)
            conn.Open()
            Dim cmd As New OleDb.OleDbCommand
            cmd.Connection = conn

            cmd.CommandText = "select last_name, first_name, middle_name from tenant where last_name = '" & Me.ComboBox1.Text & "' AND first_name = '" & Me.ComboBox2.Text & "' AND middle_name='" & Me.ComboBox3.Text & "'"
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "select tenant_id, address, contact_number from tenant where last_name = '" & Me.ComboBox1.Text & "' AND first_name = '" & Me.ComboBox2.Text & "' AND middle_name='" & Me.ComboBox3.Text & "'"
            cmd.CommandType = CommandType.Text

            Dim dr As OleDb.OleDbDataReader = cmd.ExecuteReader()
            If dr.Read() Then
                TextBox9.Text = dr.Item("tenant_id")

                dr.Dispose()
                dr.Close()
            End If

            cmd.CommandText = "select rental_number from rental where tenant_id = '" & Me.TextBox9.Text & "' "
            cmd.CommandType = CommandType.Text

            Dim dr3 As OleDb.OleDbDataReader = cmd.ExecuteReader()
            If dr3.Read() Then
                TextBox11.Text = dr3.Item("rental_number")

                dr3.Dispose()
                dr3.Close()
            End If


            cmd.CommandText = "select room_type from room where tenant_id = '" & Me.TextBox9.Text & "' "
            cmd.CommandType = CommandType.Text

            Dim dr1 As OleDb.OleDbDataReader = cmd.ExecuteReader()
            If dr1.Read() Then
                TextBox1.Text = dr1.Item(0)
                dr1.Dispose()
                dr1.Close()
            End If

            'combobox start here
            Const Sin As Integer = 2000
            Const Dob As Integer = 1900
            Const Tri As Integer = 1800
            Const Qua As Integer = 1700
            Const Six As Integer = 1600
            Const Eig As Integer = 1500

            Dim sum As Integer

            If Me.TextBox1.Text = "1" Then
                sum += Sin
            End If
            If Me.TextBox1.Text = "2" Then
                sum += Dob
            End If
            If Me.TextBox1.Text = "3" Then
                sum += Tri
            End If
            If Me.TextBox1.Text = "4" Then
                sum += Qua
            End If
            If Me.TextBox1.Text = "6" Then
                sum += Six
            End If
            If Me.TextBox1.Text = "8" Then
                sum += Eig
            End If

            TextBox2.Text = sum
            'comboc ends here


            cmd.CommandText = "select ROUND(((date_from - date_to)/30),0) as months from rental where tenant_id = '" & Me.TextBox9.Text & "'"
            cmd.CommandType = CommandType.Text

            cmd.CommandType = CommandType.Text
            Dim dr9 As OleDb.OleDbDataReader = cmd.ExecuteReader()
            If dr9.Read() Then
                TextBox3.Text = dr9.Item("months")
            End If
            dr9.Dispose()
            dr9.Close()

            'for additional charges


            Dim sumcharge As Integer


            sumcharge = Val(Form11.TextBox2.Text)

            'end


            Dim stayear As Integer
            Dim stayeartotal As Integer

            stayear = TextBox3.Text

            stayeartotal = (stayear * sum)

            TextBox6.Text = stayeartotal


            Dim FinalTotal As Integer

            FinalTotal = (sum * TextBox3.Text) + sumcharge

        TextBox8.Text = FinalTotal

        'Else

        'End If

        dr.Dispose()
        dr.Close()
        cmd.Dispose()
        conn.Dispose()


    Catch ex As Exception
        MessageBox.Show("Error while retrieving record on table..." & ex.Message, "")
    Finally


    End Try
End Sub

Why multiple reader instances? If you declare your DataReader outside of the Try block, you can re-use the same instance - you just have to close() it after each read. I don't use the OleDBDataReader so I'm not sure whether you have to also Dispose() the reader instance each time, ny gut call is "No"... to me, Dispose() implies that I am completely done with it and no longer need the object.

Clean up your code a bit and re-use the DataReader, I'll bet it works for you then.

This article has been dead for over six months. Start a new discussion instead.