?
Solved

Passing parameters between a VB6 program and a stored procedure

Posted on 2005-03-07
26
Medium Priority
?
617 Views
Last Modified: 2013-12-25
I have another question concerning last Friday's question. I think I got a connection on the SQL Server. I am using VB6. I want to send an input  parameter to the Stored Procedure and then return an output procedure. Right now I have the code set up as follows:

dbCmdLastNames.Parameters.Append dbCmdLastNames.CreateParameter("@LNAME", adVarChar, adParamInput, 20, strLName)

dbCmdLastNames.Parameters.Append dbCmdLastNames.CreateParameter("@LNAMECASE", adVarChar, adParamOutput, 20, strLNameCase)

dbCmdLastNames.Execute

My stored procedure is pretty basic:

CREATE PROCEDURE [dbo].[GetLastNames]
    (              
   @LName   varchar(25),
   @LNameCase varchar(25)  Output
      )
 AS
    Select  @LNameCase
    From
      LNameFix
Where LName = @LName
GO

In order to receive LNAMECASE, what else do I need to do?  Thank you bvery much for all your help.
0
Comment
Question by:Harryinpa
[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
  • 9
  • 9
  • 5
  • +1
26 Comments
 
LVL 13

Expert Comment

by:Michael_D
ID: 13481003
seems like you did everything right

dbCmdLastNames.Parameters.Append dbCmdLastNames.CreateParameter("@LNAME", adVarChar, adParamInput, 20, strLName)

dbCmdLastNames.Parameters.Append dbCmdLastNames.CreateParameter("@LNAMECASE", adVarChar, adParamOutput, 20, strLNameCase)

dbCmdLastNames.Execute


' Get your Result
Debug.Print dbCmdLastNames.Parameters("@LNAMECASE").Value
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13481014
Is there a particular reason why you are using output parameters instead of returning a recordset?

Leon
0
 

Author Comment

by:Harryinpa
ID: 13481076
I only assumed that I would be returning an out put parameter. I am relatively new to this so that is what I expected. If I should return a dataset instead, just let me know how or what I'm doing wrong. Thank you?
0
Industry Leaders: 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!

 

Author Comment

by:Harryinpa
ID: 13481091
I meant to return a recordset, please let me know how I can retrieve that value and use it in the VB program. Thank you.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13481093
Are you using VB or VB.NET?
0
 

Author Comment

by:Harryinpa
ID: 13481108
I am using VB 6.0 I haven't used VB.Net yet.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13481158
Change the store proc to a simple Select with an input param

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
strSQL = "Exec GetLastNames '" & strLName & "'"
rs.Open strSQL, cnConn, adOpenStatic, adLockPessimistic
MsgBox rs.Fileds(1).Value & ""


0
 
LVL 13

Expert Comment

by:Michael_D
ID: 13481159
Harryinpa,
> If I should return a dataset instead, just let me know how or what I'm doing
Its up to you.
If you are expecting single value (or even single record) - output parameters can do the job.
Returning recordset is more generic
 
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13481197
>Returning recordset is more generic

In 90% of the cases returning a recordset is the way to go. I wold setup a separate procedure which returns a recordset based on a SQL string you pass to it.

Leon
0
 

Author Comment

by:Harryinpa
ID: 13481518
I don't understand where these values or fields are coming from:

rs.Open strSQL, cnConn, adOpenStatic, adLockPessimistic
MsgBox rs.Fileds(1).Value & ""
 
Please fill me in. Thank you.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13482447
0
 

Author Comment

by:Harryinpa
ID: 13482935
Leon,
    Thank you, I am looking at the tutorial now. i just had one other question that may not be contained in the tutorial. On the example program code you gave me:

               rs.Open strSQL, cnConn, adOpenStatic, adLockPes

is it possible to have more than one cnConn-like statement in a program. Let's say I had two calls to a database but to two different tables. Would I need, for instance, a similar Set cnConn = New.ADODB.Connection statements (along with the other statements to open a connection) for each table. I don't think any of the books cover that.

Finally, for returning a recordset value from a stored procedure, can I take that value and output it to a dataset?

Thanks again.
                 Harry    
0
 

Author Comment

by:Harryinpa
ID: 13482941
One more question - how do I know that a database connection has worked. If it doesn't work, I'll get an error meesage but what if I don't get a message. Does that mean that it is working? Thanks.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13483045
You can reuse the connection.  Just create separate recordset objects for each call, or reuse the first one if you do not need it any more.

You should test to see if you have an open connection before making the call to the database.  Connection object has a connection status property.

Leon
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13491212
Leon,

>>In 90% of the cases returning a recordset is the way to go. I wold setup a separate procedure which returns a recordset based on a SQL string you pass to it.<<
I beg to differ with you on that one.  Creating a recordset is expensive (which may explain one of the reasons why it did not get incorporated into ADO.NET), using output variables is always going to be more efficient.  The original code with Michael_D's additon is correct.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13491266
Actually on second thoughts it is not totally correct, it should be as follows:

CREATE PROCEDURE [dbo].[GetLastNames]
    (              
   @LName   varchar(25),
   @LNameCase varchar(25)  Output
      )
 AS

SET NOCOUNT ON
 
Select  @LNameCase = YourColumnNameGoesHere
From  LNameFix
Where LName = @LName

And your VB code:
dbCmdLastNames.Parameters.Append dbCmdLastNames.CreateParameter("@LNAME", adVarChar, adParamInput, 25, strLName)
dbCmdLastNames.Parameters.Append dbCmdLastNames.CreateParameter("@LNAMECASE", adVarChar, adParamOutput, 25)
dbCmdLastNames.Execute ,, adExecuteNoRecords

Debug.Print dbCmdLastNames.Parameters("@LNAMECASE").Value
0
 

Author Comment

by:Harryinpa
ID: 13496337
Everything that was mentioned by acPerkins works. The final question is: how do I turn the statement    "Debug.Print dbCmdLastNames.Parameters("@LNAMECASE").Value" into a variable that I can use.  For instance the stored procedure changes the name Abrunzo to ABrunzo. If I use the expression:

MsgBox A (where A= LNameCase)
I want it to show the updated last name ABrunzo as the value of A. How can this be done? Thank you (still worth 500 points).  
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13496416
>Creating a recordset is expensive

Not anymore so than creating a Command object.  Yes, there is a benifit if you are returning a a single value, but then this falls in the 10% scenerio and in my book not worth it since it is likely to be an exception rather than the rule.

Leon
0
 
LVL 13

Expert Comment

by:Michael_D
ID: 13496840
Instead of

Debug.Print dbCmdLastNames.Parameters("@LNAMECASE").Value

use

msgbox dbCmdLastNames.Parameters("@LNAMECASE").Value
0
 
LVL 13

Expert Comment

by:Michael_D
ID: 13496890
leonstryker,

I agree with you about 10%
but Harryinpa had posted his SP that return vaiue in output parameter instead of recordset, that the reason that I suggested to use Command object.

0
 
LVL 13

Accepted Solution

by:
Michael_D earned 2000 total points
ID: 13496922
oops I missread the last post of Harryinpa about variable

Correction:

dbCmdLastNames.Parameters.Append dbCmdLastNames.CreateParameter("@LNAME", adVarChar, adParamInput, 25, strLName)
dbCmdLastNames.Parameters.Append dbCmdLastNames.CreateParameter("@LNAMECASE", adVarChar, adParamOutput, 25)
dbCmdLastNames.Execute ,, adExecuteNoRecords

A=dbCmdLastNames.Parameters("@LNAMECASE").Value

msgbox  A
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13496972
Michael_D,

No problem.  I find that a lot of people on EE do not know that there are several solutions to their particular question.  Sometimes it is as benefitial to answer the question they did not ask, as to answer the one they did, because we are never sure what their level of expertise is.

Leon  
0
 

Author Comment

by:Harryinpa
ID: 13497894
Sorry, one more question for this.
After looking at this statement - A=dbCmdLastNames.Parameters("@LNAMECASE").Value
                                              MsgBox A
I received the correct value. But what if the Select statement returned an empty data set, is there a way in VB 6.0 to take this into account?
When I run:  If strNewLastName Is Null Then
                      strNewLastName = ""
                   Else
                      strNewLastName = dbCmdLastNames.Parameters("@LNameCase").Value
                   End If
I receive the error message "Object required" on the 1st statement.

Also, "A" is designated as a Variant - this makes the  "A=" statement work. Is there a way to test a return code from the Select Statement to determine if the Select returned no records? Any thoughts? Thank you.
                                                                                                    Harry




What if the value returned from the stored procedure is null (the select statement executed found an empty data set), is there a way in VB 6.0 to take this into account.
Also, in order to receive the value
                     
               
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13497981
This work very nicely

strNewLastName = dbCmdLastNames.Parameters("@LNameCase").Value & ""

Leon
0
 

Author Comment

by:Harryinpa
ID: 13498167
Thank you all for your help. I wish I could have given you all 500 points. Your ansswers were extremely helpful and enlightening.
                                                                                                Harry
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13498306
Leon,

>>Not anymore so than creating a Command object. <<
I cannot agree with you on that.

>>I find that a lot of people on EE do not know that there are several solutions to their particular question. Sometimes it is as benefitial to answer the question they did not ask, as to answer the one they did, because we are never sure what their level of expertise is.<<
Absolutely.  And this is particularly true in .NET when you have ten different ways to do basically the same thing.

>>Is there a way to test a return code from the Select Statement to determine if the Select returned no records?
 ...
What if the value returned from the stored procedure is null (the select statement executed found an empty data set), is there a way in VB 6.0 to take this into account.<<
There is no dataset involved here (that is what makes is so efficient) the SELECT is not returning records it is assigning to a variable (in this case an output parameter),  you either have a value or you don't (Null).

So you should do:
If IsNull(dbCmdLastNames.Parameters("@LNAMECASE").Value) Then

Else

End If
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month11 days, 16 hours left to enroll

752 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