how do i retrieve date from sql database and show it in datetimepicker

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) Then

MsgBox(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

.Columns.Remove(

"SendBit")End With

dgvBlock_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 & ">")

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Karthikeyan Anbarasan15y ago
if you are not going to do any manipulation with the date... then save it as a string and show in the cell
zatie p15y ago
actually i used calender cell as in http://msdn.microsoft.com/en-us/library/7tas5c80.aspx to easily update the date.
now, in the date column, it will show blank cell. is it possible to show the date from database?
Suthish Nair15y ago
What error or problem facing?
vijay15y ago
hi...
clearly depicts your requirements...
your question is about date if you want display a string in date format,,use this code......
date="22-06-2010"
date=format(cdate(date),"dd/MM/yyyy")

MY BLOG