Error message accessing stored proc with C#

[Jay]

I hope this is the correct place to post this.

I'm using a stored procedure to simply look up and return a value from a
database. The db key is an integer, everything else is varchar. The stored
proc is:
CREATE procedure SP_IMAGE_NAME(
@MAT_ID varchar(13),
@TYPE varchar(1),
@IMAGE_NM varchar(100) OUTPUT
) as
begin

SELECT @IMAGE_NM = FileName FROM IPSAvailableIma gesFinal
WHERE (MaterialNumber = @MAT_ID) AND (type = @TYPE)
RETURN @IMAGE_NM
end
GO

But when I run it using C# ASP.NET, I get this error:
Syntax error converting the varchar value '53_8663_69_MU_ LSLV_HG' to a
column of data type int.

On my command.Execute NonQuery() call. Any ideas?

My ASP.NET code is below just in case . . . Thanks in advance.

--
Thanks,
Jay Allen

public string ImageName(strin g Code)
{
string Image_Name;
string connectionStrin g = CommonAppSettin gs.SqlConnectio nString;
System.Data.Sql Client.SqlConne ction connection = new
System.Data.Sql Client.SqlConne ction(connectio nString);
connection.Open ();

System.Data.Sql Client.SqlComma nd command = new
System.Data.Sql Client.SqlComma nd();
command.Connect ion = connection;

command.Command Text= "SP_IMAGE_NAME" ;
command.Command Type = CommandType.Sto redProcedure;

System.Data.Sql Client.SqlParam eter param;

param = command.Paramet ers.Add("@IMAGE _NM", SqlDbType.VarCh ar);
param.Direction = ParameterDirect ion.Output;
param.Size = 100;

param = command.Paramet ers.Add("@MAT_I D", SqlDbType.VarCh ar);
param.Direction = ParameterDirect ion.Input;
param.Size = 13;
param.Value = Code;

param = command.Paramet ers.Add("@Type" , SqlDbType.VarCh ar);
param.Direction = ParameterDirect ion.Input;
param.Size = 1;
param.Value = "1";

command.Execute NonQuery();
//return command.Paramet ers["@IMAGE_NM"].Value.ToString ();
param = command.Paramet ers["@IMAGE_NM"];
Image_Name = param.Value.ToS tring();
return Image_Name;
connection.Clos e();
}

[Leon Langleyben]

Hi Jay,
You should not return varchar variables from SP. Skip "RETURN @IMAGE_NM" line.
--
Leon Langleyben
MCSD
http://dotnetjunkies.com/WebLog/leon/
"Jay" wrote:

I hope this is the correct place to post this.

I'm using a stored procedure to simply look up and return a value from a
database. The db key is an integer, everything else is varchar. The stored
proc is:
CREATE procedure SP_IMAGE_NAME(
@MAT_ID varchar(13),
@TYPE varchar(1),
@IMAGE_NM varchar(100) OUTPUT
) as
begin

SELECT @IMAGE_NM = FileName FROM IPSAvailableIma gesFinal
WHERE (MaterialNumber = @MAT_ID) AND (type = @TYPE)
RETURN @IMAGE_NM
end
GO

But when I run it using C# ASP.NET, I get this error:
Syntax error converting the varchar value '53_8663_69_MU_ LSLV_HG' to a
column of data type int.

On my command.Execute NonQuery() call. Any ideas?

My ASP.NET code is below just in case . . . Thanks in advance.

--
Thanks,
Jay Allen

public string ImageName(strin g Code)
{
string Image_Name;
string connectionStrin g = CommonAppSettin gs.SqlConnectio nString;
System.Data.Sql Client.SqlConne ction connection = new
System.Data.Sql Client.SqlConne ction(connectio nString);
connection.Open ();

System.Data.Sql Client.SqlComma nd command = new
System.Data.Sql Client.SqlComma nd();
command.Connect ion = connection;

command.Command Text= "SP_IMAGE_NAME" ;
command.Command Type = CommandType.Sto redProcedure;

System.Data.Sql Client.SqlParam eter param;

param = command.Paramet ers.Add("@IMAGE _NM", SqlDbType.VarCh ar);
param.Direction = ParameterDirect ion.Output;
param.Size = 100;

param = command.Paramet ers.Add("@MAT_I D", SqlDbType.VarCh ar);
param.Direction = ParameterDirect ion.Input;
param.Size = 13;
param.Value = Code;

param = command.Paramet ers.Add("@Type" , SqlDbType.VarCh ar);
param.Direction = ParameterDirect ion.Input;
param.Size = 1;
param.Value = "1";

command.Execute NonQuery();
//return command.Paramet ers["@IMAGE_NM"].Value.ToString ();
param = command.Paramet ers["@IMAGE_NM"];
Image_Name = param.Value.ToS tring();
return Image_Name;
connection.Clos e();
}