i have datagridview consist with card number, expiry date and accessibility.I want to retrieve date from database and show it with datetimepicker in 'expiry date' column. Attached is coding to populate the information.
Private Sub PopulateData()
DBClass.DBServer = DBPath
Dim DBClass As New clsDB.clsDatabase
Dim sQuery As String = "SELECT Cards.CardNo AS [Card No], Personnel.Name AS [Name], Cards.Expiry_Date AS [Expiry Date], " & _"Cards.AccTag AS [Door Access Rights], Cards.LiftAccTag AS [Lift Access Rights], 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 dsBlock_Ext As New DataSet()
Try
If Not DBClass.ExecuteSQLTable(sQuery, dsBlock_Ext) ThenMsgBox(LOG.WriteErrLog(0,
Exit SubEnd If
With dsBlock_Ext.Tables(0)
Columns.Add("Car Park Access Rights"
If .Rows.Count > 0 ThenReDim aStfNo(.Rows.Count - 1)
ReDim aCardNo(.Rows.Count - 1)
For icnt As Integer = 0 To .Rows.Count - 1
For jcnt As Integer = 0 To .Columns.Count - 1If jcnt = 0 Then
aCardNo(icnt) = Trim(.Rows(icnt).Item(jcnt))
ElseIf jcnt = 1 Then
.Rows(icnt).Item(jcnt) = Trim(.Rows(icnt).Item(jcnt))
ElseIf jcnt = 2 Then
.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
.Rows(icnt).Item(jcnt) = GetDoorAccess(Trim(.Rows(icnt).Item(0)))
ElseIf jcnt = 3 Then ElseIf jcnt = 4 Then
If .Rows(icnt).Item(jcnt) Is Convert.DBNull Then .Rows(icnt).Item(jcnt) =
Else End If
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) Is Convert.DBNull 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")End WithdgvBlock_Extend.DataSource = dsBlock_Ext.Tables(0)
Catch ex As Exception
LOG.WriteErrLog(0,
DBClass.Close()
"Block & Extend - Populate Data Grid : " & ex.Message)FinallyEnd Try
End Sub
thanx in advance =)
.Rows(icnt).Item(jcnt) = Trim(.Rows(icnt).Item(jcnt))
""
)
"Unable to retrieve information from database!", MsgBoxStyle.Exclamation, FrmTitle)"fBlock_Extend - PopulateDataGrid : Failed to retrieve info from database <" & sQuery & ">")