dim ac_tmp as new adodb.connection With ac_tmp '打开数据库连接 If .State = adStateOpen Then .Close .ConnectionString = "driver=SQL Server;server=" & 服务器名称或IP地址 & ";uid=sa;pwd=;database=" & 数据库名称 .CommandTimeout = 120 .Open End With
Initial Catalog=wzgl'数据库名称 Data Source=127.0.0.1'数据库服务器地址或名称
to _131_(\*咕咚*/) 我用我自已的的语句后是这样的错误。 实时错误 ‘3704’ Operation is not allowed when the object is closed. 用您的后也是这个错误
to maskzha(★) 我试了,打开表时还是这个错误 实时错误 ‘3704’ Operation is not allowed when the object is closed. 您能把你的系统和调试方法详细说一下吗?
'odb.CursorLocation = adUseClient 把上面着一句现注释掉,在测试! 我习惯的作法: dim cn as new adodb.connection dim conn as string conn="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=wzgl;Data Source=127.0.0.1" cn.open conn还有为什么你一旦连接到数据库以后马上就关闭呢?
to wtcooler(中文本) 用您的也连不上呀,打开表时还是那个错误 您能写的详细点吗?(最好把你的系统配置也写出)
我连接上有成功经验, 我的连接如下: Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim sql As String
另一种ADO连接方法:'********************************************************************** ' 过程名称:OpenADODatabase ' 功能描述:打开全局的数据库连接 ' 参数说明:(无) ' 返 回 值:(无) '********************************************************************** Public Sub OpenADODatabase() Dim strConnStr As String
Set gDBConnection = New ADODB.Connection gDBConnection.Open strConnStr
Exit Sub
ErrHandle: ' Call msgbox("请检查输入是否正确,再重新启动该程序") End Sub 附:Global gdbServer As String Global gdbDBName As String Global gdbUserID As String Global gdbPassWD As String gdbServer = "127.0.0.1" gdbDBName = "name" gdbUserID = "sa" gdbPassWD = ""
下面是模块中所有的代码,请各位帮找一下,哪错了? 这是主窗体调用的代码 Private Sub Form_Load() Dim rs1 As New ADODB.Recordset Dim SQL1 As String Combo1.Clear Text1.Text = "" SQL1 = "select bh,xm,ma from czy where qx=3" Set rs1 = GetRsBySQL(SQL1) Do While Not rs1.EOF Combo1.AddItem Trim(rs1!bh) rs1.MoveNext Loop rs1.Close Set rs1 = Nothing GuestName = "Ver 3.0" frmain.Caption = GuestName
End Sub这是模块的代码 Option Explicit
Public DBServerName As String '数据库服务器 Public DBUserName As String '数据库服务器登录名 Public DBPassword As String '数据库服务器登录密码 Public DBName As String '数据库名称 Public SQL1 As String Public RS2 As New ADODB.Recordset Public My_jzsj As String Public GuestName As String
Public UserID As String Public UserName As String Public UserMa As String
Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
'************************************************************************************* '************************************************************************************* '打开数据库对象,返回ADODB.Connection对象,SQL Server '************************************************************************************* Public Function OpenDB() As ADODB.Connection On Error GoTo ErrOpenDB Dim odb As ADODB.Connection Dim odbcnn As String Set odb = New ADODB.Connection 'odb.CursorLocation = adUseClient DBServerName = "" DBUserName = "" DBPassword = "" DBName = ""
odb.Open odbcnn Set OpenDB = odb Set odb = Nothing Exit Function ErrOpenDB: Set OpenDB = Nothing End Function'************************************************************************************* '************************************************************************************* '根据SQL语句,打开数据库表对象,返回ADODB.RecordSet对象集 '************************************************************************************* Public Function GetRsBySQL(ByVal strsql As String) As ADODB.Recordset On Error GoTo ErrGetRsBySQL Dim odb As ADODB.Connection Dim rs As ADODB.Recordset
Set odb = OpenDB Set rs = New ADODB.Recordset rs.CursorLocation = adUseClient rs.Open strsql, odb, adOpenDynamic, adLockOptimistic Set GetRsBySQL = rs Set rs = Nothing Exit Function ErrGetRsBySQL: Set GetRsBySQL = Nothing End Function
PSS ID Number: Q235340Article Last Modified on 08-23-2001 -------------------------------------------------------------------------------- The information in this article applies to: ActiveX Data Objects (ADO) 2.1, 2.1 SP1, 2.1 SP2, 2.5, 2.6, 2.7 Microsoft Visual Basic Learning Edition for Windows 5.0, 6.0 Microsoft Visual Basic Professional Edition for Windows 5.0, 6.0-------------------------------------------------------------------------------- Symptoms When you try to create a ADO recordset based on a #Temp table created within a stored procedure called by ADO using the SQLOLEDB provider, you may encounter one of the following error messages: 3704 - The operation requested by the application is not allowed if the object is closed. -or- Run-time error '3704': Operation is not allowed when the object is closed. Cause The SQL Server OLEDB provider (SQLOLEDB) has a new behavior designed to provide more accurate information to the caller about what happened in the procedure. Each SQL statement within a stored procedure returns a "result," either a count of rows affected, or a resultset. The SQL Server ODBC provider (MSDASQL) does not provide information about the results of individual SQL statements within a stored procedure. The only result that comes back from a stored procedure execution is the result of the SELECT statement if it has one. This is why the problem does not manifest with MSDASQL. Resolution To get SQLOLEDB to behave as the SQL ODBC driver did, you must use SET NOCOUNT ON. Put SET NOCOUNT ON in the stored procedure or in the SQL statement used with ADO as shown in the code sample below. Alternatively, you could use MSDASQL. Status This behavior is by design. More Information To reproduce the error: Create a new Visual Basic Standard EXE project and paste the following code in the General Declarations section of a Form. Set a reference to Microsoft ActiveX Data Objects Library. Change the connection string as necessary for your environment: Private Sub Form_Load()Dim adoCn As adoDb.Connection Dim adoRs As adoDb.Recordset Dim adoCm As adoDb.Command Dim strSQL As StringSet adoCn = New adoDb.Connection With adoCn .ConnectionString = "Provider=SQLOLEDB;Server=<ServerName>;" & _ "Database=Pubs;Uid=sa;Pwd=" '.ConnectionString = "Provider=MSDASQL;Driver={SQL Server};Server=<ServerName>;" & _ "Database=Pubs;Uid=sa;Pwd=" .CursorLocation = adUseServer .Open End WithSet adoCm = New adoDb.Command With adoCm Set .ActiveConnection = adoCn .CommandType = adCmdText .CommandText = "if exists (select * from sysobjects " & _ "where id = object_id('dbo.spADOTempTest') " & _ "and sysstat & 0xf = 4) " & _ "drop procedure dbo.spADOTempTest" .Execute .CommandText = "Create procedure spADOTempTest " & _ "as CREATE TABLE #test (field1 VARCHAR(20) NULL) " & _ "INSERT INTO #test(field1) SELECT fname FROM Employee " & _ "SELECT * FROM #test go" .Execute .CommandType = adCmdStoredProc .CommandText = "spADOTempTest" 'the default for Prepared statements is false. '.Prepared = False End With
Set adoRs = New adoDb.Recordset With adoRs Set .ActiveConnection = adoCn .LockType = adLockOptimistic .CursorLocation = adUseServer .CursorType = adOpenForwardOnly 'Uncomment the next line with the SQLOLEDB provider to fix the error. '.Open "SET NOCOUNT ON" End With adoRs.Open adoCm, , , , adCmdStoredProcMsgBox "Recordset returned...", vbOKOnlyWhile Not adoRs.EOF Debug.Print adoRs.Fields(0).Value adoRs.MoveNext WendadoCn.Close Set adoCn = Nothing Set adoRs = NothingEnd Sub
实时错误 '-2147467295 (800004005)' [Microsoft][ODBC SQL Server Driver] [Shared Memory]SQL server does not exist or access denied
With ac_tmp '打开数据库连接
If .State = adStateOpen Then .Close
.ConnectionString = "driver=SQL Server;server=" & 服务器名称或IP地址 & ";uid=sa;pwd=;database=" & 数据库名称
.CommandTimeout = 120
.Open
End With
Data Source=127.0.0.1'数据库服务器地址或名称
我用我自已的的语句后是这样的错误。
实时错误 ‘3704’
Operation is not allowed when the object is closed.
用您的后也是这个错误
调试通过
.ConnectionString="Provider=SQLOLEDB;" & _
"User ID=" & USERID & ";" & _
"Password=" & 登录口令 & ";" & _
"Data Source=" & 服务器名 & ";" & _
"Initial Catalog=" & 数据库名
我试了,打开表时还是这个错误
实时错误 ‘3704’
Operation is not allowed when the object is closed.
您能把你的系统和调试方法详细说一下吗?
把上面着一句现注释掉,在测试!
我习惯的作法:
dim cn as new adodb.connection
dim conn as string
conn="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=wzgl;Data Source=127.0.0.1"
cn.open conn还有为什么你一旦连接到数据库以后马上就关闭呢?
用您的也连不上呀,打开表时还是那个错误
您能写的详细点吗?(最好把你的系统配置也写出)
我的连接如下:
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
cn.Open "driver={SQL Server};server=127.0.0.1;uid=sa;pwd=;database=dbname"
Set rs = New ADODB.Recordset
sql = "select * from yourtable"
rs.Open sql, cn, adOpenStatic, adLockBatchOptimistic
MsgBox rs.Fields(0).Value
在引用中加ADO2.6
cn.Open "driver={SQL Server};server=127.0.0.1;uid=sa;pwd=;database=dbname"
我是在本机上调试的,我的IP不是127.0.0.1,用您的方法行不通呀。
' 过程名称:OpenADODatabase
' 功能描述:打开全局的数据库连接
' 参数说明:(无)
' 返 回 值:(无)
'**********************************************************************
Public Sub OpenADODatabase()
Dim strConnStr As String
On Error GoTo ErrHandle
strConnStr = "Provider=SQLOLEDB.1;Password=" _
& gdbPassWD & ";Persist Security Info=True;User ID=" _
& gdbUserID & ";Initial Catalog=" _
& gdbDBName & ";Data Source=" _
& gdbServer
Set gDBConnection = New ADODB.Connection
gDBConnection.Open strConnStr
Exit Sub
ErrHandle:
' Call msgbox("请检查输入是否正确,再重新启动该程序")
End Sub
附:Global gdbServer As String
Global gdbDBName As String
Global gdbUserID As String
Global gdbPassWD As String
gdbServer = "127.0.0.1"
gdbDBName = "name"
gdbUserID = "sa"
gdbPassWD = ""
如果你是要连接别的PC,就改一下IP就可以啦
把IP换成你的要的IP 地址
这是主窗体调用的代码
Private Sub Form_Load()
Dim rs1 As New ADODB.Recordset
Dim SQL1 As String Combo1.Clear
Text1.Text = ""
SQL1 = "select bh,xm,ma from czy where qx=3"
Set rs1 = GetRsBySQL(SQL1)
Do While Not rs1.EOF
Combo1.AddItem Trim(rs1!bh)
rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing GuestName = "Ver 3.0"
frmain.Caption = GuestName
End Sub这是模块的代码
Option Explicit
Public DBServerName As String '数据库服务器
Public DBUserName As String '数据库服务器登录名
Public DBPassword As String '数据库服务器登录密码
Public DBName As String '数据库名称
Public SQL1 As String
Public RS2 As New ADODB.Recordset
Public My_jzsj As String
Public GuestName As String
Public UserID As String
Public UserName As String
Public UserMa As String
Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
'*************************************************************************************
'*************************************************************************************
'打开数据库对象,返回ADODB.Connection对象,SQL Server
'*************************************************************************************
Public Function OpenDB() As ADODB.Connection
On Error GoTo ErrOpenDB
Dim odb As ADODB.Connection
Dim odbcnn As String Set odb = New ADODB.Connection
'odb.CursorLocation = adUseClient
DBServerName = ""
DBUserName = ""
DBPassword = ""
DBName = ""
DBServerName = GetINIFile("DBInfo", "DBServerName")
DBUserName = GetINIFile("DBInfo", "DBUserName")
DBPassword = GetINIFile("DBInfo", "DBPassword")
DBName = GetINIFile("DBInfo", "DBName")
odbcnn = "driver={SQL Server};server=" & CStr(DBServerName)
odbcnn = odbcnn & ";uid=" & CStr(DBUserName) & ";pwd=" & CStr(DBPassword)
odbcnn = odbcnn & ";database=" & CStr(DBName)
'odb.ConnectionString = "Provider=SQLOLEDB;" & _
"User ID=" & "sa" & ";" & _
"Password=" & "" & ";" & _
"Data Source=" & "tang" & ";" & _
"Initial Catalog=" & "zsyfang"
'odb.Open
odb.Open odbcnn
Set OpenDB = odb
Set odb = Nothing
Exit Function
ErrOpenDB:
Set OpenDB = Nothing
End Function'*************************************************************************************
'*************************************************************************************
'根据SQL语句,打开数据库表对象,返回ADODB.RecordSet对象集
'*************************************************************************************
Public Function GetRsBySQL(ByVal strsql As String) As ADODB.Recordset
On Error GoTo ErrGetRsBySQL
Dim odb As ADODB.Connection
Dim rs As ADODB.Recordset
Set odb = OpenDB
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open strsql, odb, adOpenDynamic, adLockOptimistic
Set GetRsBySQL = rs
Set rs = Nothing
Exit Function
ErrGetRsBySQL:
Set GetRsBySQL = Nothing
End Function
--------------------------------------------------------------------------------
The information in this article applies to: ActiveX Data Objects (ADO) 2.1, 2.1 SP1, 2.1 SP2, 2.5, 2.6, 2.7
Microsoft Visual Basic Learning Edition for Windows 5.0, 6.0
Microsoft Visual Basic Professional Edition for Windows 5.0, 6.0--------------------------------------------------------------------------------
Symptoms
When you try to create a ADO recordset based on a #Temp table created within a stored procedure called by ADO using the SQLOLEDB provider, you may encounter one of the following error messages: 3704 - The operation requested by the application is not allowed if the object is closed.
-or-
Run-time error '3704': Operation is not allowed when the object is closed. Cause
The SQL Server OLEDB provider (SQLOLEDB) has a new behavior designed to provide more accurate information to the caller about what happened in the procedure. Each SQL statement within a stored procedure returns a "result," either a count of rows affected, or a resultset. The SQL Server ODBC provider (MSDASQL) does not provide information about the results of individual SQL statements within a stored procedure. The only result that comes back from a stored procedure execution is the result of the SELECT statement if it has one. This is why the problem does not manifest with MSDASQL. Resolution
To get SQLOLEDB to behave as the SQL ODBC driver did, you must use SET NOCOUNT ON. Put SET NOCOUNT ON in the stored procedure or in the SQL statement used with ADO as shown in the code sample below. Alternatively, you could use MSDASQL. Status
This behavior is by design. More Information
To reproduce the error: Create a new Visual Basic Standard EXE project and paste the following code in the General Declarations section of a Form.
Set a reference to Microsoft ActiveX Data Objects Library.
Change the connection string as necessary for your environment:
Private Sub Form_Load()Dim adoCn As adoDb.Connection
Dim adoRs As adoDb.Recordset
Dim adoCm As adoDb.Command
Dim strSQL As StringSet adoCn = New adoDb.Connection
With adoCn
.ConnectionString = "Provider=SQLOLEDB;Server=<ServerName>;" & _
"Database=Pubs;Uid=sa;Pwd="
'.ConnectionString = "Provider=MSDASQL;Driver={SQL Server};Server=<ServerName>;" & _
"Database=Pubs;Uid=sa;Pwd="
.CursorLocation = adUseServer
.Open
End WithSet adoCm = New adoDb.Command
With adoCm
Set .ActiveConnection = adoCn
.CommandType = adCmdText
.CommandText = "if exists (select * from sysobjects " & _
"where id = object_id('dbo.spADOTempTest') " & _
"and sysstat & 0xf = 4) " & _
"drop procedure dbo.spADOTempTest"
.Execute
.CommandText = "Create procedure spADOTempTest " & _
"as CREATE TABLE #test (field1 VARCHAR(20) NULL) " & _
"INSERT INTO #test(field1) SELECT fname FROM Employee " & _
"SELECT * FROM #test go"
.Execute
.CommandType = adCmdStoredProc
.CommandText = "spADOTempTest"
'the default for Prepared statements is false.
'.Prepared = False
End With
Set adoRs = New adoDb.Recordset
With adoRs
Set .ActiveConnection = adoCn
.LockType = adLockOptimistic
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
'Uncomment the next line with the SQLOLEDB provider to fix the error.
'.Open "SET NOCOUNT ON"
End With
adoRs.Open adoCm, , , , adCmdStoredProcMsgBox "Recordset returned...", vbOKOnlyWhile Not adoRs.EOF
Debug.Print adoRs.Fields(0).Value
adoRs.MoveNext
WendadoCn.Close
Set adoCn = Nothing
Set adoRs = NothingEnd Sub
[Microsoft][ODBC SQL Server Driver] [Shared Memory]SQL
server does not exist or access denied