VB连接ORACLE一次成功二次报错

[popMod]

我用的是ADO访问oracle远程数据库，本机不装客户端，但装了一个驱动包。
第一次连接成功，并且可以查询，但是关闭连接之后再连接就不行了。重新软件又可以连了。。。
下面是代码 求高人指点

Option Explicit

Private Const gsOracleDBName = "ebpdm"
Private Const gsOracleHost = "(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.8.78)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = iman) ))"
Private Const gsOracleDBUserName = "EB_PDM_USER"
Private Const gsOracleDBPW = "EB_PDM_USER"
Private Const gsOracleTableName = " kseciman.ksec_buy_view "
Private Const gsOracleFlagTableName = "EB_PDM"
Private Const gsTableMaterialFiled = "SPECIFY like "


Private Sub Command1_Click()
  Dim Conn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim connStr As String
  Dim sqlStr As String
  Dim sqlString As String
  Dim i As Long
  
  On Error GoTo errorhanlder
  
  Set Conn = New ADODB.Connection
  Conn.CursorLocation = adUseClient
  connStr = "Provider=MSDAORA.1;Password=" & gsOracleDBPW & ";User ID=" _
            & gsOracleDBUserName & ";Data Source=" & gsOracleHost & ";Persist Security Info=True"
  Conn.Open connStr
  MsgBox "connection successful"
  sqlString = "select * from" & gsOracleTableName & "where rownum=1 "
  Set rs = New ADODB.Recordset
  rs.Open sqlString, Conn, 1, 1
  If rs.RecordCount > 0 Then
    MsgBox "SQL successful"
  End If
  If rs.State <> adStateClosed Then rs.Close
  Call rs.Close
  If Conn.State <> adStateClosed Then Conn.Close
  Call Conn.Close
  Set rs = Nothing
  Set Conn = Nothing
  
  Exit Sub
errorhanlder:
  MsgBox Err.Description
End Sub

[popMod]

中午大家都午休去了哇。。。
在线等 急啊

[帝企鹅已被占用]

哪里出错？
重新 = 重启？

[simon__sun]

没ORACLE环境哦，看起好像也没啥问题只是 Call rs.Close Call Conn.Close 这2个语句应该可以不要的吧！ 因为上面的条件判断会自动关闭，rs 和conn

[饮水需思源]

'打开之前先关闭一下：

if conn.state<>adstateclosed then conn.close
Conn.Open connStr

if rs.state<>adstateclosed then rs.close
rs.open ...

[simon__sun]

看一下这里http://hi.baidu.com/379415273/blog/item/a8f778faf31f1d62024f5695.html
是否有帮助

[孤独剑_LPZ]

[Quote=引用 4 楼 leftie 的回复:]
VB code'打开之前先关闭一下：
'打开之前先关闭一下：
if conn.state<>adstateclosed then conn.close
Conn.Open connStr
if rs.state<>adstateclosed then rs.close
rs.open ...
[/Quote]
这样应该就可以了

[jhone99]

貌似lz的代码里存在错误语句，如3楼所说，
象4楼这么做可能可以解决问题，但是lz的代码错误问题依然存在，
建议lz先把On Error GoTo errorhanlder注释掉，
等代码调试没有问题后，再把On Error GoTo errorhanlder的注释去掉

[ACMAIN_CHM]

把ON ERROR 去掉，然后贴出你的错误语句和提示。

[苏格兰之风]

不知道

[salior2006]

应是打开表之后没有进行关闭。又再次打开。加个状态检测就OK了。

[popMod]

Option Explicit

Private Const gsOracleHost = "(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.8)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = AUCOTEC.Test) ))"
Private Const gsOracleDBUserName = "SYSTEM"
Private Const gsOracleDBPW = "MANAGER"
Private Const gsOracleTableName = " system.PDM "

Private Sub Command2_Click()
  Dim Conn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim connStr As String
  Dim sqlStr As String
  Dim sqlString As String
  Dim i As Long
  
  On Error GoTo errorhanlder
  
  Set Conn = New ADODB.Connection
  Conn.CursorLocation = adUseClient
  connStr = "Provider=MSDAORA.1;Password=" & gsOracleDBPW & ";User ID=" _
            & gsOracleDBUserName & ";Data Source=" & gsOracleHost & ";Persist Security Info=True"
  If Conn.State <> adStateClosed Then Conn.Close
  Conn.Open connStr
  MsgBox "connect successful"
  sqlString = "select * from" & gsOracleTableName & "where rownum=1 "
  Set rs = New ADODB.Recordset
  If rs.State <> adStateClosed Then rs.Close
  rs.Open sqlString, Conn, 1, 1
  If rs.RecordCount > 0 Then
    MsgBox "SQL successful"
  End If
  If rs.State <> adStateClosed Then rs.Close
  If Conn.State <> adStateClosed Then Conn.Close
  Set rs = Nothing
  Set Conn = Nothing
  
  Exit Sub
errorhanlder:
  MsgBox Err.Description
End Sub

运行环境为：XP SP3没有装Oracel客户端，但通过注册表的方式安装了一个驱动包。
驱动包的说明：
1、拷贝文件到c:\oracle_client（也可以是别的路径）
2、导入注册表文件（在导入之前，确认里面的路径和第一步拷贝的路径一致）
3、在环境变量中添加 c:\oracle_client\bin （该路径也要和第一步路径一致）
4、reboot

我在以上环境中用该代码连接Oracle8g的服务器是没问题的，可以反复多次连接。
但是访问11g的服务器就出现了只能连接一次的问题，第2次连接就报错：Error while trying to retrieve text for error ORA-12638

PS:元旦没有及时结贴，很不好意思。本周一定结贴，谢谢大家了。

[guyehanxinlei]

使用数据库连接时需要注意其状态。如果是打开状态时先将其关闭掉。

[nettt]

这么麻烦呀 看看这个行不行
Option Explicit

Public MES_DBCN As ADODB.Connection

Public re As ADODB.Recordset

'DB connect string
Public Const MES_DBConnStr = "Provider=MSDAORA;Password=mes;" _
+ "Persist Security Info=True;User ID=mes;Data Source=xe"

Public mesConTrans As Boolean ' Transaction


Public Function MES_DBConnect() As Boolean
On Error GoTo error_handling

Set MES_DBCN = New ADODB.Connection
Set re = New ADODB.Recordset
Set service.MesConEvent = MES_DBCN
mesConTrans = False

MES_DBCN.CursorLocation = adUseClient
MES_DBCN.Open MES_DBConnStr
MES_DBConnect = True
Exit Function

error_handling:
MES_DBConnect = False
If Not MES_DBCN Is Nothing Then Set MES_DBCN = Nothing
End Function

Public Function MES_DBDisConnect() As Boolean
On Error GoTo error_handling

MES_DBCN.Close
Set MES_DBCN = Nothing
MES_DBDisConnect = True
Exit Function
error_handling:
MES_DBDisConnect = False
If Not MES_DBCN Is Nothing Then Set MES_DBCN = Nothing
End Function