CODEWITHSUNDEEP
asp.netvb.net
user1055487
user1055487

Reputation: 261

There is already an open DataReader associated with this Command which must be closed first

Dim staffid = TextBox1.Text
    Dim conn As New SqlConnection
    conn.ConnectionString = SqlDataSource1.ConnectionString

    Dim command1 As New SqlCommand("SELECT StaffDetails.StaffID, SUM(HolidayRequests.RequestTotalDays) AS Expr1 FROM HolidayRequests INNER JOIN StaffDetails ON HolidayRequests.Username = StaffDetails.UserName WHERE (StaffDetails.StaffID = staffID) GROUP BY StaffDetails.StaffID, HolidayRequests.ApprovalStatus HAVING (HolidayRequests.ApprovalStatus = N'approved')", conn)
    Dim command2 As New SqlCommand()
    conn.Open()
    Dim rdr As SqlDataReader
    rdr = command1.ExecuteReader
    Dim UpdateQuery As String
    While (rdr.Read())

        UpdateQuery = "UPDATE HolidayEntitlement set Holiday_Taken = @Expr1"
        command2.Parameters.AddWithValue("@Expr1", rdr("Expr1").ToString())

        'run update query

        command2.CommandText = UpdateQuery
        command2.ExecuteNonQuery()

        conn.Close()
    End While

The following error: command2.ExecuteNonQuery: Connection property has not been initialized.

Upvotes: 0

Views: 1315

Answers (2)

paulsm4
paulsm4

Reputation: 121599

Create a second command. In other words, use one cursor for reading, the other for updating.

Upvotes: 1

Gambrinus
Gambrinus

Reputation: 2136

Hi you need to create a second command object. Since you are reading from the same command object you are trying to execute the update statement with.

Furthermore you are closing the connection inside the while-loop. you have to do this afterwards.

Upvotes: 2

Related Questions