Another UserProduct table of all products ( 2 columns: ProdID(int) and ProdShortDesc(varchar))
A page with a GridView defined as: <asp:GridViewID="gvUsers"runat="server"></asp:GridView>
The VB code creates a dynamic DataTable of userID, UserName and columns for every product in the UserProduct table. Then it reads the Users table and determines which products are used. It then uses the dynamic datatable as the DataSource for the GridView
and binds the data. Note: I separate the products in the Users table with a single '#', so I can split them. By using the UserProduct table the Gridview can have new products added by adding a product to the UserProduct table, without any code changes.
VB Code:
Imports System.Data.SqlClient
Public Class dynamicGV
Inherits System.Web.UI.Page
Dim tblUsers As New DataTable
Dim alProdIDs As New ArrayList
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
' Dynamically create the tblUsers - Columns: UserID, UserName, Product1, Product2...ProductN
Create_tblUsers()
' Get each users data
GetGvData()
' Bind Users data to GridView
gvUsers.DataSource = tblUsers
gvUsers.DataBind()
End Sub
Protected Sub Create_tblUsers()
' Read the UserProducts table for all Products
' UsersProducts : Columns: ProdID(int), ProdShortDesc(varchar)
' Use the ProdShortDesc to dynamically add column names to tblUsers
tblUsers.Columns.Add(New DataColumn("userID", GetType(String)))
tblUsers.Columns.Add(New DataColumn("UserName", GetType(String)))
Using SQLConn As New SqlConnection("YourConnectionString")
Using SQLcmd As New SqlCommand("SELECT ProdID, ProdShortDesc FROM UsersProducts", SQLConn)
SQLcmd.Connection = SQLConn
SQLcmd.CommandType = CommandType.Text
SQLConn.Open()
Using reader As SqlDataReader = SQLcmd.ExecuteReader(CommandBehavior.CloseConnection)
If reader.HasRows Then
While reader.Read()
alProdIDs.Add(reader("ProdID")) ' Save ProdIDs to the ArrayList
tblUsers.Columns.Add(New DataColumn(reader("ProdShortDesc"), GetType(Boolean))) ' Boolean=CheckBox
End While
End If 'reader.HasRows
End Using
End Using
End Using
End Sub
Protected Sub GetGvData()
' Read Users table and populate the dynamic DataTable tblUsers
Using SQLConn As New SqlConnection("YourConnectionString")
Using SQLcmd As New SqlCommand("SELECT userID, UserName, UserProduct FROM Users", SQLConn)
SQLcmd.Connection = SQLConn
SQLcmd.CommandType = CommandType.Text
SQLConn.Open()
Using reader As SqlDataReader = SQLcmd.ExecuteReader(CommandBehavior.CloseConnection)
If reader.HasRows Then
While reader.Read()
Dim dr As DataRow = tblUsers.NewRow()
dr("userID") = reader("userID")
dr("UserName") = reader("UserName")
Dim ProdList As String() = Split(reader("UserProduct"), "#")
For I As Int16 = 0 To alProdIDs.Count - 1 ' Check all products
Dim CheckedOrNot As Int16 = 0
' Determine if User uses Product:
' Compare the Users.UserProduct to the Arraylist of the ProdIDs
For J = 0 To ProdList.Count - 1
If alProdIDs(I) = ProdList(J) Then
CheckedOrNot = 1
Exit For
End If
Next J
dr(I + 2) = CheckedOrNot ' Skip first 2 columns in tblUsers(userID and UserName)
Next I
tblUsers.Rows.Add(dr)
End While
End If 'reader.HasRows
End Using
End Using
End Using
End Sub
End Class
Data:
Table: Users
userID UserName UserProduct
1 Name 1 1#3#2#5
2 Name 2 3#2
3 Name 3 1
4 Name 4 5#2
md_refay
Member
416 Points
280 Posts
Need help to Bind Data from Database in Gridview
May 17, 2011 01:32 PM|LINK
Hi all,
i table in my database called Users which contains UserID , UserName , UserProducts
the data in table can be like that :-
userID = 1 , UserName = John , UserProduct = #0##1##3# (in my system i have 10 Products)
so #0# means P1 and #3# means P3
so another user can have UserProduct = #0##1##9# (which means user have P1,P2,P10)
so i want to Bind user products in Gridview to be like this
and any user have product from the 10 Products will have checked checkbox control and if he doesn't have the checkbox will not be checked
Please help me
Thanks alot
Darknight_36...
Member
55 Points
20 Posts
Re: Need help to Bind Data from Database in Gridview
May 17, 2011 03:53 PM|LINK
use a dynamic datatable , create the columns and rows dynamically based on the query results, then bind the datatable to the gridview
mandarj123
Member
708 Points
291 Posts
Re: Need help to Bind Data from Database in Gridview
May 18, 2011 07:06 AM|LINK
i wonder if you can change your database structure a little bit.
having another table called products with ten columns p1-p10 bit columns and userId column
your coding would be very straight forward. if you are showing these code somewhere then just for the display purpose have
the current UserProduct column in User Table.
Capgemini India.
paindaasp
Star
12092 Points
2035 Posts
Re: Need help to Bind Data from Database in Gridview
May 18, 2011 01:57 PM|LINK
Here is another possible solution, which uses:
The VB code creates a dynamic DataTable of userID, UserName and columns for every product in the UserProduct table. Then it reads the Users table and determines which products are used. It then uses the dynamic datatable as the DataSource for the GridView and binds the data. Note: I separate the products in the Users table with a single '#', so I can split them. By using the UserProduct table the Gridview can have new products added by adding a product to the UserProduct table, without any code changes.
VB Code:
Imports System.Data.SqlClient Public Class dynamicGV Inherits System.Web.UI.Page Dim tblUsers As New DataTable Dim alProdIDs As New ArrayList Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load ' Dynamically create the tblUsers - Columns: UserID, UserName, Product1, Product2...ProductN Create_tblUsers() ' Get each users data GetGvData() ' Bind Users data to GridView gvUsers.DataSource = tblUsers gvUsers.DataBind() End Sub Protected Sub Create_tblUsers() ' Read the UserProducts table for all Products ' UsersProducts : Columns: ProdID(int), ProdShortDesc(varchar) ' Use the ProdShortDesc to dynamically add column names to tblUsers tblUsers.Columns.Add(New DataColumn("userID", GetType(String))) tblUsers.Columns.Add(New DataColumn("UserName", GetType(String))) Using SQLConn As New SqlConnection("YourConnectionString") Using SQLcmd As New SqlCommand("SELECT ProdID, ProdShortDesc FROM UsersProducts", SQLConn) SQLcmd.Connection = SQLConn SQLcmd.CommandType = CommandType.Text SQLConn.Open() Using reader As SqlDataReader = SQLcmd.ExecuteReader(CommandBehavior.CloseConnection) If reader.HasRows Then While reader.Read() alProdIDs.Add(reader("ProdID")) ' Save ProdIDs to the ArrayList tblUsers.Columns.Add(New DataColumn(reader("ProdShortDesc"), GetType(Boolean))) ' Boolean=CheckBox End While End If 'reader.HasRows End Using End Using End Using End Sub Protected Sub GetGvData() ' Read Users table and populate the dynamic DataTable tblUsers Using SQLConn As New SqlConnection("YourConnectionString") Using SQLcmd As New SqlCommand("SELECT userID, UserName, UserProduct FROM Users", SQLConn) SQLcmd.Connection = SQLConn SQLcmd.CommandType = CommandType.Text SQLConn.Open() Using reader As SqlDataReader = SQLcmd.ExecuteReader(CommandBehavior.CloseConnection) If reader.HasRows Then While reader.Read() Dim dr As DataRow = tblUsers.NewRow() dr("userID") = reader("userID") dr("UserName") = reader("UserName") Dim ProdList As String() = Split(reader("UserProduct"), "#") For I As Int16 = 0 To alProdIDs.Count - 1 ' Check all products Dim CheckedOrNot As Int16 = 0 ' Determine if User uses Product: ' Compare the Users.UserProduct to the Arraylist of the ProdIDs For J = 0 To ProdList.Count - 1 If alProdIDs(I) = ProdList(J) Then CheckedOrNot = 1 Exit For End If Next J dr(I + 2) = CheckedOrNot ' Skip first 2 columns in tblUsers(userID and UserName) Next I tblUsers.Rows.Add(dr) End While End If 'reader.HasRows End Using End Using End Using End Sub End ClassData:
Table: Users
userID UserName UserProduct
1 Name 1 1#3#2#5
2 Name 2 3#2
3 Name 3 1
4 Name 4 5#2
Table: UsersProducts
ProdID ProdShortDesc
1 Prod1
2 Prod2
3 Prod3
4 Prod4
5 Prod5
6 Prod6
I hope this helps and makes sense.