Grid Control in VB.Net 2008

Null values shouldn't bog down your system when filtering 50 columns or even 100 columns.
this will apply to both C# and VB.Net.
also you can convert null values to empty strings but I am afraid you may have a bigger problem.
check out the  NullDisplayText here
http://msdn.microsoft.com/en-us/library/ms366709(v=vs.100).aspx


Good luck.

When trying to filter out columns that have null values, meaning remove them, it's bogging down the system.

I took the filter out to just return all rows and it's taking 30 seconds to populate the data grid with 54 columns with 160 rows of data. This doesn't seem right. Why would it take so long?

I really cannot answer any of your questions without looking at your code.
one thing to consider is your sql query.
you can test your query in the SQL Query Analyzer (You have to have a full SQL license as it doesn't exist in sqlExpress)

It takes 2 seconds to run the query in Query Analyzer. Code I use is below. Let me know if I need to change something.

        sSQL = "select main_rate_table.origin_postal_code as 'Origin_Postal_Code', main_rate_table.origin_postal_zone as 'Origin_Postal_Zone', main_rate_table.origin_city as 'Origin_City', main_rate_table.origin_ctry as 'Origin_Country', main_rate_table.dest_postal_code as 'Dest_Postal_Code', main_rate_table.dest_postal_zone as 'Dest_Postal_Zone', main_rate_table.dest_city as 'Dest_City', main_rate_table.dest_ctry as 'Dest_Country', main_rate_table.zone as 'Zone', main_rate_table.carrier_name as 'Carrier_Name', main_rate_table.customer as 'Customer', main_rate_table.mode as 'Mode', equip_size as 'Equipment_Size', equip_type as 'Equipment_Type', main_rate_table.currency as 'Currency', cast(lm_mincharge as varchar) as 'LM_Min_Charge', loading_meter as 'Loading_Meter', cast(lm_rate as varchar) as 'LM_Rate', cast(wt_ctgry_mincharge as varchar) as 'Weight_Catagory_Min_Charge', cast(min_wt as varchar) as 'Min_Weight', cast(max_wt as varchar) as 'Max_Weight', cast(Wt_cate_rate as varchar) as 'Weight_Cate_Rate', delivery_time as 'Delivery_Time', cast(ftl_min_charge as varchar) as 'FTL_Min_Charge', cast(ftl_fcl_rate as varchar) as 'FTL_FCL_Rate', cast(min_pieces as varchar) as 'Min_Pieces', cast(max_pieces as varchar) as 'Max_Pieces', cast(piece_cate_rate as varchar) as 'Piece_Cate_Rate', rate_qualifier as 'Rate_Qualifier', trans_time_days as 'Trans_Time_Days', cast(service_level as varchar) as 'Service_Level', cast(effective_date as varchar) as 'Effective_Date', cast(rev_date as varchar) as 'Rev_Date', cast(exp_date as varchar) as 'Exp_Date', lm as 'LM', rate_qualifier1 as 'Rate_Qualifier_One', fuel_surcharge as 'Fuel_Surcharge', cast(freight_surcharge as varchar) as 'Freight_Surcharge', cast(preferred_delivery as varchar) as 'Preferred_Delivery', cast(sat_delivery as varchar) as 'Saturday_Delivery', cast(sat_pickup as varchar) as 'Saturday_Pickup', cast(late_pickup as varchar) as 'Late_Pickup', cast(island_delivery as varchar) as 'Island_Delivery', oversize_surcharge as 'Oversize_Surcharge', cast(dangerous_goods as varchar) as 'Dangerous_Goods', toll as 'Toll', cast(dry_ice as varchar) as 'Dry_Ice', cast(priorty_delivery as varchar) as 'Priority_Delivery', COD as 'COD', cast(security_surcharge_min as varchar) as 'Security_Surcharge_Min', cast(security_surcharge_max as varchar) as 'Security_Surcharge_Max', cast(security_surcharge_perkg as varchar) as 'Security_Surcharge_PerKG', cast(import_clearance as varchar) as 'Import_Clearance', cast(nine_express as varchar) as 'Nine_Express', cast(ten_express as varchar) as 'Ten_Express', cast(twelve_express as varchar) as 'Twelve_Express', cast(priority as varchar) as 'Priority', cast(inv_fixed_duty as varchar) as 'Inv_Fixed_Duty', storage_fee as 'Storage_Fee', redelivery_rates as 'Redelivery_Rates', rturn_to_sender as 'Return_To_Sender', wasted_Journey as 'Wasted_Journey', cast(wait_over_15min as varchar) as 'Wait_over_15_Min', cast(wait_over_2hrs as varchar) as 'Wait_over_2_Hrs', cast(additional_drops_route as varchar) as 'Additional_Drops_Route', out_of_hrs_bookings as 'Out_of_Hrs_Bookings', cast(per_dealer_administation as varchar) as 'Per_Dealer_Administration', cast(AWH as varchar) as 'AWH', cast(CHH as varchar) as 'CHH', cast(return_chassis as varchar) as 'Return_Chassis', cast(unpacking as varchar) as 'Unpacking', cast(cust_document as varchar) as 'Cust_Document', cast(handling_in as varchar) as 'Handling_In', cast(handling_out as varchar) as 'Handling_Out', cast(return_empty_racks as varchar) as 'Return_Empty_Racks', cast(THC as varchar) as 'THC', cast(B_L_fee as varchar) as 'B_L_Fee', cast(ISPS as varchar) as 'ISPS', cast(BAF as varchar) as 'BAF', cast(free_house_delivery as varchar) as 'Free_House_Delivery', cast(harbour_dues as varchar) as 'Harbour_Dues', cast(marpol as varchar) as 'Marpol', cast(third_party_collection as varchar) as 'Third_Party_Collection' from main_rate_table left join accessorials on accessorials.accessorial_id = main_rate_table.accessorial_id  where main_rate_table.origin_ctry = 'Belgium'"


        sSQL = sSQL + " order by main_id desc"

        command = New SqlCommand(sSQL, connection)

        adapter = New SqlDataAdapter(command)

        dt = New DataTable

        ds = New DataSet

        Try
            adapter.Fill(ds, "ID")

        Catch ex As Exception
            GoTo NextStep1
        End Try

NextStep1:

        dt = ds.Tables("ID")

        dv = New DataView(dt)
        dgRates.DataSource = dt
        DataBind()
        connectclose()

Wow that is a huge query. My only suggestion for query is to remove  ' ' from around the column names.

What do you have in DataBind() function?

I would change following code

        sSQL = sSQL + " order by main_id desc"

        command = New SqlCommand(sSQL, connection)

        adapter = New SqlDataAdapter(command)

        dt = New DataTable

        ds = New DataSet

        Try
            adapter.Fill(ds, "ID")

        Catch ex As Exception
            GoTo NextStep1
        End Try

NextStep1:

        dt = ds.Tables("ID")

        dv = New DataView(dt)
        dgRates.DataSource = dt
        DataBind()
        connectclose()


to

        sSQL = sSQL + " order by main_id desc"

        adapter = New SqlDataAdapter(sSQL, connection)

        dt = New DataTable

        Try
            adapter.Fill(dt)

        Catch ex As Exception
            GoTo NextStep1
        End Try

NextStep1:

        dgRates.DataSource = dt
        DataBind()
        connectclose()

To add to CodeCruiser comment, I would try to eliminate some of the casting.
there is a whole lot of casting in your query.

I'll try the new code to see if that helps. The reason for the quote is because there is a space in column names. Do you not need that when running in .net like you do if you run it in query analyzer?

Thanks for your help so far.

I tried new code, took all casting out, and all quotes out. It's still doing the same thing. With the databind() function. I'm just using the system default databind function to bind data to the grid. Is there a better way to bind the data?

There is no need for databinding in winforms. You only need to set the datasource.

Does it make any difference if you reduce the number of columns by half?

it's a web form not win form. If you cut the columns in half, it still takes awhile, but returns info on the form and doesn't throw an error.

resolved