?
Solved

query and displaying data

Posted on 2006-05-04
3
Medium Priority
?
159 Views
Last Modified: 2010-04-07
Hi Experts,

I have a form that calls a select query “on load”, since there will be more than one row returned from the query, what can I do to represent the data in an appealing way to the user?

Example:

       Label                    Label2                    Label3                    Label4                    Label5
1    queryresult           queryresult             queryresults            queryresults            queryresults    
2    queryresult           queryresult             queryresults            queryresults            queryresults    
3    queryresult           queryresult             queryresults            queryresults            queryresults    
4    queryresult           queryresult             queryresults            queryresults            queryresults    

In .asp it is a repeat region, how can I accomplish this with VB 2006?

Your assistance is greatly appreciated.
0
Comment
Question by:NCSO
  • 2
3 Comments
 
LVL 6

Accepted Solution

by:
PhilAI earned 900 total points
ID: 16606569
The ListView control should help...
It's a Component in the Microsoft Windows Common Controls 6.0 (SP6)

Here's my generic module. All you do is add a ListView with Columns where the Key field matches the field required from the database.

Calling ListView_RecSetPopulate with ListView object, Recordset that holds the result from your query, the filter on the recordset if necessary, the field to use as the Key property for each row, whether to automatically re-size all the columns to fit the data, whether to de-select the default record, whether to clear the listview before adding the recordset data)

Any more questions, please ask...

-------------------------------------------

Option Explicit

Public Const LVW_COL_4SORT As String = "4Sorting"
Public Const LVW_DT_FRMT As String = "yyyy-mm-dd hh:nn:ss"
Public Const LVW_KEY_SEP As String = "¦"

Private Declare Function SendMessage Lib "user32.dll" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

Public Sub ListView_ColAutoSize(objListView As Object, bLeaveZero As Boolean, bSizeByHeader As Boolean, ParamArray vExceptions())
'---------------------------------------------------------------------------------
' Description : Auto sizes the Columns within objListView, ignoring zero width
'               columns and sizing by the Headers or by the column data
'               N.B. Accepts collections of ListViews, or single ListView
'                    Cannot de-size the first column in ListView
'---------------------------------------------------------------------------------

Const LVM_SETCOLUMNWIDTH = &H1000 + 30
Const LVSCW_AUTOSIZE = -1
Const LVSCW_AUTOSIZE_USEHEADER = -2

Dim C As Integer
Dim lParam As Long

On Error Resume Next

lParam = IIf(bSizeByHeader, LVSCW_AUTOSIZE_USEHEADER, LVSCW_AUTOSIZE)

' Loop thru all the Columns and set the default width
For C = 0 To objListView.ColumnHeaders.Count - 1
    If Not bLeaveZero Or (bLeaveZero And objListView.ColumnHeaders(C + 1).Width > 0) Then
        Call SendMessage(objListView.hWnd, LVM_SETCOLUMNWIDTH, C, ByVal lParam)
    End If
Next C

' Loop thru the exceptions, zeroing their widths to hide them
For C = LBound(vExceptions) To UBound(vExceptions)
    If vExceptions(C) >= 0 Then
        Call SendMessage(objListView.hWnd, LVM_SETCOLUMNWIDTH, vExceptions(C), ByVal lParam)
    Else
        objListView.ColumnHeaders(Abs(vExceptions(C)) + 1).Width = 0
    End If
Next C

End Sub

Public Sub ListView_RecSetPopulate(lvwObject As ListView, rsList As Recordset, Optional vFilter, Optional ByVal vKeyField, Optional ByVal bAutoSizeCols As Boolean = True, Optional ByVal bDeSelectDefItem As Boolean = True, Optional ByVal bListViewClear As Boolean = True)
'---------------------------------------------------------------------------------
' Description : Loads a rsList into lvwObject, auto-sizing the columns,
'               de-selecting the default item, clearing lvwObject and emptying
'               rsList afterwards
'---------------------------------------------------------------------------------

Dim bFirstRec As Boolean
Dim fldKey As Field
Dim fldList() As Field
Dim lExists As Long
Dim lIndex As Long
Dim lvItem As ListItem
Dim lvcHeader As ColumnHeader
Dim lvcSorting As ColumnHeader
Dim mpcScreen As MousePointerConstants
Dim sKey As String
Dim sTextCur As String

On Error Resume Next

mpcScreen = Screen.MousePointer
Screen.MousePointer = vbHourglass

' If required to, Clear list first
If bListViewClear Then Call lvwObject.ListItems.Clear
DoEvents

' If the Recordset is valid, build the ListView
If Not rsList Is Nothing Then
    With rsList
        bFirstRec = True
        ' If required to, Filter the Recordset as requested
        If Not IsMissing(vFilter) Then .Filter = vFilter
        ReDim fldList(1 To lvwObject.ColumnHeaders.Count) As Field
        If Not IsMissing(vKeyField) Then Set fldKey = .Fields(vKeyField)
        If Err <> 0 Then Call Err.Clear

        ' Loop thru the Recordset matching the ListView Column Keys to the
        ' Database Field Names
        Do While Not .EOF
            If lvwObject.ListItems.Count > 10000 Then Exit Do
            If Err = 3704 Then GoTo PROCEDURE_EXIT

            For Each lvcHeader In lvwObject.ColumnHeaders
                With lvcHeader
                    If fldList(.Index) Is Nothing Then Set fldList(.Index) = rsList.Fields(.Key)
                    If Err = 0 Then sTextCur = IsNullEx(fldList(.Index), vbNullString)
                    If Err = 3265 Then ' Item cannot be found in the collection corresponding to the requested name or ordinal.
                        If .Index = 1 Then Set lvItem = Nothing
                        Call Err.Clear
                    ElseIf .Index = lIndex Or lIndex = 0 Then
                        If fldKey Is Nothing Then
                            If .Index = 1 Then
                                Set lvItem = lvwObject.ListItems.Add(Text:=sTextCur)
                            Else
                                Set lvItem = lvwObject.ListItems.Add(Text:=vbNullString)
                                lvItem.SubItems(.Index - 1) = sTextCur
                                Call Err.Clear
                            End If
                        ElseIf fldList(.Index).Name = fldKey.Name Then
                            sKey = fldKey.Name & LVW_KEY_SEP & sTextCur
                            sKey = lvwObject.ListItems(sKey).Key
                            If Err <> 0 Then
                                Set lvItem = lvwObject.ListItems.Add(Text:=sTextCur)
                                lvItem.Key = sKey
                                Call Err.Clear
                            End If
                        Else
                            sKey = sTextCur & LVW_KEY_SEP & IsNullEx(fldKey)
                            Set lvItem = lvwObject.ListItems(sKey)
                            If Err <> 0 Then
                                If .Index = 1 Then
                                    Set lvItem = lvwObject.ListItems.Add(Text:=sTextCur)
                                    lvItem.Key = sKey
                                Else
                                    Set lvItem = lvwObject.ListItems.Add(Text:=vbNullString)
                                    lvItem.Key = sKey
                                    lvItem.SubItems(.Index - 1) = sTextCur
                                End If: Call Err.Clear
                            End If
                        End If
                        ' Allow for flexible column sizes, by including a Tag
                        If Not lvItem Is Nothing Then
                            If sTextCur <> "0" And sTextCur <> vbNullString Then
                                If .Tag <> vbNullString Then .Width = 1
                            ElseIf .Tag <> vbNullString Then
                                If bAutoSizeCols And bFirstRec Then .Width = 0
                            End If
                        End If
                        ' Store the Index that first matches a Field in the database
                        If lIndex = 0 Then lIndex = .Index
                    ElseIf Not lvItem Is Nothing And .Key <> vbNullString Then
                        ' Convert any Fields for display purposes
                        Select Case .Key
                        Case Else
                            Set lvcSorting = lvwObject.ColumnHeaders(.Key & LVW_COL_4SORT)
                            If Err = 0 Then
                                If IsDate(sTextCur) Then
                                    lvItem.SubItems(lvcSorting.Index - 1) = Format(sTextCur, LVW_DT_FRMT)
                                End If
                            Else
                                Call Err.Clear
                            End If
                        End Select
                        lvItem.SubItems(.Index - 1) = sTextCur
                        ' Allow for flexible column sizes, by including a Tag
                        If sTextCur <> "0" And sTextCur <> vbNullString Then
                            If .Tag <> vbNullString Then .Width = 1
                        ElseIf .Tag <> vbNullString Then
                            If bAutoSizeCols And bFirstRec Then .Width = 0
                        End If
                    End If
                End With
            Next lvcHeader
            ' Move to the Next record and check for errors
            Call .MoveNext
            bFirstRec = False
            If Err <> 0 Then Exit Do
        Loop
    End With
End If

' If required to, Re-size the Columns based on the new data
If bAutoSizeCols Then Call ListView_ColAutoSize(lvwObject, True, True)
' If required to, De-select the default ListItem
If bDeSelectDefItem Then Call ListView_SelectState(lvwObject)

PROCEDURE_EXIT:
Set lvItem = Nothing
Set lvcSorting = Nothing
Set lvcHeader = Nothing
Set fldKey = Nothing
Erase fldList
Call lvwObject.Refresh

Screen.MousePointer = mpcScreen
DoEvents

End Sub

Public Function ListView_SelectState(lvwObject As ListView, Optional ByVal vListItem, Optional bEnsureVis As Boolean = True) As Boolean
'---------------------------------------------------------------------------------
' Description : Selects or De-Selects items within lvwObject
'               N.B. Accepts collections of ListViews, or single ListView
'---------------------------------------------------------------------------------

Dim L As Integer

On Error Resume Next

' Validate the vListItem parameter
Set vListItem = Var2ListItem(lvwObject, vListItem)

' If no Item is supplied, de-select the current Selected Item
If vListItem Is Nothing And Not lvwObject.SelectedItem Is Nothing Then
    lvwObject.SelectedItem.Selected = False
    Set lvwObject.SelectedItem = Nothing
' If Item is supplied, select that Item and ensure it's visible
Else
    vListItem.Selected = True
    Set lvwObject.SelectedItem = vListItem
    If bEnsureVis Then Call vListItem.EnsureVisible
End If

End Function
0
 

Author Comment

by:NCSO
ID: 16607612
PhilAI,

How do I set the value of each listview.item with the a TableAdapter.fill?  I hope that made sense!  I have a tblIncidentsTableAdapter.fill that returns (4) columns of data (Agency Report Number, Username, Officer, Date Added) respectfully.  I need for every row of each column to be shown.

Would I use:  listview1.Items.Add("TableAdapter.fill information here") or ????


Thanks
0
 
LVL 6

Expert Comment

by:PhilAI
ID: 16712846
Sorry, I don't recognise the TableAdapter.fill object and method you describe.

Is this VB.NET?
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month17 days, 7 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question