VB LISTVIEW 动态加载数据

[panyong]

表1:
ID-------代码--------用量
1--------101----------1
1--------201----------5
1--------301----------4
4--------101----------6
5--------401----------20
5--------501----------8
9--------101----------3
9--------201----------5
9--------301----------28
9--------401----------1
9--------501----------2


表2
代码----------名称
101-----------花
201-----------虫
301-----------蟹
401-----------虾
501-----------鱼

LISTVIEW 最终显示为
ID-----------101花-----201虫------301蟹--------401虾--------501鱼
1------------1----------5----------4------------无------------无
4------------6---------无----------无-----------无------------无
5------------无--------无----------无-----------20------------8
9------------3---------5------------28----------1-------------2


怎么用VB LISTVIEW 将数据读出来

这个应该是动态的添加列

[jhone99]

用你以前的例子，测试成功

Private Sub Command1_Click()
    Dim strSql As String
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim i As Integer
    Dim j As Integer
    
    cn.CursorLocation = adUseClient
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\tt\t1.mdb;Persist Security Info=False"
    
    strSql = ""
    strSql = strSql & " Transform Sum(用量) " & vbNewLine
    strSql = strSql & " SELECT 日期, 产量" & vbNewLine
    strSql = strSql & " FROM (select a1.日期, 明细, 用量, 产量" & vbNewLine
    strSql = strSql & " from a1, a2, (select 日期, sum(a1.产量) as 产量 from a1 group by 日期) a3" & vbNewLine
    strSql = strSql & " Where a1.id = a2.id And a1.日期 = a3.日期" & vbNewLine
    strSql = strSql & " )" & vbNewLine
    strSql = strSql & " GROUP BY 日期, 产量" & vbNewLine
    strSql = strSql & " Pivot 明细" & vbNewLine

    rs.Open strSql, cn, adOpenStatic, adLockOptimistic
    
    
    With ListView1
        '设置ListView1的标题、显示类型、整行选择和网格线属性
        .ColumnHeaders.Clear
        .ListItems.Clear
        .View = lvwReport
        .FullRowSelect = True
        .GridLines = True
        '为ListView1设置标题
        For i = 0 To rs.Fields.Count - 1
            .ColumnHeaders.Add , , rs.Fields(i).Name
        Next i
        '为ListView1设置各行数据
        .ListItems.Clear
        i = 0
        While Not rs.EOF
            i = i + 1
            .ListItems.Add , , rs.Fields(0).Value
            For j = 1 To rs.Fields.Count - 1
                .ListItems(i).SubItems(j) = rs.Fields(j).Value & ""
            Next j
            rs.MoveNext
        Wend
    End With

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    
End Sub

[Tiger_Zhao]

Option Explicit

Private Sub Command1_Click()
    Dim cn          As ADODB.Connection
    Dim rsCol       As ADODB.Recordset
    Dim rsRow       As ADODB.Recordset
    Dim rsData      As ADODB.Recordset
    Dim oSubCode    As Collection
    Dim lvi         As ListItem
    Dim sCriteria   As String
    Dim i           As Long
    
    Set cn = New ADODB.Connection
    cn.Open "..."
    
    Set rsCol = cn.Execute("SELECT * FROM 表2")
    Set rsRow = cn.Execute("SELECT DISTINCT ID FROM 表1")
    Set rsData = cn.Execute("SELECT * FROM 表1")
    Set oSubCode = New Collection
    
    '添加列头'
    ListView1.ListItems.Clear
    ListView1.ColumnHeaders.Clear
    ListView1.ColumnHeaders.Add , , "ID"
    While Not rsCol.EOF
        ListView1.ColumnHeaders.Add , , rsCol("代码") & rsCol("名称")
        oSubCode.Add rsCol("代码").Value
        
        rsCol.MoveNext
    Wend
    
    '添加数据'
    While Not rsRow.EOF
        Set lvi = ListView1.ListItems.Add(, , rsRow("ID"))
        
        For i = 1 To oSubCode.Count
            rsData.Filter = "(ID=" & rsRow("ID") & ") AND (代码='" & oSubCode(i) & "')"
            
            If rsData.EOF Then
                lvi.SubItems(i) = "无"
            Else
                lvi.SubItems(i) = rsData("用量")
            End If
        Next
        
        rsRow.MoveNext
    Wend
End Sub

Private Sub Form_Load()
    ListView1.View = lvwReport
End Sub

[panyong]

高手天天有 今天特别高啊！马上测试！谢谢大哥

[panyong]

谢谢各位大哥！

[panyong]

[Quote=引用 9 楼 tiger_zhao 的回复:]
VB code
Option Explicit

Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim rsCol As ADODB.Recordset
Dim rsRow As ADODB.Recordset
Dim rsData A……
[/Quote]

大哥的也是对了
还可以给你分吗？要怎么给

[Tiger_Zhao]

新开一帖，放上本帖链接，说明理由。

同时吧新帖的链接也放在这里。

[panyong]

http://topic.csdn.net/u/20100806/16/3ceafcbb-6b98-4074-96be-70e14bb3a9dc.html

[QQ342151559]

感觉表好像有点混乱....

[panyong]

没有吧 不是很清晰吗
表1 表2 最后用VB LISTVIEW 显示出来
哪里有不清楚的呢

[jhone99]

这样就可以动态获取
for i=0 to rs.fields.count-1
rs.fields(i).name 列名
rs.fields(i) 数值
next i

[panyong]

[Quote=引用 3 楼 jhone99 的回复:]
这样就可以动态获取
for i=0 to rs.fields.count-1
rs.fields(i).name 列名
rs.fields(i) 数值
next i
[/Quote]
那SQL语句要怎么写呢
能具体点吗 大哥

[jhone99]

不是已经有吗

http://topic.csdn.net/u/20100801/13/9913e1dc-6316-415c-b5e4-2616be133a93.html

[panyong]

[Quote=引用 5 楼 jhone99 的回复:]
不是已经有吗

http://topic.csdn.net/u/20100801/13/9913e1dc-6316-415c-b5e4-2616be133a93.html
[/Quote]

那个不对的 数据能正确读出 但是在listview中就是显示不出来
说是找不到项目 估计是语句不对 没有把那一列读出来

[panyong]

[Quote=引用 3 楼 jhone99 的回复:]
这样就可以动态获取
for i=0 to rs.fields.count-1
rs.fields(i).name 列名
rs.fields(i) 数值
next i
[/Quote]

这个读取的时候也有问题啊
提示 对象不支持属性