ASP/OO4O Problem: Unexpect results from package

[CJM]

Repeated for the benefit of m.p.i.asp.general, which I forgot to include in
the original posting...
"CJM" <cj*******@REMOVEMEyahoo.co.ukwrote in message
news:4l************@individual.net...

[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
) ;