An exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Question

This is my code. help me to solve it thanks!

An exception of type 'System.InvalidOperationException' occurred in System.Data.dll but was not handled in user code Additional information: ExecuteReader requires an open and available Connection. The connection's current state is closed

Imports System.Data.SqlClient
Partial Class Staff
    Inherits System.Web.UI.Page

    ' Dim conn As New SqlConnection("Data Source=USER-PC\SQLEXPRESS;Initial Catalog=carrental;Integrated Security=True;Pooling=False")
    Dim con As New Data.SqlClient.SqlConnection
    Dim cmd As New Data.SqlClient.SqlCommand
    Dim dr As Data.SqlClient.SqlDataReader

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            con.ConnectionString = ("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\user\Desktop\oh manisku\PROJECT ABIS\project baru\project baru\App_Data\order.mdf;Integrated Security=True;Connect Timeout=30")
            con.Open()


        Catch ex As Exception
            '  MsgBox(ex.Message)
        End Try
    End Sub
    Protected Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
        cmd.CommandText = ("Select Username, Password from Admin WHERE Username ='" & txtusername.Text & "'   and Password = '" & txtPass.Text) & "' "
        cmd.Connection = con
        dr = cmd.ExecuteReader

        con.Close()
        If dr.HasRows Then

            MsgBox("Succesfully Login")
            Response.Redirect("recalled.aspx")

        Else
            MsgBox("Invalid Username and Password")

        End If
    End Sub
    Private Sub btnReset_Click(sender As Object, e As EventArgs) Handles btnReset.Click


    End Sub

    Protected Sub SqlDataSource1_Selecting(sender As Object, e As SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting

    End Sub
End Class

At a guess, I'm going to say it's because youre calling `con.Close()` and then calling `dr.HasRows`. `SqlDataReader` streams data, it doesn't load it all at once. `ExecuteReader` simply opens the stream. `con.Close()` then closes it before you can use it. — ProgrammingLlama, Dec 16 '17 at 04:51

ProgrammingLlama · Answer 1 · 2017-12-16T05:17:47.983

As I said in my comment, you're closing the connection before reading the data. You should move the connection close to after you finished with the data reader.

Protected Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
    Dim con As New Data.SqlClient.SqlConnection
    con.ConnectionString = ("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\user\Desktop\oh manisku\PROJECT ABIS\project baru\project baru\App_Data\order.mdf;Integrated Security=True;Connect Timeout=30")
    con.Open()
    cmd.CommandText = ("Select Username, Password from Admin WHERE Username ='" & txtusername.Text & "'   and Password = '" & txtPass.Text) & "' "
    cmd.Connection = con
    dr = cmd.ExecuteReader
    If dr.HasRows Then

        MsgBox("Succesfully Login")
        Response.Redirect("recalled.aspx")

    Else
        MsgBox("Invalid Username and Password")

    End If
    dr.Close() ' close the datareader
    con.Close() ' close the connection
End Sub
Private Sub btnReset_Click(sender As Object, e As EventArgs) Handles btnReset.Click


End Sub

Calling ExecuteReader simply opens the stream. If you close the connection, you close the stream. To use a telephone analogy: it's like hanging up on someone, and then trying to have a conversation.

Please also switch to using parameterized queries since, as it stands, I could enter my username as ' OR 1 = 1 ; -- and I'd gain full access to the first account in your system.

Also, please look into ways to securely store passwords. You should never store passwords in your database in plain text, and you should never store passwords in a way that allows you to reverse them to the original user input. Passwords should be hashed with a salt. see here.

I've moved your connection open code into the same method, since I realise that these could be causing you issues by being in a separate method. — ProgrammingLlama, Dec 16 '17 at 05:13

An exception of type 'System.InvalidOperationException' occurred in System.Data.dll

1 Answers1