How to Display Data in Listview in VB6 using mysql

Question

fritzgarcia_29 0 Newbie Poster

14 Years Ago

I have dtpker1 and dtpker2 to choose from the date and I want to display in Listview. the problem with this code is that it cannot display dates from the listview.

Please help me with the right syntax plzzzzzzzz.........:)

Thank you so much!!

Option Explicit
Dim rs  As New Recordset
Dim sum As Double
Dim alert As String
Private Sub cmdDisplay_Click()
If Val(Me.Datefrom.Month) < Val(Me.Dateto.Month) Then
    alert = MsgBox("Invalid Date Range. Begining range should be earlier than end range.", vbOKOnly + vbInformation, "Invalid Date")
Else
    If Val(Me.Dateto.Day) > Val(Me.Datefrom.Day) Then
        alert = MsgBox("Invalid Date Range. Begining range should be earlier than end range.", vbOKOnly + vbInformation, "Invalid Date")
        Me.Dateto.Value = Date
    Else
    End If
End If
Me.lvList.ListItems.Clear
    Set rs = New ADODB.Recordset
    With rs
        .Open "select * from tbl_planted where Date_Projectedharvest between '" & (Datefrom.Value) & "' And '" & (Dateto.Value) & "'", myCn, 1, 2
        While Not .EOF
            lvList.ListItems.Add , "Key" & rs("Account_No"), rs("Account_No")
            lvList.ListItems("Key" & rs("Account_No")).SubItems(1) = rs("Lastname")
            lvList.ListItems("Key" & rs("Account_No")).SubItems(2) = rs("Firstname")
            lvList.ListItems("Key" & rs("Account_No")).SubItems(3) = rs("MI")
            lvList.ListItems("Key" & rs("Account_No")).SubItems(4) = rs("Area")
            lvList.ListItems("Key" & rs("Account_No")).SubItems(5) = rs("Date_Planted")
            lvList.ListItems("Key" & rs("Account_No")).SubItems(6) = rs("Date_Projectedharvest")
            lvList.ListItems("Key" & rs("Account_No")).SubItems(7) = rs("Inspector1")
            lvList.ListItems("Key" & rs("Account_No")).SubItems(8) = rs("Inspector2")
            .MoveNext
        Wend
        
    End With
    Dim sql  As String
    Dim X
    sum = 0
    For X = 1 To lvList.ListItems.Count
    sum = sum + CDbl(lvList.ListItems.Item(X).SubItems(4))
    Next X
    Me.lblTotalareaplanted.Caption = "Total Area Planted: " & sum
End Sub


Private Sub Form_Load()

Me.Datefrom.Value = Now
Me.Dateto.Value = Now
Set rs = New adodb.Recordset
rs.Open "select * from tbl_planted", myCn, 1, 2
While Not rs.EOF
 lvList.ListItems.Add , "Key" & rs("Account_No"), rs("Account_No")
 lvList.ListItems("Key" & rs("Account_No")).SubItems(1) = rs("Lastname")
 lvList.ListItems("Key" & rs("Account_No")).SubItems(2) = rs("Firstname")
 lvList.ListItems("Key" & rs("Account_No")).SubItems(3) = rs("MI")
 lvList.ListItems("Key" & rs("Account_No")).SubItems(4) = rs("Area")
 lvList.ListItems("Key" & rs("Account_No")).SubItems(5) = rs("Date_Planted")
 lvList.ListItems("Key" & rs("Account_No")).SubItems(6) = rs("Date_Projectedharvest")
 lvList.ListItems("Key" & rs("Account_No")).SubItems(7) = rs("Inspector1")
 lvList.ListItems("Key" & rs("Account_No")).SubItems(8) = rs("Inspector2")
 rs.MoveNext
 Wend
 Me.lblnumberofapplicants.Caption = "Number of Applicants: " & Me.lvList.ListItems.Count
 rs.Close
 
 'add
    
End Sub

Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)
If Button.Index = 1 Then
Unload Me
frmLoadingreportlistofharvest.Show 1
ElseIf Button.Index = 3 Then
MsgBox "Printing", vbInformation
Else
Unload Me
End If
End Sub

mysql visual-basic

4 Contributors
5 Replies
2K Views
10 Months Discussion Span
Latest Post 14 Years Ago Latest Post by AndreRet

Pilgrim-Last 0 Newbie Poster

14 Years Ago

I have dtpker1 and dtpker2 to choose from the date and I want to display in Listview. the problem with this code is that it cannot display dates from the listview.

Please help me with the right syntax plzzzzzzzz.........:)

Thank you so much!!

Option Explicit
Dim rs  As New Recordset
Dim sum As Double
Dim alert As String
Private Sub cmdDisplay_Click()
If Val(Me.Datefrom.Month) < Val(Me.Dateto.Month) Then
    alert = MsgBox("Invalid Date Range. Begining range should be earlier than end range.", vbOKOnly + vbInformation, "Invalid Date")
Else
    If Val(Me.Dateto.Day) > Val(Me.Datefrom.Day) Then
        alert = MsgBox("Invalid Date Range. Begining range should be earlier than end range.", vbOKOnly + vbInformation, "Invalid Date")
        Me.Dateto.Value = Date
    Else
    End If
End If
Me.lvList.ListItems.Clear
    Set rs = New ADODB.Recordset
    With rs
        .Open "select * from tbl_planted where Date_Projectedharvest between '" & (Datefrom.Value) & "' And '" & (Dateto.Value) & "'", myCn, 1, 2
        While Not .EOF
            lvList.ListItems.Add , "Key" & rs("Account_No"), rs("Account_No")
            lvList.ListItems("Key" & rs("Account_No")).SubItems(1) = rs("Lastname")
            lvList.ListItems("Key" & rs("Account_No")).SubItems(2) = rs("Firstname")
            lvList.ListItems("Key" & rs("Account_No")).SubItems(3) = rs("MI")
            lvList.ListItems("Key" & rs("Account_No")).SubItems(4) = rs("Area")
            lvList.ListItems("Key" & rs("Account_No")).SubItems(5) = rs("Date_Planted")
            lvList.ListItems("Key" & rs("Account_No")).SubItems(6) = rs("Date_Projectedharvest")
            lvList.ListItems("Key" & rs("Account_No")).SubItems(7) = rs("Inspector1")
            lvList.ListItems("Key" & rs("Account_No")).SubItems(8) = rs("Inspector2")
            .MoveNext
        Wend
        
    End With
    Dim sql  As String
    Dim X
    sum = 0
    For X = 1 To lvList.ListItems.Count
    sum = sum + CDbl(lvList.ListItems.Item(X).SubItems(4))
    Next X
    Me.lblTotalareaplanted.Caption = "Total Area Planted: " & sum
End Sub


Private Sub Form_Load()

Me.Datefrom.Value = Now
Me.Dateto.Value = Now
Set rs = New adodb.Recordset
rs.Open "select * from tbl_planted", myCn, 1, 2
While Not rs.EOF
 lvList.ListItems.Add , "Key" & rs("Account_No"), rs("Account_No")
 lvList.ListItems("Key" & rs("Account_No")).SubItems(1) = rs("Lastname")
 lvList.ListItems("Key" & rs("Account_No")).SubItems(2) = rs("Firstname")
 lvList.ListItems("Key" & rs("Account_No")).SubItems(3) = rs("MI")
 lvList.ListItems("Key" & rs("Account_No")).SubItems(4) = rs("Area")
 lvList.ListItems("Key" & rs("Account_No")).SubItems(5) = rs("Date_Planted")
 lvList.ListItems("Key" & rs("Account_No")).SubItems(6) = rs("Date_Projectedharvest")
 lvList.ListItems("Key" & rs("Account_No")).SubItems(7) = rs("Inspector1")
 lvList.ListItems("Key" & rs("Account_No")).SubItems(8) = rs("Inspector2")
 rs.MoveNext
 Wend
 Me.lblnumberofapplicants.Caption = "Number of Applicants: " & Me.lvList.ListItems.Count
 rs.Close
 
 'add
    
End Sub

Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)
If Button.Index = 1 Then
Unload Me
frmLoadingreportlistofharvest.Show 1
ElseIf Button.Index = 3 Then
MsgBox "Printing", vbInformation
Else
Unload Me
End If
End Sub

I assume that the dates in question that do not display correctly are:

rs("Date_Planted")

rs("Date_Projectedharvest")

I think that all you need to do is use the format statement.

So if rs("Date_Planted") is say 27th of June 2010 for example then
just replace the rs("Date_Planted") with:

Format(rs("Date_Planted"),"dd mmmm yyyy") to Output 27 June 2010
or
Format(rs("Date_Planted"),"dd mmm yyyy") to Output 27 Jun 2010
or
Format(rs("Date_Planted"),"dd/mm/yyyy") to Output 27/06/2010 (Uk date)
or
Format(rs("Date_Planted"),"mm/dd/yy") to Output 06/26/10 (Us date)

Look in help for other formats it can do times as well !

Hope thats what your after.

AndreRet 526 Senior Poster

14 Years Ago

Hi guys. Just some unspoken of rules here, please do not raise the dead. I am sure that Fritz found a solution to his problem by now. His post appeared in Jauary 2010 already.

The problem is that not every one marks his posts as solved and it keeps "hanging" around wishing someone will raise it again.

Nice in replying though, thanks

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

adol 0 Newbie Poster · Answer 1 · 2010-07-13T14:45:39+00:00

ahm nice,,..
give me a lot of knowledge ty..,.

fritzgarcia_29 0 Newbie Poster · Answer 2 · 2010-11-13T08:59:21+00:00

fritzgarcia_29 0 Newbie Poster

14 Years Ago

Solved! Thanks for youre reply guys!

AndreRet 526 Senior Poster · Answer 3 · 2010-11-13T15:53:24+00:00

Thanks for your reply Fritz. Please mark the thread as solved as well, thanks.

You'll find the solve option at the bottom of this page.:)