Solved

Passing a number to a stored procedure using VBScript

Posted on 2006-07-17
6
1,347 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 Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

808 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