Solved

Stored Procedure Call in VB6

Posted on 2004-10-07
16
1,590 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
[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
  • 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
Technology Partners: 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 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 100 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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this post we will learn different types of Android Layout and some basics of an Android App.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Progress

615 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