Error Prompted at RunTime

[dr223]

Hi,

I have the following code;

Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click

        Dim strConn As String

        strConn = ConfigurationManager.ConnectionStrings("CCMSConnectionString").ConnectionString

        Dim conn As New SqlConnection(strConn)
        'Dim nextconn As New SqlConnection(strConn)

        conn.Open()

        Dim strSql As String

        strSql = "INSERT INTO dbo.TblChangeControlDet (RequestBy, CreatedBy, ChangeType, TechnicalPerson, Req, BusImp,ChangeRaisedDate, PeerRvw) VALUES (@RequestBy, @CreatedBy, @ChangeType, @TechnicalPerson, @Req, @BusImp,@ChangeRaisedDate, @PeerRvw )"

        Dim cmd As New SqlCommand(strSql, conn)

        With cmd.Parameters
            .AddWithValue("@RequestBy", Me.cmbchngreq.SelectedItem.ToString)
            .AddWithValue("@CreatedBy", Me.cmbchngcrt.SelectedItem.ToString)
            .AddWithValue("@ChangeType", Me.cmbchngtyp.SelectedItem.ToString)
            .AddWithValue("@TechnicalPerson", Me.cmbTechname.SelectedItem.ToString)
            .AddWithValue("@Req", txtreq.Text)
            .AddWithValue("@BusImp", txtbusimp.Text)
            .AddWithValue("@ChangeRaisedDate", DateTime.Now.ToString("dd\/MM\/yyyy HH:mm:ss"))
            .AddWithValue("@PeerRvw", Me.cmbpeername.SelectedItem.ToString)
        End With

        cmd.ExecuteNonQuery()


        Dim query As String
        Dim RefID As New System.Data.SqlClient.SqlCommand(("Select RefNo From dbo.QryRefNo"), conn)

        Try

            Using Autoreader As System.Data.SqlClient.SqlDataReader = RefID.ExecuteReader()

                While Autoreader.Read()
                    Dim RefNo As String = (Autoreader.GetValue(0))

                    query = "UPDATE dbo.TblChangeControlDet SET RefNo = @RefNo WHERE  ChangeRaisedDate = @ChangeRaisedDate"
                    cmd = New SqlCommand(query, conn)
                    cmd.Parameters.AddWithValue("@RefNo", RefNo)
                    cmd.Parameters.AddWithValue("@ChangeRaisedDate", DateTime.Now.ToString("dd\/MM\/yyyy HH:mm:ss"))

                    cmd.ExecuteNonQuery()


                End While
            End Using

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, "Spare Elements")
        End Try


        conn.Close()


    End Sub

At run time when I click the button I receive the error -

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

The INSERT works fine the problem us with the UPDATE statement.

Thanks

[HanneSThEGreaT]

Have you tried closing and re-opening the connection after your INSERT statement?

Otherwise, you could have made a SQL Transaction that could do both these things for you, but that might be a bit tricky

[dr223]

How can you do the first one please

[HanneSThEGreaT]

How can you do the first one please

Seriously?? Huh?

OK, well, seeing the fact that opening and closing a connection is too complicated for you to do in your code ( eventhough you have used the very same codes in your codes ), I'll take time off my busy schedule - just for you and spoonfeed you :

Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click

        Dim strConn As String

        strConn = ConfigurationManager.ConnectionStrings("CCMSConnectionString").ConnectionString

        Dim conn As New SqlConnection(strConn)
        'Dim nextconn As New SqlConnection(strConn)

        conn.Open()

        Dim strSql As String

        strSql = "INSERT INTO dbo.TblChangeControlDet (RequestBy, CreatedBy, ChangeType, TechnicalPerson, Req, BusImp,ChangeRaisedDate, PeerRvw) VALUES (@RequestBy, @CreatedBy, @ChangeType, @TechnicalPerson, @Req, @BusImp,@ChangeRaisedDate, @PeerRvw )"

        Dim cmd As New SqlCommand(strSql, conn)

        With cmd.Parameters
            .AddWithValue("@RequestBy", Me.cmbchngreq.SelectedItem.ToString)
            .AddWithValue("@CreatedBy", Me.cmbchngcrt.SelectedItem.ToString)
            .AddWithValue("@ChangeType", Me.cmbchngtyp.SelectedItem.ToString)
            .AddWithValue("@TechnicalPerson", Me.cmbTechname.SelectedItem.ToString)
            .AddWithValue("@Req", txtreq.Text)
            .AddWithValue("@BusImp", txtbusimp.Text)
            .AddWithValue("@ChangeRaisedDate", DateTime.Now.ToString("dd\/MM\/yyyy HH:mm:ss"))
            .AddWithValue("@PeerRvw", Me.cmbpeername.SelectedItem.ToString)
        End With

        cmd.ExecuteNonQuery()
        
        conn.Close() 'Here I close the connection
        conn.Open() 'Here I open the connection

        Dim query As String
        Dim RefID As New System.Data.SqlClient.SqlCommand(("Select RefNo From dbo.QryRefNo"), conn)

        Try

            Using Autoreader As System.Data.SqlClient.SqlDataReader = RefID.ExecuteReader()

                While Autoreader.Read()
                    Dim RefNo As String = (Autoreader.GetValue(0))

                    query = "UPDATE dbo.TblChangeControlDet SET RefNo = @RefNo WHERE  ChangeRaisedDate = @ChangeRaisedDate"
                    cmd = New SqlCommand(query, conn)
                    cmd.Parameters.AddWithValue("@RefNo", RefNo)
                    cmd.Parameters.AddWithValue("@ChangeRaisedDate", DateTime.Now.ToString("dd\/MM\/yyyy HH:mm:ss"))

                    cmd.ExecuteNonQuery()


                End While
            End Using

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, "Spare Elements")
        End Try


        conn.Close()


    End Sub

Happy now? Look at the bolded words with comments

[dr223]

Tried that it gave same error message.

Even tried to create another string;

Dim nextconn As New SqlConnection(strConn)

Then I opened and closed same location as you did in bold.

Same error message....

Thats when I asked...

[dr223]

Tried;

Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click

        Dim strConn As String

        strConn = ConfigurationManager.ConnectionStrings("CCMSConnectionString").ConnectionString

        Dim conn As New SqlConnection(strConn)
        Dim nextconn As New SqlConnection(strConn)

        conn.Open()

        Dim strSql As String

        strSql = "INSERT INTO dbo.TblChangeControlDet (RequestBy, CreatedBy, ChangeType, TechnicalPerson, Req, BusImp,ChangeRaisedDate, PeerRvw) VALUES (@RequestBy, @CreatedBy, @ChangeType, @TechnicalPerson, @Req, @BusImp,@ChangeRaisedDate, @PeerRvw )"

        Dim cmd As New SqlCommand(strSql, conn)

        With cmd.Parameters
            .AddWithValue("@RequestBy", Me.cmbchngreq.SelectedItem.ToString)
            .AddWithValue("@CreatedBy", Me.cmbchngcrt.SelectedItem.ToString)
            .AddWithValue("@ChangeType", Me.cmbchngtyp.SelectedItem.ToString)
            .AddWithValue("@TechnicalPerson", Me.cmbTechname.SelectedItem.ToString)
            .AddWithValue("@Req", txtreq.Text)
            .AddWithValue("@BusImp", txtbusimp.Text)
            .AddWithValue("@ChangeRaisedDate", DateTime.Now.ToString("dd\/MM\/yyyy HH:mm:ss"))
            .AddWithValue("@PeerRvw", Me.cmbpeername.SelectedItem.ToString)
        End With

        cmd.ExecuteNonQuery()

        conn.Close()


        nextconn.Open()

        Dim query As String
        Dim RefID As New System.Data.SqlClient.SqlCommand(("Select RefNo From dbo.QryRefNo"), conn)

        Try

            Using Autoreader As System.Data.SqlClient.SqlDataReader = RefID.ExecuteReader()

                While Autoreader.Read()
                    Dim RefNo As String = (Autoreader.GetValue(0))

                    query = "UPDATE dbo.TblChangeControlDet SET RefNo = @RefNo WHERE  ChangeRaisedDate = @ChangeRaisedDate"
                    cmd = New SqlCommand(query, conn)
                    cmd.Parameters.AddWithValue("@RefNo", RefNo)
                    cmd.Parameters.AddWithValue("@ChangeRaisedDate", DateTime.Now.ToString("dd\/MM\/yyyy HH:mm:ss"))

                    cmd.ExecuteNonQuery()


                End While
            End Using

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, "Spare Elements")
        End Try


        nextconn.Close()


    End Sub

Error Message;

ExecuteReader requires an open and available connection. The connection's current status is closed.

[dglienna]

Might be failing at the TRY, and bubbling down. STEP THRU THE CODE to see the values as they are generated.

[HanneSThEGreaT]

Tried that it gave same error message.

Even tried to create another string;

Dim nextconn As New SqlConnection(strConn)

Then I opened and closed same location as you did in bold.

Same error message....

Thats when I asked...

Oh, OK, I'm sorry for my little unwarranted outburst yesterday, please forgive me.

Anyways, a silly suggestion from my side would be to have your INSERT operations and UPDATE operations in two different subs. Or, a trigger