[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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.
0
Jenny Coulthard
Asked:
Jenny Coulthard
  • 5
  • 2
  • 2
  • +2
1 Solution
 
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
 
Chris WatsonCommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Jenny CoulthardIT 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 CoulthardIT 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 CoulthardIT 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 CoulthardIT ManagerAuthor Commented:
It turns out that either
sClientGUID = cmd("@result")
and
sClientGUID = cmd.Parameters("@result").Value

Open in new window


are interchangeable.
0
 
Jenny CoulthardIT 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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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