none
Odd error while populating a DataGridView from a MSSQL database RRS feed

  • Question

  • Hey Guys,

    I am populating a bunch of DataGridViews reprenting cross sections of different tables from a MSSQL database.  The DGV's repopulate themselves on certain events such as when the user changes a drop down box, etc.  I am new to VB so I might be doing this oddly.  The error occurs around these lines...

    Code Snippet

        Public Sub fillDataGridView(ByVal query As String, ByRef view As DataGridView)
            command.CommandText = query
            Dim da As SqlDataAdapter
            Dim ds As DataSet
            Dim dt As DataTable
            da = New SqlDataAdapter(command)
            ds = New DataSet()
            da.Fill(ds, "TableX")    <----- Error happens here
            dt = ds.Tables("TableX")
            view.DataSource = dt
            'view.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
            view.Update()
        End Sub


    The error is "InvalidOperationException is Unhandled: The command execution cannot proceed due to a pending asynchronous operation already in progress.". 

    I can handle the error but I don't know what the "... command execution cannot proceed due to a pending asynchronous operation already in progress." part means.  I haven't even been able to Google an answer to this one.

    Thanks all,

    Thursday, August 30, 2007 9:05 PM

Answers

  • its bad practice to open your connection when you are not using it and leaving it open until your app closes - DO NOT do this, connecting to databases are expensive and should only use it when you require it - so open the connection when you are going to do something with the database and close it straight after and dispose of the SqlCommand and SqlConnection objects to free up memory afterwards

     

     

    Friday, August 31, 2007 5:08 PM

All replies



  • Hi
       My first doubt is to the parameter query which statement u r going to pass. As per the code it will be the select statement.
     There is no need of command object there and u forgot to specify connection in dataadapter. Here is an example

      dim con as sqlconnection ,ad as sqldataadapter
      dim ds as dataset

               con=new  sqlconnection
               con.connectionstring=specifyconnectionstring here
               ad=new sqldataadapter
               ad.selectcommand=new sqlcommand("select * from emp", con)
               ds = New DataSet
               ad.Fill(ds)
              datagrid1.DataSource = ds


    Thank u

    Marke yes if u feel this post helped u
    Friday, August 31, 2007 3:20 AM
  • Heya

    Thank you very much for your help!  I am having just one lingering problem..

    This is probably more my fault then your own, but when I try installing your code it runs fine except that the DataGridView doesn't display the results.  It stays blank.  Any ideas?
    Friday, August 31, 2007 12:58 PM
  • Incidentally the SQL connection it opened when the form is loaded, and closed when the form is closed. 

    The way I was doing it worked for two weeks, and suddenly I am getting the above mentioned error. 
    Friday, August 31, 2007 2:57 PM
  • its bad practice to open your connection when you are not using it and leaving it open until your app closes - DO NOT do this, connecting to databases are expensive and should only use it when you require it - so open the connection when you are going to do something with the database and close it straight after and dispose of the SqlCommand and SqlConnection objects to free up memory afterwards

     

     

    Friday, August 31, 2007 5:08 PM
  • Thanks for the advice, I will alter my code so that I do not keep the connection open all the time.

    My more pressing issue is still the error I get.  Does anyone even know what "The command execution cannot proceed due to a pending asynchronous operation already in progress. " means?   TYVM
    Friday, August 31, 2007 6:30 PM
  • Are you executing another query before calling your current method where it throws the exception? It maybe relating to the previous command

     

    Friday, August 31, 2007 6:36 PM
  • I don't believe so.  This method is called to update the DataGridView from the OK_Button_Click subroutine in a dialog box.  That is also the only time it produces the error.

    The same subroutine works in every other case (such as when you load the application, change tabs, etc).  I put the function (along with the variables it requires, etc) in a new blank application and used it in a loop hundreds of times without getting the error. 
    Friday, August 31, 2007 6:46 PM
  • I got it fixed, thank you very much for your help.

    I changed the code so that it only connects to the database as needed and it runs fine now. 
    Friday, August 31, 2007 7:59 PM
  • i forgot to include the property datagrid1.databaind
    Thursday, September 6, 2007 5:27 AM