Viewing selected columns

Question

Hi all,

I'm doing .NET 3.5 programming in VB for a class. I have a .mdb database with 3 related tables, and a table adapter with some queries on it that look like this:

SELECT     PropertyID, Street, Unit, City, Zip, Type, Bedrooms, Bathrooms, Area, MonthlyRent
FROM         tblProperties

Then in a form i have a DataGridView. What i want to do is take the data that is returned from the query and display it in the DGV. However, when i do this, it displays all 35 columns in the database, not the 10 i selected (The ten are the only ones that have data in them however... so it's basically a table with a bunch of blank columns).

My current, inelegant solution is to return the query to a DataTable, then iterate through the table's columns, deleting the one's i dont want. This is not robust, efficient, and does not like me delete the primary key column.

My TA suggested trying to use an untyped databinding... he said this should display only the data I pull, but neither of us has been able to figure this out yet.

Thank You!

UPDATE

I'm not sure what you mean by the .aspx/.aspx.vb pages, but this is the query code i have from the table adapter

SELECT     tblRent.PaymentID, tblTenant.TenantName, tblProperties.Street, tblProperties.Unit, tblProperties.City, tblRent.AmountPaid, tblRent.PaymentDate, 
                      tblTenant.Telephone
FROM         ((tblProperties INNER JOIN
                      tblRent ON tblProperties.PropertyID = tblRent.PropertyID) INNER JOIN
                      tblTenant ON tblProperties.PropertyID = tblTenant.PropertyID)

and here is where i use it in code:

Public Sub getRent()
        propView.DataSource = TblPropertiesTableAdapter.GetAllRentReceipts()
        propView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
        propView.ReadOnly = True
    End Sub

propView is a DataGridView that does not have a DataSource selected at load

Answer 1

I'm assuming that you're using Windows forms and a DataGridView with AutoGenerateColumns turned on.

If you add you own columns only the ones that you select will appear:

propView.AutoGenerateColumns = false

For Each //of the columns that you want

    dim column as DataGridViewColumn = New DataGridViewColumn()
        column.DataPropertyName = "DB field name"
        column.HeaderText = "column title"

    propView.Columns.Add( column )
Next

Answer 2

If you build the pages using tags, you'll want the following code in the page...

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1">
</asp:GridView>

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:MY_ConnectionString %>" 
            SelectCommand="SELECT     tblRent.PaymentID, tblTenant.TenantName, tblProperties.Street, tblProperties.Unit, tblProperties.City, tblRent.AmountPaid, tblRent.PaymentDate,                       tblTenant.TelephoneFROM         ((tblProperties INNER JOIN                      tblRent ON tblProperties.PropertyID = tblRent.PropertyID) INNER JOIN                      tblTenant ON tblProperties.PropertyID = tblTenant.PropertyID)"></asp:SqlDataSource>

If alternatively you want to use code for the data layer, you could use something along the lines of the following...

    Public Class DataLayer
    Public Function GetData(ByVal query As String, ByVal params As System.Data.Common.DbParameter()) As System.Data.DataTable
        Dim dt As New System.Data.DataTable
        Dim constr As String = System.Configuration.ConfigurationManager.ConnectionStrings("constr").ConnectionString()
        Using cnObject As New System.Data.SqlClient.SqlConnection(constr)
            Using cmd As New System.Data.SqlClient.SqlCommand(query, cnObject)
                If Not params Is Nothing Then
                    For Each param In params
                        cmd.Parameters.Add(param)
                    Next
                End If
                Using da As New System.Data.SqlClient.SqlDataAdapter(cmd)
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Function
End Class

If you are using OLEDB connection, this function can be changed as follows (meaning you only have to change one function to update every use of it in the application - nice)

  Public Function GetDataOLE(ByVal query As String, ByVal params As System.Data.Common.DbParameter()) As System.Data.DataTable
        Dim dt As New System.Data.DataTable
        Dim constr As String = System.Configuration.ConfigurationManager.ConnectionStrings("constr").ConnectionString()
        Using cnObject As New System.Data.OleDb.OleDbConnection(constr)
            Using cmd As New System.Data.OleDb.OleDbCommand(query, cnObject)
                If Not params Is Nothing Then
                    For Each param In params
                        cmd.Parameters.Add(param)
                    Next
                End If
                Using da As New System.Data.OleDb.OleDbDataAdapter(cmd)
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Function

This function works is a generic data layer that will take in any SQL Command and output the data in a DataTable, which can simply be bound to a grid view or similar. You can check that the "query" is only selecting the columns you want but debugging the code and checking the SQL command to your database.

I would build it into a class so that any page could access it to load data into a data table.

Your code would become

Public Sub getRent()
    Dim dataLayer As New DataLayer()
    Dim sqlText As String = "<insert your query text here>"
    propView.DataSource = dataLayer.getData(sqlText, Nothing)
    propView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
    propView.ReadOnly = True
End Sub