1 / 30

SQL CE

SQL CE. 註冊 RDA. REGSVR32. IIS 設定. 新增虛擬目錄. IIS 設定. 輸入虛擬目錄名稱. IIS 設定. 輸入虛擬目錄位置. IIS 設定. 輸入虛擬目錄權限 ( 執行一定要勾 ). IIS 設定. 設定完成. 連線 SQL Server. reference. installation. 連線 SQL Server. InitDB. 圖片顯示. 顯示圖片 Private Sub Form_Activate() Dim strpath As String strpath = App.Path

kiele
Download Presentation

SQL CE

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL CE

  2. 註冊 RDA • REGSVR32

  3. IIS 設定 • 新增虛擬目錄

  4. IIS 設定 • 輸入虛擬目錄名稱

  5. IIS 設定 • 輸入虛擬目錄位置

  6. IIS 設定 • 輸入虛擬目錄權限 (執行一定要勾)

  7. IIS 設定 • 設定完成

  8. 連線SQL Server reference installation

  9. 連線SQL Server InitDB

  10. 圖片顯示 • 顯示圖片 • Private Sub Form_Activate() • Dim strpath As String • strpath = App.Path • If strpath = "\" Then • strpath = "" • End If • PictureBox1.Picture = strpath & "\a1.bmp" • End Sub

  11. 宣告連線變數 所有變數宣告在Module中 Const DBFileSpec = "\My Documents\" ' PDA上SQL資料庫位置所在 Const DBName = “employee.sdf”‘ PDA上SQL資料庫名稱 ' 連接server上的SQL Server Const strRemoteProvider = "Provider=sqloledb;Initial Catalog=Northwind;" Const SQLEProvider = "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0; data source=" ' ------------------------------------------------------------------------------------ Dim strRemoteSQLServerName As String 'SQL主機名稱 Dim strRemoteSQLServerUID As String 'SQL帳號 Dim strRemoteSQLServerPwd As String 'SQL密碼 Dim strInternetURL As String 'RDA.DLL所在位置 Dim strRemoteConnect As String ‘連接SQL SERVER連線參數 Dim strDBFileName As String ‘pda上的資料庫 Dim cnADO As ADOCE.Connection ‘操作pda上的sdf資料庫的connection

  12. 設定連線參數值 • 設定連接參數 • Private Sub InitDB() • ' 聯結PDA上的SQL資料庫，判斷是否要覆蓋或繼續使用 • strRemoteSQLServerName = txtRemoteHost.Text ' SQL Server • strRemoteSQLServerUID = txtUserID.Text ' SQL Server帳號 • strRemoteSQLServerPwd = txtUserPasswd.Text ' SQL Server密碼 • strInternetURL = txtsaURL.Text 'RDA.DLL所在位置 • ' 連接資料庫 • strRemoteConnect = strRemoteProvider & "Data Source=" & strRemoteSQLServerName & ";UID=" & strRemoteSQLServerUID & ";password=" & strRemoteSQLServerPwd • MsgBox “SQL Server資料庫初始成功，可以開始進行上傳或下載資料的動作！“ • download_upload.Show • End Sub

  13. 連線SQL Server download_upload COMMAND1 command2

  14. 從SQL Server下載資料 I • Dim ceRDA As SSCE.RemoteDataAccess ‘全域變數 • Private Sub Command1_Click() • ' 下載資料 • Dim CreateDBFlag As Boolean • Dim intTemp As Integer • '-------------------------------------------------------------------------- • ‘判斷是否要在PDA重新產生SQL檔案 • CreateDBFlag = True • '-------------------------------------------------------------------------- • strDBFileName = DBFileSpec & DBName ' PDA上資料庫所在位置與路徑 • If DBExists(strDBFileName) = True Then '檢查PDA上的資料庫是否存在 • intTemp = MsgBox("要覆寫資料庫嗎？ " & strDBFileName & " ?", vbYesNoCancel, "資料庫已經存在") • If intTemp = vbYes Then '確定覆寫 • FileSystem1.Kill strDBFileName ' 刪除目前資料 • Else • CreateDBFlag = False • End If • End If

  15. 從SQL Server下載資料 II ‘接前頁 '---------------------------------------------------------------------------- If CreateDBFlag = True Then CreateDB '在PDA上建立SQL資料庫 download_from_sql ' 開始下載資料**************** Else Exit Sub End If End Sub

  16. CreateDB • ' 在PDA上建立SQL資料庫 • Private Sub CreateDB() • Dim cat As ADOXCE.Catalog • Set cat = CreateObject("ADOXCE.Catalog.3.1") • '在PDA上建立SQL資料庫 • cat.Create (SQLEProvider & strDBFileName) • Set cat = Nothing • End Sub

  17. download_from_sql - I • ' 下載資料************************ • Sub download_from_sql() • Dim sr As String • ShowHourGlass '顯示漏斗 • If connOpen=true then '開啟PDA上的SQL資料庫 • '---------------------------------------------------- • If TableExists(cnADO, “employees") = True Then • 'cnADO代表開啟的SQL資料庫的employees資料表 • cnADO.Execute ("drop table employees") '刪除資料表employees • End If

  18. download_from_sql - II '---------------------------------------------------- connClose '關閉SQL資料庫 '------------------------------------------------------------ '連結遠端SQL SERVER Set ceRDA = CreateObject("SSCE.RemoteDataAccess.2.0") ceRDA.LocalConnectionString = SQLEProvider & strDBFileName ceRDA.InternetURL = strInternetURL ' strRemoteConnect代表SQL Server資料庫 ‘Pull代表將資料由SQL Serve取出放入PDA上的SQL資料庫中 ceRDA.Pull “employees", "SELECT * FROM employees", strRemoteConnect, TRACKINGON

  19. download_from_sql - III If ceRDA.ErrorRecords.Count > 0 Then ShowErrors ceRDA.ErrorRecords '顯示錯誤訊息 Else MsgBox "資料下載成功!" End If Set ceRDA = Nothing End if ShowArrow‘恢復游標 End Sub

  20. connOpen • ' 開啟PDA上的SQL資料庫 GPS.SDF檔案 • Function connOpen() As Boolean • connOpen = False • Set cnADO = CreateObject(“ADOCE.Connection.3.1”) ‘module中宣告 • cnADO.Open (SQLEProvider & strDBFileName) • If cnADO.Errors.Count = 0 Then • connOpen = True '沒有錯誤代表開啟成功 • Else • MsgBox "PDA上的SQL資料庫開啟失敗" • End If • End Function

  21. connClose • ' 關閉PDA上的SQL資料庫 • Sub connClose() • On Error Resume Next • cnADO.Close • End Sub

  22. TableExists • Private Function TableExists(paramcnADO As ADOCE.Connection, paramTableName As String) As Boolean • Dim catTable As ADOXCE.Table • Dim cat As ADOXCE.Catalog • TableExists = False • Set cat = CreateObject("ADOXCE.Catalog.3.1") • cat.ActiveConnection = paramcnADO • For Each catTable In cat.Tables • If InStr(1, catTable.Name, paramTableName) <> 0 Then TableExists = True • Next • Set catTable = Nothing • Set cat = Nothing • End Function

  23. ShowHourGlass & ShowArrow ' 改變游標變成漏斗 Public Sub ShowHourGlass() Screen.MousePointer = 11 End Sub ' 回覆游標 Public Sub ShowArrow() Screen.MousePointer = 1 End Sub

  24. Summary • 透過RDA連接SQL Server • CERDA • Set ceRDA = CreateObject("SSCE.RemoteDataAccess.2.0") • ceRDA.LocalConnectionString = SQLEProvider • ceRDA.InternetURL = RDA的URL • 取值/回傳值 • ceRDA.Pull "goods", "SELECT * FROM goods", strRemoteConnect, TRACKINGON • ceRDA.Push "goods", strRemoteConnect • 連接遠端SQL Server參數 • strRemoteProvider ="Provider=sqloledb;Initial Catalog=wealth;" • strRemoteConnect = strRemoteProvider & "Data Source=" & strRemoteSQLServerName & ";UID=" & strRemoteSQLServerUID & ";password=" & strRemoteSQLServerPwd • 操作PDA本機SQL Server CE • ADOCE • SQLEProvider ="Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0; data source=a.sdf" • Set cnADO = CreateObject("ADOCE.Connection.3.1") • cnADO.Open (SQLEProvider)

  25. 顯示資料 Dim ssql As String connOpen '開啟PDA SQL資料庫 ssql = "select * from student" Set rs = CreateObject("ADOCE.Recordset.3.1") rs.Open ssql, cnADO, adOpenDynamic, adLockPessimistic rs.MoveFirst Label1.Caption = rs("name") Label2.Caption = rs("age") grid Private Sub Form_OKClick() connClose Me.Hide End Sub

  26. 顯示資料 Sub grid() Dim i, j As Integer GridCtrl1.Rows = totalcount + 1 GridCtrl1.Cols = 2 GridCtrl1.Row = 0 GridCtrl1.Col = 0 GridCtrl1.Text = “編號" GridCtrl1.Col = 1 GridCtrl1.Text = "名稱" rs.movefirst For i = 1 To totalcount GridCtrl1.Row = i For j = 0 To 1 GridCtrl1.Col = j Select Case j Case 0: GridCtrl1.Text = rs("goods_code") Case 1: GridCtrl1.Text = rs("goods_name") End Select Next rs.MoveNext Next End Sub

  27. 更改資料 Dim ssql As String Set rs1 = CreateObject("ADOCE.Recordset.3.1") rs1.Open "update student set name = '" & Text1.Text & "' where id ='" & rs("id") & "'", cnADO, adOpenDynamic, adLockPessimistic Set rs1 = Nothing If rs.State = 1 Then rs.Close ssql = "select * from student" rs.Open ssql, cnADO, adOpenDynamic, adLockPessimistic grid

  28. 新增資料－資料庫欄位屬性修正

  29. 新增資料 Dim ssql As String Set rs1 = CreateObject("ADOCE.Recordset.3.1") rs1.Open "insert into student (name,age) values ('" & Text1.Text & "',12)", cnADO, adOpenDynamic, adLockPessimistic Set rs1 = Nothing If rs.State = 1 Then rs.Close ssql = "select * from student" rs.Open ssql, cnADO, adOpenDynamic, adLockPessimistic grid

  30. 上傳資料 • ' 上傳資料到Server • Private Sub Command2_Click() • connClose • ShowHourGlass '顯示漏斗 • strDBFilename = DBFileLoc & DBName ' PDA上資料庫所在位置與路徑 • Set ceRDA = CreateObject("SSCE.RemoteDataAccess.2.0") • ceRDA.LocalConnectionString = SQLEProvider & strDBFilename • ceRDA.InternetURL = strInternetURL • ' 將資料回存到SQL Server中 • ceRDA.Push "student", strRemoteConnect • If ceRDA.ErrorRecords.Count > 0 Then • ShowErrors ceRDA.ErrorRecords '顯示錯誤訊息 • Else • MsgBox "資料上傳成功!" • End If • Set ceRDA = Nothing • ShowArrow '恢復游標 • End Sub

More Related