Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1612
  • Last Modified:

Stored Procedure Call in VB6

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
rian
Asked:
rian
  • 5
  • 4
  • 3
  • +3
1 Solution
 
mrloumrlouCommented:
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
 
avi247Commented:
Try with these changes..

            Set prmCost = CmdPrice.CreateParameter("BCost", adDouble, adParamOutput, 8, dCost)
           
           
            CmdPrice.Execute
            dCost = CmdPrice.Parameters.Item("BCost")
Debug.print dCost
0
 
rianAuthor Commented:
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's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Snarf0001Commented:
Instead of

  rstPrice.Open CmdPrice.Execute

put in

  set rstPrice = cmdPrice.Execute
0
 
Snarf0001Commented:
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
 
rianAuthor Commented:
I tried both of them. I am still getting null in the @BCost Parameter. I even changed the type to char(20).
0
 
avi247Commented:
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
 
Snarf0001Commented:
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
 
rianAuthor Commented:
Snarf0001 - I did change it to @BlwsCost and I still get NULL value returned back.
0
 
Snarf0001Commented:
Can you post the full proc code?
Does it work if you actually have it as just set @BlwCost = 10.25?
0
 
Snarf0001Commented:
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
 
mrloumrlouCommented:
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
 
mrloumrlouCommented:
make the parameters your sending in the same name as the variables your putting them in. They have to be the same
0
 
rianAuthor Commented:
Let me try that.
0
 
huyntminhCommented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now