There is already an open datareader associated wit

yasinirshad · August 12th, 2007, 01:55 AM

Hi.. Thanks a lot..i tried with it. It says "There is already an open datareader associated with this connection which must be closed first." how to close the existing data reader? pls help. below is my code.. i need to insert values from insert sql statement into database.. am not able to..plss help.

Code:

Private Sub btn_save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_save.Click

        Dim CALLID, RequestorID, CommentsFromITD, MessageFromITD, MessageToITD, CallStatus, strSQL As String
        CALLID = Request.QueryString("CallID")
        RequestorID = Session("USER_ID")
        CommentsFromITD = lblcomments.Text
        MessageFromITD = lblmessage.Text
        MessageToITD = txt_desc.Text
        CallStatus = Request.Form(ddl_callstatus.UniqueID)

        strSQL = "SELECT * FROM MESSAGES WHERE CALL_ID=" & CALLID & ""
        'Dim Objcmd As New SqlCommand(strSQL, ObjConn)
        Dim ObjCmd As SqlCommand
        Dim ObjDR As SqlDataReader
        'ObjConn.Open()
        Try
            'ObjDR = Objcmd.ExecuteReader()
            ObjConn.Open()

            ObjCmd = New SqlCommand(strSQL, ObjConn)
            ObjDR = ObjCmd.ExecuteReader()

            'If Not ObjDR Is DBNull.Value Then
            If ObjDR.HasRows Then
                'If Not ObjDR("MESSAGE_TO_ITD") = MessageToITD Then
                If Not IsDBNull(ObjDR("MESSAGE_TO_ITD")) Then
                    'If Not ObjDR.HasRows Then
                    strSQL = "UPDATE MESSAGES SET MESSAGE_TO_ITD='" & MessageToITD & "',FLAG='1' WHERE CALL_ID=  " & CALLID & ""
                Else
                    strSQL = "UPDATE MESSAGES SET MESSAGE_TO_ITD='" & MessageToITD & "',FLAG='2' WHERE CALL_ID=  " & CALLID & ""
                End If
            Else
                    strSQL = "INSERT INTO MESSAGES (CALL_ID,MESSAGE_TO_ITD,FLAG) VALUES ( " & CALLID & " ,'" & MessageToITD & "','1')"
                End If
            Dim ObjCmd1 As New SqlCommand(strSQL, ObjConn)
            ObjCmd1.ExecuteNonQuery()
            ObjConn.Close()
            ObjDR.Close()
            ObjConn.Close()
        Catch ex As SqlException
            lblmsg.Text = ex.Message.ToString

        End Try
        Response.Redirect("UserCreateMessage.aspx")
    End Sub

Imar · August 12th, 2007, 03:37 AM

Hi there,

You can simply call Close() on the DataReader before you call ExecuteNonQuery. E.g.:

ObjDR.Close()
Dim ObjCmd1 As New SqlCommand(strSQL, ObjConn)
ObjCmd1.ExecuteNonQuery()
ObjConn.Close()

Tip: if you post code here, can you please remove "dead code". You have commented out quite a bit of code. This is easy to spot in an IDE with color coding, but more difficult to read on a forum like this. Removing irrelevant code makes it easier for us to focus on the problem.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004