Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Grid Control in VB.Net 2008

Posted on 2013-06-10
13
Medium Priority
?
165 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
13 Comments
 
LVL 27

Expert Comment

by:Sammy Ageil
ID: 39236040
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
ID: 39236076
When trying to filter out columns that have null values, meaning remove them, it's bogging down the system.
0
 

Author Comment

by:jeremyzit
ID: 39236106
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 27

Expert Comment

by:Sammy Ageil
ID: 39237896
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
ID: 39238125
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
ID: 39238881
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
 
LVL 27

Expert Comment

by:Sammy Ageil
ID: 39238916
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
ID: 39238972
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
ID: 39239038
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
ID: 39239390
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
ID: 39239424
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 1500 total points
ID: 39239538
>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
ID: 40061757
resolved
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

636 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