涓嬮潰鏄垜鎵鎺屾彙鐨勪嬌鐢ˋDO瀵規暟鎹簱鎿嶄綔鐨勪竴浜涘父鐢ㄦ柟娉曪紝涓昏鏄彁渚涚粰鍒濆鑰呬綔涓哄弬鑰冿紝鏈変笉瀵圭殑鍦版柟璇鋒寚姝ｃ傚鏈夎ˉ鍏呬笉鑳滆崳騫?br>鍑嗗宸ヤ綔
========
Dim conn As New ADODB.Connection '鍒涘緩涓涓?Connection 瀹炰緥錛屽湪榪欓噷浣跨敤New絳変簬灝咲im鍜孲et鍚堝茍涓轟竴孌?a >浠ｇ爜鎵ц
Dim rs As ADODB.Recordset '鍒涘緩涓涓?Recordset 瀹炰緥錛屼笉浣跨敤New 鏄洜涓猴紝緇忓父闇瑕侀噸澶嶄嬌鐢⊿et錛屽洜姝ゆ病蹇呰鍦ㄨ繖閲屼嬌鐢?br>Dim CnStr As String, Sql As String '鍒涘緩涓や釜瀛楃涓插彉閲忓垎鍒瓨鏀句袱涓泦鍚堢殑SQL璇彞浠ｇ爜孌?br>1銆佽杞芥暟鎹簱(涓嶅睘浜嶳ecordset闆嗗悎)
=============
Dim FileNamw$, DbIp$, DbName$, DbUser$, DbPw$
'浠ヤ笂5涓瓧絎︿覆鍙橀噺鍒嗗埆琛ㄧず鏂囦歡璺緞鍜屾枃浠跺悕銆佹暟鎹簱鍦板潃銆佹暟鎹簱鍚嶃佹暟鎹搷浣滃憳鐢ㄦ埛鍚嶃佹搷浣滃憳瀵嗙爜
FileName = App.Path & "\'鏁版嵁搴撳悕'"
DbIp = "鏁版嵁搴撳湴鍧"
DbName = "鏁版嵁搴撳悕"
DbUser = "鏁版嵁鎿嶄綔鍛樼敤鎴峰悕"
DbPw = "鎿嶄綔鍛樺瘑鐮?
'浠ヤ笂鍙橀噺鏍規嵁鏁版嵁搴撶被鍨嬬殑涓嶅悓鑰屼笉鍚岋紝鏈夊彲鑳藉彧闇瑕?鑷充袱涓彉閲?br>'1)榪炴帴Access鏁版嵁搴?
'-------------------
CnStr = "PROVIDER=microsoft.jet.oledb.3.51;persist security info =false;data source=" & FileName & ";Jet OLEDB:Database Password=" & DbPw
'2)榪炴帴Oracle鏁版嵁搴?
'-------------------
CnStr = "PROVIDER=MSDAORA.1;Password=" & DbPw & ";User ID=" & DbUser & ";Data Source=" & FileName & ";Persist Security Info=True"
'鍏朵腑:
'PASSWORD: 瀵嗙爜
'User ID: 鐢ㄦ埛鍙?br>'Data Source: 鏁版嵁搴撳悕
'Persist Security Info:
'Provider:
'3)榪炴帴VF鐨凞BF搴?
'----------------
CnStr = "PROVIDER=MSDASQL.1;Persist Security Info=False;Driver={Microsoft Visual FoxPro Driver};UID=" & DbUser & ";SourceDB=" & FileName &
";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;"
'4)榪炴帴SQL鐨勬暟鎹簱
'------------------
CnStr = "PROVIDER=MSDataShape;Data PROVIDER=MSDASQL;uid=" & DbUser & ";pwd=" & DbPw & ";DRIVER=SQL Server;DATABASE=" & DbName & ";WSID=GQSOFT;SERVER=" &
DbIP
'涔熷彲浠ヤ嬌鐢ㄨ繖孌電畝鏄撲唬鐮?CnStr = "Provider=SQLOLEDB;Data Source=" & DbIp & ";DATABASE=" & DbName & ";UID=" & DbUser & ";pwd=" & DbPw
Conn.Open cnstr '浣跨敤 Connection 闆嗗悎鐨?Open 鏂規硶 涓庢暟鎹簱寤虹珛榪炴帴
2銆丷ecordset闆嗗悎鐨勫父鐢ㄦ柟娉?br>==========================
'1)鎵撳紑涓涓〃
'------------
Sql = "select * from 琛ㄥ悕" 'SQL鏌ヨ璇彞
Set rs = New ADODB.Recordset '鏂板緩涓涓疄渚?br>rs.Open Sql, conn '浣跨敤 Open 鏂規硶鎵撳紑鏁版嵁搴撲腑鐨勪竴涓〃
'娉ㄦ剰錛岃繖縐嶆墦寮鏂瑰紡鍙兘浣跨敤 rs.MoveNext 錛堝嵆錛屽悜鍚庣Щ鍔ㄨ鍧愭爣錛夎屼笉鑳藉儚鍏朵粬鏂瑰悜錛屽茍涓斾笉鑳戒慨鏀規暟鎹唴瀹?br>'
'rs.Open Sql, conn錛? '铏界劧鍙姞浜嗕釜鈥?鈥濓紝浣嗚繖縐嶆柟娉曞彲浠ュ悜浠諱綍鏂瑰悜縐誨姩琛屽潗鏍囥?br>'
'浠ヤ笅鍙傛暟浠ｈ〃浜嗚繖涓彲閫夊肩殑鍚箟
'0 = adOpenForwardOnly 錛堥粯璁ゅ鹼級鎵撳紑浠呭悜鍓嶇被鍨嬫父鏍囥?
'1 = adOpenKeyset 鎵撳紑閿泦綾誨瀷娓告爣銆?
'2 = adOpenDynamic 鎵撳紑鍔ㄦ佺被鍨嬫父鏍囥?
'3 = adOpenStatic 鎵撳紑闈欐佺被鍨嬫父鏍囥?br>'
'铏界劧浣跨敤浠ヤ笂鏂規硶鍙互鍙互瀹炵幇琛屽潗鏍囷紙娓告爣錛夌殑浠繪剰縐誨姩錛屼絾鏄粛鐒?a >鏃犳硶鍐欏叆鏁版嵁銆傚洜姝ら渶瑕佽繘涓姝ョ殑瀵筄pen 鏂規硶榪涜瀹屽杽
'rs.Open Sql, conn, 1, 3 '鍚庨潰鐨?鏄‘瀹氳鍐欐潈闄愮殑
'浠ヤ笅鍙傛暟浠ｈ〃浜嗚繖涓彲閫夊肩殑鍚箟
'1 = adLockReadOnly 錛堥粯璁ゅ鹼級鍙 鈥?涓嶈兘鏀瑰彉鏁版嵁銆?
'2 = adLockPessimistic 淇濆畧寮忛攣瀹氾紙閫愪釜錛?鈥?鍦ㄧ紪杈戞椂绔嬪嵆閿佸畾鏁版嵁婧愮殑璁板綍銆?
'3 = adLockOptimistic 寮鏀懼紡閿佸畾錛堥愪釜錛?鈥?鍙湪璋冪敤 Update 鏂規硶鏃舵墠閿佸畾璁板綍銆?
'4 = adLockBatchOptimistic 寮鏀懼紡鎵規洿鏂?鈥?鐢ㄤ簬鎵規洿鏂版ā寮忥紙涓庣珛鍗蟲洿鏂版ā寮忕浉瀵癸級銆?br>'
'2)璇誨啓鏁版嵁
'----------
'澧炲姞涓琛岃褰曞茍瀵規柊璁板綍鐨勫唴瀹硅繘琛屼慨鏀瑰茍淇濆瓨鍙互濡備笅鍐欐硶
rs.AddNew '澧炲姞涓琛岃褰?br>rs("...")="..." '鏁版嵁璇誨啓鎿嶄綔
...
rs.UpDate '淇濆瓨鍐欏叆璧勬枡錛屽鏋滀嬌鐢ㄥ彧璇繪潈闄愶紝鍒欎笉鑳戒嬌鐢ㄨ繖涓柟娉?br>rs.Close '榪欎釜鏂規硶鐢ㄦ潵鍏抽棴浣犳墍浠ｅ紑鐨勮〃錛屽鏋滀笉浣跨敤榪欎釜鏂規硶涔熷彲浠ワ紝浣嗘槸鏁版嵁搴撲粛鐒惰涓轟綘鍦ㄥ鏍囪繘琛岄攣瀹氾紝鍙犳垚鏁版嵁搴撹礋鎷呰繃閲?br>2銆佸鏁版嵁榪涜絳涢夊拰鎺掑簭
=======================
Sql = "select * from 琛ㄥ悕" 'SQL鏌ヨ璇彞
'浠ヤ笂涓烘墦寮涓涓〃鐨勬墍鏈夊唴瀹?br>Sql = "select top 50 * from 琛ㄥ悕" 'SQL鏌ヨ璇彞
'浠ヤ笂涓哄彧鎵撳紑鍓?0琛岀殑璁板綍
Sql = "select top 50 鍒楀悕1,鍒楀悕2,鍒楀悕5 from 琛ㄥ悕" 'SQL鏌ヨ璇彞
'浠ヤ笂涓哄彧鎵撳紑鍓?0琛岀殑璁板綍錛屽茍涓斿彧鎵撳紑絎?銆?銆?鍒楁暟鎹?br>Sql = "select * from 琛ㄥ悕 where 鍒楀悕1='" & 瀛楁 & "'" 'SQL鏌ヨ璇彞
'浠ヤ笂涓轟竴涓畝鍗曠殑絳涢夛紝琛ㄧず鎵撳紑鐨勫唴瀹瑰繀欏葷鍚圼鍒楀悕1='" & 瀛楁 & "'"]鐨勫唴瀹?br>'闇瑕佹敞鎰忕殑鏄紝鏁版嵁搴撳垪鐨勭被鍨嬪繀欏誨尮閰嶏紝姣斿瀛楃涓茬被鍨嬮渶瑕佷互鍗曞紩鍙鋒嫭璧?br>'鑰屾暟瀛楃被鍨嬪垯涓嶈兘鐢ㄥ崟寮曞彿鎷搗
'鍙﹀SQL璇彞榪樻敮鎸侀氶厤絎︼紝渚嬪 鍒楀悕1 like '%" & 瀛楁 & "%'" 琛ㄧず鍖呭惈[瀛楁]
'鍦ㄨ〃杈懼紡涓殑鍖歸厤
'? _錛堜笅鍒掔嚎錛?浠諱綍鍗曚竴瀛楃
'* or % 闆朵釜鎴栧涓瓧絎?
'# 浠諱綍鍗曚竴鏁板瓧錛? 鈥?9錛?
'[charlist] 浠諱綍鍦ㄥ瓧絎﹁〃涓殑鍗曚竴瀛楃
'[!charlist] 浠諱綍涓嶅湪瀛楃琛ㄤ腑鐨勫崟涓瀛楃
'娉細鏍規嵁鏁版嵁搴撶殑涓嶅悓?銆乢鍜?銆?鐨勫簲鐢ㄦ湁鎵宸埆錛屾瘮濡係QL鍙嬌鐢? 鍜?_ 鍒嗗埆浠ｈ〃澶氫釜瀛楃鍜屽崟涓瀛楃
Sql = "select * from 琛ㄥ悕 where 鍒楀悕1='" & 瀛楁 & "' ORDER BY 瀛楁1 ASC" '瀵規煡璇㈢粨鏋滆繘琛屽崌搴忔帓鍒?br>Sql = "select * from 琛ㄥ悕 where 鍒楀悕1='" & 瀛楁 & "' ORDER BY 瀛楁1 DESC" '瀵規煡璇㈢粨鏋滆繘琛岄檷搴忔帓鍒?br>Sql = "select * from 琛ㄥ悕 where 鍒楀悕1='" & 瀛楁 & "' ORDER BY 瀛楁1 DESC, ORDER BY 瀛楁2 DESC" '瀵規煡璇㈢粨鏋滆繘琛屽緇撮檷搴忔帓鍒楋紙閫熷害濂囨參錛屼笉鎺ㄨ崘浣跨敤錛?br>3銆丷ecordset闆嗗悎鐨勫父鐢ㄥ睘鎬ф柟娉?br>==============================
rs.BOF '鏄惁娓告爣鍦ㄦ渶涓婅竟
rs.EOF '鏄惁娓告爣鍦ㄦ渶涓嬭竟
rs.RecordCount '璁板綍闆嗙殑琛屾暟錛堝鏋滀嬌鐢ㄥ儚鍓嶇被鍨嬬殑娓告爣錛屽彲鑳借繑鍥炰笉鍑嗙‘錛?br>rs.AddNew '鏂板緩涓琛岃褰?br>rs.Update '淇濆瓨褰撳墠琛岃淇敼鐨勮褰?br>rs.Delete '鍒犻櫎褰撳墠琛?br>rs.Close '鍏抽棴璁板綍闆?br>4銆丗ields闆嗗悎鐨勪竴浜涘睘鎬х殑瑙ｉ噴
=============================
'浠ヤ笅涓虹畝鍐欙紝姝ｅ父鎯呭喌闇瑕乺s.Fileds.銆傘傘備絾鏄疐ields灞炰簬Recordset鐨勪竴涓粯璁ら泦鍚堬紝鍥犳鍙互鐪佺暐
rs() '鎷彿鍐呭彲浠ユ槸鍒楀悕涔熷彲浠ユ槸鍒楃殑搴忓彿渚嬪錛歳s("濮撳悕")銆乺s(3) 閮芥槸鏄彲浠ョ殑
rs(3).Name '榪斿洖鍒楀悕
rs(3).Type '榪斿洖鍒楃殑綾誨瀷
rs(3).Value '榪斿洖褰撳墠琛岀殑鍊?br>rs.Fields.Count '榪斿洖鍒楁暟
]]>