Solved

Stored Procedure Call in VB6

Posted on 2004-10-07
16
1,586 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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 22

Expert Comment

by:Snarf0001
ID: 12251319
Instead of

  rstPrice.Open CmdPrice.Execute

put in

  set rstPrice = cmdPrice.Execute
0
 
LVL 22

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 22

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
 
LVL 55

Expert Comment

by:Julian Hansen
ID: 12251622
0
 

Author Comment

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

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 22

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This is an explanation of a simple data model to help parse a JSON feed
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
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…

828 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