Stored procedure problem

ashish908
11-02-2004, 09:35 AM
I have written the following stored procedure

CREATE PROCEDURE GetManufacturerCurrency
@Manufacturerid varchar(10),
@Symbol varchar(25) Output
as
select @Symbol = b.symbol from Manufacturer a , Currency b where a.currencyid = b.currencyid and a.manufacturerid=@Manufacturerid
GO

Then i wrote a vb code to access the stored procedure.

Dim vCurrency As String
Dim cmd As Command
Set cmd = New ADODB.Command

cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "GetManufacturerCurrency"
cmd.Parameters.Append cmd.CreateParameter("ManufacturerID", adVarChar, adParamInput, 10, vManufacturerid)
cmd.Parameters.Append cmd.CreateParameter("Symbol", adVarChar, adParamOutput, 25, vCurrency)
Dim xrs As New ADODB.Recordset
Set xrs = cmd.Execute
Msgbox vCurrency
Set cmd = Nothing
Set xrs = Nothing

When i run the stored procedure from the Query Analyzer i get the desired result, but with VB i get a blank Msgbox, what could be the issue?

Can someone help please, i am a bit new to Stored procedure?

Dennis DVR
11-03-2004, 12:07 AM
I have written the following stored procedure

CREATE PROCEDURE GetManufacturerCurrency
@Manufacturerid varchar(10),
@Symbol varchar(25) Output
as
select @Symbol = b.symbol from Manufacturer a , Currency b where a.currencyid = b.currencyid and a.manufacturerid=@Manufacturerid
GO

Then i wrote a vb code to access the stored procedure.

Dim vCurrency As String
Dim cmd As Command
Set cmd = New ADODB.Command

cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "GetManufacturerCurrency"
cmd.Parameters.Append cmd.CreateParameter("ManufacturerID", adVarChar, adParamInput, 10, vManufacturerid)
cmd.Parameters.Append cmd.CreateParameter("Symbol", adVarChar, adParamOutput, 25, vCurrency)
Dim xrs As New ADODB.Recordset
Set xrs = cmd.Execute
Msgbox vCurrency
Set cmd = Nothing
Set xrs = Nothing

When i run the stored procedure from the Query Analyzer i get the desired result, but with VB i get a blank Msgbox, what could be the issue?

Can someone help please, i am a bit new to Stored procedure?

if you want to retrieve the return value of an output variable from a stored procedure, you need to access the parameter collection not the parameter itself.

vCurrency = cmd.Parameters("Symbol").Value
msgbox vCurrency


if that approach still doen't work try to use the SET statement instead of SELECT i.e.

SET @Symbol = (SELECT b.symbol blah..,blah..,)


Just make sure that the return value of your SELECT statement always return single record.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum