ADO.NET error

[Guest]

Maybe someone can see what I am missing. I have a class making a call to a
stored procedure:

cmd.CommandText = "spSubjectCodesUpdate";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@subjectcode", SqlDbType.VarChar, 4, subjectCode);
cmd.Parameters["@subjectcode"].Direction = ParameterDirection.Input;
cmd.Parameters.Add("@subjectcode_desc", SqlDbType.VarChar, 60,
subjectCode_desc);
cmd.Parameters["@subjectcode_desc"].Direction = ParameterDirection.Input;
cmd.Parameters.Add("@active", SqlDbType.VarChar, 1,active);
cmd.Parameters["@active"].Direction = ParameterDirection.Input;
cmd.Parameters.Add("@retval", SqlDbType.VarChar, 250,null);
cmd.Parameters["@retval"].Direction = ParameterDirection.InputOutput;
conn.Open();
string result = Convert.ToString(cmd.ExecuteScalar());

When I run this, I get an unhandled exception:
Procedure or Function 'spSubjectCodesUpdate' expects parameter
'@subjectcode', which was not supplied.

The profiler only says this:
declare @p4 varchar(250)
set @p4=default
exec spSubjectCodesUpdate
@subjectcode=default,@subjectcode_desc=default,@active=default,@retval=@p4
output
select @p4

Any ideas?

Thanks,

 

[Darren Kopp]

What about this

cmd.CommandText = "spSubjectCodesUpdate";
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@subjectcode", SqlDbType.VarChar, 4);
cmd.Parameters["@subjectcode"].Value = "<subjectcode>";

cmd.Parameters.Add("@subjectcode_desc", SqlDbType.VarChar, 60);
cmd.Parameters["@subjectcode_desc"].Value = "<description>";

cmd.Parameters.Add("@active", SqlDbType.VarChar, 1);
cmd.Parameters["@active"].Value = "<active>";

SqlParameter return = new SqlParameter("@retval", SqlDbType.VarChar,
250);
return.Direction = ParameterDirection.Output;
cmd.Parameters.Add(return);

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

string result = return.ToString();

I don't understand why you would set the return value = to @p4 and then
select @p4. So I kinda just left that out, so the sproc would need to
be changed appropriately. I also dropped the adding of the "column" to
the sql parameter.

HTH,
Darren Kopp
http://blog.secudocs.com/

 

[Guest]

Thanks Darren,

Yes, the profiler information was confusing, because I did not select @p4.

I found that I could get my code to work by assigning the parameter
information of each parameter to an instance of SQLParameter, and then adding
the instance to the command object one at a time--cmd.add(param).

I like your handling of the return value, which is more readable than mine.
Thanks for suggesting it.

 

[Darren Kopp]

Glad to hear you get it to work.

Happy .NETing,
Darren Kopp
http://blog.secudocs.com/