资源描述：
《DAlessonSQLCE连线ppt课件》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
SQLCE
1註冊RDAREGSVR32
2IIS設定新增虛擬目錄
3IIS設定輸入虛擬目錄名稱
4IIS設定輸入虛擬目錄位置
5IIS設定輸入虛擬目錄權限(執行一定要勾)
6IIS設定設定完成
7連線SQLServer
8連線SQLServerInitDB
9圖片顯示顯示圖片PrivateSubForm_Activate()DimstrpathAsStringstrpath=App.PathIfstrpath="\"Thenstrpath=""EndIfPictureBox1.Picture=strpath&"\a1.bmp"EndSub
10宣告連線變數所有變數宣告在Module中ConstDBFileSpec="\MyDocuments\"'PDA上SQL資料庫位置所在ConstDBName=“employee.sdf”‘PDA上SQL資料庫名稱'連接server上的SQLServerConststrRemoteProvider="Provider=sqloledb;InitialCatalog=Northwind;"ConstSQLEProvider="Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;datasource="'------------------------------------------------------------------------------------DimstrRemoteSQLServerNameAsString'SQL主機名稱DimstrRemoteSQLServerUIDAsString'SQL帳號DimstrRemoteSQLServerPwdAsString'SQL密碼DimstrInternetURLAsString'RDA.DLL所在位置DimstrRemoteConnectAsString‘連接SQLSERVER連線參數DimstrDBFileNameAsString‘pda上的資料庫DimcnADOAsADOCE.Connection‘操作pda上的sdf資料庫的connection
11設定連線參數值設定連接參數PrivateSubInitDB()'聯結PDA上的SQL資料庫，判斷是否要覆蓋或繼續使用strRemoteSQLServerName=txtRemoteHost.Text'SQLServerstrRemoteSQLServerUID=txtUserID.Text'SQLServer帳號strRemoteSQLServerPwd=txtUserPasswd.Text'SQLServer密碼strInternetURL=txtsaURL.Text'RDA.DLL所在位置'連接資料庫strRemoteConnect=strRemoteProvider&"DataSource="&strRemoteSQLServerName&";UID="&strRemoteSQLServerUID&";password="&strRemoteSQLServerPwdMsgBox“SQLServer資料庫初始成功，可以開始進行上傳或下載資料的動作！“download_upload.ShowEndSub
12連線SQLServerdownload_uploadCOMMAND1command2
13從SQLServer下載資料IDimceRDAAsSSCE.RemoteDataAccess‘全域變數PrivateSubCommand1_Click()'下載資料DimCreateDBFlagAsBooleanDimintTempAsInteger'--------------------------------------------------------------------------‘判斷是否要在PDA重新產生SQL檔案CreateDBFlag=True'--------------------------------------------------------------------------strDBFileName=DBFileSpec&DBName'PDA上資料庫所在位置與路徑IfDBExists(strDBFileName)=TrueThen'檢查PDA上的資料庫是否存在intTemp=MsgBox("要覆寫資料庫嗎？"&strDBFileName&"?",vbYesNoCancel,"資料庫已經存在")IfintTemp=vbYesThen'確定覆寫FileSystem1.KillstrDBFileName'刪除目前資料ElseCreateDBFlag=FalseEndIfEndIf
14從SQLServer下載資料II‘接前頁'----------------------------------------------------------------------------IfCreateDBFlag=TrueThenCreateDB'在PDA上建立SQL資料庫download_from_sql'開始下載資料****************ElseExitSubEndIfEndSub
15CreateDB'在PDA上建立SQL資料庫PrivateSubCreateDB()DimcatAsADOXCE.CatalogSetcat=CreateObject("ADOXCE.Catalog.3.1")'在PDA上建立SQL資料庫cat.Create(SQLEProvider&strDBFileName)Setcat=NothingEndSub
16THANKYOUSUCCESS2022/10/2117可编辑
17download_from_sql-I'下載資料************************Subdownload_from_sql()DimsrAsStringShowHourGlass'顯示漏斗connOpen'開啟PDA上的SQL資料庫'----------------------------------------------------IfTableExists(cnADO,“employees")=TrueThen'cnADO代表開啟的SQL資料庫的employees資料表cnADO.Execute("droptableemployees")'刪除資料表employeesEndIf
18download_from_sql-II'----------------------------------------------------connClose'關閉SQL資料庫'------------------------------------------------------------'連結遠端SQLSERVERSetceRDA=CreateObject("SSCE.RemoteDataAccess.2.0")ceRDA.LocalConnectionString=SQLEProvider&strDBFileNameceRDA.InternetURL=strInternetURLceRDA.InternetLogin=strInternetLoginceRDA.InternetPassword=strInternetPwd'strRemoteConnect代表SQLServer資料庫‘Pull代表將資料由SQLServe取出放入PDA上的SQL資料庫中ceRDA.Pull“employees","SELECT*FROMemployees",strRemoteConnect,TRACKINGON
19download_from_sql-IIIIfceRDA.ErrorRecords.Count>0ThenShowErrorsceRDA.ErrorRecords'顯示錯誤訊息ElseMsgBox"資料下載成功!"EndIfSetceRDA=NothingShowArrow‘恢復游標EndSub
20connOpen'開啟PDA上的SQL資料庫GPS.SDF檔案FunctionconnOpen()AsBooleanconnOpen=FalseSetcnADO=CreateObject("ADOCE.Connection.3.1")cnADO.Open(SQLEProvider&strDBFileName)IfcnADO.Errors.Count=0ThenconnOpen=True'沒有錯誤代表開啟成功ElseMsgBox"PDA上的SQL資料庫開啟失敗"EndIfEndFunction
21connClose'關閉PDA上的SQL資料庫SubconnClose()OnErrorResumeNextcnADO.CloseSetcnADO=NothingOnErrorGoTo0EndSub
22TableExistsPrivateFunctionTableExists(paramcnADOAsADOCE.Connection,paramTableNameAsString)AsBooleanDimcatTableAsADOXCE.TableDimcatAsADOXCE.CatalogTableExists=FalseSetcat=CreateObject("ADOXCE.Catalog.3.1")cat.ActiveConnection=paramcnADOForEachcatTableIncat.TablesIfInStr(1,catTable.Name,paramTableName)<>0ThenTableExists=TrueNextSetcatTable=NothingSetcat=NothingEndFunction
23ShowHourGlass&ShowArrow'改變游標變成漏斗PublicSubShowHourGlass()Screen.MousePointer=11EndSub'回覆游標PublicSubShowArrow()Screen.MousePointer=1EndSub
24Summary透過RDA連接SQLServerCERDASetceRDA=CreateObject("SSCE.RemoteDataAccess.2.0")ceRDA.LocalConnectionString=SQLEProviderceRDA.InternetURL=RDA的URL取值/回傳值ceRDA.Pull"goods","SELECT*FROMgoods",strRemoteConnect,TRACKINGONceRDA.Push"goods",strRemoteConnect連接遠端SQLServer參數strRemoteProvider="Provider=sqloledb;InitialCatalog=wealth;"strRemoteConnect=strRemoteProvider&"DataSource="&strRemoteSQLServerName&";UID="&strRemoteSQLServerUID&";password="&strRemoteSQLServerPwd操作PDA本機SQLServerCEADOCESQLEProvider="Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;datasource=a.sdf"SetcnADO=CreateObject("ADOCE.Connection.3.1")cnADO.Open(SQLEProvider)
25顯示資料DimcnADOAsADOCE.ConnectionDimrsAsADOCE.RecordsetconnOpen'開啟PDASQL資料庫ssql="SELECT*FROMemployees"Setrs=CreateObject("ADOCE.Recordset.3.1")rs.Openssql,cnADO,adOpenKeyset,adLockPessimisticCombo1.Clear'清空下拉選單WhileNotrs.EOF‘顯示資料Combo1.AddItemTrim(rs(“employeeid"))Label7.Caption=rs(“FirstName")Label8.Caption=rs(“LastName")Label9.Caption=rs("Tel")rs.MoveNextWendrs.CloseSetrs=NothingconnClose
26顯示資料Subgrid()Dimi,jAsIntegerGridCtrl1.Rows=totalcount+1GridCtrl1.Cols=2GridCtrl1.Row=0GridCtrl1.Col=0GridCtrl1.Text=“編號"GridCtrl1.Col=1GridCtrl1.Text="名稱"rs.movefirstFori=1TototalcountGridCtrl1.Row=iForj=0To1GridCtrl1.Col=jSelectCasejCase0:GridCtrl1.Text=rs("goods_code")Case1:GridCtrl1.Text=rs("goods_name")EndSelectNextrs.MoveNextNextEndSub
27更改資料Setrs1=CreateObject("ADOCE.Recordset.3.1")rs1.Open"updateemployeessetfirstname='"&Text1.Text&"'whereemployeeid=1",cnADO,adOpenDynamic,adLockOptimistic
28新增資料－資料庫欄位屬性修正
29新增資料Setrs1=CreateObject("ADOCE.Recordset.3.1")rs1.Open"insertintogoods(goods_name)values('tt')",cnADO,adOpenDynamic,adLockOptimistic
30上傳資料'上傳資料到ServerPrivateSubCommand2_Click()ShowHourGlass'顯示漏斗SetceRDA=CreateObject("SSCE.RemoteDataAccess.2.0")ceRDA.LocalConnectionString=SQLEProvider&strDBFileNameceRDA.InternetURL=strInternetURL'將資料回存到SQLServer中ceRDA.Push“employees",strRemoteConnect'employees資料表回存IfceRDA.ErrorRecords.Count>0ThenShowErrorsceRDA.ErrorRecords'顯示錯誤訊息ElseMsgBox"資料上傳成功!"EndIfSetceRDA=NothingShowArrow'恢復游標EndSub
31THANKYOUSUCCESS2022/10/2132可编辑
