vb連接sql數(shù)據(jù)庫(kù)的模塊以及使用實(shí)例

1、打開(kāi)VB6.0,新建工程SQL_data,新建一個(gè)EXE,并添加一個(gè)模塊。如圖 一：添加引用和部件（如下兩圖）藍(lán)色的兩個(gè)，是要選中打勾的。注意，一個(gè)是 引用”，一個(gè)是 部件”。:添加一個(gè)模塊 Data_Sql,并把圖下面的復(fù)制粘貼到模塊中。三國(guó)-*設(shè)置為啟動(dòng)己工程1屆性.保存工程理）移除工程危）卓添加膏體虐）T添加MDI窗體也恭加類模塊©添加模煥侃）國(guó)添加用戶控件電） I間誨加屬性頁(yè)麒 性）市加甫尸文拍也） 添加文件值）一.添加區(qū)）詈打印。v可連接的虞）隱藏01）'連接SQL的模塊Public conn As ADODB.ConnectionPublic rs As ADOD

2、B.RecordsetPublic addFlag As Boolean'聲明部分Public Function OpenCn(ByV al Cip As String, ByVal users As String, ByV al pw As String) AsBoolean '連接模塊填寫數(shù)據(jù)庫(kù)等信息Dim mag As StringOn Error GoTo strerrmagSet conn = New ADODB.Connectionconn.ConnectionTimeout = 25conn.Provider = "sqloledb"conn.

3、Properties("data source").Value = Cip'服務(wù)器的名字conn.Properties("initial catalog").V alue = "pubs" '庫(kù)名'conn.Properties("integrated security").V alue = "SSPI"'登陸類型conn.Properties("user id").V alue = users 'SQL 庫(kù)名conn.Properti

4、es("password").V alue = pw '密碼'sql = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;password=;InitialCatalog=pubs;Data Source=”'如果不用這個(gè)模塊也行可以，這一句便是常用的引'conn.ConnectionString = sqlconn.OpenOpenCn = TrueIf conn.State = 1 Then addFlag = TrueExit Funct

5、ionstrerrmag:mag = "Data can't connect"Call MsgBox(mag, vbOKOnly, "Error:Data connect")addFlag = FalseExit Function '連接錯(cuò)誤消息End Function'關(guān)閉數(shù)據(jù)庫(kù)，釋放連接Public Sub cloCn()On Error Resume NextIf conn.State <> adStateClosed Then conn.CloseSet conn = NothingEnd SubPublic

6、Function openRs(ByV al strsql As String) As Boolean'連接數(shù)據(jù)庫(kù)記錄集Dim mag As StringDim rpy As BooleanOn Error GoTo strerrmagSet rs = New ADODB.RecordsetIf addFlag = False Then rpy = TrueWith rs.ActiveConnection = conn.CursorLocation = adUseClient.CursorType = adOpenKeyset.LockType = adLockOptimistic.O

7、pen strsqlEnd WithaddFlag = TrueopenRs = True'End'將記錄集給rsExit Functionstrerrmag:mag = "data not connect"Call MsgBox(mag, vbOKOnly, "error:connect")openRs = FalseEnd'Exit Function '連接錯(cuò)誤消息End FunctionPublic Sub cloRs()On Error Resume NextIf rs.State <> adState

8、Closed Then rs.CloneSet rs = Nothing '釋放記錄集End Sub把它放入到模塊文件中，以備調(diào)用。三：下面兩圖是模塊的代碼圖片和窗體的代碼圖片<H巨,三滲篇X二龜s _。_卜I .新曹陌皆又臼行8,列32通用) j OpeaCn |'連接胡誦根塊=Public eanii As ADQUB. C«iuk«clioTiFubLM rs As ADQDB. Ewor 姑或Fublic 電ddFlaa 姑 Bglean '聲明部紂Public Fucicti on OpenCn (ByYal Cip kw Strin

9、gy ByVsl users As Stringy ByVkl pw As iDim m&g As Stri ngOn Error GoTt> strerrmsgStt conn = H也* ADODE. Coiuk&ctionconn. Cormecti onTimeout = 25conn. Provider - "wqlcd.磨dVconn. Proper ties Cdatfi Eource). Valut = Cip '服務(wù)器的名字conn. Frcp«rti as (inititl catalog) Vlua ='庫(kù)名

10、9;conn. Froprtiesated secnrityn ). Value 二"SSPI" ,登陸類型conn. Proper ti es (user id*). Value 二 users J SQL-conn. Proper ties fpasswori'7). Value = pw ''密均“珥1 = "Provi i&r=SQLOLEI)B. 1: Per si s t Stcurily In£rt=FslEa：UEer TD=EaL pasEwcr(i=; In'conn. Connectionst

11、ring - sqlC9iui. OpenOpanCn - TrueI£ corm. Stta = 1 Th電n LiJFlag = Tru»Exit Functionstrerrmag:mag =七也la can't cwuiectCall MsgBox(jrac vlOKDnlyT "Error：Data connect) addFl - Fls«Exit Function'連接錯(cuò)誤捎息End Functiflii'關(guān)閉數(shù)覺(jué)庫(kù)，蜂敢理接Public Sub cloCn0On Error 普 N$xtIf coMi. State

12、 <> 毋dStatwClomed Th«n conn. CloseSet conn = NothingEnd Edb Public Function openE (Tyal strsql As String) ks Baolean ' 座接象將庫(kù)記錄鳥Dim mag As String(Dim rpy As BoaleanTl匕占Ig c | ,CoBBUldl Cli ckPrivate Sub Coniinaiidl_Click0包=Trim CTextlL Text)b 二 Trim CTextE. Text?c = Trim CText3. TextiC

13、all Ope成n(冬 氐 c)If 履強(qiáng)1幅=True Th商 MsgBos (：DIT)Call openKs Tselect * from Jois*)Set Eat&Gridl. D&taScvirce = rs'rs. d?seEnd SubPrivate Sub Command2_Cl i ck 0Unload M«End SubPrivate Sub Command1_Click()a = Trim(Text1.Text)b = Trim(Text2.Text)c = Trim(Text3.Text)Call OpenCn(a, b, c)If addFlag = True Then MsgBox ("OK&qu