Unable to resolve open data reader error (needs closing)

Question

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

The idea is for getMaxID() to return the highest value in the field + 1- setting this as a new ReportID (for now). However I keep getting the aforementioned error message. I attempted to pass getMaxID() to a variable, and then assign the variable to @ReportID, but still got the error. I've also tried using conn.close(), but no luck. Any help or suggestions would be appreciated.

I've looked at other answers on here, but still can't get rid of the error.

 Private Sub addReport()

            Dim Str As String = _
           <String> INSERT INTO
                        Report(
                            ReportID, 
                            ScoutID, 
                            FixtureID, 
                            PlayerID, 
                            ReportDate,
                            Comments) 
                    VALUES(
                            @ReportID,
                            '2',
                            '3',
                            '6',
                            '10/15/2014',
                            @comments) 
           </String>

            Try
                Using conn As New SqlClient.SqlConnection(DBConnection)
                    conn.Open()
                    Using cmdQuery As New SqlClient.SqlCommand(Str, conn)
                        cmdQuery.Parameters.Add("@ReportID", SqlDbType.Int).Value = getMaxID("ReportID", "Report")       
                        cmdQuery.Parameters.Add("@Comments", SqlDbType.VarChar).Value = txtComments.Text
                        cmdQuery.ExecuteNonQuery()
                    End Using
                End Using

                MsgBox("Report Added")

            Catch ex As Exception
                MsgBox("Add Report Exception: " & ex.Message & vbNewLine & Str)
            End Try

        End Sub

            Public Function getMaxID(ByVal fieldName As String, ByVal tableName As String) As Integer

                Dim newID As Integer
                Dim Str = "SELECT MAX(" & fieldName & ") FROM " & tableName & ""

                Try
                    Using conn As New SqlClient.SqlConnection(DBConnection)
                        conn.Open()
                        Using cmdQuery As New SqlClient.SqlCommand(Str, conn)
                            cmdQuery.ExecuteReader()

                            If IsDBNull(cmdQuery.ExecuteScalar()) = True Then
                                newID = 1
                            Else
                                newID = cmdQuery.ExecuteScalar()
                                newID = newID + 1
                            End If

                        End Using
                    End Using

                Catch ex As Exception
                    MsgBox("Generate Max ID Exception: " & ex.Message & vbNewLine & Str)
                End Try

                Return newID

            End Function

In getMaxID, you are doing a ExecuteScalar right after a ExecuteReader. The ExecuteReader is pointless, close the reader or remove the ExecuteReader. — the_lotus, Jan 02 '15 at 15:46
In addition to above your code will ExecuteScalar twice if its not null. store the result of the call in a local variable and test that rather than call twice. — Fred, Jan 02 '15 at 15:51

score 0 · Accepted Answer · answered Jan 02 '15 at 15:56

You are executing ExecuteScalar after a ExecuteReader. The ExecuteReader returns a reader that needs to be closed before. I would suggest you use the IsNull function in sql to simplify your function.

        Public Function getMaxID(ByVal fieldName As String, ByVal tableName As String) As Integer

            Dim newID As Integer
            Dim Str = "SELECT IsNull(MAX(" & fieldName & "), 0)+1 FROM " & tableName & ""

            Try
                Using conn As New SqlClient.SqlConnection(DBConnection)
                    conn.Open()
                    Using cmdQuery As New SqlClient.SqlCommand(Str, conn)
                        newID = cmdQuery.ExecuteScalar()
                    End Using
                End Using

            Catch ex As Exception
                MsgBox("Generate Max ID Exception: " & ex.Message & vbNewLine & Str)
            End Try

            Return newID

        End Function

I would strongly suggest you do not concatenate strings for the sql and also, if possible, use the auto increment properties that already exists in sql server.

Unable to resolve open data reader error (needs closing)

1 Answers1