setting output parameter of stored procedure in VB6.0

[susan]

Hi.
What is wrong?
=================================================================
Private Sub cmdShowIt_Click()
Dim par1 As ADODB.Parameter
Dim par2 As ADODB.Parameter

Set cmnd = New ADODB.Command
Set cmnd.ActiveConnection = cn
cmnd.CommandType = adCmdStoredProc
cmnd.CommandText = "spShowIt"

Set par1 = cmnd.CreateParameter("Input", adTinyInt, adParamInput)
cmnd.Parameters.Append par1
par1.Value = txtNumber.Text

Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput)
cmnd.Parameters.Append par2

cmnd.Execute

Debug.Print cmnd(1).Value
txtName.Text = cmnd(1).Value

txtName.Enabled = True
Set cmnd = Nothing

End Sub
=====================================================================
And the Stored procedure "spShowIt" :
=====================================================================
CREATE PROCEDURE spShowIt(@number tinyint,@name varchar(20) OutPut)
AS

SET NOCOUNT ON
SELECT @name = Personel.Name
FROM Personel
WHERE Personel.NO = @number;

RETURN @name
GO

Any guide please

[jy]

"susan" <iamsmahd@yahoo.com> wrote:
>
>Hi.
>What is wrong?
>=================================================================
>Private Sub cmdShowIt_Click()
> Dim par1 As ADODB.Parameter
> Dim par2 As ADODB.Parameter
>
> Set cmnd = New ADODB.Command
> Set cmnd.ActiveConnection = cn
> cmnd.CommandType = adCmdStoredProc
> cmnd.CommandText = "spShowIt"
>
> Set par1 = cmnd.CreateParameter("Input", adTinyInt, adParamInput)
> cmnd.Parameters.Append par1
> par1.Value = txtNumber.Text
>
> Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput)
> cmnd.Parameters.Append par2
>
> cmnd.Execute
>
> Debug.Print cmnd(1).Value
> txtName.Text = cmnd(1).Value
>
> txtName.Enabled = True
> Set cmnd = Nothing
>
>End Sub
>=====================================================================
>And the Stored procedure "spShowIt" :
>=====================================================================
>CREATE PROCEDURE spShowIt(@number tinyint,@name varchar(20) OutPut)
>AS
>
>SET NOCOUNT ON
>SELECT @name = Personel.Name
>FROM Personel
>WHERE Personel.NO = @number;
>
>RETURN @name
>GO
>
>Any guide please
>
you need to specify the size of the adVarChar-type parameter "Output"; in
this case:

Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput, 20)

since the varchar datatype does not have a predefined fixed size.

jy

[jy]

"jy" <jy_mead@hotmail.com> wrote:
>
>"susan" <iamsmahd@yahoo.com> wrote:
>>
>>Hi.
>>What is wrong?
>>=================================================================
>>Private Sub cmdShowIt_Click()
>> Dim par1 As ADODB.Parameter
>> Dim par2 As ADODB.Parameter
>>
>> Set cmnd = New ADODB.Command
>> Set cmnd.ActiveConnection = cn
>> cmnd.CommandType = adCmdStoredProc
>> cmnd.CommandText = "spShowIt"
>>
>> Set par1 = cmnd.CreateParameter("Input", adTinyInt, adParamInput)
>> cmnd.Parameters.Append par1
>> par1.Value = txtNumber.Text
>>
>> Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput)
>> cmnd.Parameters.Append par2
>>
>> cmnd.Execute
>>
>> Debug.Print cmnd(1).Value
>> txtName.Text = cmnd(1).Value
>>
>> txtName.Enabled = True
>> Set cmnd = Nothing
>>
>>End Sub
>>=====================================================================
>>And the Stored procedure "spShowIt" :
>>=====================================================================
>>CREATE PROCEDURE spShowIt(@number tinyint,@name varchar(20) OutPut)
>>AS
>>
>>SET NOCOUNT ON
>>SELECT @name = Personel.Name
>>FROM Personel
>>WHERE Personel.NO = @number;
>>
>>RETURN @name
>>GO
>>
>>Any guide please
>>
>you need to specify the size of the adVarChar-type parameter "Output"; in
>this case:
>
>Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput, 20)
>
>since the varchar datatype does not have a predefined fixed size.
>
>jy
(you would have to specify parameter size for all char, varchar, nvarchar,
binary & varbinary parameters. jy)

[michael]

You cannot 'return @name'. You can only return integers, at least no characters.
Remove the return line I think the procedure will work correctly.

michael

"jy" <jy_mead@hotmail.com> wrote:
>
>"jy" <jy_mead@hotmail.com> wrote:
>>
>>"susan" <iamsmahd@yahoo.com> wrote:
>>>
>>>Hi.
>>>What is wrong?
>>>=================================================================
>>>Private Sub cmdShowIt_Click()
>>> Dim par1 As ADODB.Parameter
>>> Dim par2 As ADODB.Parameter
>>>
>>> Set cmnd = New ADODB.Command
>>> Set cmnd.ActiveConnection = cn
>>> cmnd.CommandType = adCmdStoredProc
>>> cmnd.CommandText = "spShowIt"
>>>
>>> Set par1 = cmnd.CreateParameter("Input", adTinyInt, adParamInput)
>>> cmnd.Parameters.Append par1
>>> par1.Value = txtNumber.Text
>>>
>>> Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput)
>>> cmnd.Parameters.Append par2
>>>
>>> cmnd.Execute
>>>
>>> Debug.Print cmnd(1).Value
>>> txtName.Text = cmnd(1).Value
>>>
>>> txtName.Enabled = True
>>> Set cmnd = Nothing
>>>
>>>End Sub
>>>=====================================================================
>>>And the Stored procedure "spShowIt" :
>>>=====================================================================
>>>CREATE PROCEDURE spShowIt(@number tinyint,@name varchar(20) OutPut)
>>>AS
>>>
>>>SET NOCOUNT ON
>>>SELECT @name = Personel.Name
>>>FROM Personel
>>>WHERE Personel.NO = @number;
>>>
>>>RETURN @name
>>>GO
>>>
>>>Any guide please
>>>
>>you need to specify the size of the adVarChar-type parameter "Output";
in
>>this case:
>>
>>Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput, 20)
>>
>>since the varchar datatype does not have a predefined fixed size.
>>
>>jy
>(you would have to specify parameter size for all char, varchar, nvarchar,
>binary & varbinary parameters. jy)

[susan]

jy and michael,
thanks alot for your efficient helps.Now my program works.
Yes michael you were right,I removed the return line and with Jy advise
for setting the size of Varchar variable now my program works properly
thank you again
Susan





"michael" <mmayfield_NOSPAM@houston.rr.com> wrote:
>
>You cannot 'return @name'. You can only return integers, at least no characters.
>Remove the return line I think the procedure will work correctly.
>
>michael
>
>"jy" <jy_mead@hotmail.com> wrote:
>>
>>"jy" <jy_mead@hotmail.com> wrote:
>>>
>>>"susan" <iamsmahd@yahoo.com> wrote:
>>>>
>>>>Hi.
>>>>What is wrong?
>>>>=================================================================
>>>>Private Sub cmdShowIt_Click()
>>>> Dim par1 As ADODB.Parameter
>>>> Dim par2 As ADODB.Parameter
>>>>
>>>> Set cmnd = New ADODB.Command
>>>> Set cmnd.ActiveConnection = cn
>>>> cmnd.CommandType = adCmdStoredProc
>>>> cmnd.CommandText = "spShowIt"
>>>>
>>>> Set par1 = cmnd.CreateParameter("Input", adTinyInt, adParamInput)
>>>> cmnd.Parameters.Append par1
>>>> par1.Value = txtNumber.Text
>>>>
>>>> Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput)
>>>> cmnd.Parameters.Append par2
>>>>
>>>> cmnd.Execute
>>>>
>>>> Debug.Print cmnd(1).Value
>>>> txtName.Text = cmnd(1).Value
>>>>
>>>> txtName.Enabled = True
>>>> Set cmnd = Nothing
>>>>
>>>>End Sub
>>>>=====================================================================
>>>>And the Stored procedure "spShowIt" :
>>>>=====================================================================
>>>>CREATE PROCEDURE spShowIt(@number tinyint,@name varchar(20) OutPut)
>>>>AS
>>>>
>>>>SET NOCOUNT ON
>>>>SELECT @name = Personel.Name
>>>>FROM Personel
>>>>WHERE Personel.NO = @number;
>>>>
>>>>RETURN @name
>>>>GO
>>>>
>>>>Any guide please
>>>>
>>>you need to specify the size of the adVarChar-type parameter "Output";
>in
>>>this case:
>>>
>>>Set par2 = cmnd.CreateParameter("Output", adVarChar, adParamOutput, 20)
>>>
>>>since the varchar datatype does not have a predefined fixed size.
>>>
>>>jy
>>(you would have to specify parameter size for all char, varchar, nvarchar,
>>binary & varbinary parameters. jy)
>