Solved

Passing a number to a stored procedure using VBScript

Posted on 2006-07-17
6
1,344 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

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 Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

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