Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > ASP/OO4O Problem: Unexpect results from package

Reply
Thread Tools

ASP/OO4O Problem: Unexpect results from package

 
 
CJM
Guest
Posts: n/a
 
      08-31-2006
Repeated for the benefit of m.p.i.asp.general, which I forgot to include in
the original posting...


"CJM" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> [Apologies for the premature posting previous to this one]
>
> I'm working on my first Oracle DB, so bear with me...
>
> I have a couple of validation routines which are both returning the same
> result regardless of the inputs. One checks if a Serial No already exists,
> the other checks if a Part No is valid.
>
> Currently there are no Serial Nos in the system, so this check shouldnt
> fail. And I get a response to indicate that the Part No is valid
> regardless
> of whether it is or not.
>
> I strongly suspect that I'm made the same mistake in each case, but since
> I'm not getting any errors, I can't see where.
>
> In both cases, the PL/SQL procedures return a value of 1, whereas if I run
> the PL/SQL in SQL Developer with suitable values inserted I get the
> expected
> results.
>
> [I actually dont know how to output the value of iResult to the screen in
> SQL Developer, so I removed the 'INTO iResult' to return a row via the
> conventional method- so strictly speaking I'm not testing the exact same
> code. Out of interest, how would I run this code and then output the value
> of iResult?]
>
> I know the problem will be something silly, but I simply dont know enough
> to spot it.
>
> Thanks in advance.
>
> CJM
>
>
>>>>>>>>>>>>>>>>>>>>>>>>>

> ASP Snippets:
>>>>>>>>>>>>>>>>>>>>>>>>>

> Function SerialExists (sSerialNo, sPartNo)
> Dim iResult, bResult
>
> With oDB
> .Parameters.Add "sSerialNo", sSerialNo, ORAPARM_INPUT
> .Parameters ("sSerialNo").ServerType = ORATYPE_VARCHAR2
>
> .Parameters.Add "sPartNo", sPartNo, ORAPARM_INPUT
> .Parameters ("sPartNo").ServerType = ORATYPE_VARCHAR2
>
> .Parameters.Add "iResult", 0, ORAPARM_OUTPUT
> .Parameters ("iResult").ServerType = ORATYPE_NUMBER
>
> iResult = oDB.ExecuteSQL("Begin VALIDATION_PKG.SerialExists(:sSerialNo,
> :sPartNo, :iResult); end;")
>
> If iResult > 0 then bResult = true Else bResult = false
> response.Write bResult & "<BR>"
>
> SerialExists = bResult
>
> .Parameters.Remove "sSerialNo"
> .Parameters.Remove "sPartNo"
> .Parameters.Remove "iResult"
>
> End With
> End Function
>
> Function IsValidPartNo(sPartNo)
> Dim iResult, bResult
>
> With oDB
> .Parameters.Add "sPartNo", sPartNo, ORAPARM_INPUT
> .Parameters ("sPartNo").ServerType = ORATYPE_VARCHAR2
>
> .Parameters.Add "iResult", 0, ORAPARM_OUTPUT
> .Parameters ("iResult").ServerType = ORATYPE_NUMBER
>
> iResult = oDB.ExecuteSQL("Begin VALIDATION_PKG.IsValidPartNo(:sPartNo,
> :iResult); end;")
>
> If iResult > 0 then bResult = true Else bResult = false
>
> Response.Write bResult & "<BR>"
> IsValidPartNo = bResult
>
> .Parameters.Remove "sPartNo"
> .Parameters.Remove "iResult"
> End With
> End Function
>
>
> 'check that SerialNo/PartNo not used
> If SerialExists(sSerialNo, sPartNo) then iError = iError + 2
>
> 'check for valid partnos
> If Not IsValidPartNo(sPartNo) then iError = iError + 4
>
>
>>>>>>>>>>>>>>>>>>>>>>>

> Package Specification:
>>>>>>>>>>>>>>>>>>>>>>>

> CREATE OR REPLACE
> PACKAGE "VALIDATION_PKG" AS
> PROCEDURE SerialExists(sSerialNo in varchar2, sPartNo in varchar2,
> iResult Out number);
> PROCEDURE IsValidPartNo(sPartNo in varchar2, iResult Out number);
> END;
>
> CREATE OR REPLACE
> PACKAGE BODY "VALIDATION_PKG" AS
> PROCEDURE SerialExists(sSerialNo in varchar2, sPartNo in varchar2,
> iResult Out number)
> IS
> BEGIN
> Select Count(*)
> into iResult
> from Part_Serial_Catalog_Tab --note: should be
> IFSAPP.Part_Serial_Catalog_Tab on live system
> where Serial_No = sSerialNo
> and Part_No = sPartNo;
> END;
>
> PROCEDURE IsValidPartNo(sPartNo in varchar2, iResult Out number)
> IS
> BEGIN
> Select Count(*)
> into iResult
> from IFSAPP.Inventory_Part_Tab
> where Part_No = sPartNo;
> END;
> END;
>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

> Tables (trimmed)
>>>>>>>>>>>>>>>>>>>>>>>>>>>

>
> CREATE TABLE "SNE"."PART_SERIAL_CATALOG_TAB"
> ( "PART_NO" VARCHAR2(25 BYTE) NOT NULL ENABLE,
> "SERIAL_NO" VARCHAR2(50 BYTE) NOT NULL ENABLE,
> CONSTRAINT "PART_SERIAL_CATALOG_PK" PRIMARY KEY ("PART_NO", "SERIAL_NO")
> ENABLE
> ) ;
>
> CREATE INDEX "SNE"."PART_SERIAL_CATALOG_RENAMED_IX" ON
> "SNE"."PART_SERIAL_CATALOG_TAB" ("PART_NO", "RENAMED_TO_SERIAL_NO")
> ;
>
> CREATE TABLE "IFSAPP"."INVENTORY_PART_TAB"
> ( "PART_NO" VARCHAR2(25 BYTE) NOT NULL ENABLE,
> "CONTRACT" VARCHAR2(5 BYTE) NOT NULL ENABLE,
>
> CONSTRAINT "INVENTORY_PART_PK" PRIMARY KEY ("PART_NO", "CONTRACT") ENABLE
> ) ;
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using ajax call to fetch multiple results from multiple queries and showing them 1 by 1 as the results comes. Biranchi Narayan Panda ASP .Net 0 02-21-2010 03:46 PM
How can I make this more efficient? (combining DataSet results with the results of a DB lookup.) Ken Fine ASP .Net 3 07-23-2008 08:11 AM
ASP/OO4O Problem: Unexpect result from package CJM ASP General 1 08-31-2006 12:27 PM
Prefix increment/decrement results in lvalue, but postfix one results in rvalue? lovecreatesbeauty C++ 8 09-12-2005 10:23 PM
Displaying results as "pages" of a JTable and sorting across all results ... Monique Y. Mudama Java 1 06-28-2005 01:01 AM



Advertisments