Solved

Passing a number to a stored procedure using VBScript

Posted on 2006-07-17
6
1,340 Views
Last Modified: 2010-08-05
I need to call an Oracle stored procedure where there is a numeric input parameter and string and numeric output parameters
The procedure is called from ASP page using VBScript.
My code contains the following where "my_procedure_name", "input_par", "output_par_str", "output_par_num" are correct procedure and parameters names correspondingly, and input_value is a number:

set Conn = Server.CreateObject("ADODB.Connection")
Conn.open strConnect ' where strConnect is a string with correct connection data

Set oCmd = Server.CreateObject("ADODB.Command")
oCmd.ActiveConnection = Conn
oCmd.CommandText = "my_procedure_name"
oCmd.CommandType = 4

Set pCustID = oCmd.CreateParameter("input_par", adNumeric, adParamInput, 4, input_value)
oCmd.Parameters.Append pCustID

Set pAction = oCmd.CreateParameter("output_par_str", adVarChar, adParamOutput, 15)
oCmd.Parameters.Append pAction

Set pAction = oCmd.CreateParameter("output_par_num", adNumeric, adParamOutput, 4)
oCmd.Parameters.Append pAction
oCmd.Execute

The PROBLEM is that the input parameter is relatively big, for example, 456789.
This number is passed into the function that calls a stored procedure as a string subtype.
So if I execute as is shown in the code, I have an error:
ADODB.Command error '800a0d5d'  Application uses a value of the wrong type for the current operation.
This is understandable.  I need to pass a number.

BUT...  If I use the code like this:
Set pCustID = oCmd.CreateParameter("input_par", adNumeric, adParamInput, 4, CLng(input_value))
or
Set pCustID = oCmd.CreateParameter("input_par", adNumeric, adParamInput, 4, CLng(CStr(input_value)))

I have an error: Type mismatch: 'CLng'

If I literary enter the number like this, the code works fine:
Set pCustID = oCmd.CreateParameter("input_par", adNumeric, adParamInput, 4, 456789)
or
Set pCustID = oCmd.CreateParameter("input_par", adNumeric, adParamInput, 4, CLhg("456789"))


What am I doing wrong?  How should I pass a variable to the stored procedure?




0
Comment
Question by:pavelmed
  • 3
  • 2
6 Comments
 
LVL 31

Accepted Solution

by:
alorentz earned 500 total points
ID: 17126455
I think your value in input_value is NULL or empty.

VBScript is a variant language, so if input_value was = to 456789, the procedure wouldn't know the difference...

Also, CLng will give you that error when the value is NULL.

Do "response.write input_value", and I think you'll see that input_value is NULL or blank.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 17126466
You could try this also, and it would work:
========================
input_value=456789
Set pCustID = oCmd.CreateParameter("input_par", adNumeric, adParamInput, 4, input_value))

OR

input_value="456789"
Set pCustID = oCmd.CreateParameter("input_par", adNumeric, adParamInput, 4, input_value))
==============================

Both would work...VBScript doesn't care.

Basically, you think you have a value in input_value, but you don't.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17127946
Please post your stored procedure or if it is very long just the signature (the name and parameters with all the data types) I suspect you need to define the Precision and Scale.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17127956
The other potential problem is that the Numeric type requires more than the 4 bytes you have allocated.
0
 

Author Comment

by:pavelmed
ID: 17130010
alorentz,

You are right.  The input_value argument in the call to the the function that calls the stored procedure was incorrectly stated and therefore in the body of the function input_value was NULL.
Thank you!
0
 
LVL 31

Expert Comment

by:alorentz
ID: 17130163
Glad to help.... when something is not working as expected, or erroring, always response.write the values <grin>...
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now