?
Solved

Passing a number to a stored procedure using VBScript

Posted on 2006-07-17
6
Medium Priority
?
1,366 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 31

Accepted Solution

by:
alorentz earned 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

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…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

650 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