VB连接数据库的方法2.doc

VB连接数据库的方法2006-11-22 16:45:19|分类： 默认分类 |标签： |字号大中小订阅 一、ASP的对象存取数据库方法在ASP中，用来存取数据库的对象统称ADO（Active Data Objects），主要含有三种对象：Connection、Recordset 、CommandConnection：负责打开或连接数据Recordset：负责存取数据表Command：负责对数据库执行行动查询命令二、连接各数据库的驱动程序连接各数据库可以使用驱动程序，也可以使用数据源，不过我建议大家使用驱动程序，因为使用驱动程序非常方便、简单，而使用数据源比较麻烦。ODBC链接适合数据库类型 链接方式 access Driver=microsoft access driver(*.mdb);dbq=*.mdb;uid=admin;pwd=pass; dBase Driver=microsoft dbase driver(*.dbf);driverid=277;dbq=-; Oracle Driver=microsoft odbc for oracle;server=oraclesever.world;uid=admin;pwd=pass; MSSQL serverDriver=sql server;server=servername;database=dbname;uid=sa;pwd=pass; MS text Driver=microsoft text driver(*.txt; *.csv);dbq=-;extensions=asc,csv,tab,txt;Persist SecurityInfo=false; Visual Foxpro Driver=microsoft Visual Foxpro driver;sourcetype=DBC;sourceDB=*.dbc;Exclusive=No; MySQL Driver=mysql;database=yourdatabase;uid=username;pwd=yourpassword;option=16386; OLEDB链接适合的数据库类型 链接方式 access Provider=microsoft.jet.oledb.4.0;data source=your_database_path;user Oracle Provider=OraOLEDB.Oracle;data source=dbname;user MS SQL Server Provider=SQLOLEDB;data source=machinename;initial catalog=dbname;userid=sa;password=pass; MS text Provider=microsof.jet.oledb.4.0;data source=your_path;Extended Propertiestext;FMT=Delimited 而我们在一般情况下使用Access的数据库比较多，在这里建议连接Access数据库使用下面的方法：dim connset conn = server.createobject(adodb.connection)conn.open = provider=microsoft.jet.oledb.4.0; & data source = & server.mappath(./db/bbs.mdb)其中./db/bbs.mdb是你的数据库存放的相对路径！如果你的数据库和ASP文件在同一目录下，你只要这样写就可以了：dim connset conn = server.createobject(adodb.connection)conn.open = provider=microsoft.jet.oledb.4.0; & data source = & server.mappath(bbs.mdb)有许多初学者在遇到数据库连接时总是会出问题，然而使用上面的驱动程序只要你的数据库路径选对了就不会出问题了。sqlserver的连接可以如下：Set conn=Server.CreateObject(ADODB.Connection)on error resume nextconnstr=Provider=SQLOLEDB;Password=*;Persist Security Info=True;User Catalog=msdb;Data Source=localhost;Connect Timeout=15 conn.Open connstr经常会在连接DB的时候用到，就是不知道代表什么意思。RS.OPEN SQL,CONN,A,BA: ADOPENFORWARDONLY(=0) 只读,且当前数据记录只能向下移动ADOPENKEYSET(=1) 只读,当前数据记录可自由移动ADOPENDYNAMIC(=2) 可读写,当前数据记录可自由移动ADOPENSTATIC(=3) 可读写,当前数据记录可自由移动,可看到新增记录B:ADLOCKREADONLY(=1) 缺省锁定类型，记录集是只读的，不能修改记录ADLOCKPESSIMISTIC(=2) 悲观锁定，当修改记录时，数据提供者将尝试锁定记录以确保成功地编辑记录。只要编辑一开始，则立即锁住记录。ADLOCKOPTIMISTIC(=3) 乐观锁定 ，直到用Update方法提交更新记录时才锁定记录。ADLOCKBATCHOPTIMISTIC(=4) 批量乐观锁定，允许修改多个记录，只有调用UpdateBatch方法后才锁定记录。当不需要改动任何记录时，应该使用只读的记录集，这样提供者不用做任何检测。对于一般的使用，乐观的锁定可能是最好的选择，因为记录只被锁定一小段时间，数据在这段时间被更新。这减少了资源的使用。VB几种连接数据库的方法(2008-04-03 12:47:58) 转载标签： 杂谈、用DAO控件连接数据库1.与Access2000数据库连接Private Sub Command1_Click()也可直接在控件属性中设置以下各项但在控件属性中不能写入密码只有在数据数没有密码的情况下可以省略Data1.RefreshData1.Connect = Access 2000;Data1.DatabaseName = App. Path + chncmadb.mdb数据库没有密码此句可省Data1.Connect = ;pwd=123456Data1.RecordSource = 耕地资源管理单元属性数据表2004Data1.RecordSource = select * from 耕地资源管理单元属性数据表2004Data1.Refreshmove后才能正确显示记录个数End Sub2.与没有密码的DBF文件数据库连接Private Sub Command2_Click()Data1.Connect = dBASE III;Data1.DatabaseName = App. Path Data1.RecordSource =DBFData1.RecordSource = select * from dbfData1.Refreshmove后才能正确显示记录个数End Sub3.与没有密码的Excel文件数据库连接Private Sub Command3_Click()Data1.Connect = Excel 8.0;Data1.DatabaseName = App.Path & EXcel.xlsData1.RecordSource = select * from EXcel.xlsData1.Refreshmove后才能正确显示记录个End Sub二、用DAO代码连接数据库在使用DAO对象前应选定Visual Basic菜单下的工程中的引用了菜单中的Microsoft DAO 3.6 Object Library选项，或其它版本1.DAO代码与Access数据库连接Private Sub Command1_Click()Dim Db As DatabaseDim Rs As Recordset以共享、读写方式打开如果无密码最后一个参数可以不要SetDb= OpenDatabase(App.Path & chncmadb.mdb, False, False, ;pwd=123456)不需要move来更新记录个数Set Rs = Db.OpenRecordset(耕地资源管理单元属性数据表2004)需要move来更新记录个数Set Rs = Db.OpenRecordset(select * from 耕地资源管理单元属性数据表2004)If Rs.RecordCount 0 Then Rs.MoveLast Rs.MoveFirstEnd IfEnd Sub2.DAO代码与没有密码的DBF文件数据库连接Private Sub Command2_Click()Dim Db As DatabaseDim Rs As Recordset以共享、读写方式打开Set Db = OpenDatabase(App.Path, False, False, dbase III;)不需要move来更新记录个数Set Rs = Db.OpenRecordset(DBF)需要move来更新记录个数Set Rs = Db.OpenRecordset(select * from DBF)If Rs.RecordCount 0 Then Rs.MoveLast Rs.MoveFirstEnd IfEnd sub3. DAO代码与没有密码的Excel文件数据库连接Private Sub Command3_Click()Dim Db As DatabaseDim Rs As Recordset以共享、读写方式打开如果无密码最后一个参数可以不要Set Db = OpenDatabase(App.Path & EXcel.xls, False, False, Excel 8.0;)不需要move来更新记录个数 Set Rs = Db.OpenRecordset(EXcel.xls) 表格中的工作目录sheet需要move来更新记录个数Set Rs = Db.OpenRecordset(select * from EXcel.xls) 表格中的工作目录sheetIf Rs.RecordCount 0 Then Rs.MoveLast Rs.MoveFirstEnd IfEnd Sub三、用ADO控件连接数据库也可直接在控件属性中设置以下各项1.ADO控件与Access2000数据库连接Private Sub Command1_Click() 连接有密码的Access数据库 Adodc1.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= & App.Path & chncmadb1.mdb;Jet OLEDB:DataBase PASSWORD=123456 连接没有密码的Access数据库 Adodc1.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= & App.Path & chncmadb.mdb;Persist Security Info=False Adodc1.RecordSource = 耕地资源管理单元属性数据表2004 Adodc1.RecordSource = select * from 耕地资源管理单元属性数据表2004 Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.RefreshEnd Sub2.ADO控件与DBF表连接Private Sub Command2_Click() Adodc1.ConnectionString = Provider=MSDASQL.1;Persist Security Info=False;Data Source=dBASE Files;DBQ= & App.Path & ;SourceType=DBF; Adodc1.ConnectionString = Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=DSN=Visual FoxPro Tables;UID=;SourceDB=”& app.path &”;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes; Adodc1.ConnectionString = Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=DSN=dBASE Files;DBQ=”& app.path &”;DefaultDir=”& app.path &”;DriverId=533;MaxBufferSize=2048;PageTimeout=5; 能使表名长度不受限制 Adodc1.ConnectionString = Provider=MSDASQL.1;Driver=Microsoft Visual Foxpro Driver;SourceDB= & App.Path & ;SourceType=DBF;Locale Identifier=2052 Adodc1.RecordSource = DBF1 Adodc1.RecordSource = select * from DBF1 Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.RefreshEnd Sub3.ADO控件与Excel表连接Private Sub Command3_Click() 下面一句测试未能通过 Adodc1.ConnectionString = Data Provider=MSDASQL.1;driver=Microsoft Excel Driver *.xls);DBQ= & App.Path & EXcel.xls Adodc1.ConnectionString=Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=DSN=Excel Files;DBQ= & App.Path & EXcel.xls;DefaultDir=”&app.path &”;DriverId=790;MaxBufferSize=2048;PageTimeout=5; Adodc1.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source= & App.Path & EXcel.xls;Extended Properties=Excel 8.0;HDR=Yes Adodc1.RecordSource = EXcel.xls Adodc1.RecordSource = select * from EXcel.xls Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.RefreshEnd Sub4.ADO控件与Oracle数据库连接Private Sub Command4_Click() Adodc1.ConnectionString = Provider=MSDAORA.1;Password=chncmadb;User ID=chncmadb;Data Source=towebserver;Persist Security Info=TrueAdodc1.ConnectionString=Provider=OraOLEDB.Oracle.1;Password=chncmadb;Persist Security Info=True;User ID=chncmadb;Data Source=towebserver Adodc1.RecordSource = T320481TR012004 表名不能加方括号 Adodc1.RecordSource = select * from T320481TR012004 Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.RefreshEnd Sub5.ADO控件与SQLserver数据库连接未测试Private Sub Command5_Click() Adodc1.ConnectionString = Provider=SQLOLEDB.1;Password=111;Persist Security Info=True;User ID=111;Initial Catalog=111;Data Source=111 Adodc1.RecordSource = T320481TR012004 Adodc1.RecordSource = select * from T320481TR012004 Adodc1.Refresh Set DataGrid1.DataSource = Adodc1 DataGrid1.RefreshEnd Sub四、用ADO代码连接数据库在使用ADO对象前应选定Visual Basic菜单下的工程中的引用了菜单中的Microsoft.ActiveX Data Object 2.5 Library选项，或其它版本1.ADO代码与Access2000数据库连接Private Sub Command1_Click() Dim AdoCnn As ADODB.Connection Dim AdoRs As ADODB.Recordset Set AdoCnn = New ADODB.Connection Set AdoRs = New ADODB.Recordset AdoCnn.CursorLocation = adUseClient .open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串 AdoCnn.Open Provider=Microsoft.Jet.OLEDB.4.0;Data Source= & App.Path & chncmadb1.mdb;Jet OLEDB:DataBase PASSWORD=123456AdoRs.Open select * from 耕地资源管理单元属性数据表2004, AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub2.ADO代码与DBF表连接Private Sub Command2_Click() Dim AdoCnn As ADODB.Connection Dim AdoRs As ADODB.Recordset Set AdoCnn = New ADODB.Connection Set AdoRs = New ADODB.Recordset AdoCnn.CursorLocation = adUseClient .open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串 AdoCnn.Open Provider=MSDASQL.1;Driver=Microsoft Visual Foxpro Driver;SourceDB= & App.Path & ;SourceType=DBF;Locale Identifier=2052AdoRs.Open select * from DBF1, AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub3.ADO代码与Excel表连接Private Sub Command3_Click() Dim AdoCnn As ADODB.Connection Dim AdoRs As ADODB.Recordset Set AdoCnn = New ADODB.Connection Set AdoRs = New ADODB.Recordset AdoCnn.CursorLocation = adUseClient .open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串 AdoCnn.OpenProvider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source= & App.Path & EXcel.xls;Extended Properties=Excel 8.0;HDR=YesAdoRs.Open select * from EXcel.xls, AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub4.ADO代码与Oracle数据库连接Private Sub Command4_Click() Dim AdoCnn As ADODB.Connection Dim AdoRs As ADODB.Recordset Set AdoCnn = New ADODB.Connection Set AdoRs = New ADODB.Recordset AdoCnn.CursorLocation = adUseClient .open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串 AdoCnn.Open Provider=OraOLEDB.Oracle.1;Password=chncmadb;Persist Security Info=True;User ID=chncmadb;Data Source=towebserverAdoRs.Open select * from T320481TR012004, AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub5.ADO代码与SQLserver数据库连接未测试Private Sub Command5_Click() Dim AdoCnn As ADODB.Connection Dim AdoRs As ADODB.Recordset Set AdoCnn = New ADODB.Connection Set AdoRs = New ADODB.Recordset AdoCnn.CursorLocation = adUseClient .open后面的字符串可以参考ADO控件连接.ConnectionString后面的的字符串 AdoCnn.Open Provider=SQLOLEDB.1;Password=111;Persist Security Info=True;User ID=111;Initial Catalog=111;Data Source=111AdoRs.Open select * from T320481TR012004, AdoCnn, adOpenDynamic, adLockPessimistic, adCmdText Set DataGrid1.DataSource = AdoRs Set AdoRs = Nothing Set AdoCnn = NothingEnd Sub连库Dim DLYHM, DLMM, DLSLM As StringDim Grecordset As New ADODB.RecordsetDim Gconnection_HQ As New ADODB.ConnectionDLYHM = USER ID= + 用户名DLMM = password= + 登录密码DLSLM = Data Source= + DBCS中的实例名LSsjk = DLYHM + ; + DLMM + ; + DLSLMLSsjk = Provider=OraOLEDB.Oracle.1;Pe