在vb.net中使用odbc数据源。需要odbc.net的支持。
1.下载ODBC.NET (FrameWork 2.0以上默认未安装)
下载地址:http://www.microsoft.com/downloads/details.aspx?familyid=6ccd8427-1017-4f33-a062-d165078e32b1
2.创建项目，添加Microsoft.Data.ODBC.dll 引用
3.vb.net里要通过odbc连接数据库
在vb.net中创建一个module
imports System.Data
Imports System.Data.Odbc
‘数据连接部分
myStrConn = "DSN=Check2005;PWD=xg2005;"
Try
myConn = New OdbcConnection(myStrConn)
myConn.Open()
Catch ex As Exception
MsgBox("数据库连接发生错误!", MsgBoxStyle.OKOnly, "系统错误")
End Try
4.数据获取部分
Imports Microsoft.Data.Odbc
Module Module1
Sub Main()
Dim conn As OdbcConnection
Dim comm As OdbcCommand
Dim dr As OdbcDataReader
Dim connectionString As String
Dim sql As String
connectionString = "DSN=PracticalLotusScriptTest;UID=Chester;Pwd=Tester;"
sql = "SELECt CustomerID, ContactName, ContactTitle FROM Customers"
conn = New OdbcConnection(connectionString)
conn.Open()
comm = New OdbcCommand(sql, conn)
dr = comm.ExecuteReader()
While (dr.Read())
Console.WriteLine(dr.GetValue(0).ToString())
Console.WriteLine(dr.GetValue(1).ToString())
Console.WriteLine(dr.GetValue(2).ToString())
End While
conn.Close()
dr.Close()
comm.Dispose()
conn.Dispose()
End Sub
End Module
5.插入记录
Dim myConnectionString As String
myConnectionString = "DRIVER={SQL Server};SERVER=SHA-RICKIE-01;UID=user;Trusted_connection=true;DATABASE=test;"
Dim myConn As New OdbcConnection(myConnectionString)
Dim myInsertQuery As String = "INSERT INTO friends (name,age,gender,address) Values('odbc', 88,'male','shanghai')"
Dim myOdbcCommand As New OdbcCommand(myInsertQuery)
myOdbcCommand.Connection = myConn
myConn.Open()
myOdbcCommand.CommandType=CommandType.StoredProcedure ‘(这句是关键)
myOdbcCommand.ExecuteNonQuery()
myConn.Close()
6.delete record
Dim myInsertQuery1 As String = "delete all from DBA.dossierm;" ' where id =000001"
Dim myOdbcCommand1 As OdbcCommand
myOdbcCommand1 = New OdbcCommand(myInsertQuery1)
myOdbcCommand1.Connection = conn
myOdbcCommand1.CommandType = CommandType.StoredProcedure
myOdbcCommand1.ExecuteNonQuery()
myOdbcCommand1.Dispose()
Dim dbfile As String
dbfile = OpenFileDialog2.FileName
Dim key As Microsoft.Win32.RegistryKey, subkey As Microsoft.Win32.RegistryKey
'key = Microsoft.Win32.Registry.CurrentUser 'key为HKEY_USERS根键
'subkey = key.CreateSubKey("Softwarevb.net") '在HKEY_USERSsoftware下创建一个vb.net子键
'subkey.SetValue("GOOD?", "of course") '在HKEY_USERSsoftwarevb.net下设置一个键值，键值名为good?,值为of course了
key = Microsoft.Win32.Registry.LocalMachine
subkey = key.CreateSubKey("SOFTWAREODBCODBCINST.INIasa")
subkey.SetValue("driver", "c:\sdkj\dbodbc6.dll")
subkey.SetValue("setup", "c:\sdkj\dbodbc6.dll")
' [HKEY_LOCAL_MACHINESOFTWAREODBCODBCINST.INIasa]
'"driver"="c:\sdkj\dbodbc6.dll"
'"setup"="c:\sdkj\dbodbc6.dll"
Dim permissionCheck As Microsoft.Win32.RegistryKeyPermissionCheck
permissionCheck = Microsoft.Win32.RegistryKeyPermissionCheck.ReadWriteSubTree
key = Microsoft.Win32.Registry.CurrentUser
subkey = key.OpenSubKey("SoftwareODBCODBC.INIODBC Data Sources", permissionCheck)
subkey.SetValue("sutao", "asa")
'[HKEY_CURRENT_USERSoftwareODBCODBC.INIODBC Data Sources]
'"sutao"="asa"
key = Microsoft.Win32.Registry.CurrentUser
subkey = key.CreateSubKey("SoftwareODBCODBC.INIsutao")
subkey.SetValue("AutoStop", "YES")
subkey.SetValue("DatabaseFile", dbfile)
subkey.SetValue("Debug", "NO")
subkey.SetValue("DisableMultiRowFetch", "NO")
subkey.SetValue("Driver", "c:\sdkj\dbodbc6.dll")
subkey.SetValue("Integrated", "NO")
subkey.SetValue("PWD", "sql")
subkey.SetValue("UID", "dba")
subkey.SetValue("start", "c:\sdkj\dbeng6.exe -c 8m -Q")
8.获取表中记录的个数
Dim sqlstr = "select count(0) from DBA.dossierm;"
Dim SqlComm As OdbcCommand
SqlComm = New OdbcCommand(sqlstr, conn)
Dim currentCardNumer As Integer
currentCardNumer = SqlComm.ExecuteScalar()
SqlComm.Dispose()
9.使用注册表来创建odbc数据源
创建一个pp.reg注册表文件，添加如下
注册表(使用odbc的情况)
(1) 做安装程序的时候修改注册表数据源配置
(2) 程序中获取当前的文件路径(主要是.db文件和dbodbc6.dll,dbeng6.exe那个文件的路径)进行odbc配置
具体的注册表配置类似如下(用户数据源)：
[HKEY_CURRENT_USERSoftwareODBCODBC.INIYYHIS]
"AutoStop"="YES"
"DatabaseFile"="D:yyhisjkcDataBaseyyhis.db" //数据库文件
"Debug"="NO"
"DisableMultiRowFetch"="NO"
"Driver"="D:yyhisASAdbodbc6.dll" //odbc驱动
"Integrated"="NO"
"PWD"="sql"
"UID"="dba"
"Start"="D:yyhisASAdbeng6.exe -c 8m -Q" //隐藏启动
如果还要能让别人通过控制面板中的数据源来修改这个数据源
你还需要修改注册表，首先要注册该数据库odbc driver(这里是dbodbc.dll)类似于如下(注册为asa):
[HKEY_LOCAL_MACHINESOFTWAREODBCODBCINST.INIasa]
"driver"="D:yyhisASAdbodbc6.dll"
"setup"="D:yyhisASAdbodbc6.dll"
其次你要登记数据源
[HKEY_CURRENT_USERSoftwareODBCODBC.INIODBC Data Sources]
"yyhis"="asa"//asa就是上面注册的
这样子别人就可以通过控制面板中的数据源管理来修改
另外如果不是登记用户数据源改变一下YYHIS的位置(在第一个注册表配置中把HKEY_CURRENT_USER替换为HKEY_LOCAL_MACHINE)
还有你要吧你所用到的其它文件(比方系统的和自己写的一些动态连接库)
如果不在应用程序同一目录中，那么你就要为该应用app.exe指明一些路径
类似于下：
[HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindowsCurrentVersionApp Pathsapp.exe]
@="d:PROGRA~1cardapp.exe"
"path"="d:program filescarddll;d:\PROGRA~1cardasa"
10.vb.net 中也可以直接写注册表来实现如上功能
Dim key As Microsoft.Win32.RegistryKey, subkey As Microsoft.Win32.RegistryKey
key = Microsoft.Win32.Registry.CurrentUser 'key为HKEY_USERS根键
subkey = key.CreateSubKey("Softwarevb.net") '在HKEY_USERSsoftware下创建一个vb.net子键
subkey.SetValue("GOOD?", "of course") '在HKEY_USERSsoftwarevb.net下设置一个键值，键值名为good?,值为of course了
上面是在注册表里创建键了，下面我们用程序来访问我们刚创建的注册表的键吧：
key = microsoft.Win32.Registry.CurrentUser '如上
subkey=key.OpenSubKey("softwarevb.net",True) 'subkey即为HKEY_USERSsoftwarevb.net键
dim value as object=subkey.GetValue("Good?") 'value为HKEY_USERSsoftwarevb.net键中的GOOD?的值
msgbox(value.tostring()) '将出现of course了
上面是获取注册表键值了，要设值，还是用subkey.setvalue了
来讲讲删除吧：
key.DeleteSubKey("softwarevb.net") '删除这个键了也可以用：
key.DeleteSubKeyTree("softwarevb.net")
还可以用key.GetSubKeyNames来遍历子键
即：for i=0 to ubound(key.GetSubKeyNames())
subkey=key.OpenSubKey(key.GetSubKeyNames()(i),True)
....... '对每个子键的处理
next
用key.GetValueNames来遍历各个值
即：
for i=0 to ubound(key.GetValueNames())
key.GetValueNames()(i) '为每个值的名称
value=key.Getvalue(key.GetValueNames()(i)) '为每个值的值
获取一个subkey
subkey = key.OpenSubKey("SoftwareODBCODBC.INIODBC Data Sources", permissionCheck)
11. 含odbc.net的程序在发布到客户机上后运行出现错误
System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.Data.Odbc, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.Data.Odbc, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
原因就是没有安装ODBC.NET
到microsoft的地址上下载来安装上就可以了。
参考了：odbc没有死
