Solved

Grid Control in VB.Net 2008

Posted on 2013-06-10
13
152 Views
Last Modified: 2014-05-13
Is there a property in grid control on vb.net to take null or blank columns? Or are there custom grid controls that will do this for you? I'm doing this in the back end and it's bogging down the system and sometimes blowing it up with system out of memory error. I'm filtering 50 plus columns.
0
Comment
Question by:jeremyzit
  • 7
  • 3
  • 3
13 Comments
 
LVL 27

Expert Comment

by:Sammy
Comment Utility
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.
0
 

Author Comment

by:jeremyzit
Comment Utility
When trying to filter out columns that have null values, meaning remove them, it's bogging down the system.
0
 

Author Comment

by:jeremyzit
Comment Utility
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?
0
 
LVL 27

Expert Comment

by:Sammy
Comment Utility
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)
0
 

Author Comment

by:jeremyzit
Comment Utility
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()
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
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()
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 27

Expert Comment

by:Sammy
Comment Utility
To add to CodeCruiser comment, I would try to eliminate some of the casting.
there is a whole lot of casting in your query.
0
 

Author Comment

by:jeremyzit
Comment Utility
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.
0
 

Author Comment

by:jeremyzit
Comment Utility
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?
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
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?
0
 

Author Comment

by:jeremyzit
Comment Utility
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.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
Comment Utility
>it's a web form not win form

Hence the question is not in ASP.NET zone and we have not been talking about gridview :-)    

Try using tracing to understand where the bottle neck is

http://msdn.microsoft.com/en-us/library/z48bew18(v=vs.80).aspx
0
 

Author Comment

by:jeremyzit
Comment Utility
resolved
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now