Hi All,

In my ASP Project, I have Two Database Connections for oracle 9i... One is Global file Connection (DSN-Less or Direct Provider) at Application Start and One more connection I used for temporary purpose that was DSN Connection. Means Connection through System DSN.  

At finally, I want to remove the DSN Connection and try to use Global file Connection through out the Project.

I tried to change connection in corresponding page; it gave some Oracle Error massage.
Like:
OraOLEDB (0x80040E51)
Provider cannot derive parameter information and SetParameterInfo has not been called.
/admin/Report2Data.asp, line 393

But I couldn’t change any things except the Database Connection.

Please find the Code below and please help me regarding the same.  


My code:

My Global File Application Start:

'Create ADO Objects
Set objConn = Server.CreateObject("ADODB.Connection")

strConns = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User
ID=Hyattuser;Password=Hyattuser;Data Source=hyatt"

'Open the Database connection
objConn.Open strConns

'Set Connection object in Application Level
Set Application("DBCON") = objConn


Report2Data.asp File

Before Change:

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.CursorLocation = adUseClient

'System DSN connection
oConn.Open "DSN=hyatt;Uid=hyattuser;Pwd=hyattuser"

'Creates Command
Dim objCmd
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = oConn

'Command for Oracle
With objCmd
.CommandText = "{call GenReport2(?,?,?)}"
.CommandType = adCmdText
.Parameters(0).Value = loc_year1  
.Parameters(1).Value = loc_month1
End With

'creates a recordset object.
Set oRsDetails = Server.CreateObject("ADODB.Recordset")
oRsDetails.CursorType = adOpenStatic
'oRsDetails.LockType = adLockOptimistic
Set oRsDetails.Source = objCmd
Set oRsDetails = objCmd.Execute()


After Change:

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.CursorLocation = adUseClient

'Connection object from Global file
Set oConn = Application("DBCON")

'Creates Command
Dim objCmd
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = oConn

'Command for Oracle
With objCmd
.CommandText = "{call GenReport2(?,?,?)}"
.CommandType = adCmdText
.Parameters(0).Value = loc_year1      ' Error Line 393
.Parameters(1).Value = loc_month1
End With

'creates a recordset object.
Set oRsDetails = Server.CreateObject("ADODB.Recordset")
oRsDetails.CursorType = adOpenStatic
'oRsDetails.LockType = adLockOptimistic
Set oRsDetails.Source = objCmd
Set oRsDetails = objCmd.Execute()

Error ASP Page:

OraOLEDB (0x80040E51)
Provider cannot derive parameter information and SetParameterInfo has not been
called.
/admin/Report2Data.asp, line 393


Oracle Store Procedure:

procedure GenReport2
(
Year1 number,
Month1 number,
cur_Report2 OUT pckghyatt.ref_cursor
)
is
ShisetsuID nvarchar2(5);
ShisetsuNM nvarchar2(40);
NowCount number;

-
-
-Some Code
-

/*Out put the result*/
open cur_Report2 for Select * from RepTable2 Order By
RTShisetsuID;
End;

There is no problem with Store Procedure, This is for your reference.  

please help me regarding the same.