Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Stored Procedure Call in VB6

Posted on 2004-10-07
16
Medium Priority
?
1,609 Views
Last Modified: 2012-08-14
I wanted to find out how to get a output value from a Stored Procedure back into a VB ADO 2.6 Recordset using Command and parameter objects.

Here is a stored procedure for simplicity.

CREATE PROCEDURE test_calc_Cost

@Part varchar(25),
@qty int,
@sd varchar(1),
@BlwsCost decimal(8,2) OUTPUT

AS

Set @BlwsCost = 10.25

GO


Here is the code in VB

            Dim CmdPrice As New ADODB.Command
            Dim prmPartNum As ADODB.Parameter
            Dim prmQty As ADODB.Parameter
            Dim prmsd As ADODB.Parameter
            Dim prmCost As ADODB.Parameter
            Dim sCost, intRec
            Set prmPartNum = CmdPrice.CreateParameter("PartNum", adChar, adParamInput, 25, Trim(bom(i).part_num))
            Set prmQty = CmdPrice.CreateParameter("Qty", adInteger, adParamInput, 4, bom(i).qty)
            Set prmsd = CmdPrice.CreateParameter("sd", adChar, adParamInput, 1, "A")
            Set prmCost = CmdPrice.CreateParameter("BCost", adDecimal, adParamOutput, 8, sCost)
            With CmdPrice
                .CommandType = adCmdStoredProc
                .CommandText = "test_calc_config_blws"
                .Parameters.Append prmPartNum
                .Parameters.Append prmQty
                .Parameters.Append prmsd
                .Parameters.Append prmCost
                .ActiveConnection = oConfigObj
            End With
            Dim rstPrice As New ADODB.Recordset
            rstPrice.Open CmdPrice.Execute
            If Not rstPrice.EOF Then
                dStdCost = rstPrice.Fields(0)
           End if

When I call this code. I get a Invalid scale Value while opening the Recordset with the Command Execute method.


0
Comment
Question by:rian
  • 5
  • 4
  • 3
  • +3
16 Comments
 
LVL 1

Expert Comment

by:mrloumrlou
ID: 12250380
First, why are you sending in the prmCost when that should be the output?  That might be part of the problem.  Because if sCost = 0.00 or some other decimal value then the decimal is getting truncated because it's an output parameter.  Anyways a decimal is getting truncated.  Check this out - http://support.microsoft.com/default.aspx?kbid=188574
0
 
LVL 4

Expert Comment

by:avi247
ID: 12250539
Try with these changes..

            Set prmCost = CmdPrice.CreateParameter("BCost", adDouble, adParamOutput, 8, dCost)
           
           
            CmdPrice.Execute
            dCost = CmdPrice.Parameters.Item("BCost")
Debug.print dCost
0
 

Author Comment

by:rian
ID: 12250735
Even an output value needs to have a parameter object.  Unless I have been doing that wrong in the past.

Avi247: I am getting a null value coming back from Cmdprice.Execute.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 23

Expert Comment

by:Snarf0001
ID: 12251319
Instead of

  rstPrice.Open CmdPrice.Execute

put in

  set rstPrice = cmdPrice.Execute
0
 
LVL 23

Expert Comment

by:Snarf0001
ID: 12251348
and try adding "@" to the parameter names:

            Set prmPartNum = CmdPrice.CreateParameter("@PartNum", adChar, adParamInput, 25, Trim(bom(i).part_num))
            Set prmQty = CmdPrice.CreateParameter("@Qty", adInteger, adParamInput, 4, bom(i).qty)
            Set prmsd = CmdPrice.CreateParameter("@sd", adChar, adParamInput, 1, "A")
            Set prmCost = CmdPrice.CreateParameter("@BCost", adDecimal, adParamOutput, 8, sCost)
0
 

Author Comment

by:rian
ID: 12251518
I tried both of them. I am still getting null in the @BCost Parameter. I even changed the type to char(20).
0
 
LVL 4

Accepted Solution

by:
avi247 earned 400 total points
ID: 12251544
Rian:

I used your code and the only changes that I did were those mentioned above..I was able to get the value back. hmm


            Dim CmdPrice As New ADODB.Command
            Dim prmPartNum As ADODB.Parameter
            Dim prmQty As ADODB.Parameter
            Dim prmsd As ADODB.Parameter
            Dim prmCost As ADODB.Parameter
            Dim dCost as Double

            Set prmPartNum = CmdPrice.CreateParameter("PartNum", adChar, adParamInput, 25, Trim(bom(i).part_num))
            Set prmQty = CmdPrice.CreateParameter("Qty", adInteger, adParamInput, 4, bom(i).qty)
            Set prmsd = CmdPrice.CreateParameter("sd", adChar, adParamInput, 1, "A")

            Set prmCost = CmdPrice.CreateParameter("BCost", adDouble, adParamOutput, 8, dCost)
            With CmdPrice
                .CommandType = adCmdStoredProc
                .CommandText = "test_calc_config_blws"
                .Parameters.Append prmPartNum
                .Parameters.Append prmQty
                .Parameters.Append prmsd
                .Parameters.Append prmCost
                .ActiveConnection = oConfigObj
            End With

            CmdPrice.Execute
            dCost = CmdPrice.Parameters.Item("BCost")
0
 
LVL 23

Expert Comment

by:Snarf0001
ID: 12251550
I didn't notice before, but in the simplified proc you supplied, the variable is called @BlwsCost, but when adding the param, you call it @BCost.  They must be the same.
0
 

Author Comment

by:rian
ID: 12251645
Snarf0001 - I did change it to @BlwsCost and I still get NULL value returned back.
0
 
LVL 23

Expert Comment

by:Snarf0001
ID: 12251797
Can you post the full proc code?
Does it work if you actually have it as just set @BlwCost = 10.25?
0
 
LVL 23

Expert Comment

by:Snarf0001
ID: 12251826
ah damn, didn't look close enough.

If you're trying to get an output param, you can't get it from the recordset, just use the variable.

           If Not rstPrice.EOF Then
                dStdCost = rstPrice.Fields(0)
           End if

will not work unless you had "select @Blwcost" in the proc

use

  dStdCost = prmCost.Value

sorry, can't believe i didn't see that sooner
0
 
LVL 1

Expert Comment

by:mrloumrlou
ID: 12252237
get rid of the cost parameter that your trying to send in and just recieve what comes out as a just the way your doing it in the code.  Just change the sp to this:

creat PROCEDURE test_calc_Cost
@Part varchar(25),
@qty int,
@sd varchar(1)

AS
declare @BlwsCost decimal(8,2)
set @BlwsCost = 10.25
select @BlwsCost as theCost
--test_calc_Cost 'asdf', 5, 'A'

0
 
LVL 1

Expert Comment

by:mrloumrlou
ID: 12252255
make the parameters your sending in the same name as the variables your putting them in. They have to be the same
0
 

Author Comment

by:rian
ID: 12252270
Let me try that.
0
 

Expert Comment

by:huyntminh
ID: 12255632
Sorry if I missed the point but why did you declared "test_calc_Cost" as your store proc' name but  using your command text :
   .CommandText = "test_calc_config_blws".


0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Introduction to Processes

885 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