[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How Can a DataGrid Using a DataGridView and a Query String as a Source While Keeping the Grids properties?

Posted on 2011-05-08
10
Medium Priority
?
259 Views
Last Modified: 2012-05-11
When repopulating a DataGridView in VB.NET 2008 its original properties such as .Hide, Readonly, .Headers, and so on are lost. Instead the new query results are displayed as if no properties existed. Except for parameters, the same query is always used. I tried Grid.Roes.Clear, but the problem remains.

The headers are replaced by the table's column names, hidden fields are displaed, autosizing removed.

What am I missing?
Public gc_SelectFieldPickRequest As String = _
    "SELECT bob.segid, bob.lhnd, " + _
        "bob.stname, lkbo.Description " + _
        "FROM  BOBAADR AS bob INNER JOIN LK_BOROUGH AS lkbo " + _
            "ON bob.boro = lkbo.Code " + _
        "WHERE bob.segid = "

========================================================

Dim clsDataGrid As New DataGrid
clsDataGrid.FillGrid(dtgrdProcessFP, & "bob.lhnd")

==========================================================

Public Class DataGrid
    Public Sub FillGrid( _
        ByRef Grid As System.Windows.Forms.DataGridView, _
        ByVal strQuery As String)

        Grid.DataSource = Nothing
        Grid.Rows.Clear()

        Dim tbl As New DataTable
        Dim da As New SqlServerCe.SqlCeDataAdapter(strQuery, gc_ConnString)
        da.Fill(tbl)
        Grid.DataSource = tbl
    End Sub
End Class

Open in new window

GRIDB.jpg
GRIDC.jpg
0
Comment
Question by:bobcann
  • 5
  • 4
10 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35716327
You are passing a DataGrid to a function which is expecting a DataGridView. Why?
0
 

Author Comment

by:bobcann
ID: 35716482
Thanks for the quick reply.

My naming of the Class is DataGrid. What I am passing is a dataGridView control to a DataGridView parameter (please see image to verify this the type of  dtgrdProcessFP).

Sorry about that. I see haw that could confuse. Any ideas on how to correct the re-population issue?

(Error for last argument in method call corrected)
'Error for last argument for method call corrected:

Dim clsDataGrid As New DataGrid
clsDataGrid.FillGrid(dtgrdProcessFP, gc_SelectFieldPickRequest & "bob.lhnd")

Open in new window

Type.jpg
0
 
LVL 17

Accepted Solution

by:
nepaluz earned 2000 total points
ID: 35716501
you should set the properties AFTER assigning the datasource to the DataGridView.
Below, I have added some ways to apply some of the properties you want
Public Class DataGrid
    Public Sub FillGrid( _
        ByRef Grid As System.Windows.Forms.DataGridView, _
        ByVal strQuery As String)

        Grid.DataSource = Nothing
        Grid.Rows.Clear()

        Dim tbl As New DataTable
        Dim da As New SqlServerCe.SqlCeDataAdapter(strQuery, gc_ConnString)
        'you can set the captions on specific columns here
        tbl.Columns(0).Caption = "MyColumn Name"
        da.Fill(tbl)
        Grid.DataSource = tbl
        'here you can hide specific columns
        Grid.Columns(0).Visible = False
    End Sub
End Class

Open in new window


PS. Because you clear the Datasource ofthe grid, all settings / properties are lost and the grid inherits the properties from the new datasource.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:nepaluz
ID: 35716505
you can actually reference the columns by their names rather than their indexes, i.e
       ............
        'you can set the captions on specific columns here
        tbl.Columns("stname").Caption = "Street Name"
        tbl.Columns("Description").Caption = "Borough"
        .........
        Grid.DataSource = tbl
        'here you can hide specific columns
        Grid.Columns("segId").Visible = False
        ..........

Open in new window

0
 

Author Comment

by:bobcann
ID: 35722598
Thank you for the examples, nepaluz.

1. I tried your example and got an error "Object reference not set to an instance of an object." on tbl.Columns("RequestID").Caption = "Request ID"

2. I tried a variation on your example and get  "Object reference not set to an instance of an object." on Grid.Columns("RequestID").Name = "colSR_RequestID" The DataGridView was created by dragging it from the Toolbox. It is passed to Grid.FillGrid by Ref. I am not certain why it should give this exception.  Also tried it without Grid.Rows.Clear() with same result, and so on.

Any ideas?

-Bob
1. 
Public Class DataGrid
    Public Sub FillGrid( _
        ByRef Grid As System.Windows.Forms.DataGridView, _
        ByVal strQuery As String)

        Grid.DataSource = Nothing
        Grid.Rows.Clear()

        Dim tbl As New DataTable
        Dim da As New SqlServerCe.SqlCeDataAdapter(strQuery, gc_ConnString)

        'you can set the captions on specific columns here
        tbl.Columns("RequestID").Caption = "Request ID" '<<<
        da.Fill(tbl)
        Grid.DataSource = tbl

        'here you can hide specific columns
        Grid.Columns("Request ID").Visible = False
    End Sub
End Class

2.
Public Class DataGrid
    Public Sub FillGrid( _
        ByRef Grid As System.Windows.Forms.DataGridView, _
        ByVal strQuery As String)

        Grid.DataSource = Nothing
        Grid.Rows.Clear()

        Dim tbl As New DataTable
        Dim da As New SqlServerCe.SqlCeDataAdapter(strQuery, gc_ConnString)
        da.Fill(tbl)
        Grid.DataSource = tbl

        tbl.Columns("RequestID").Caption = "Request ID" '<<<

        Grid.Columns("RequestID").Name = "colSR_RequestID"
        Grid.Columns("RequestID").Visible = True
        Grid.Columns("RequestID").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
    End Sub
End Class

Open in new window

0
 
LVL 17

Expert Comment

by:nepaluz
ID: 35722747
I can only assume that the column named RequestID does not exist from the error message. Try putting a conditional statement......
If tbl.Columns.Contains("RequestID") Then tbl.Columns("RequestID").Caption = "Request ID" '<<<

Open in new window

0
 

Author Comment

by:bobcann
ID: 35722757
Ok, I'll do that. Thanks.
0
 

Author Comment

by:bobcann
ID: 35724730
Thank you, nepaluz. I created a dedicated method for each grid. For datagrid propeties the datagrid column name was used. It works perfectly!

-Bob
Public Sub FillFieldPickupGrid(ByRef Grid As System.Windows.Forms.DataGridView)
        Dim strQuery As String = gc_SelectFieldPickRequest & gv_SegmentID & gc_OrderBy & "bob.lhnd"

        Dim tbl As New DataTable
        Dim da As New SqlServerCe.SqlCeDataAdapter(strQuery, gc_ConnString)

        da.Fill(tbl)

        tbl.Columns("LHND").Caption = "House Number"
        tbl.Columns("STNAME").Caption = "Street Name"
        tbl.Columns("Description").Caption = "Borough"
        tbl.Columns("DESCRIPTION").Caption = "Status"
        tbl.Columns("SEGID").Caption = "SegmentID"

        Grid.DataSource = tbl

        Grid.Columns("colFP_HouseNumber").Visible = True
        Grid.Columns("colFP_HouseNumber").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells

        Grid.Columns("colFP_StreetName").Visible = True
        Grid.Columns("colFP_StreetName").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells

        Grid.Columns("colFP_Status").Visible = False
        Grid.Columns("colFP_Status").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells

        Grid.Columns("colFP_SeqmentID").Visible = False
        Grid.Columns("colFP_SeqmentID").AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
    End Sub

Open in new window

0
 
LVL 17

Expert Comment

by:nepaluz
ID: 35725318
I looked at the code again and the error was due to setting the captions on the columns BEFORE filling the table. I see you corrected that.

You can additionally improve the class by having a key-value collection (dictionary) to hold the column & caption names (passing them to the class if desired?), and assigning the values through a list intersect. (just some obiter dicta....)

On the whole, pleased to have helped you!
0
 

Author Comment

by:bobcann
ID: 35741932
Hi. I just discussed with my manager what you suggested. I will do that before the next project.

Thanks again.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

834 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