Member Avatar for hhaannyy

this is the code of the from which i trying to open form the login form it is say invalid attempt to call read when reader is closed

Imports System.Data.SqlClient
Public Class Form40
    Private Sub Form40_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Label1.Text = users
        Dim user_Name As String
        Dim main_menu1, main_menu2 As String
        If (con.State = ConnectionState.Closed) Then
            con.Open()
            Dim cmd As New SqlCommand(" SELECT User_name, main_menu1,main_menu2 FROM users WHERE (User_name = '" & TextBox1.Text & "' )", con)
            Dim ddr As SqlDataReader = cmd.ExecuteReader()
            If ddr.HasRows Then
                While ddr.Read()
                    main_menu1 = ddr("main_menu1").ToString()
                    main_menu2 = ddr("main_menu2").ToString()
                    user_Name = ddr("User_Name").ToString()
                    If user_Name = TextBox1.Text And main_menu1 = "True" Then
                        TextBox2.Visible = False
                    End If
                End While
            End If
            ddr.Close()
        End If
        con.Close()
    End Sub
End Class

and this is the code of the form login

Imports System.Data
Imports System.Data.SqlClient
Public Class LoginForm2
    Dim cmd As New SqlCommand
    Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
        ConnectToSQL()
    End Sub
    Private Sub ConnectToSQL()
        Dim Pass_word As String
        Dim Pass_word2 As String
        Dim user_Name As String

        Try
            con.Open()
            Dim cmd As New SqlCommand(" SELECT  User_name, Pass_word FROM   users WHERE (User_name = '" & UsernameTextBox.Text & "' ) AND (Pass_word = '" & PasswordTextBox.Text & "')", con)
            Dim lrd As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            If lrd.HasRows Then
                While lrd.Read()

                    Pass_word = lrd("Pass_word").ToString()
                    user_Name = lrd("User_Name").ToString()
                    Pass_word2 = PasswordTextBox.Text()
                    If Pass_word = Pass_word2 And user_Name = UsernameTextBox.Text Then
                        MessageBox.Show("Logged in successfully as " & user_Name, "", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        users = user_Name
                        Me.Hide()
                        Form40.Show()
                        'Clear all fields
                        PasswordTextBox.Text = ""
                        UsernameTextBox.Text = ""
                    End If
                End While
            Else
                MessageBox.Show("Username and Password do not match..", "Authentication Failure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                'Clear all fields
                PasswordTextBox.Text = ""
                UsernameTextBox.Text = ""
            End If
            lrd.Close()
        Catch ex As Exception
            MessageBox.Show("Error while connecting to SQL Server." & ex.Message)
        Finally
            con.Close()
        End Try
    End Sub

    Private Sub Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel.Click
        Me.Close()
    End Sub
End Class
  • 2 Contributors
  • 4 Replies
  • 2K Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by hhaannyy

Dani AI

Generated

Likely cause: Form40 is shown while the login SqlDataReader (lrd) is still open and both forms share the same SqlConnection (con). When Form40_Load fires it tries to open another reader on that same connection. That race regularly results in a closed/competing reader and the exception you see. is on the right track with cleanup, but the key fix is to close the reader and connection before showing the next form, and stop sharing one global connection across forms.

Practical fixes you can apply now:

  • Do not call Form40.Show() inside the lrd.Read loop. Read what you need, close lrd, close the connection, then show Form40.
  • Pass the username into Form40 (constructor or public property). Do not read TextBox1 on Form40 during Load.
  • Use Using blocks so disposal order is guaranteed. Prefer ExecuteScalar for login.
  • Parameterize queries (avoid string concatenation).
  • If you truly must read multiple result sets concurrently, enable MultipleActiveResultSets=True in the connection string, but the cleaner approach is to close the first reader before opening the next.

Minimal pattern:

' Login
Dim ok As Boolean
Using con As New SqlConnection(cs), _
      cmd As New SqlCommand("SELECT 1 FROM users WHERE User_name=@u AND Pass_word=@p", con)
    cmd.Parameters.AddWithValue("@u", UsernameTextBox.Text)
    cmd.Parameters.AddWithValue("@p", PasswordTextBox.Text)
    con.Open()
    ok = (cmd.ExecuteScalar() IsNot Nothing)
End Using

If ok Then
    Dim f As New Form40(UsernameTextBox.Text)
    Me.Hide()
    f.Show()
End If

' Form40
Public Sub New(currentUser As String)
    InitializeComponent()
    Me.CurrentUser = currentUser
End Sub

Private Sub Form40_Load(...) Handles MyBase.Load
    Using con As New SqlConnection(cs), _
          cmd As New SqlCommand("SELECT main_menu1, main_menu2 FROM users WHERE User_name=@u", con)
        cmd.Parameters.AddWithValue("@u", Me.CurrentUser)
        con.Open()
        Using rdr = cmd.ExecuteReader()
            If rdr.Read() Then
                TextBox2.Visible = Not Convert.ToBoolean(rdr("main_menu1"))
            End If
        End Using
    End Using
End Sub

This removes the global connection, ensures the first reader is closed before the second opens, and avoids the closed-reader error.

Recommended Answers

All 4 Replies

Member Avatar for hericles

Generally you get into that error if you've closed the db connection before you've finished using the reader but you're not doing that here.
Try including the CommandBehavior.CloseConnection to the ExecuteReader as you have it in the second code section.

Member Avatar for hhaannyy

thank you
The problem remains found

Member Avatar for hhaannyy

The problem remains found

Member Avatar for hhaannyy

any help

Edited by hhaannyy
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.