Dynamic DataGrid

G

Guest

I have been trying for the last two weeks to display a dynamic DataGrid. The
data that I'm pulling from a SQL Server DB will have whole columns that will
be either NULL or 0. I want to display only the fields that have actual data
in them and either not display or hide the columns that have NULL or 0
values. As an example, one page may need to display a total of 5 columns and
another page may need to display three columns based on the product category
the user chooses. I have done this in classic ASP, but I can't figure out
how to do it with the DataGrid and ASP.NET.

The code I have so far looks like this.

Dim strFamily as String
strFamily = Request.QueryString("fam")
Dim strConnection as String, sqlConn as SQLConnection
strFamily = Request.QueryString("fam")
strConnection = ConfigurationSettings.AppSettings("ConnectionString")
sqlConn = New SqlConnection(strConnection)

Dim MyDataAdapter as SQLDataAdapter, MyDataSet as New DataSet
MyDataAdapter = New SQLDataAdapter("SELECT * FROM Items WHERE Part = '" &
Request.QueryString("part") & "'", strConnection)

MyDataAdapter.Fill(MyDataSet, "Items")

Dim i as Integer

'To navigate through the records.
For i = 0 To MyDataSet.Tables(0).Columns.Count - 1
Dim objbc As New BoundColumn()
objbc.DataField = MyDataSet.Tables(0).Columns(i).ColumnName
objbc.HeaderText = MyDataSet.Tables(0).Columns(i).ColumnName

If objbc.DataField = MyDataSet.Tables(0).Columns("Part").ColumnName Then
objbc.Visible = False
End If
If objbc.DataField = MyDataSet.Tables(0).Columns("Footnote").ColumnName Then
objbc.Visible = False
End If
If objbc.DataField = MyDataSet.Tables(0).Colum("Packaged").ColumnNameThen
objbc.Visible = False
End If
If objbc.DataField = MyDataSet.Tables(0).Column("PkgQuantity").ColumnNameThen
objbc.Visible = False
End If
If objbc.DataField = MyDataSet.Tables(0).Columns("PrintName").ColumnName Then
objbc.Visible = False
End If

dgItemDetail.Columns.Add(objbc)
dgItemDetail.DataSource = MyDataSet.Tables(0)
dgItemDetail.DataBind()
Next 'i

Any help is greatly appreciated. I'm already losing my hair and the last
few days it hasn't been getting any better!
 
T

tom pester

Hi,

This code works fine. Your reasoning was goed but the details were no good?

I used the Northwind db to test the code.

<%@ Page Language="VB" %>

<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<script runat="server">


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

Dim strFamily As String
strFamily = Request.QueryString("fam")
Dim strConnection As String, sqlConn As SqlConnection
strFamily = Request.QueryString("fam")
strConnection = Application("connNW")
sqlConn = New SqlConnection(strConnection)

Dim MyDataAdapter As SqlDataAdapter
Dim MyDataSet As New DataSet
MyDataAdapter = New SqlDataAdapter("SELECT * FROM Products", strConnection)

MyDataAdapter.Fill(MyDataSet, "Items")

Dim i As Integer


For i = 0 To MyDataSet.Tables(0).Columns.Count - 1

Dim objbc As New BoundColumn()

objbc.DataField = MyDataSet.Tables(0).Columns(i).ColumnName
objbc.HeaderText = MyDataSet.Tables(0).Columns(i).ColumnName

If objbc.DataField = MyDataSet.Tables(0).Columns("ProductName").ColumnName
Then
objbc.Visible = False
End If
If objbc.DataField = MyDataSet.Tables(0).Columns("SupplierID").ColumnName
Then
objbc.Visible = False
End If

dgItemDetail.Columns.Add(objbc)

Next

'Put that databind column out of the loop! You only need to call
it once
dgItemDetail.DataSource = MyDataSet.Tables(0)
dgItemDetail.DataBind()

End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataGrid runat="server" ID="dgItemDetail">
</asp:DataGrid>
</div>
</form>
</body>
</html>

Let me know if you have any more questions...

Cheers,
Tom Pester
 
G

Guest

Hi Tom,

First of all, I would like to apologize for my other post. The changeover
from classic ASP to ASP.NET has been frustrating so far to say the least.
Thanks for your reply.

I have applied the changes that you recommended and we are getting close. I
can see in the code where my problem is. I am wanting to generate columns
dynamically based on if the value of a column is NULL or 0. When I display a
DataGrid on this page, sometimes I may need to display 3 columns of data and
sometimes I may need to display 6 columns, depending on what product the user
chooses. If the value of a column is NULL, all the items in the product line
will be NULL. I haven't been able to figure out how to do this in a DataGrid
yet.

Thanks again,

Joe
 
T

tom pester

You have all the ingredients to make it work and I ran your code against
Northwind.

Can you be more specific where it goes wrong? Maybe paste your code again?


Cheers,
Tom Pester
 
S

Sparky Arbuckle

This sounds like a perfect candidate for ItemDataBound. Or you can do
what I'm currently doing and write a function that customizes and fills
a dataset accordingly.
 
G

Guest

I think that my problem is in this code.

If objbc.DataField = MyDataSet.Tables(0).Columns("Description3").ColumnName
Then
objbc.Visible = False
End If
If objbc.DataField = MyDataSet.Tables(0).Columns("Description4").ColumnName
Then
objbc.Visible = False
End If

These columns do not show in the DataGrid, but I think that I'm needing to
check for a NULL value first instead of calling the Column by name ex.
"Description3".

I think I need something like this pseudocode.

Start Loop

If Column ISNULL Then
Don't display the column
Else If there is something besides a NULL value Then
Display the column
End If

Loop

This is what I can't figure out how to do.

Thx,

Joe
 
S

Sparky Arbuckle

Dim dsRowCount as Integer = ds.Tables("DataTable").Rows.Count()
Dim strFieldName as string =
ds.Tables("DataTable").Rows(i)("FieldName")
ds.Tables("DataTable").Columns.Add("NewFieldName")
Dim i as Integer = 0

FOR i = 0 to dsRowCount - 1
If strFieldName = "" Then
ds.Tables("DataTable").Rows(i)("NewFieldName") = "A"
Else
ds.Tables("DataTable").Rows(i)("NewFieldName") = "B"
End If
Next i


Return ds.Tables("DataTable").DefaultView


Hopefully this can get you started with the logic. This is a longer way
to do it I am sure, but you will learn the in's and out's of the
problem and maybe force yourself to learn ItemDataBound!
 
T

tom pester

Hmmm I'm a right in stating the problem like this :

If the dataset looks like this

Col1 Col2 Col3 Col4
1 NULL 2 2
9 NULL 6 NULL
5 NULL 3 NULL

I want a datagrid that looks like this

Col1 Col3 Col4
1 2 2
9 6 NULL
5 3 NULL

Or do you want to also hide Col4 ? Or does a column containe either all values
for its columns or all NULL values

Before I start coding I want to know the poblem exactly.

Cheers,
Tom Peste
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dynamic DataGrid 1
database not updating 0
problems adding a second table to a dataset 4
Urgent.. prob w/ Datagrid edit button 2
adding edit button to datagrid problem 0
how to search/replace items in specific column of a datatable 2
Help Needed with VBA please help 0
Dynamic BoundColumn 2

Members online

Total: 84 (members: 2, guests: 82)
Robots: 283

Forum statistics

Threads
473,968
Messages
2,570,153
Members
46,699
Latest member
AnneRosen

Latest Threads

Top