VB.net - Load Datagridview Record Processing

Jimbo99999
Jimbo99999 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
2) How do I just load 1 of the records Username, First and Last to the DataGridView?
Private sub load1user()
Dim dt As New DataTable
        Dim ss As String = "select username, first, last, permision from yourtable where username=$username"
        Dim sa As SQLiteDataAdapter = New SQLiteDataAdapter(ss, yourconnectionstring)
        sa.SelectCommand.Parameters.AddWithValue("$username", Textbox1.Text)
        sa.Fill(dt)
        If dt.Rows.Count = 0 Then
            GoTo NoRecord
        End If
        DataGridView1.DataSource = dt
        DataGridView1.ReadOnly = True
        DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
NoRecord:
        sa.Dispose()
    End Sub

Open in new window


1) How do I loop through the records and get all the permissions to an array so I can
    check the corresponding checkboxes?
The loop can be found at number 2 question
and try to add this code to have a checkbox in datagridview
http://vb.net-informations.com/datagridview/vb.net_datagridview_checkbox.htm
Dim chk As New DataGridViewCheckBoxColumn()
DataGridView1.Columns.Add(chk)
chk.HeaderText = "Check Data"
chk.Name = "chk"
DataGridView1.Rows(2).Cells(3).Value = True

Open in new window

Or you can simply use pivot in sql to get the data in following format:-

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/
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("Permission"))
                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
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Most Valuable Expert 2012
Top Expert 2014

Commented:
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, ...

Commented:
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,Delete,Edit)
   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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial