sql ce n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL CE PowerPoint Presentation
Download Presentation
SQL CE

Loading in 2 Seconds...

play fullscreen
1 / 30

SQL CE - PowerPoint PPT Presentation


  • 155 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL CE' - kiele


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide2
註冊 RDA
  • REGSVR32
slide3
IIS 設定
  • 新增虛擬目錄
slide4
IIS 設定
  • 輸入虛擬目錄名稱
slide5
IIS 設定
  • 輸入虛擬目錄位置
slide6
IIS 設定
  • 輸入虛擬目錄權限 (執行一定要勾)
slide7
IIS 設定
  • 設定完成
sql server

連線SQL Server

reference

installation

slide10
圖片顯示
  • 顯示圖片
  • Private Sub Form_Activate()
    • Dim strpath As String
    • strpath = App.Path
    • If strpath = "\" Then
    • strpath = ""
    • End If
    • PictureBox1.Picture = strpath & "\a1.bmp"
  • End Sub
slide11
宣告連線變數

所有變數宣告在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

slide12
設定連線參數值
  • 設定連接參數
  • 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
sql server2
連線SQL Server

download_upload

COMMAND1

command2

sql server i
從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
sql server ii
從SQL Server下載資料 II

‘接前頁

'----------------------------------------------------------------------------

If CreateDBFlag = True Then

CreateDB '在PDA上建立SQL資料庫

download_from_sql ' 開始下載資料****************

Else

Exit Sub

End If

End Sub

createdb
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
download from sql i
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
download from sql ii
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

download from sql iii
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

connopen
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
connclose
connClose
  • ' 關閉PDA上的SQL資料庫
  • Sub connClose()
    • On Error Resume Next
    • cnADO.Close
  • End Sub
tableexists
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
showhourglass showarrow
ShowHourGlass & ShowArrow

' 改變游標變成漏斗

Public Sub ShowHourGlass()

Screen.MousePointer = 11

End Sub

' 回覆游標

Public Sub ShowArrow()

Screen.MousePointer = 1

End Sub

summary
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)
slide25
顯示資料

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

slide26
顯示資料

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

slide27
更改資料

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

slide29
新增資料

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

slide30
上傳資料
  • ' 上傳資料到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