交叉表查询.txt

vb+ado连接MSSQL数据库

'vb+ado 连接MSSQL数据库

'新建form1
'加入ADODC1、DataGrid1两个控件

Private Sub Form_Load()
  Me.Caption = "交叉表查询试验"
  Dim CN As ADODB.Connection
  Dim RC As ADODB.Recordset
  Dim STR As String
  dim SQL As String
  Dim i, J As Integer
  Set CN = New ADODB.Connection
  CN.Open "Provider=SQLOLEDB.1;Password=Password;Persist Security Info=False;User ID=sa;Initial Catalog=数据库名称;Data Source=服务器名称"
  Set RC = New ADODB.Recordset
  RC.Open "SELECT distinct 列字段 From 表名称 ORDER BY 列字段", CN, adOpenStatic, adLockReadOnly '用adopenstatic
  '首先生成的交叉表列字段的数量（J)和名称(RC.Fields(0))
  J = RC.RecordCount
  For i = 0 To J - 1
    STR = ",SUM(CASE 列字段 WHEN '" & RC.Fields(0) & "' THEN 数值字段 ELSE 0 END) AS " & RC.Fields(0)'定义每一列的列标题和值的计算方法
    SQL = SQL + STR
    RC.MoveNext
  Next i
  SQL = "SELECT 行字段" + SQL + " FROM QX GROUP BY 行字段"
  Adodc1.RecordSource = SQL
  Adodc1.Refresh
End Sub