sql server stored procedures from vb6

Hi,
This is possibly a very easy question for those who use sql server all the time.
I am trying to run a stored procedure in sql server express from vb6.  I've attached screen shots of the two sets of code.  One for the stored procedure and one for the vb6 code.

STORED PROCEDURE

USE [Standard Time]
GO
/****** Object:  StoredProcedure [dbo].[GetClientGUID]    Script Date: 08/19/2014 17:28:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:      Given a client id return the GUID for the client.  Client ID is stored in the terms field
-- =============================================
ALTER PROCEDURE [dbo].[GetClientGUID]
      @p1 int = 0,
      @result varchar(255) OUTPUT
AS
BEGIN
      SET NOCOUNT ON;

    SELECT    @result= GUID
FROM         dbo.Clients
WHERE     (Terms = @p1)
<-- I had a RETURN @result here but it thinks I'm returning an integer and errors out converting the varchar to int.
END


VB6 code


Set cmd = New ADODB.Command
cmd.ActiveConnection = aConnection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "[dbo].[GetClientGUID]"
cmd.Parameters.Append cmd.CreateParameter("p1", adInteger, adParamInput, "1")
cmd.Parameters.Append cmd.CreateParameter("result", adVarChar, adParamOutput, 255)
cmd.Execute
sClientGUID = cmd("result")
Set cmd.ActiveConnection = Nothing

I think the error is in the stored procedure but I'm not sure why it thinks the return value is an integer.
Jenny CoulthardInformation Technology ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carl TawnSystems and Integration DeveloperCommented:
You shouldn't need a RETURN statement as yo haven't declared that you are returning anything. The SELECT itself is populating the OUTPUT parameter, and that should be all you need.
0
ste5anSenior DeveloperCommented:
You're mixing two things.

1. The RETURN statement is not necessary. But every stored procedure returns a single integer value.

USE tempdb;
GO

CREATE PROCEDURE dbo.GetClientGUID
    @p1 INT = 0 ,
    @result VARCHAR(255) OUTPUT
AS
    SET NOCOUNT ON;

    SELECT TOP 1
            @result = T.name
    FROM    sys.tables T;

    RETURN 123;
GO

DECLARE @output VARCHAR(255);
DECLARE @result INT;

EXECUTE @result = dbo.GetClientGUID 1, @output OUTPUT;

SELECT  @result ,
        @output;
GO

DROP PROCEDURE dbo.GetClientGUID;
GO

Open in new window


It's common to use the return value to indicate success or failure of the procedure exectution.

2. Your VB parameter declaration is wrong. The @ is part of the parameter name.

Set cmd = New ADODB.Command
 cmd.ActiveConnection = aConnection
 cmd.CommandType = adCmdStoredProc
 cmd.CommandText = "[dbo].[GetClientGUID]"
 cmd.Parameters.Append cmd.CreateParameter("@p1", adInteger, adParamInput, "1")
 cmd.Parameters.Append cmd.CreateParameter("@result", adVarChar, adParamOutput, 255)
 cmd.Execute
 sClientGUID = cmd("@result")
 Set cmd.ActiveConnection = Nothing

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris WatsonSoftware DeveloperCommented:
To add to what Carl has already said, you can't change the return type of a stored procedure. They always return an integer. You might have to change your VB6 code to read back the value of the @result output parameter. Try:

sClientGUID = cmd.Parameters("result").Value

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jenny CoulthardInformation Technology ManagerAuthor Commented:
OK, thanks.  This is starting to make more sense re: the return value of the stored proecedure being an integer.  
I'll update my code and let you know how this goes.
0
Jenny CoulthardInformation Technology ManagerAuthor Commented:
OK, so the stored procedure is working and producing the expected results
sql-stored-procedure-result.rtf
When I attempt to retrieve the parameters in vb the first parameter has no value and the second is null.
I need to do some more work on the vb execution to return the GUID.
0
Jenny CoulthardInformation Technology ManagerAuthor Commented:
I found the missing line.  I had not actually set a value for the first parameter.
I added the following lines and now get required result using Chris's suggestion parameters.value to retrieve value.

Will close question tomorrow at work.
cmd.Parameters("@p1").Value = "1"

...
sClientGUID = cmd.Parameters("@result").Value

Open in new window

0
Jenny CoulthardInformation Technology ManagerAuthor Commented:
It turns out that either
sClientGUID = cmd("@result")
and
sClientGUID = cmd.Parameters("@result").Value

Open in new window


are interchangeable.
0
Jenny CoulthardInformation Technology ManagerAuthor Commented:
and for completeness of vb code.  Parameter for intenger was missing second comma so not receiving value.
 cmd.Parameters.Append cmd.CreateParameter("@p1", adInteger, adParamInput,, "1")

Open in new window

0
Anthony PerkinsCommented:
Not to be picky, but:
Your VB parameter declaration is wrong. The @ is part of the parameter name.
is not entirely true.  When the NamedParameters property is false (the default), parameter names are ignored and the provider uses the order of parameters to match values to parameters in the CommandText or CommandStream properties.

Also, the value parameter is a variant, so for clarity it should be:
cmd.Parameters("@p1").Value = 1
0
ste5anSenior DeveloperCommented:
Picky is okay. But this was not a wrong:

Your VB parameter declaration is wrong. The @ is part of the parameter name.

Also NamedParameters = False requires imho the parameters to be quotation marks and not named parameters (@paramname).
But have you an working sample to prove this?
0
Anthony PerkinsCommented:
Picky is okay. But this was not a wrong:
Like most languages, English has its subtleties.  And I can say that because it is not my first language.  However, I did not say it was wrong.  I stated that it was not entirely true.

Also NamedParameters = False requires imho the parameters to be quotation marks and not named parameters (@paramname)
I am not following you.

But have you an working sample to prove this?
It is the way ADO has always functioned (provided that NamedParameters is not set explicitly to True)  I am not saying that you should not use @, but you could have just as well call them X, Y and Z.  Did you not read the link I posted?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.