Private Sub PopulateAccess()
Dim sQuery As String = "SELECT Cards.CardNo AS [Card No], Personnel.Name AS [Name], Cards.Expiry_Date AS [Door & Lift Expiry Date], CP_Card.ExpiryDate AS [Car Park Expiry Date], " & _ "Cards.AccTag AS [Door Access Rights], Cards.LiftAccTag AS [Lift Access Rights], Personnel.StaffNo, CP_Card.SendBit " & _ "FROM Cards LEFT OUTER JOIN Personnel ON Cards.StaffNo = Personnel.StaffNo " & _ "LEFT OUTER JOIN CP_Card ON Cards.CardNo = CP_Card.CardNo " & _ "ORDER BY Cards.CardNo " Dim dsAccess As New DataSet() Dim DBClass As New clsDB.clsDatabase DBClass.DBServer = DBPath
Try
If Not DBClass.ExecuteSQLTable(sQuery, dsAccess) Then MsgBox("Unable to retrieve information from database!", MsgBoxStyle.Exclamation, frmTitle) LOG.WriteErrLog(0, "fBlock_Extend - PopulateAccess : Failed to retrieve info from database <" & sQuery & ">") Exit Sub End If
With dsAccess.Tables(0) .Columns.Add("Car Park Access Rights")
If .Rows.Count > 0 Then ReDim SearchList(.Rows.Count - 1) ReDim aStfNo(.Rows.Count - 1)
For icnt As Integer = 0 To .Rows.Count - 1 For jcnt As Integer = 0 To .Columns.Count - 1
If jcnt = 1 Then .Rows(icnt).Item(jcnt) = Trim(.Rows(icnt).Item(jcnt))
ElseIf jcnt = 2 Or jcnt = 3 Then If .Rows(icnt).Item(jcnt) Is Convert.DBNull Then .Rows(icnt).Item(jcnt) = "-" Else .Rows(icnt).Item(jcnt) = Mid(.Rows(icnt).Item(jcnt), 7, 2) & "/" & _ Mid(.Rows(icnt).Item(jcnt), 5, 2) & "/" & _ Mid(.Rows(icnt).Item(jcnt), 1, 4) End If
ElseIf jcnt = 4 Then .Rows(icnt).Item(jcnt) = GetDoorAccess(Trim(.Rows(icnt).Item(0))) If .Rows(icnt).Item(jcnt) = "" Then .Rows(icnt).Item(jcnt) = "-" End If
ElseIf jcnt = 6 Then .Rows(icnt).Item(jcnt) = Trim(.Rows(icnt).Item(jcnt)) aStfNo(icnt) = .Rows(icnt).Item(jcnt)
ElseIf jcnt = .Columns.Count - 2 Then If .Rows(icnt).Item(jcnt) Is Convert.DBNull Then .Rows(icnt).Item(jcnt + 1) = "-" Else .Rows(icnt).Item(jcnt + 1) = GetCPAccess(Trim(.Rows(icnt).Item(0))) End If
Else If .Rows(icnt).Item(jcnt) = "" Then .Rows(icnt).Item(jcnt) = "-" Else .Rows(icnt).Item(jcnt) = Trim(.Rows(icnt).Item(jcnt)) End If End If
Next Next End If .Columns.Remove("SendBit") .Columns.Remove("StaffNo")
End With
dgvBlock_Acc.DataSource = dsAccess.Tables(0)
addCheckBox()
Catch ex As Exception LOG.WriteErrLog(0, "Block & Extend - Populate Access : " & ex.Message) Finally DBClass.Close() End Try
End Sub
Private Sub addCheckBox()
Try Dim chk As New DataGridViewCheckBoxColumn dgvBlock_Acc.Columns.Insert(5, chk)
Dim chk2 As New DataGridViewCheckBoxColumn dgvBlock_Acc.Columns.Insert(7, chk2)
Dim chk3 As New DataGridViewCheckBoxColumn dgvBlock_Acc.Columns.Insert(9, chk3)
For i As Integer = 0 To dgvBlock_Acc.RowCount - 1 If dgvBlock_Acc.Rows(i).Cells(4).Value <> "-" Then dgvBlock_Acc.Rows(i).Cells(5).Value = True End If If dgvBlock_Acc.Rows(i).Cells(6).Value <> "-" Then dgvBlock_Acc.Rows(i).Cells(7).Value = True End If If dgvBlock_Acc.Rows(i).Cells(8).Value <> "-" Then dgvBlock_Acc.Rows(i).Cells(9).Value = True End If Next
Catch ex As Exception LOG.WriteErrLog(0, "frmBlockExt - Add check box : " & ex.Message) End Try
so how i'm gonna solve this error?thanx in advance.