Jimbo99999
asked on
VB.net - Load Datagridview Record Processing
Good Day Experts!
Man do I need some direction on this one. I have a Permissions table in a SQL db that I am querying by User. In the table, each permission is a record. So, a User can have several permissions(several records in the table).
Lets say Bob has 3 permissions attached to his Username...so he has 3 records in the table.
Username First Last ..... Permission
BobS Bob Smith Add
BobS Bob Smith Update
BobS Bob Smith Delete
Requirement:
1) Put Username, First, Last in only one line of a DataGridView.
2) Check the corresponding checkbox on the Tab based on each permission( I have
checkboxes for each possible permission on a panel on the Tab).
Background:
I can get all 3 records to load in the DataGridView so I know I can return the data from
db.
Questions:
1) How do I loop through the records and get all the permissions to an array so I can
check the corresponding checkboxes?
2) How do I just load 1 of the records Username, First and Last to the DataGridView?
I appreciate any assistance you all can provide with this task,
jimbo99999
Man do I need some direction on this one. I have a Permissions table in a SQL db that I am querying by User. In the table, each permission is a record. So, a User can have several permissions(several records in the table).
Lets say Bob has 3 permissions attached to his Username...so he has 3 records in the table.
Username First Last ..... Permission
BobS Bob Smith Add
BobS Bob Smith Update
BobS Bob Smith Delete
Requirement:
1) Put Username, First, Last in only one line of a DataGridView.
2) Check the corresponding checkbox on the Tab based on each permission( I have
checkboxes for each possible permission on a panel on the Tab).
Background:
I can get all 3 records to load in the DataGridView so I know I can return the data from
db.
Questions:
1) How do I loop through the records and get all the permissions to an array so I can
check the corresponding checkboxes?
2) How do I just load 1 of the records Username, First and Last to the DataGridView?
I appreciate any assistance you all can provide with this task,
jimbo99999
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I give you a very clear demonstration on how to achieve and you fine tune the code when you understand the concept.
Assumming the possibilities of permission are : Add, Edit, Delete, Update
I created a datagridview with predesigned columns:
Username, First Name, Last Name as textboxes
Then, Add, Edit, Delete, Update as checkboxes
'The following assumming calling the database to get one user information.
'You can actually further edit it and you can handle all users populated into the same grid
'This example will populate one line into the datagridview showing all permissions of
'one User, checking the relevant box of the permission given
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'So Assumming you find 4 records for user, all checkboxes will be checked
Dim eUser, eFirst, eLast As String
DataGridView1.Rows.Clear()
Dim Cmd As New SqlCommand("SELECT Username, Firstname, Lastname, Permission FROM Mytable WHERE Username = @Username", SqlConnection)
Cmd.Parameters.Add(New SqlParameter("@Username", "User1"))
Dim rd As SqlDataReader = Nothing
rd = Cmd.ExecuteReader
Try
While rd.Read
eUser = rd("Username").ToString
eFirst = rd("Firstname").ToString
eLast = rd("Lastname").ToString()
Dim Myresults() As Boolean = CheckPermission(rd("Permis sion"))
Dim Str As String() = {rd("Username"), rd("Firstname"), rd("Lastname").ToString, Myresults(0), Myresults(1), Myresults(2), Myresults(3)}
DataGridView1.Rows.Add(Str )
End While
Catch xSql As SqlException
Dim Er As String = CatchSQLError(xSql)
MsgBox("Exception Error executing (" & Cmd.CommandText & ")" & vbCrLf & "Error file is logged in default error folder as " & Er, MsgBoxStyle.Exclamation, "Fishing Web Manager")
Catch eExr As Exception
Dim Err As String = CatchError(eExr)
MsgBox("Exception Error executing (" & Cmd.CommandText & ")" & vbCrLf & "Error file is logged in default error folder as " & Err, MsgBoxStyle.Exclamation, "Fishing Web Manager")
Finally
rd.Close()
Cmd.Dispose()
End Try
End Sub
Private Function CheckPermission(ByVal Per As String) As Boolean()
Dim results(3) As Boolean
Dim xAdd As Boolean = False
Dim xEdit As Boolean = False
Dim xDelete As Boolean = False
Dim xUpdate As Boolean = False
Select Case Per
Case "Add"
xAdd = True
Case "Edit"
xEdit = True
Case "Delete"
xDelete = True
Case "Update"
xUpdate = True
End Select
results(0) = xAdd
results(1) = xEdit
results(2) = xDelete
results(3) = xUpdate
Return results
End Function
Assumming the possibilities of permission are : Add, Edit, Delete, Update
I created a datagridview with predesigned columns:
Username, First Name, Last Name as textboxes
Then, Add, Edit, Delete, Update as checkboxes
'The following assumming calling the database to get one user information.
'You can actually further edit it and you can handle all users populated into the same grid
'This example will populate one line into the datagridview showing all permissions of
'one User, checking the relevant box of the permission given
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'So Assumming you find 4 records for user, all checkboxes will be checked
Dim eUser, eFirst, eLast As String
DataGridView1.Rows.Clear()
Dim Cmd As New SqlCommand("SELECT Username, Firstname, Lastname, Permission FROM Mytable WHERE Username = @Username", SqlConnection)
Cmd.Parameters.Add(New SqlParameter("@Username", "User1"))
Dim rd As SqlDataReader = Nothing
rd = Cmd.ExecuteReader
Try
While rd.Read
eUser = rd("Username").ToString
eFirst = rd("Firstname").ToString
eLast = rd("Lastname").ToString()
Dim Myresults() As Boolean = CheckPermission(rd("Permis
Dim Str As String() = {rd("Username"), rd("Firstname"), rd("Lastname").ToString, Myresults(0), Myresults(1), Myresults(2), Myresults(3)}
DataGridView1.Rows.Add(Str
End While
Catch xSql As SqlException
Dim Er As String = CatchSQLError(xSql)
MsgBox("Exception Error executing (" & Cmd.CommandText & ")" & vbCrLf & "Error file is logged in default error folder as " & Er, MsgBoxStyle.Exclamation, "Fishing Web Manager")
Catch eExr As Exception
Dim Err As String = CatchError(eExr)
MsgBox("Exception Error executing (" & Cmd.CommandText & ")" & vbCrLf & "Error file is logged in default error folder as " & Err, MsgBoxStyle.Exclamation, "Fishing Web Manager")
Finally
rd.Close()
Cmd.Dispose()
End Try
End Sub
Private Function CheckPermission(ByVal Per As String) As Boolean()
Dim results(3) As Boolean
Dim xAdd As Boolean = False
Dim xEdit As Boolean = False
Dim xDelete As Boolean = False
Dim xUpdate As Boolean = False
Select Case Per
Case "Add"
xAdd = True
Case "Edit"
xEdit = True
Case "Delete"
xDelete = True
Case "Update"
xUpdate = True
End Select
results(0) = xAdd
results(1) = xEdit
results(2) = xDelete
results(3) = xUpdate
Return results
End Function
I think the first thing that you need to do is do some normalization. Break the table into two
Table: Users
UserId, UserName, FirstName, LastName, ...
Table: Permissions
PermissionID, UserID, Permission, ...
Table: Users
UserId, UserName, FirstName, LastName, ...
Table: Permissions
PermissionID, UserID, Permission, ...
Man, you have a good code style, simple.
Ok,
You want the output like this?
Username First Last ..... Permission
BobS Bob Smith Add
BobS Bob Smith Update
BobS Bob Smith Delete
Because base from your codes snippet the output looks like this:
Username First Last Permission(Add,Update,Dele te,Edit)
BobS Bob Smith True False False False True
Ryan Bro Jack False True True False True
Ok,
You want the output like this?
Username First Last ..... Permission
BobS Bob Smith Add
BobS Bob Smith Update
BobS Bob Smith Delete
Because base from your codes snippet the output looks like this:
Username First Last Permission(Add,Update,Dele
BobS Bob Smith True False False False True
Ryan Bro Jack False True True False True
As advised by CodeCruiser, it's a very good practice, it will make your future manipulation much easier
FullName Add Update Delete
Bob Petty Y Y Y
see here for example:-
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/