VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Object = "{67397AA1-7FB1-11D0-B148-00A0C922E820}#6.0#0"; "MSADODC.OCX"
Begin VB.Form 查詢 
   Caption         =   "查詢"
   ClientHeight    =   7770
   ClientLeft      =   60
   ClientTop       =   420
   ClientWidth     =   8820
   LinkTopic       =   "Form1"
   ScaleHeight     =   7770
   ScaleWidth      =   8820
   StartUpPosition =   3  'Windows Default
   Begin VB.CommandButton Command2 
      Caption         =   "Command2"
      Height          =   615
      Left            =   5760
      TabIndex        =   6
      Top             =   6240
      Width           =   1815
   End
   Begin VB.TextBox Text2 
      Height          =   615
      Left            =   6720
      TabIndex        =   3
      Top             =   1080
      Width           =   1455
   End
   Begin MSAdodcLib.Adodc Adodc1 
      Height          =   375
      Left            =   840
      Top             =   5520
      Visible         =   0   'False
      Width           =   1200
      _ExtentX        =   2117
      _ExtentY        =   661
      ConnectMode     =   0
      CursorLocation  =   3
      IsolationLevel  =   -1
      ConnectionTimeout=   15
      CommandTimeout  =   30
      CursorType      =   3
      LockType        =   3
      CommandType     =   8
      CursorOptions   =   0
      CacheSize       =   50
      MaxRecords      =   0
      BOFAction       =   0
      EOFAction       =   0
      ConnectStringType=   1
      Appearance      =   1
      BackColor       =   -2147483643
      ForeColor       =   -2147483640
      Orientation     =   0
      Enabled         =   -1
      Connect         =   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\tv.mdb;Persist Security Info=False"
      OLEDBString     =   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\tv.mdb;Persist Security Info=False"
      OLEDBFile       =   ""
      DataSourceName  =   ""
      OtherAttributes =   ""
      UserName        =   ""
      Password        =   ""
      RecordSource    =   ""
      Caption         =   "Adodc1"
      BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} 
         Name            =   "MS Sans Serif"
         Size            =   8.25
         Charset         =   0
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      _Version        =   393216
   End
   Begin MSFlexGridLib.MSFlexGrid MSFlexGrid1 
      Height          =   2415
      Left            =   960
      TabIndex        =   2
      Top             =   2520
      Width           =   6615
      _ExtentX        =   11668
      _ExtentY        =   4260
      _Version        =   393216
      Cols            =   7
   End
   Begin VB.CommandButton Command1 
      Caption         =   "查詢"
      Height          =   615
      Left            =   3840
      TabIndex        =   1
      Top             =   1080
      Width           =   1215
   End
   Begin VB.TextBox Text1 
      Height          =   495
      Left            =   1680
      TabIndex        =   0
      Top             =   1080
      Width           =   1575
   End
   Begin VB.Label Label2 
      Caption         =   "人數"
      Height          =   375
      Left            =   5400
      TabIndex        =   5
      Top             =   1200
      Width           =   1095
   End
   Begin VB.Label Label1 
      Caption         =   "節目名稱"
      Height          =   495
      Left            =   240
      TabIndex        =   4
      Top             =   1080
      Width           =   1215
   End
End
Attribute VB_Name = "查詢"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Public cn As ADODB.Connection          '定義連接對象
Public rs As ADODB.Recordset           '定義記錄集對象

Private Sub Command1_Click()


SQL = "select * from 機頂盒回傳信息 where 頻道信息 =" + "'" + Text1 + "'" + " order by 人員編號 asc;"
 
 Set cn = New ADODB.Connection        '創建新的連接對象

    cn.Open "DSN=tv"                  '打開數據源  dsn:Data Source Name
    
    

Set rs = cn.Execute(SQL)    'Execte：執行指定的查詢、SQL語句，存儲過程或特定提供者的文本等內容
Dim j As Integer
Dim n As Integer
j = 1
n = 0


If rs.EOF = True Then
    MsgBox "庫中無此記錄!", vbInformation + vbOKOnly, "錯誤信息"
     Text1 = ""
End If


While rs.EOF = False
   MSFlexGrid1.Rows = j + 1
   For i = 0 To 4
    If i = 3 Then
       MSFlexGrid1.TextMatrix(j, i + 1) = ""
        Else
         If IsNull(rs.Fields(i)) Then

          MSFlexGrid1.TextMatrix(j, i + 1) = ""

         Else

         MSFlexGrid1.TextMatrix(j, i + 1) = rs.Fields(i)
        End If
     End If
    Next i
j = j + 1
n = n + 1

rs.MoveNext
Text2.Text = n
Wend
End Sub

Private Sub Command2_Click()

Dim i As Integer, j As Integer
Dim newxls As Excel.Application
Dim newbook As Excel.Workbook
Dim newsheet As Excel.Worksheet
Set newxls = CreateObject("excel.application")
newxls.Visible = True
Set newbook = newxls.Workbooks.Add
Set newsheet = newbook.Worksheets(1)
For i = 0 To MSFlexGrid1.Rows - 1
For j = 0 To MSFlexGrid1.Cols - 1
    newsheet.Cells(i + 1, j + 1) = MSFlexGrid1.TextMatrix(i, j)
 Next j
Next i

End Sub



Private Sub Form_Load()                              ' 在第一行添加標題項

    MSFlexGrid1.TextMatrix(0, 1) = "自動編號"
    MSFlexGrid1.TextMatrix(0, 2) = "頻道信息"
    MSFlexGrid1.TextMatrix(0, 3) = "人員編號"
    MSFlexGrid1.TextMatrix(0, 4) = "收看開始時間"
    MSFlexGrid1.TextMatrix(0, 5) = "收看結束時間"
    
    
    
    
    MSFlexGrid1.ColWidth(0) = 1000                   '設置列寬
    MSFlexGrid1.ColWidth(1) = 1000
    MSFlexGrid1.ColWidth(2) = 1000
    MSFlexGrid1.ColWidth(3) = 1000
    MSFlexGrid1.ColWidth(4) = 2000
    MSFlexGrid1.ColWidth(5) = 2000
    
    
End Sub