扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条
来源:电子技术 2007年10月14日
关键字:数据库 SQL Server VB
Dim mydb As Database Dim mydynaset As Dynaset オ Private Sub Form_Load() Set mydb = OpenDatabase("", False, False, "ODBC; DSN=Myserver; WSID=LCL; DATABASE = sales") Set mydynaset = mydb CreateDynaset("Select*from Customers") オ End Sub |
Global giHEnv As Long Global giHDB As Long Global giHStmt As Long Dim myResult As integer Dim myConnection As Srting Dim myBuff As String*256 Dim myBufflen As Integer If SQLAllocEnv(giHEnv)<>SQL_SUCCESS Then MsgBox"Allocation couldn注释:t happen!" End If If SQLAllocConnect(giHEnv,giHDB)<>SQL_SUCCESS Then MsgBox "SQL Server couldn注释:t connect!" End If myConnection="DSN=myServer;UID=LCL;PWD=;APP=ODBCTest;WSID=LCL;DATABASE=sales" myResult=SQLDriverConnect(giHDB,Test,form1.hWnd,myConnection.len(myConnection), myBuff,256,myBufflen,SQL_DRIVER_COMPLETE_REQU IE D) myResult=SQLAllocStmt(giHDS,giHStmt) myResult=SQLFreeStmt(giHStmt,SQL_COLSE) rsSQL="Select * from Customers Where City = "Hunan"" myResult = SQLExecDirect(giHStmt,rsSQL,Len(rsSQL)) |
Private Sub InitializeApplication() DBLIB_VERSION=SqlInit() If DBLIB_VERSION=""Then MsgBox"Could not initialize DBLIB!Exit application.", MB_ICONEXCLAMATION End If End Sub Private Function LoginToServer() As integer loginToServer=SUCCEED Status%=SqlSetloginTime%(loginTimeOut) If giSqlConn<>0 Then SqlClose(giSqlConn) 注释:关闭已打开的连接 giSqlConn=SqlOpenConnection(gsServerName, gsLoginID, gsPassword, ProgramName, ProgramName) If giSqlConn<>0 Then liresuit=SqlUse(giSqlConn,"Sales") Else LogintoServer=FAIL End If End Function |
Dim Cn As rdoConnection Dim En As rdoEnvironment Dim Conn As String Conn = "DSN = MyDSN; UID = Jacob;" & "PWD = 123456; DATA BASE = MyDb;" Set Cn= En.OpenConnection("", rdDriverPrompt, False, Co nn) Set Cn= En.OpenConnection(Prompt:= rdDriverPrompt, Rea dOnly:= False,Connect:= Cnn) |
Public WithEvents Eng As rdoEngine Public WithEvents Cn As rdoConnection Private Sub Form_Load() Set Eng = New rdoEngine Set Cn = New rdoConnection With Cn .Connect = "UID = ; PWD = ;" & "DATABASE = pubs; DSN = biblio" .LoginTimeout = 5 .EstablishConnection rdoDriverNoPromt, True, rdAsyncEna ble End With End Sub |
Private Sub Cn_BeforeConnect(ConnetString As String, Pro mpt As Variant) MsgBox "正在连接" & ConnectString, vbOKOnly, "连接前" End Sub |
Private Sub Cn_Connect(ByVal ErrorOccurred As Boolean) Dim M As String If ErrorOccurred Then For Each er In rdoErrors M = M & er & vbCrLf & M Next MsgBox "连接失败" & vbCrLf & M Else MsgBox "连接成功" 注释:这是确认连接状态的测试代码 Cn.Excute "use pubs" End Sub |
Private Sub Eng_InfoMessage() For Each er In rdoErrors Debug.Print er Next RdoErrors.Clear End Sub |
Cn.Close Set Cn = Nothing 注释:释放对象所占的内存资源 En.Close Set En = Nothing 注释:释放对象所占的内存资源 |
Public Function SQLGetCurrentDatabaseName(Cn As ADODB.Connection) As String Dim sSQL As String Dim RS As New ADODB.Recordset On Error GoTo errSQLGetCurrentDatabaseName sSQL="select CurrentDB=DB_NAME ( )" RS.Open sSQL, Cn SQLGetCurrentDatabaseName=Trim $ (RS! CurrentDB) RS.Close Exit Function errSQLGetCurrentDatabaseName: SQLGetCurrentDatabaseName=" " End Function |
Public Function SQLGetDataPath(Cn As ADODB.Connection) As String Dim sSQL As String Dim RS As New ADODB.Recordset Dim sFullPath As String On Error GoTo errSQLGetDataPath sSQL="select phyname from master..sysdevices where name=注释:master注释: " RS.Open sSQL, Cn sFullPath = RS! phyname RS.Close SQLGetDataPath=Left $ (sFullPath, Len(sFullPath) -10) 注释:MASTER.DAT的大小 Exit Function errSQLGetDataPath: SQLGetDataPath=" " End Function |
Public Function SQLCreateDatabase65 (Cn As ADODB.Connection,sDBName As String, sDataDeviceName As String, nDataSize As Integer, Optional sLogDeviceName, Optional nLogSize) As Boolean Dim sSQL As String On Error GoTo errSQLCreateDatabase65 Dim sDB As String sDB =SQLGetCurrentDatabaseName(Cn) sSQL = "USE master" Cn.Execute sSQL sSQL ="CREATE DATABASE" & sDBName sSQL = sSQL &" ON " & sDataDeviceName & "=" & nDataSize If Not IsMissing(sLogDeviceName) And Not IsMissing(nLogSize) Then sSQL = sSQL & "LOG ON" & sLogDeviceName & "="& nLogSize End If Cn.Execute sSQL sSQL = "USE" & sDB Cn.Execute sSQL SQLCreateDatabase65 = True Exit Function errSQLCreateDatabase65: On Error Resume Next sSQL = "USE " & sDB Cn.Execute sSQL SQLCreateDatabase65 = False End Function |
Public Function SQLExistDatabase(Cn As ADODB.Connection, sDBName As String) As Boolean Dim sSQL As String Dim RS As New ADODB.Recordset Dim bTmp As Boolean on Error GoTo errSQLExistDatabase sSQL = "select CntDB = count ( * ) " sSQL = sSQL & "From master.dbo.sysdatabases" sSQL = sSQL & "Where name = 注释: "& sDBName & " 注释: " RS.Open sSQL, Cn If RS! CntDB = 0 Then bTmp = False Else bTmp = True RS.Close SQLExistDatabase = bTmp Exit Function errSQLExistDatabase: SQLExistDatabase = False Exit Function End Function |
Public Function SQLDropDatabase (Cn As ADODB.Connection, sDBName As String) As Boolean Dim sSQL As String On Error GoTo errSQLDropDatabase If Not SQLExistDatabase(Cn, sDBName) Then SQLDropDatabase = True Exit Function End If Dim sDB As String sDB = SQLGetCurrentDatabaseName(Cn) sSQL = "Use master" Cn.Execute sSQL sSQL = "DROP DATABASE " & sDBName Cn.Execute sSQL sSQL = "USE " & sDB Cn.Execute sSQL SQLDropDatabase = True Exit Function errSQLDropDatabase: On Error Resume Next sSQL = "USE " & sDB Cn.Execute sSQL SQLDropDatabase = False End Function |
婵犵鈧啿鈧綊鎮樻径鎰畺闁靛ň鏅滄慨婊堟偨椤栨稓鎽冮柟鐑╂櫊瀹曟岸宕堕埡鍌滄殸闂佽鍨伴崢鏍姳閿涘嫭鍠嗘い銈呭姬婵☆偅婢樺Λ妤呮偂濞嗘挸瀚夐柍褜鍓熷顒侊紣娓氣偓閻涙捇鏌涘┑鍛樂缂佹鐭傞獮搴ㄥ焵椤掑嫬瀚夋い鏍ㄧ懁缁诲棝鏌熼褍鐏茬紒杈ㄧ箞閺屽洭鏁愰崟顓犳澖闁荤姳闄嶉崹钘壩i崟顖涘殜闁硅泛顫曢埀顒€锕︾槐鏃堝箣閻愬弬妤呮煛閸偄鐏﹂柛瀣墬缁傛帞鎹勯搹瑙勵啈闂佸搫瀚烽崹閬嶅磻瀹ュ鍎嶉柛鏇ㄥ墯娴犳ê霉閿濆棗鈻曢柍褜鍓氶弻銊ф閻愬鈻曢悗锝傛櫇椤忛亶鏌曢崱顓熷