表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 将数据读出来这个应该是动态的添加列
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 将数据读出来这个应该是动态的添加列
表1 表2 最后用VB LISTVIEW 显示出来
哪里有不清楚的呢
for i=0 to rs.fields.count-1
rs.fields(i).name 列名
rs.fields(i) 数值
next i
能具体点吗 大哥
那个不对的 数据能正确读出 但是在listview中就是显示不出来
说是找不到项目 估计是语句不对 没有把那一列读出来
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
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 SubPrivate Sub Form_Load()
ListView1.View = lvwReport
End Sub