Slow Response Filling Table

R

Rick_Fla

Well-known member
Joined
Nov 19, 2003
Messages
188
Location
Melbourne, Florida
Ok, I am having a wierd issue I can not seem to resolve. I have a Sub (code below) that fills a datatable, which inturn is the datasource of a combobox. I have a MAIN and SUB combobox. So when I change the MAIN combobox, on its selectindexchange event, I call the code below to change the information in the datatable for the SUB combobox. Pretty simple.

The issue is when I first run the program and select a main category, there is a pause at the oAdapter.Fill(oSubCategoryTB) command of maybe 1-2 seconds. Which makes the application seem to freeze. But I get the correct results after that pause. Any other calls show no pause at all. This only seems to appear when I use the .cler method on the Datatable, which I must use or I get duplicates in the SUB combobox. Is there something I am missing? if you need more infor please let me know.

Code: 
Private Sub CategoryList(ByVal Id As Integer, ByVal Type As CategoryType)

        Variables
        Dim sSQL As String = "SELECT * FROM Categories WHERE SubCategoryID = " & Id & ""

        Try
            Fill DataSet
            oAdapter = New OleDbDataAdapter(sSQL, sConnectionString)

            Fill Correct DataSet
            Select Case Type
                Case CategoryType.MainCategory
                    oAdapter.Fill(oMainCategoryTB)
                Case CategoryType.SubCategory
                    oSubCategoryTB.Clear()
                    oAdapter.Fill(oSubCategoryTB)
            End Select

            Dispose
            oAdapter.Dispose()
            sSQL = Nothing

        Catch ex As Exception
            MessageBox.Show(ex.ToString, "Error - CategoryList")
        End Try

    End Sub
 
Dont go back to the database each time you need to fill the combobox and dont keep your database connection open.
 
I dont see where I am keeping the database open?

The main reason I am going back to the database is incase more entries were added to the category list. I guess I could just refresh the DataTable then huh? Thanks for the help, might do some redesign on the way I am grabbing the information.
 
OK, I have gone to not going to the database when there is no updates. I fill a master table and then look through it when the main category change. But I still seem to have a slow down when I clear a table and then fill it again using the class.
 
No matter what method you use you are going to see some pause. How are you calling the function to fill the combobox? Did you add it to the selectedindexchanged event handler of the combobox?

comboBox1.SelectedIndexChanged += new EventHandler(CategoryList);

Also, I am assuming the connection is open while you are using the dataAdapter. As far as I know an Adapter doesnt open a connection itself.

Do you need to clear the table before filling it? Doesnt the fill method of an adapter erase all existing data?

Also, keep a global data adapter throughout your app. This will get rid of the con and destruction code.

Another performance enhancement would be to used a stored procedure instead of a dyanic query (if you can).

Also, you should never catch generic exceptions, instead change it to a System.Data.SqlClient.SqlException
 
Back
Top