ASP/OO4O Problem: Unexpect result from package

From: CJM <cjmnews04_at_REMOVEMEyahoo.co.uk>
Date: Thu, 31 Aug 2006 13:23:05 +0100
Message-ID: <4lo2l9F2qef0U1@individual.net>

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

>>>>>>>>>>>>>>>>>>>>>>>>

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

 '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:

>>>>>>>>>>>>>>>>>>>>>>

Received on Thu Aug 31 2006 - 07:23:05 CDT