'建立数据库连接 DataConn.Open '若RecordSet建立出错,则转向RecordsetERR On Error GoTo RecordSetERR
'这个SQL语句有些特殊,它完成了大部分的数值统计工作,请读者仔细理解 strSQL = "SELECT CategoryName," '返回记录数量 strSQL = strSQL & "Count(1) AS COUNT1," '返回此类别的库存总量 strSQL = strSQL & "SUM(UnitsInStock) AS STOCK " '从视图Products by Category查询 strSQL = strSQL & "FROM [Products by Category] " '按CategoryName分类 strSQL = strSQL & "GROUP BY CategoryName" DataRec.Open strSQL, DataConn
Dim lngRecordCount As Long Dim lngI As Long, lngJ As Long lngRecordCount = 0 '计算纪录数量,因为Recordset的RecordCount属性不稳定 '很多时候通过RecordCount属性无法取得确切的记录数量 Do Until DataRec.EOF lngRecordCount = lngRecordCount + 1 DataRec.MoveNext Loop
看看能不能骗点分Private Sub Form_Load()
'建立一个ADO数据连接
Dim DataConn As New ADODB.Connection
Dim DataRec As New ADODB.Recordset
Dim strSQL As String
'若数据库连接出错,则转向ConnectionERR
On Error GoTo ConnectionERR
'建立一个连接字串
'这个连接串可能根据数据库配置的不同而不同
DataConn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;"
DataConn.ConnectionString = DataConn.ConnectionString & "Persist Security Info=False;"
DataConn.ConnectionString = DataConn.ConnectionString & "Initial Catalog=Northwind;"
DataConn.ConnectionString = DataConn.ConnectionString & "Data Source=localhost"
'建立数据库连接
DataConn.Open
'若RecordSet建立出错,则转向RecordsetERR
On Error GoTo RecordSetERR
'这个SQL语句有些特殊,它完成了大部分的数值统计工作,请读者仔细理解
strSQL = "SELECT CategoryName,"
'返回记录数量
strSQL = strSQL & "Count(1) AS COUNT1,"
'返回此类别的库存总量
strSQL = strSQL & "SUM(UnitsInStock) AS STOCK "
'从视图Products by Category查询
strSQL = strSQL & "FROM [Products by Category] "
'按CategoryName分类
strSQL = strSQL & "GROUP BY CategoryName"
DataRec.Open strSQL, DataConn
Dim lngRecordCount As Long
Dim lngI As Long, lngJ As Long
lngRecordCount = 0
'计算纪录数量,因为Recordset的RecordCount属性不稳定
'很多时候通过RecordCount属性无法取得确切的记录数量
Do Until DataRec.EOF
lngRecordCount = lngRecordCount + 1
DataRec.MoveNext
Loop
On Error GoTo OtherERR
'设置MSChart显示二维条形图
MSChart1.chartType = VtChChartType2dBar
'设置行数和列数
MSChart1.RowCount = lngRecordCount
MSChart1.ColumnCount = 2
'设置Column的标签,这个标签将会在图例中显示出来
MSChart1.Column = 1
MSChart1.ColumnLabel = "商品种类数"
MSChart1.Column = 2
MSChart1.ColumnLabel = "库存商品数"
DataRec.MoveFirst
'填充表格数据
For lngI = 1 To lngRecordCount
MSChart1.Row = lngI
'设置Row标签,这个标签将会在横轴上显示出来
MSChart1.RowLabel = DataRec.Fields("CategoryName").Value
MSChart1.Column = 1
'用Data属性向MSChart数据网格填充数据
MSChart1.Data = Val(DataRec.Fields("COUNT1").Value)
MSChart1.Column = 2
MSChart1.Data = Val(DataRec.Fields("STOCK").Value)
DataRec.MoveNext
Next lngI
DateRec.Close'以下是图例显示和处理代码
MSChart1.ShowLegend = True
MSChart1.SelectPart VtChPartTypePlot, index1, index2, _
index3, index4
MSChart1.EditCopy
MSChart1.SelectPart VtChPartTypeLegend, index1, _
index2, index3, index4
MSChart1.EditPaste
Exit Sub
ConnectionERR:
'错误处理程序
MsgBox "数据库连接错误," & Err.Description, vbCritical, "出错"
Exit Sub
RecordSetERR:
MsgBox "RecordSet生成错误," & Err.Description, vbCritical, "错误"
Exit Sub
OtherERR:
MsgBox "其他错误," & Err.Description, vbCritical, "出错"
Exit Sub
End Sub