"select * from User where UserName=" & sUserName & " and Pwd=" & sPwd
没有任何问题亚 附上我的测试程序: Public Function GetRecordSet(cnnConnection As ADODB.Connection, sQry As String) As ADODB.Recordset Dim rsCustomers As ADODB.Recordset Set rsCustomers = New ADODB.Recordset
Set GetRecordSet = rsCustomers End FunctionPrivate Sub Command1_Click() Dim adoCnn As New ADODB.Connection adoCnn.Open GetCnnStrBySQLServer("(local)", "sa", "", "ZJS_ZB") Call GetRecordSet(adoCnn, "select * from 珠宝表")
End Sub'======================================================================= ' 函数: GetCnnStrBySQLServer ' ' 功能: 得到 SQL SERVER 数据库的连接字符串 ' ' 入口: strServerName 服务名称 ' strUserName 用户名称 ' strPassWord 用户密码 ' strDBName 数据库名称 ' ' 返回: 连接字符串 ' ' Public Function GetCnnStrBySQLServer(ByVal strServerName As String, _ ByVal strUserName As String, _ ByVal strPassWord As String, _ ByVal strDBName As String) As String GetCnnStrBySQLServer = "Provider=SQLOLEDB.1" & _ ";Persist Security Info=True" & _ ";User ID=" & strUserName & _ ";Password=" & strPassWord & _ ";Initial Catalog=" & strDBName & _ ";Data Source=" & strServerName
End Function '======================================================================= 还有提一下,我觉得你这种写法是不是太耗资源了,完全没有必要浪费了一个CONNECTION对象和一个RECORDSET对象,还有就是建议开REDORDSET对象时一句话搞定,不要这么多句话,搞得人发晕。可读性下降
Public Function GetRecordSet(cnnConnection As ADODB.Connection, sQry As String) As ADODB.Recordset Dim rsCustomers As ADODB.Recordset Set rsCustomers = New ADODB.Recordset rsCustomers.CursorType = adOpenKeyset rsCustomers.LockType = adLockOptimistic rsCustomers.CursorLocation = adUseClient rsCustomers.Source = sQry cnnConnection.Open Set rsCustomers.ActiveConnection = cnnConnection rsCustomers.Open
liuli263(一剑飘香)调出来了? 注意,他的“Dim adoCnn As New ADODB.Connection”是在外面。
飘香:我也是定义在窗体的按扭下的是cnnUser
to:fayeada(fayeada) 调用函数时不要吧cnnUser打开。 private button1_click() dim cnnUser as new ADODB.connection dim rs as new ADODB.recordset dim strTe as string cnnUser.connectionstring="provider=........." strTe="Select ....." set rs=GetRecordSet(cn,strTe) end private
你的CONNECTION对象都不打开,怎么用 对不起,刚看了一下后来的帖子,在里面打开的 我又测试了一下,OK 还是可以的 Public Function GetRecordSet(cnnConnection As ADODB.Connection, sQry As String) As ADODB.Recordset Dim rsCustomers As ADODB.Recordset Set rsCustomers = New ADODB.Recordset
Set GetRecordSet = rsCustomers End FunctionPrivate Sub Command1_Click() Dim adoCnn As New ADODB.Connection adoCnn.ConnectionString = GetCnnStrBySQLServer("(local)", "sa", "", "ZJS_ZB") Call GetRecordSet(adoCnn, "select * from 珠宝表")
End Sub
既然只是传个连接字符串没有必要用到CONNECTION对象8,好像小题大做了
Private Sub cmdOK_Click() Dim sQuery As String Dim sUserName As String Dim sPwd As String Dim itsConnectionString As String Dim rsUser As ADODB.Recordset Dim cnnUser As ADODB.ConnectionsUserName = Me.txtUserName sPwd = Me.txtPwd Set cnnUser = New ADODB.Connection Set rsUser = New ADODB.RecordsetsQuery = "select * from User where UserName=" & sUserName & " and Pwd=" & sPwd itsConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\finance\finance.mdb" cnnUser.ConnectionString = itsConnectionString 'cnnUser.OpenSet rsUser = GetRecordSet(cnnUser, sQuery)If rsUser.RecordCount = 0 Then MsgBox ("输入错误的用户名或密码") Else Unload Me End If End Sub上面调用的代码
set cnnConnection= new ADODB.Connection
cnnConnection 是传过来的参数。NEW完了,传的参数就没了呀!~
附上我的测试程序:
Public Function GetRecordSet(cnnConnection As ADODB.Connection, sQry As String) As ADODB.Recordset
Dim rsCustomers As ADODB.Recordset
Set rsCustomers = New ADODB.Recordset
rsCustomers.CursorType = adOpenKeyset
rsCustomers.LockType = adLockOptimistic
rsCustomers.CursorLocation = adUseClient
rsCustomers.Source = sQry
Set rsCustomers.ActiveConnection = cnnConnection
rsCustomers.Open
Set GetRecordSet = rsCustomers
End FunctionPrivate Sub Command1_Click()
Dim adoCnn As New ADODB.Connection
adoCnn.Open GetCnnStrBySQLServer("(local)", "sa", "", "ZJS_ZB")
Call GetRecordSet(adoCnn, "select * from 珠宝表")
End Sub'=======================================================================
' 函数: GetCnnStrBySQLServer
'
' 功能: 得到 SQL SERVER 数据库的连接字符串
'
' 入口: strServerName 服务名称
' strUserName 用户名称
' strPassWord 用户密码
' strDBName 数据库名称
'
' 返回: 连接字符串
'
'
Public Function GetCnnStrBySQLServer(ByVal strServerName As String, _
ByVal strUserName As String, _
ByVal strPassWord As String, _
ByVal strDBName As String) As String GetCnnStrBySQLServer = "Provider=SQLOLEDB.1" & _
";Persist Security Info=True" & _
";User ID=" & strUserName & _
";Password=" & strPassWord & _
";Initial Catalog=" & strDBName & _
";Data Source=" & strServerName
End Function
'=======================================================================
还有提一下,我觉得你这种写法是不是太耗资源了,完全没有必要浪费了一个CONNECTION对象和一个RECORDSET对象,还有就是建议开REDORDSET对象时一句话搞定,不要这么多句话,搞得人发晕。可读性下降
Dim rsCustomers As ADODB.Recordset
Set rsCustomers = New ADODB.Recordset
rsCustomers.CursorType = adOpenKeyset
rsCustomers.LockType = adLockOptimistic
rsCustomers.CursorLocation = adUseClient
rsCustomers.Source = sQry
cnnConnection.Open
Set rsCustomers.ActiveConnection = cnnConnection
rsCustomers.Open
Set GetRecordSet = rsCustomers
End Function
注意,他的“Dim adoCnn As New ADODB.Connection”是在外面。
调用函数时不要吧cnnUser打开。
private button1_click()
dim cnnUser as new ADODB.connection
dim rs as new ADODB.recordset
dim strTe as string
cnnUser.connectionstring="provider=........."
strTe="Select ....."
set rs=GetRecordSet(cn,strTe)
end private
对不起,刚看了一下后来的帖子,在里面打开的
我又测试了一下,OK
还是可以的
Public Function GetRecordSet(cnnConnection As ADODB.Connection, sQry As String) As ADODB.Recordset
Dim rsCustomers As ADODB.Recordset
Set rsCustomers = New ADODB.Recordset
rsCustomers.CursorType = adOpenKeyset
rsCustomers.LockType = adLockOptimistic
rsCustomers.CursorLocation = adUseClient
rsCustomers.Source = sQry
cnnConnection.Open
Set rsCustomers.ActiveConnection = cnnConnection
rsCustomers.Open
Set GetRecordSet = rsCustomers
End FunctionPrivate Sub Command1_Click()
Dim adoCnn As New ADODB.Connection
adoCnn.ConnectionString = GetCnnStrBySQLServer("(local)", "sa", "", "ZJS_ZB")
Call GetRecordSet(adoCnn, "select * from 珠宝表")
End Sub
Dim sQuery As String
Dim sUserName As String
Dim sPwd As String
Dim itsConnectionString As String
Dim rsUser As ADODB.Recordset
Dim cnnUser As ADODB.ConnectionsUserName = Me.txtUserName
sPwd = Me.txtPwd
Set cnnUser = New ADODB.Connection
Set rsUser = New ADODB.RecordsetsQuery = "select * from User where UserName=" & sUserName & " and Pwd=" & sPwd
itsConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\finance\finance.mdb"
cnnUser.ConnectionString = itsConnectionString
'cnnUser.OpenSet rsUser = GetRecordSet(cnnUser, sQuery)If rsUser.RecordCount = 0 Then
MsgBox ("输入错误的用户名或密码")
Else
Unload Me
End If
End Sub上面调用的代码
' 函数: GetCnnStrBySQLServer
'
' 功能: 得到 SQL SERVER 数据库的连接字符串
'
' 入口: strServerName 服务名称
' strUserName 用户名称
' strPassWord 用户密码
' strDBName 数据库名称
'
' 返回: 连接字符串
'
'
Public Function GetCnnStrBySQLServer(ByVal strServerName As String, _
ByVal strUserName As String, _
ByVal strPassWord As String, _
ByVal strDBName As String) As String GetCnnStrBySQLServer = "Provider=SQLOLEDB.1" & _
";Persist Security Info=True" & _
";User ID=" & strUserName & _
";Password=" & strPassWord & _
";Initial Catalog=" & strDBName & _
";Data Source=" & strServerName
End Function
'=======================================================================
'=======================================================================
' 函数: GetCnnStrByOracle
'
' 功能: 得到 SOracle 数据库的连接字符串
'
' 入口: strServerName 服务名称
' strUserName 用户名称
' strPassWord 用户密码
'
' 返回: 连接字符串
'
'
Public Function GetCnnStrByOracle(ByVal strServerName As String, _
ByVal strUserName As String, _
ByVal strPassWord As String) As String GetCnnStrByOracle = "Provider=SQLOLEDB.1" & _
";User ID=" & strUserName & _
";Password=" & strPassWord & _
";Data Source=" & strServerName
End Function
'======================================================================='=======================================================================
' 函数: GetCnnStrByAccess
'
' 功能: 得到 Access 数据库的连接字符串
'
' 入口: strServerName 服务名称
' strUserName 用户名称
' strPassWord 用户密码
'
' 返回: 连接字符串
'
'
Public Function GetCnnStrByAccess(ByVal strServerName As String, _
ByVal strUserName As String, _
ByVal strPassWord As String) As String GetCnnStrByAccess = "Provider=Microsoft.Jet.OLEDB.4.0" & _
";Persist Security Info=True" & _
";User ID=" & strUserName & _
";Password=" & strPassWord & _
";Data Source=" & strServerName
End Function
'=======================================================================