Problem with Oracle Stored Procedures using ODP.NET in VB.NET (VS 2003)
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 |
|
asiaenforcer 
asp.net.oracle
2269 articles. 
0 followers.
4 Replies
1776 Views
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 |
|
ramana123 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 |
|
asiaenforcer 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 |
|
ramana123 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 |
|
asiaenforcer 
Similar Artilces: Powerbuilder version 6.0.00 / 6.5 Build 444 Oracle Net 8.0.4.0 I found there is problem with Oracle store procedure having more than 6 in parameters. I got GPF error on Window 95 and Dr. Watson error on NT. Also, I found it works find in Oracle Net 8.0.3.0. Is this a bug? Is there any new patch for this if it is a bug. Any recommendation about What version Oracle Net or SQL Net should I used for Oracle server version 8.0.4.0 (I don't think I am using any Oracle 8 new features)? Thanks in advance, Sharon Li If you use PB 6.5, you can try the new Oracle 8 driver: ftp...
Sending SMS using vb.net or C#.net using vb.net or c#.net
Hi My requirement is I hav one csv file with these fields id,mobilenum,messgae,status.intiallu staus is 0. once i read the all fileds and take that mobile number.using tat mobile number i need to send sms .after sending sms i shuld change status as 1 How to send sms thru coding (please dont provide any links.if it is provide also please give working links becox i checked codeproject .i didnt get any nice link. and also provide the how to update the status field im csv file Thank ssandhya To send SMS, you need some third party SMS providers. if you consul...
vb.net vs c#.net vs j#.net
just out of curiousity which looks, acts most like java? i'm still learning trying to used to vb.net but it seems that it was easier to do some stuff with java. C# is Java++ in my opinion. If you have done C or Jave you will prefer C#.Mike Schellenberger - MCAD If you are looking for language similar to java go for J#. However most code samples you find on the net will be in VB.NET and C#....
Return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle
Hello, I am wondering if anyone in this community knows the shorter way to return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle, something likereturn (IDataReader) SqlHelper.ExecuteReader(ConnectionString, "GET_ACCOUNT", Id); instead of using the way below (look at the C# code) Stored Procedure create or replace PROCEDURE "GET_ACCOUNT" ( p_ID IN ACCOUNT.ID%type, retCursor OUT SA.MYGEN.sqlcur ) IS BEGIN OPEN retCursor FOR SELECT ID, NAME FROM ACCOUNT WHERE ID = p_ID ; ...
which is shou i use? Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NET
Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NETI am confused?Which is better or which one should I use?BTW, I am going to use .net 2.0 with oracle 9. Can anyone tell me what shou I use for working with oracle database?Thanks very much in advance!Justin Oracle's data provider has more functionality than Microsoft's.If your doing transaction processing you can have SAVEPOINTs, which Microsoft's data provider does not provide.Also, Oracle has PL/SQL Associative Arrays which are very powerful.Oracle also allows you to bind parameters by name and pos...
is there any way to call oracle stored function, not stored procedure using .net?
I am just wondering if I can call oracle stored function instead of stored procedure.Thank in advance! you call a function just like s procedure.except you need to add a parameter for the return value.myparam ... ParameterDirection.ReturnValue;--dweezilSELECT * FROM users WHERE clue = 'yes';Records found: 0...
Switch back from using VS.Net 2005 to VS.Net 2003
Hi All, Last Month I installed VS.Net 2005 using Framework 2.0 beta 1. Everything worked great using VS.Net 2005. Today, I decided to switch back to VS.Net 2003. I need to do some other projects that has been developed using Framework 1.1. When I open my ASP.Net project using VS.Net 2003, I got this error "The Web server reported the following error when attempting to create or open the Web Project located at the following URL:....... 'HTTP/1.1 500 Internal Server Error'. Does anyone know how I can fix this problem without uninstall VS.Net2005 and reinstall VS.Net2003? Great...
Can I buy VB .NET 2003 instead of VS .NET 2003?
Hi if I code my web applications using just Visual Basic can I buy Visual Basic .NET 2003 instead of having to buy the whole Visual Studio .NET 2003? Will that work the same? Thanks Hi, When I got VB.NET 2003, it included VS.NET 2003 (Standard Edition). I don't think there is a version of VB.NET 2003 that doesn't include VS.NET as the IDE. While there are some projects templates that are not include in the standard edition, you still have the ASP.NET web application, web service, win forms, etc..., projects available. If you are asking...
How to use InterBase with VS.NET (vb.net)??
Hi all; Q1:How can I use "InterBase" as a RDMS with Visual Studio.NET (VB.NET) for developing a small application?? Q2:What should I use for a Data Provider? Q3:How ConnectionString will be build?? Thanks in advanced!Regards,CS4Ever~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Dont forget to click "Mark as Answer" on the post that helped you.This credits that member, earns you a point and marks your thread as Resolved so we will ALL know you have been helped. This will help me, you and others....
Problem VB.NET 2003 and DataWindows.net
Greetings: When trying to add manually DataWindows.net 2.0 control to my Vb.Net 2003 studio I get the following message: DataWindowInterop.dll is not a Microsoft.Net module I get the same error when trying to add DataWindow.dll Can someone advise how to resolve this problem? Thanks Hello, I am having the exact same problem. Is it even possible to use Datawindow 2.0 in Vb.Net 2003? If so what's the trick? DW .NET installed fine but the controls on the tool box don't show up. I tried adding DLL's both ways as a .NET and a COM component but neither worked. Any ...
VS.NET 2005 vs VS.NET 2003
I have used VS.NET 2003 extensively and exploited all features like drag and drop functions for creating components, drag and drop data access components...For example VS.NET 2003 allows creating sqldataadapter for table mapping , stored proc parameters mappings etc..We can drag pretty much any object on component designer....Not it seems like all that gone...VS.NET 2005, there is no way we can drag the typed dataset, sqldata adapter, sqlcommand on forms designer . There is no concept of component. There is no component designer any more....Biggest flaw i found is sqldatasource and...
Issue using control created in VS.net 2003 in VS.net 2002
I have a friend that has an issue using my asp.net C# custom control in Visual Studio.net 2002 that I have compiled under Visual Studio.net 2003. I never did test it with that version, but I was under the impression that it should still work as long as you don't use any features specific to the 1.1 framework (which I really don't think I did). My guess now is that this is correct, but only when using 2002 controls in 2003. I have searched the web, these forums, and MSDN for the last 30 minutes with no real answer. The guy that runs MetaBuilders (http://www.metabuilders.com/Faq.aspx) tell...
upgrading vb.net to vb.net 2003 crystal report viewer problem.
I have a vb.net web. app that has several pages each with a different crystal report on it. It works perfectly under vb.net. I've since installed vb.net 2003. After I open the solution in 2003 & it does its upgrade, all of the crystal report viewer controls are corrupted. If I try to run it as is I get the following error: "The base class includes the field 'myname' but its type (CrystalDecisions.Web.CrystalReportViewer) is not compatible with the type of control(CrystalDecisions.Web.CrystalReportViewer). So I tried deleting the control and adding back on. & when I ru...
vb.net function and Oracle procedure to get an Oracle Dataset??
I have similar code (a procedure and function) working perfectly with a sql server data source. I;'m trying to do the same things with Oracle, but have run into the problems. I'm not an oracle expert, so I'm not sure if producing a cursor is my only and best option. create or replace package body PK_RETAILER isPROCEDURE sp_Get_Order_History(v_ani IN number,output_cursor out PK_CURSORES.generic_cursor) ASBEGINopen output_cursor forselect o.account_number, p.phone, o.amount, o.name, o.init_datefrom orders ojoin users_phones p on p.account_number...
Web resources about - Problem with Oracle Stored Procedures using ODP.NET in VB.NET (VS 2003) - asp.net.oracle
Invasiveness of surgical procedures - Wikipedia, the free encyclopediaThere are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...
Procedure is more a snap than a snipA QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...
Rushed cosmetic procedures a 'recipe for disaster'​When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...
Jetstar procedures under investigation after planes took off too heavyJetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...
What is some of the worst behavior you've witnesses by attorneys?
NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher ReportThe NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...
Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...
TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...
'Painless' dental cavity procedure regrows tooth enamel... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...
The geeks who design our websites talk a lot about user friendliness, but they often let an exaggerated security concerns get in the way of commonsense. ...