ActiveForm hangs second form after data is read

Question

User1922567909 posted

I have a databound SelectionList control on the Form1 that calls a command to direct the user to Form2, which also has a databound SelectionList.  Form2's SelectionList is populated according to the selection in Form1's SelectionList.

The problem is that when I place the value of Form1's SelectionList on a label on Form2 and then use that value to fill the list on Form2, it hangs.  During debug I notice that the datareader goes through the loop properly, but the form is never displayed.  All you see in the browser (Internet Explorer) is the progress bar climbing very slowly forever.

=======================================================================

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim cmdDay As New OleDbCommand
        cmdDay.Connection = Me.OleDbConnection1
        cmdDay.CommandType = CommandType.StoredProcedure
        cmdDay.CommandText = "proc_cdt_acad_sched"

        cmdDay.Parameters.Add("pn_id", OleDbType.VarChar)
        cmdDay.Parameters("pn_id").Value = Session("strPN_ID")
        cmdDay.Parameters.Add("acad_yr", OleDbType.SmallInt)
        cmdDay.Parameters("acad_yr").Value = Session("strGradYr")
        cmdDay.Parameters.Add("term", OleDbType.SmallInt)
        cmdDay.Parameters("term").Value = Session("strAcTerm")

        Me.OleDbConnection1.Open()
        Dim strNewDay, strOldDay As String
        Dim rdrDay As OleDbDataReader
        rdrDay = cmdDay.ExecuteReader
        rdrDay.Read()
        lstDay.Items.Add(rdrDay.Item("hr_cd_group"))
        strNewDay = rdrDay.Item("hr_cd_group")
        strOldDay = rdrDay.Item("hr_cd_group")

        Do While rdrDay.Read
            If strNewDay <> strOldDay Then
                lstDay.Items.Add(rdrDay.Item("hr_cd_group"))
                strOldDay = rdrDay.Item("hr_cd_group")
            End If
            strNewDay = rdrDay.Item("hr_cd_group")
        Loop
        rdrDay.Close()
        Me.OleDbConnection1.Close()
    End Sub

    Protected Sub ViewCourseList(ByVal source As Object, ByVal e As ListCommandEventArgs)
        lblDay.Text = e.ListItem.Text
        ActiveForm = frmCourse
    End Sub

    Private Sub frmCourse_Activate(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles frmCourse.Activate

        Dim strHrCd As String

        Select Case lblDay.Text
            Case "DAY 1"
                strHrCd = "'A','B','C','D','E','F','M'"
            Case "DAY 2"
                strHrCd = "'G','H','I','J','K','L'"
            Case "LAB ATTENDANCE"
                strHrCd = "'R','S','T','U'"
            Case "MISCELLANEOUS"
                strHrCd = "'X','Y','Z'"
        End Select

        'Dim cmdCourse As New OleDbCommand
        'cmdCourse.Connection = Me.OleDbConnection1
        'cmdCourse.CommandType = CommandType.Text

        'cmdCourse.CommandText = "SELECT ..."

        'Me.OleDbConnection1.Open()
        'Dim rdrCourse As OleDbDataReader
        'rdrCourse = cmdCourse.ExecuteReader
        'rdrCourse.Read()

        'Do While rdrCourse.Read
        '    lstCourse.Items.Add(rdrCourse.Item("crse_nbr"))
        lstCourse.Items.Add("course 1")
        lstCourse.Items.Add("course 2")
        'Loop
        'rdrCourse.Close()
        'Me.OleDbConnection1.Close()
    End Sub

=======================================================================

Answer 1

User1922567909 posted

I modified my code a little, but the problem remains.

=================================================

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim cmdDay As New OleDbCommand("proc_cdt_acad_sched", Me.OleDbConnection1)
        cmdDay.CommandType = CommandType.StoredProcedure

        cmdDay.Parameters.Add("pn_id", OleDbType.VarChar)
        cmdDay.Parameters("pn_id").Value = Session("strPN_ID")
        cmdDay.Parameters.Add("acad_yr", OleDbType.SmallInt)
        cmdDay.Parameters("acad_yr").Value = Session("strGradYr")
        cmdDay.Parameters.Add("term", OleDbType.SmallInt)
        cmdDay.Parameters("term").Value = Session("strAcTerm")

        Me.OleDbConnection1.Open()
        Dim strNewDay, strOldDay As String
        Dim rdrDay As OleDbDataReader
        rdrDay = cmdDay.ExecuteReader(CommandBehavior.CloseConnection)

        While rdrDay.Read
            If strNewDay <> strOldDay Then
                lstDay.Items.Add(rdrDay.Item("hr_cd_group"))
                strOldDay = rdrDay.Item("hr_cd_group")
            End If
            strNewDay = rdrDay.Item("hr_cd_group")
        End While

        rdrDay.Close()
        Me.OleDbConnection1.Close()
    End Sub

    Protected Sub ViewCourseList(ByVal source As Object, ByVal e As ListCommandEventArgs)
        lblDay.Text = e.ListItem.Text
        ActiveForm = frmCourse
    End Sub

    Private Sub frmCourse_Activate(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles frmCourse.Activate

        Dim strHrCd, strSql As String

        Select Case lblDay.Text
            Case "DAY 1"
                strHrCd = "'A','B','C','D','E','F','M'"
            Case "DAY 2"
                strHrCd = "'G','H','I','J','K','L'"
            Case "LAB ATTENDANCE"
                strHrCd = "'R','S','T','U'"
            Case "MISCELLANEOUS"
                strHrCd = "'X','Y','Z'"
        End Select

        strSql = "SELECT tbl_cadet_crse_sect.crse_nbr " & _
        "FROM tbl_cadet_crse_sect, tbl_crse_sect " & _
        "WHERE tbl_cadet_crse_sect.sect_nbr = tbl_crse_sect.sect_nbr " & _
        "AND tbl_cadet_crse_sect.crse_nbr = tbl_crse_sect.crse_nbr " & _
        "AND tbl_cadet_crse_sect.term = tbl_crse_sect.term " & _
        "AND tbl_cadet_crse_sect.acad_yr = tbl_crse_sect.acad_yr " & _
        "AND tbl_cadet_crse_sect.term = " & Session("strAcTerm") & " " & _
        "AND tbl_cadet_crse_sect.acad_yr = " & Session("strAcYear") & " " & _
        "AND tbl_cadet_crse_sect.pn_id = '" & Session("strPN_ID") & "' " & _
        "AND tbl_crse_sect.hr_cd IN (" & strHrCd & ")"

        Dim cmdCourse As New OleDbCommand(strSql, Me.OleDbConnection1)

        Me.OleDbConnection1.Open()
        Dim rdrCourse As OleDbDataReader
        rdrCourse = cmdCourse.ExecuteReader(CommandBehavior.CloseConnection)

        While rdrCourse.Read
            lstCourse.Items.Add(rdrCourse.Item("crse_nbr"))
        End While

        rdrCourse.Close()
        Me.OleDbConnection1.Close()
    End Sub

Answer 2

User1922567909 posted
This only happens when I use a SQL query and doesn't happen when I use a stored procedure.

Answer 3

User1922567909 posted

As I debugged, I noticed that it was hanging on these lines:

rdrCourse.Close()
Me.OleDbConnection1.Close()

After I commented out these lines, everything worked fine.  Why would it hang when I try to close the reader and connection?

Answer 4

User-95607119 posted
Are you sure there is no error in connection string or SQL? If server is not found on expected address then error may be thrown after waiting for connection-timeout seconds. Ma sure your SQL works okay.