CODEWITHSUNDEEP
sql-serverdatabasevb.netssms
Josiah Parada
Josiah Parada

Reputation: 1

How to resolve : 'There is already an open DataReader associated with this Command which must be closed first.'

I've been trying to insert data into my sql database but this problem always show up i've tried redoing it again and the same problem occurs and i'm really stumped right now

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        Dim conn As SqlConnection = New SqlConnection("Data Source=DESKTOP-OBQR58O\SQLEXPRESS;Initial Catalog=Accounts;Integrated Security=True")
        Dim comm As SqlCommand = New SqlCommand("insert into User(username, password)values('" + TextBox1.Text + "', '" + TextBox3.Text + "')", conn)
        Dim data As SqlDataAdapter = New SqlDataAdapter(comm)

        Dim user = TextBox1.Text
        Dim pass = TextBox2.Text
        Dim cpass = TextBox3.Text

        Dim reader As SqlDataReader
        conn.Open()
        Dim cmd As SqlCommand = New SqlCommand("select Username from [User] where Username ='" + TextBox1.Text + "'", conn)
        conn.Close()
        conn.Open()
        reader = cmd.ExecuteReader

        If user.Trim() = "" Or pass.Trim() = "" Or cpass.Trim() = "" Then
            MessageBox.Show("Empty Fields", "Blank Spaces")

        ElseIf Not String.Equals(pass, cpass) Then
            MessageBox.Show("Passwords do not match", "ERROR")
            conn.Close()

        ElseIf reader.HasRows Then
            MessageBox.Show("Username already exists!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            TextBox1.Clear()
            conn.Close()
            reader.Close()

        Else
            MessageBox.Show("Account created succesfully!", "Success")
            Dim table As DataTable = New DataTable()
            data.Fill(table) ' this is where the problem occurs.
            TextBox1.Clear()
            TextBox2.Clear()
            TextBox3.Clear()
            Dim log As New Login
            Me.Close()
            log.Show()
            conn.Close()
        End If
        conn.Close()
    End Sub

I honestly don't know what to do

Upvotes: 0

Views: 140

Answers (1)

Ann L.
Ann L.

Reputation: 13965

You open your reader up at the top:

reader = cmd.ExecuteReader

So, it's open. And then, when you run the Fill command, it conflicts with the open reader!

The simplest fix - although, personally, I would restructure the code a bit, to bring the OpenReader nearer to where it is used - would be to add a Close to your reader right before the Fill.

         Else
            reader.Close()  ' what you would add
            MessageBox.Show("Account created succesfully!", "Success")
            Dim table As DataTable = New DataTable()
            data.Fill(table) ' this is where the problem occurs.

VERY IMPORTANT: If you're not familiar with the concept of "SQL Injection Attacks", read up on them, right away. You should NEVER execute SQL that's been built by constructing a string with unvalidated data from the user. You should pass parameters instead.

After all, what if I typed in the user name of "Irrelevant';DROP TABLE Users;--"? You'd wind up with a SQL Statement that contained "SELECT Username from [Users] WHERE [Username] = 'Irrelevant'; DROP TABLE Users; --'"

And, of course, you should validate the input as well, for things like embedded HTML and script! But that's more complicated than just using SQL Parameters.

Upvotes: 1

Related Questions