By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,740 Members | 854 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,740 IT Pros & Developers. It's quick & easy.

Return parameter of a SQL Store procedure

P: n/a
Dear,

I have a problem : I have a database which contains my data of hardware. The
Id is a varchar and I want at my filling form that my user know which is the
last one. So I made a store procedure in SQL Server with a input parameter
(to select the right hardware e.g. PC for a computer, CD for cdrom, etc) and
the last code would I have become with a return parameter. But when I
execute my program I get an exception :

Parameter 1: '@HdType' of type: String, the property Size has an invalid
size: 0

What do I wrong ?

Thank you very much

Hugo Lefevre

************************************************** **************************
***********************************
Below you find the code and the construction in SQL Server :

the code in C# to execute the store procedure :
-------------------------------------------------

string HardwareType ="'PC%'"

public void DbTriggerHardware(string HardwareType, out string NieuwId)
{
try
{
Database
DbLogon = new Database();
SqlConnection
conn = DbLogon.Opent();
SqlCommand
cmd = conn.CreateCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText="TriggerHardware";
SqlParameter
ParInput1 = cmd.Parameters.Add("@HdType", SqlDbType.VarChar);

ParInput1.Direction = ParameterDirection.Input;

ParInput1.Value = HardwareType;
SqlParameter
parReturn = cmd.Parameters.Add("@HdType", SqlDbType.VarChar);

parReturn.Direction = ParameterDirection.Output;

cmd.ExecuteReader();
NieuwId
=(cmd.Parameters["@HdType"].ToString());
conn.Close();
}
catch (System.Exception e)
{
throw new
System.ArgumentNullException("", e.Message);
}

}

The procedure of the store procedure in SQL Server :
------------------------------------------------------

CREATE PROCEDURE TriggerHardware
(
@HdType varchar(8)
)
AS
declare @HardwareId varchar(8)
select @HardwareId = max(Ha_Id)
from Hardware
where Ha_Id like @HdType
return @HardwareId
GO

Construction of the Table Hardware :
--------------------------------------

Ha_Id varchar(8)
Ha_Type varchar(50)
Ha_Snr varchar(20)

The data of the Table Hardware :
--------------------------------------
Ha_IdHa_TypeHa_Snr
CD000001cd-dvd writer1233444443322
PC000001draagbaar 2347678899999
PC000002Acer desktop4334456677665
Nov 15 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Hi Hugo,

Try using ParameterDirection.ReturnValue and ExecuteNonQuery().

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"Hugo Lefevre" <hu**********@belgacom.net> wrote in message
news:40**********************@news.skynet.be...
Dear,

I have a problem : I have a database which contains my data of hardware. The Id is a varchar and I want at my filling form that my user know which is the last one. So I made a store procedure in SQL Server with a input parameter
(to select the right hardware e.g. PC for a computer, CD for cdrom, etc) and the last code would I have become with a return parameter. But when I
execute my program I get an exception :

Parameter 1: '@HdType' of type: String, the property Size has an invalid
size: 0

What do I wrong ?

Thank you very much

Hugo Lefevre

************************************************** ************************** ***********************************
Below you find the code and the construction in SQL Server :

the code in C# to execute the store procedure :
-------------------------------------------------

string HardwareType ="'PC%'"

public void DbTriggerHardware(string HardwareType, out string NieuwId)
{
try
{
Database
DbLogon = new Database();
SqlConnection conn = DbLogon.Opent();
SqlCommand
cmd = conn.CreateCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText="TriggerHardware";
SqlParameter ParInput1 = cmd.Parameters.Add("@HdType", SqlDbType.VarChar);

ParInput1.Direction = ParameterDirection.Input;

ParInput1.Value = HardwareType;
SqlParameter
parReturn = cmd.Parameters.Add("@HdType", SqlDbType.VarChar);

parReturn.Direction = ParameterDirection.Output;

cmd.ExecuteReader();
NieuwId
=(cmd.Parameters["@HdType"].ToString());
conn.Close(); }
catch (System.Exception e)
{
throw new
System.ArgumentNullException("", e.Message);
}

}

The procedure of the store procedure in SQL Server :
------------------------------------------------------

CREATE PROCEDURE TriggerHardware
(
@HdType varchar(8)
)
AS
declare @HardwareId varchar(8)
select @HardwareId = max(Ha_Id)
from Hardware
where Ha_Id like @HdType
return @HardwareId
GO

Construction of the Table Hardware :
--------------------------------------

Ha_Id varchar(8)
Ha_Type varchar(50)
Ha_Snr varchar(20)

The data of the Table Hardware :
--------------------------------------
Ha_IdHa_TypeHa_Snr
CD000001cd-dvd writer1233444443322
PC000001draagbaar 2347678899999
PC000002Acer desktop4334456677665

Nov 15 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.