Option Explicit
Public conn As New ADODB.Connection
Public rs1 As New ADODB.Recordset
Private Sub Form_Load()
Dim connect_str As String
Dim i As Integer
connect_str = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=newman;password=newman;"
connect_str = connect_str & "Initial Catalog=Pubs;Data Source=192.168.11.254;Connect Timeout=30;Application Name=ListenEZ"
conn.open connect_str
If rs1.state = 1 Then rs1.Close
rs1.open "select distinct year(ord_date) as years from sales", conn, 3, 1
For i = 1 To rs1.RecordCount
List1.AddItem rs1("years")
Next i
End Sub
If Option5.Value = True Then
listbox.FixedRows = 1
MousePointer = vbHourglass
Dim rs5 As New ADODB.Recordset
Dim rs6 As New ADODB.Recordset
Dim rscount As New ADODB.Recordset
Dim countrscount As Integer
Dim countw As Integer
Dim compyear As Integer
str1 = "select year(ctime) from synthesisanalyse group by year(ctime)"
rscount.Open str1, cn, adOpenKeyset, adLockOptimistic, adCmdText
countrscount = 0
Do While Not rscount.EOF
countrscount = countrscount + 1
rscount.MoveNext
Loop
str1 = "select usertype,typename from waterpriceinfo "
rs5.Open str1, cn, adOpenKeyset, adLockOptimistic, adCmdText
On Error GoTo errorbox
countw = 0
Do While Not rs5.EOF
countw = 1 + countw
rs5.MoveNext
Loop
rs5.MoveFirst
Dim fld As ADODB.Field
listbox.Text = "年份"
Do While Not rs5.EOF
listbox.Col = listbox.Col + 1
listbox.Row = 0
listbox.Text = rs5.Fields(1)
rs5.MoveNext
Loop
rs5.MoveFirst
str2 = "select year(ctime),typename,sum(watervalue) from waterpriceinfo,synthesisanalyse "
str2 = str2 + " where waterpriceinfo.usertype=synthesisanalyse.usertype group by typename ,year(ctime) "
rs6.Open str2, cn, adOpenKeyset, adLockOptimistic, adCmdText
listbox.Row = 0
If Not rs6.EOF Then '第一行的年直接写
listbox.Row = listbox.Row + 1
listbox.Col = 0
listbox.Text = rs6.Fields(0)
Do While rs6.Fields(1) <> rs5.Fields(1) And Not rs5.EOF
rs5.MoveNext
listbox.Col = listbox.Col + 1
Loop
rs5.MoveFirst
listbox.Col = listbox.Col + 1 '还须后移
If IsNull(rs6.Fields(2)) Then
listbox.Text = ""
Else
listbox.Text = rs6.Fields(2)
End If
compyear = rs6.Fields(0)
rs6.MoveNext
End If
Do While Not rs6.EOF '第一行后的年都要比较
If rs6.Fields(0) <> compyear Then
compyear = rs6.Fields(0)
listbox.Row = listbox.Row + 1
listbox.Col = 0
listbox.Text = rs6.Fields(0)
End If
'If rs6.Fields(1) <> rs5.Fields(1) Then
Do While rs6.Fields(1) <> rs5.Fields(1) And Not rs5.EOF
rs5.MoveNext
listbox.Col = listbox.Col + 1
Loop
' rs5.MoveFirst
listbox.Col = listbox.Col + 1
If IsNull(rs6.Fields(2)) Then
listbox.Text = ""
Else
listbox.Text = rs6.Fields(2)
End If
rs5.MoveFirst
listbox.Col = 0
' Else
' listbox.Text = rs6.Fields(2)
' rs5.MoveFirst
' End If
rs6.MoveNext
Loop
MousePointer = vbDefault
If listbox.Rows = 1 Then
listbox.Rows = 2
End If