• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 769
  • Last Modified:

error message: There is already an open DataReader associated with this Command which must be closed first in vb.net 2005

Hi,

I have the following code: -

    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        If txtOrderQty.Text = "" Then
            MsgBox("Please enter Order Quantity", MsgBoxStyle.Information, "Error in Field")
            txtOrderQty.BackColor = Color.OrangeRed
            txtOrderQty.Select()
            Exit Sub
        End If

        If IsNumeric(txtOrderQty.Text) = False Then
            MsgBox("Invalid entry in Order Quantity." & vbCrLf & vbCrLf & "Entry Should be numeric", MsgBoxStyle.Information, "Error in Field")
            Exit Sub
        End If

        If txtPurchaseOrderNo.Text = "" Then
            MsgBox("Please enter Purchase Order Number", MsgBoxStyle.Information, "Error in Field")
            Exit Sub
        End If

        If txtOrderLineNo.Text = "" Then
            MsgBox("Please enter Purchase Order Line", MsgBoxStyle.Information, "Error in Field")
            Exit Sub
        End If

        If IsNumeric(txtOrderLineNo.Text) = False Then
            MsgBox("Invalid entry in Purchase Order Line Number." & vbCrLf & vbCrLf & "Entry Should be numeric", MsgBoxStyle.Information, "Error in Field")
            Exit Sub
        End If

        Dim myCommand As New SqlCommand("AddOrder", oConn)
        myCommand.CommandType = CommandType.StoredProcedure


        myCommand.Parameters.AddWithValue("@ProductID", lstProducts.SelectedValue.ToString) 'this one is optional
        myCommand.Parameters.AddWithValue("@QtyOrdered", txtOrderQty.Text)
        myCommand.Parameters.AddWithValue("@QtyAccepted", "0")
        myCommand.Parameters.AddWithValue("@Notes", txtOrderNotes.Text)
        myCommand.Parameters.AddWithValue("@Closed", "0")
        myCommand.Parameters.AddWithValue("@PurchaseOrderNo", txtPurchaseOrderNo.Text)
        myCommand.Parameters.AddWithValue("@PurchaseOrderLineNo", txtOrderLineNo.Text)

        myCommand.ExecuteNonQuery()

        cmdPlaceOrder.Enabled = True
        fraOrderDetails.Visible = False

    End Sub

when I run it I get the error message: -

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

On line: -

myCommand.ExecuteNonQuery()

what does this mean please and how to I overcome it.

Many thanks
Lee
0
ljhodgett
Asked:
ljhodgett
  • 2
  • 2
  • 2
  • +1
1 Solution
 
DhaestCommented:
Do you have any datareader in your program somewhere ?
0
 
Jai STech ArchCommented:
i think the problem is wth the connection object and not with the command object
if you are opening any DATA READER based on the connection oConn, then you can close it ...this will ensure that the connection is free to use by this comnand...
0
 
DhaestCommented:
That's also my opinion, that's why I asked if he uses a datareader somewhere in his project...
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Jai STech ArchCommented:
i was just answering the question giving some expalnations so that it is clear for the author...
0
 
McExpCommented:
With Db connections you need to be caurefull to make sure that any connecitons you open get closed.

I would recomend that you using the "using" statment to make sure that whenever you use a db command and data reader get closed/disposed.

I have coppied you're code and converted to a using statement as an example, if you do the same for all other db commands you will ensure that you have tidy code which doesn't leave undisposed objects.

Using myCommand As New SqlCommand("AddOrder", oConn)
        myCommand.CommandType = CommandType.StoredProcedure

        myCommand.Parameters.AddWithValue("@ProductID", lstProducts.SelectedValue.ToString) 'this one is optional
        myCommand.Parameters.AddWithValue("@QtyOrdered", txtOrderQty.Text)
        myCommand.Parameters.AddWithValue("@QtyAccepted", "0")
        myCommand.Parameters.AddWithValue("@Notes", txtOrderNotes.Text)
        myCommand.Parameters.AddWithValue("@Closed", "0")
        myCommand.Parameters.AddWithValue("@PurchaseOrderNo", txtPurchaseOrderNo.Text)
        myCommand.Parameters.AddWithValue("@PurchaseOrderLineNo", txtOrderLineNo.Text)

        myCommand.ExecuteNonQuery()
End Using
0
 
Éric MoreauSenior .Net ConsultantCommented:
See http://www.emoreau.com/Entries/Articles/2006/11/MARS-and-Asynchronous-ADONet.aspx.

You need to add "MultipleActiveResultSets=True" to your connection string.
0
 
McExpCommented:
You could use MARS as the other poster sugested however this doesn't promote tidy coding, You might find that you are not correctly cleaning up Recordsets and commands elsewhere in your code, which will eventually come to bite you when your run into timeouts and other limits?

Have you enough info now to close this question?
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now