VB与数据库连接不能放在模块中，请大家帮下忙，谢谢
rivate Sub cmdOK_Click()
'注册新用户
Dim cnn1 As ADODB.Connection
Dim AddUser As New ADODB.Recordset
Dim SqlStr As String
Dim DBstr As String
Dim UsrName As String
Dim UsrPwd As String
Dim strCnn As String
'与数据库连接
Set cnn1 = New ADODB.Connection
strCnn = "Provider = SQLOLEDB; Data Source = localhost ;User ID = sa; & _
Initial catalog = wastebook ;Password = ;"
cnn1.Open strCnn
'与表连接
Set AddUser = New ADODB.Recordset
AddUser.CursorType = adOpenKeyset
AddUser.LockType = adLockOptimistic
×× AddUser.Open "UserInfo", cnn1, adOpenForwardOnly, adLockOptimistic
End Sub
以上代码通过，于是我新建了一个模块，并Public g_Conn As ADODB.Connection 把g_Conn设为全局变量，在模块中
'建立连接
Set g_Conn = New ADODB.Connection
strCnn = "Provider = SQLOLEDB; Data Source = ServerName ;User ID = UserName;" & _
"Initial catalog = DBName ;Password = UserPwd; "
g_Conn.Open strCnn
而把上面与数据库的连接代码去掉，运行就会报错“连接无法用于执行此操作。在此上下文中它可能已经被关闭或无效。错误的是带××的那行，请大家帮忙，谢谢：）
问题点数：20、回复次数：30Top
1 楼hmyang（阳阳）回复于 2006-03-09 13:56:48 得分 0
没人知道吗？：（Top
2 楼ZOU_SEAFARER（颓废程序员^_^）回复于 2006-03-09 14:13:14 得分 0
Set g_Conn = New ADODB.Connection
strCnn = "Provider = SQLOLEDB; Data Source = ServerName ;User ID = UserName;" & _
"Initial catalog = DBName ;Password = UserPwd; "
g_Conn.Open strCnn
放在那里都不应该错呀Top
3 楼feiyun0112（http://feiyun0112.cnblogs.com/）回复于 2006-03-09 14:13:41 得分 0
AddUser.Open "UserInfo", g_Conn , adOpenForwardOnly, adLockOptimisticTop
4 楼hmyang（阳阳）回复于 2006-03-09 14:19:25 得分 0
Set AddUser = New ADODB.Recordset
AddUser.Open "UserInfo", g_Conn, adOpenForwardOnly, adLockOptimistic
这句我也有的啊，不过错误好像就是在这一行Top
5 楼hmyang（阳阳）回复于 2006-03-09 14:21:06 得分 0
是不是
Set g_Conn = New ADODB.Connection
strCnn = "Provider = SQLOLEDB; Data Source = ServerName ;User ID = UserName;" & _
"Initial catalog = DBName ;Password = UserPwd; "
g_Conn.Open strCnn
要放在一个函数里面？直接放在模块里面可以吗？我对模块的概念还不是最清楚Top
6 楼Cooly（☆不做开发很久了......☆）回复于 2006-03-09 14:29:08 得分 0
Dim g_Conn As ADODB.Connection '把Public声明改用Dim试试看
Set g_Conn = New ADODB.Connection
strCnn = "Provider = SQLOLEDB; Data Source = ServerName ;User ID = UserName;" & _
"Initial catalog = DBName ;Password = UserPwd; "
g_Conn.Open strCnn
Top
7 楼hmyang（阳阳）回复于 2006-03-09 14:32:51 得分 0
还是不行，我估计只是一个很简单的原则问题卡住了。。。。Top
8 楼Cooly（☆不做开发很久了......☆）回复于 2006-03-09 14:35:43 得分 0
'添加一个Module
Dim g_Conn As ADODB.Connection '把Public声明改用Dim试试看
Sub Main()
Set g_Conn = New ADODB.Connection
strCnn = "Provider = SQLOLEDB; Data Source = ServerName ;User ID = UserName;" & _
"Initial catalog = DBName ;Password = UserPwd; "
g_Conn.Open strCnn
End Sub
'----下面是在Form中使用
Private Sub cmdOK_Click()
'注册新用户
Dim AddUser As New ADODB.Recordset
Dim SqlStr As String
Dim DBstr As String
Dim UsrName As String
Dim UsrPwd As String
Dim strCnn As String
'与表连接
Set AddUser = New ADODB.Recordset
AddUser.CursorType = adOpenKeyset
AddUser.LockType = adLockOptimistic
AddUser.Open "UserInfo", strCnn, adOpenForwardOnly, adLockOptimistic
End Sub
Top
9 楼Cooly（☆不做开发很久了......☆）回复于 2006-03-09 14:37:25 得分 0
我感觉你的错误是出在新声明的Connection对象名为strCnn,而你原来使用的是cnn1
因此出错. 检查一下看看 .Top
10 楼hmyang（阳阳）回复于 2006-03-09 14:50:15 得分 0
AddUser.Open "UserInfo", strCnn, adOpenForwardOnly, adLockOptimistic
End Sub
第二个参数为strCnn？Top
11 楼Cooly（☆不做开发很久了......☆）回复于 2006-03-09 14:51:39 得分 0
对,这个是你打开的连接对象名称.Top
12 楼Cooly（☆不做开发很久了......☆）回复于 2006-03-09 14:53:30 得分 0
sorry,没看清楚
你的Connection对象名是g_ConnTop
13 楼Cooly（☆不做开发很久了......☆）回复于 2006-03-09 14:54:12 得分 0
不好意思,刚才看错了.
应该是这样
AddUser.Open "UserInfo", g_Conn, adOpenForwardOnly, adLockOptimisticTop
14 楼Cooly（☆不做开发很久了......☆）回复于 2006-03-09 14:54:59 得分 0
很久没写代码了...唉.Top
15 楼hmyang（阳阳）回复于 2006-03-09 14:58:00 得分 0
我觉得g_Conn才是连接对象啊？而且设为全局可以让其它地方都用
strCnn是一个string，用来存储与数据库的连接方式，用户名，密码等信息
而且strCnn并不是全局的，只在模块中定义过，那FORM中怎么使用它呢？Top
16 楼hmyang（阳阳）回复于 2006-03-09 14:59:25 得分 0
哦，我也这么想，不过还是不行，我要晕了：（
今天才接触ADO，来了个下马威Top
17 楼Cooly（☆不做开发很久了......☆）回复于 2006-03-09 15:04:25 得分 0
你在模块中添加以下代码
Dim g_Conn As ADODB.Connection
Sub Main()
Set g_Conn = New ADODB.Connection
strCnn = "Provider = SQLOLEDB; Data Source = ServerName ;User ID = UserName;" & _
"Initial catalog = DBName ;Password = UserPwd; "
g_Conn.Open strCnn
End Sub
然后修改工程属性,将开始对象设定为 Main()Top
18 楼Cooly（☆不做开发很久了......☆）回复于 2006-03-09 15:13:39 得分 0
你的strCnn有问题
strCnn = "Provider = SQLOLEDB; Data Source = ServerName ;User ID = UserName;" & _
"Initial catalog = DBName ;Password = UserPwd; "
........ServerName, UserName, DBName, UserPwd ??
用这句
strCnn = "Provider = SQLOLEDB; Data Source = localhost ;User ID = sa; & _
Initial catalog = wastebook ;Password = ;"
Top
19 楼hmyang（阳阳）回复于 2006-03-09 15:20:13 得分 0
Dim ServerName As String, DBName As String, UserName As String, UserPwd As String
Dim strCnn As String
'数据库相关信息
ServerName = "localhost"
DBName = "wastebook"
UserName = "sa"
UserPwd = " "
这是我定义的。
用你前面的办法试了一下，改成从sub main开始运行，不过VB就没有响应了，我导出成exe文件，运行等了一会儿然后跳出来错误信息“运行时错误，【DBNETLIB】connectionOpen（connect（））】SQL Server不存在或拒绝访问Top
20 楼Cooly（☆不做开发很久了......☆）回复于 2006-03-09 15:25:26 得分 0
.....
strCnn = "Provider = SQLOLEDB; Data Source = " & ServerName & ";User ID = " & UserName & ";Initial catalog = " & DBName & ";Password = " & UserPwd & ";"
Top
21 楼Cooly（☆不做开发很久了......☆）回复于 2006-03-09 15:26:48 得分 0
你的strCnn组成不正确,ServerName, UserName, DBName, UserPwd 这四个都是变量,需要用上面那种方式连接才可以。Top
22 楼hmyang（阳阳）回复于 2006-03-09 15:31:07 得分 0
&这个符号在这里的作用是？。。。
好像还是不行Top
23 楼Cooly（☆不做开发很久了......☆）回复于 2006-03-09 15:35:28 得分 20
&是字符串连接符, 这样吧,你把工程文件打包发给我一份,我帮你看看.
johnnylill@126.comTop
24 楼hmyang（阳阳）回复于 2006-03-09 15:43:52 得分 0
好的，谢谢，实在是麻烦你了，稍等Top
25 楼hmyang（阳阳）回复于 2006-03-09 15:52:16 得分 0
发好了，谢谢Top
26 楼hmyang（阳阳）回复于 2006-03-09 19:30:12 得分 0
好了，真是太谢谢了：）Top
27 楼mccartney_xu（(一切都变了,还能回到从前吗?)）回复于 2006-03-10 09:45:58 得分 0
是不是因为你NEW了两次啊?!Top
28 楼Nowish（看我能忍耐多久）回复于 2006-03-10 11:54:48 得分 0
Public g_Conn As new ADODB.Connection
'Set g_Conn = New ADODB.Connection '不要这句
strCnn = "Provider = SQLOLEDB; Data Source = ServerName ;User ID = UserName;" & _
"Initial catalog = DBName ;Password = UserPwd; "
g_Conn.Open strCnn
Top
29 楼boblaile（爱在13月32）回复于 2006-03-10 12:49:26 得分 0
cnn1 .close
是不是没有关闭连接
以后的连接就出错了Top
30 楼bbbbcccc（）回复于 2006-03-10 16:46:10 得分 0
http://valenhua.go3.icpcn.com/Top
