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 IPSAvailableImagesFinal
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.ExecuteNonQuery() call. Any ideas?

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

--
Thanks,
Jay Allen

public string ImageName(string Code)
{
string Image_Name;
string connectionString = CommonAppSettings.SqlConnectionString;
System.Data.SqlClient.SqlConnection connection = new
System.Data.SqlClient.SqlConnection(connectionStri ng);
connection.Open();

System.Data.SqlClient.SqlCommand command = new
System.Data.SqlClient.SqlCommand();
command.Connection = connection;

command.CommandText= "SP_IMAGE_NAME";
command.CommandType = CommandType.StoredProcedure;

System.Data.SqlClient.SqlParameter param;

param = command.Parameters.Add("@IMAGE_NM", SqlDbType.VarChar);
param.Direction = ParameterDirection.Output;
param.Size = 100;

param = command.Parameters.Add("@MAT_ID", SqlDbType.VarChar);
param.Direction = ParameterDirection.Input;
param.Size = 13;
param.Value = Code;

param = command.Parameters.Add("@Type", SqlDbType.VarChar);
param.Direction = ParameterDirection.Input;
param.Size = 1;
param.Value = "1";

command.ExecuteNonQuery();
//return command.Parameters["@IMAGE_NM"].Value.ToString();
param = command.Parameters["@IMAGE_NM"];
Image_Name = param.Value.ToString();
return Image_Name;
connection.Close();
}

[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 IPSAvailableImagesFinal
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.ExecuteNonQuery() call. Any ideas?

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

--
Thanks,
Jay Allen

public string ImageName(string Code)
{
string Image_Name;
string connectionString = CommonAppSettings.SqlConnectionString;
System.Data.SqlClient.SqlConnection connection = new
System.Data.SqlClient.SqlConnection(connectionStri ng);
connection.Open();

System.Data.SqlClient.SqlCommand command = new
System.Data.SqlClient.SqlCommand();
command.Connection = connection;

command.CommandText= "SP_IMAGE_NAME";
command.CommandType = CommandType.StoredProcedure;

System.Data.SqlClient.SqlParameter param;

param = command.Parameters.Add("@IMAGE_NM", SqlDbType.VarChar);
param.Direction = ParameterDirection.Output;
param.Size = 100;

param = command.Parameters.Add("@MAT_ID", SqlDbType.VarChar);
param.Direction = ParameterDirection.Input;
param.Size = 13;
param.Value = Code;

param = command.Parameters.Add("@Type", SqlDbType.VarChar);
param.Direction = ParameterDirection.Input;
param.Size = 1;
param.Value = "1";

command.ExecuteNonQuery();
//return command.Parameters["@IMAGE_NM"].Value.ToString();
param = command.Parameters["@IMAGE_NM"];
Image_Name = param.Value.ToString();
return Image_Name;
connection.Close();
}