0930

進階資料庫語言
1.視圖
2.預存程序
3.預存函數
4.觸發器
5.指標 存儲過程用
6.事件

視圖 view  HeidiSQL_9.3_Portable
預存程序

Public Function dataload(ByVal sql As String, ByVal dbname As String, ByVal pw As String) As String()
    Dim conn As New ADODB.Connection  '資料庫連線
    Dim rs As New ADODB.Recordset     '資料集
   
    ConnectionString = "driver={mysql odbc 5.1 driver};database=" & dbname & ";server=127.0.0.1;port=3306;uid=root;password=" & pw & ";opt=3;smt=SET NAMES 'gb2312'"
    conn.Open ConnectionString
    rs.Open sql, conn
           
    Dim r As Integer
    Dim f As Integer
    rs.MoveFirst
    Do While Not rs.EOF
       r = r + 1       '取得總計錄數
       rs.MoveNext
    Loop
    f = rs.Fields.Count  '取得總欄位數
   
    ReDim kdata(r, f) As String  '設定二維動態陣列
   
    Dim p As Integer
    Dim i As Integer
    p = 0
    rs.MoveFirst
    Do While Not rs.EOF
       For i = 0 To f - 1 Step 1
        kdata(p, i) = rs(i)
       Next
       p = p + 1
       rs.MoveNext
    Loop
       rs.Close
       conn.Close
     
       dataload = kdata
     
End Function
======================================
Private Sub CommandButton1_Click()
    Dim com5 As New Ani5
    Dim sql As String
    sql = "select * from scoredata"
   
    Dim kdata
   
    kdata = com5.dataload(sql, "persondb", "12345678")
   
    Dim listr As Integer
    Dim j As Integer
   
    listr = UBound(kdata, 1)
    ListBox1.Clear
   
    For j = 0 To listr - 1 Step 1
        ListBox1.AddItem kdata(j, 0)
        ListBox1.List(j, 1) = kdata(j, 1)
        ListBox1.List(j, 2) = kdata(j, 2)
        ListBox1.List(j, 3) = kdata(j, 3)
        ListBox1.List(j, 4) = kdata(j, 4)
        ListBox1.List(j, 5) = kdata(j, 5)
    Next j
       
End Sub