Solved

Stored Procedure Call in VB6

Posted on 2004-10-07
16
1,575 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 51

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

This is about my first experience with programming Arduino.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
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 …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now