Error Prompted at RunTime

Hi,

I have the following code;

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

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

How can you do the first one please

Quote:

---

Originally Posted by dr223

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 :

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()
       
        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

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...

Tried;

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()

        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.

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

Quote:

---

Originally Posted by 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...

---

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