CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2009
    Posts
    192

    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
    Last edited by dr223; October 19th, 2012 at 06:02 AM.

  2. #2
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,283

    Re: Error Prompted at RunTime

    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

  3. #3
    Join Date
    Feb 2009
    Posts
    192

    Re: Error Prompted at RunTime

    How can you do the first one please

  4. #4
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,283

    Re: Error Prompted at RunTime

    Quote Originally Posted by dr223 View Post
    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

  5. #5
    Join Date
    Feb 2009
    Posts
    192

    Re: Error Prompted at RunTime

    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...
    Last edited by dr223; October 19th, 2012 at 08:59 AM.

  6. #6
    Join Date
    Feb 2009
    Posts
    192

    Re: Error Prompted at RunTime

    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.

  7. #7
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Error Prompted at RunTime

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  8. #8
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,283

    Re: Error Prompted at RunTime

    Quote Originally Posted by dr223 View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured