This is the stored procedure,
Procedure RetrieveReservations(pProperty In Varchar2,
pResHeaderId In Varchar2,
pEmail In Varchar2,
pPasswd In Varchar2,
ResultSet In Out ResCursor);
This is the old .asp code (part of it)
objConn.ConnectionString = Application("BackEndConnection_ConnectionString")
objConn.OpenobjComm.ActiveConnection = objConn
objComm.CommandText = "internet_package.RetrieveReservations"
objComm.CommandType = adCmdStoredProcobjComm.Parameters.Append objComm.CreateParameter("pProperty", adVarChar , adParamInput, 8, Request.QueryString("propid"))
objComm.Parameters.Append objComm.CreateParameter("pResHeaderId", adVarChar, adParamInput, 32, Request.QueryString("confirmnum"))
objComm.Parameters.Append objComm.CreateParameter("pEmail", adVarChar, adParamInput, 64, sUserID)
objComm.Parameters.Append objComm.CreateParameter("pPasswd", adVarChar, adParamInput, 16, sPwd)
'objComm.Parameters.Append objComm.CreateParameter("pMsg", adVarChar, adParamInput, 8)
Set objRS = objComm.ExecuteWe have already use System.Data.OracleClient, System.Data.OleDB, Oracle.DataAccess.Client, and Oracle.DataAccess.Type
We are using Visual Studio 2003 (ASP.NEt-VB.NET), Oracle 8 or 9 (not sure with this version)I was able to correct the error but thers is a problem with the results of the stored procedures ... it does not return any values at all
here are my new codes ... we used ODP.NET version Release 9.2.0.7.0
which imports Oracle.DataAccess.Client and Oracle.DataAccess.Types
Imports Oracle.DataAccess
Dim odcmd As Oracle.DataAccess.Client.OracleCommand
Dim odda As Oracle.DataAccess.Client.OracleDataAdapter
Dim oddr As Oracle.DataAccess.Client.OracleDataReader
Dim odconn As Oracle.DataAccess.Client.OracleConnection
sUserID = Session("UserID")
sPwd = Session("Password")
Session("propid") = "314"
Session("confirmnum") = "314027819"
odconn = New Oracle.DataAccess.Client.OracleConnection(strOracleDataaccessString)
odconn.Open()
odcmd = New Oracle.DataAccess.Client.OracleCommand
odcmd.CommandText = "internet_package.RetrieveReservations"
odcmd.CommandType = CommandType.StoredProcedure
odcmd.Connection = odconn
odcmd.Parameters.Add("pProperty", Oracle.DataAccess.Client.OracleDbType.Varchar2, 8, Session("propid"), ParameterDirection.Input)
odcmd.Parameters.Add("pResHeaderId", Oracle.DataAccess.Client.OracleDbType.Varchar2, 32, Session("confirmnum"), ParameterDirection.Input)
odcmd.Parameters.Add("pEmail", Oracle.DataAccess.Client.OracleDbType.Varchar2, 64, sUserID, ParameterDirection.Input)
odcmd.Parameters.Add("pPasswd", Oracle.DataAccess.Client.OracleDbType.Varchar2, 16, sPwd, ParameterDirection.Input)
odcmd.Parameters.Add("ResultSet", Oracle.DataAccess.Client.OracleDbType.RefCursor, ParameterDirection.InputOutput)
odda = New Oracle.DataAccess.Client.OracleDataAdapter(odcmd)
Dim ds As New DataSet
Dim dt As New DataTable
odda.Fill(ds)
oddr = odcmd.ExecuteReader
If Not ds Is Nothing Then
If ds.Tables.Count > 0 Then
If ds.Tables(0).Rows.Count > 0 ThenElse
Me.dgrReservation.Visible = False
Me.lblList.Visible = True
Me.lblList.Text = "None"
End If
Else
Me.dgrReservation.Visible = False
Me.lblList.Visible = True
Me.lblList.Text = "None"
End If
Else
Me.dgrReservation.Visible = False
Me.lblList.Visible = True
Me.lblList.Text = "None"
End If
While oddr.Read
If oddr.FieldCount > 0 Then
oddr.GetOracleString(0)
oddr.GetString(0)
End If
End While
...both dataset and datatable return 0 rows
please advise how do I proceed? Can I convert the RefCursor to dataset or datatable?
![]() |
0 |
![]() |
Yes you can return the result set as a refcursor to the dataset from the data base tyhroigh the Stored procudre.
The following my previous post will help you out on refcursors usage in asp.net
http://forums.asp.net/thread/1532635.aspx
Cheers
Ram MCP
![]() |
0 |
![]() |
The link that you have provided here is for classic asp and java
http://www.oracle-base.com/articles/8i/UsingRefCursorsToReturnRecordsets.php.
What I am looking for is for the actual code in ASP.NET 1.1? Thanks in advance
![]() |
0 |
![]() |
Hi asiaenforcer, Yes,Its offcource conatins ASP/Java code but I gave you for Oracle Stored procedure which was there.Any how you can check the same post now I have posted some code there.
Cheers
Ram MCP
![]() |
0 |
![]() |
I was able to solve the problem it has nothing to do with the dataset ... it is all about the dataadapter used like Oracle.Dataaccess
also need to declare the parameters right...
for example,
odcmd.Parameters.Add("pProperty", Oracle.DataAccess.Client.OracleDbType.Varchar2, 8,
CStr(Session("propid")), ParameterDirection.Input)or
oracmd.Parameters.Add(New Oracle.DataAccess.Client.OracleParameter("pProperty", Client.OracleDbType.Varchar2, 8, ParameterDirection.Input))cmd.Parameters("pProperty").Value = CStr(Session("propid"))
... well ... thanks anyway ... this is the first time I used oracle database as a database for a web application(DNN)
![]() |
0 |
![]() |