vb.net datareader already open error when not used before

Question

Hi I have this sub as part of my login / logout system. When this sub is called to login users it works perfectly, but on logout it throws an error that doesn't make any sense to me.

Sub validate_session()
        Dim email As String = System.Web.HttpContext.Current.Session("email")
        Dim key As String = System.Web.HttpContext.Current.Session("sid")

        'check against db
        Dim objConnCheck As System.Data.SqlClient.SqlConnection
        Dim objCmdCheck As System.Data.SqlClient.SqlCommand
        Dim strConnStringCheck, strSQLCheck As String

        'establish connection
        objConnCheck = New SqlConnection(ConfigurationManager.ConnectionStrings("dbAlumniReadConnectionString").ConnectionString)
        objConnCheck.Open()

        strSQLCheck = "SELECT * FROM users WHERE email = @email AND session_key = @ssession_hash"
        objCmdCheck = New System.Data.SqlClient.SqlCommand(strSQLCheck, objConnCheck)

        With objCmdCheck
            .Parameters.Add(New SqlParameter("@email", email))
            .Parameters.Add(New SqlParameter("@ssession_hash", key))
        End With

        'take the approiate action
        Dim u As SqlDataReader = objCmdCheck.ExecuteReader()
        If u.HasRows = True Then
            Dim refresh = New hashing
            refresh.create_session_key(email)

        Else
            System.Web.HttpContext.Current.Session("email") = ""
            System.Web.HttpContext.Current.Session("sid") = ""

            'clean out db field
            'update db
            Dim objConnRemove As System.Data.SqlClient.SqlConnection
            Dim objCmdRemove As System.Data.SqlClient.SqlCommand
            Dim strConnStringRemove, strSQLRemove As String

            'establish connection
            objConnRemove = New SqlConnection(ConfigurationManager.ConnectionStrings("dbAlumniAdminConnectionString").ConnectionString)
            objConnRemove.Open()

            strSQLRemove = "UPDATE users SET session_key = "" WHERE email = @semail"
            objCmdRemove = New System.Data.SqlClient.SqlCommand(strSQLCheck, objConnCheck)

            With objCmdRemove
                .Parameters.Add(New SqlParameter("@semail", email))
            End With

            objCmdRemove.ExecuteNonQuery()

            objConnRemove.Close()
            objConnRemove.Close()

        End If

        objConnCheck.Close()
        objConnCheck.Close()

    End Sub

When run as part of the logout logic it throws this error:

There is already an open DataReader associated with this Command which must be closed first. - objCmdRemove.ExecuteNonQuery() -

This is the logout sub:

Sub logout() Handles Me.Load
    System.Web.HttpContext.Current.Session("email") = "ghost"
    Dim logging_out = New common
    logging_out.validate_session()

    Response.Redirect("login.aspx?l=out")

End Sub

This sets a nonsense email address so that when validate_session searches for a valid email/session combo in the db it trips the destruction of the sensitive session data.

The question boils down to this: Why is the error being thrown when the logout sub calls validate_session?

Ahmad · Accepted Answer · 2011-06-22 11:55:51Z

1

Try disposing of the DataReader after you are done with it. like this:

u.Close()
u = Nothing

so your code should look like

Sub validate_session()

        [....]  'your code here

        Dim u As SqlDataReader = objCmdCheck.ExecuteReader()

        If u.HasRows = True Then
                 'Your code here
        Else
                 'Your other code goes here
        End If

        u.close             ' This is where you should pay attention
        u = Nothing

        [...]   'rest of your code here    
    End Sub

answered Jun 22, 2011 at 11:55

Ahmad

12.8k7 gold badges53 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

YsoL8 Over a year ago

Solved this. Had an incorrect variable name in the second set of sql related commands

Collectives™ on Stack Overflow

vb.net datareader already open error when not used before

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related