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

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
bobcannAsked:
Who is Participating?
 
nepaluzConnect With a Mentor Commented:
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
 
CodeCruiserCommented:
You are passing a DataGrid to a function which is expecting a DataGridView. Why?
0
 
bobcannAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
nepaluzCommented:
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
 
bobcannAuthor Commented:
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
 
nepaluzCommented:
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
 
bobcannAuthor Commented:
Ok, I'll do that. Thanks.
0
 
bobcannAuthor Commented:
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
 
nepaluzCommented:
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
 
bobcannAuthor Commented:
Hi. I just discussed with my manager what you suggested. I will do that before the next project.

Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.