using stored procedures in .net

[rohitscripts]

could any one please help me in using the output parameters to retrieve some data from my database to a web form!!
here is a sample code:
stored procedure::

alter proc uid_availability_check
@userid varchar(25),
@tot int output
as
select * from myuserinfo where (myuserinfo.userid=@userid)
set @tot=@@rowcount

c# code::

(here con is my connection to the database)

OleDbCommand mycmd = new OleDbCommand();
mycmd.CommandType = CommandType.StoredProcedure;
mycmd.CommandText = "uid_availability_check";
mycmd.Connection = con;

OleDbParameter p1 = new OleDbParameter();
p1 = mycmd.Parameters.Add("@userid" , OleDbType.VarChar);
p1.Value = enteruserid.Text;
p1.Direction = ParameterDirection.Input;

OleDbParameter p2=new OleDbParameter();
p2.OleDbType=OleDbType.Integer;
p2.Direction= ParameterDirection.Output;
p2=mycmd.Parameters.Add("@tot", OleDbType.Integer);

con.Open();
mycmd.ExecuteNonQuery();
con.close();

label1.Text = p2.Value.ToString();

i know this is wrong....but dont know how to correct it.
and i know to do this using a data reader, without using output parameter.
so...could any one please help me in correcting this.. and tell me how to use the out parameters.

thank you

[shweta123]

Hi,

This is the right way to get the value from Out Parameter from the Procedure.
Are you getting any error in this or you just want an alternate way of doing this?

[rohitscripts]

Hi,

This is the right way to get the value from Out Parameter from the Procedure.
Are you getting any error in this or you just want an alternate way of doing this?

Hi swetha,
thank you very much for your reply to my second question too.

yes, i'm getting an error while executing this code.
the error looks like this::
Initially, when i didnt included "p2=mycmd.Parameters.Add("@tot",
OleDbType.Integer);", the out parameter stored a null value.

And when i included the above,the error is- the stored procedure
"uid_availability_check" requires another parameter "@tot" which was not
supplied.

Right now i removed the out parameter and solved this problem using data reader.

But, i would like to know how to use the out parameters.
i've tried a lot but couldn't get the result.

could you please help me by sending some sample code that u would write if
u use out parameters.
By the way....is my stored procedure right??
please correct it if its wrong..
thank you very much
cheers and regards
Rohit

[shweta123]

hi,

Whatever you have written upto Parameter p1 , I think seems to be Ok.
For Parameter p2 ,you make little change.

Dim p2 As OleDbParameter
p2 = mycmd.CreateParameter()
p2.ParameterName = "@tot"
p2.Direction = ParameterDirection.Output
p2.DbType = DbType.Int32
mycmd.Parameters.Add(p2)

mycmd.ExecuteNonQuery()

[shweta123]

hi,

Whatever you have written upto Parameter p1 , I think seems to be Ok.
For Parameter p2 ,you make little change.

Dim p2 As OleDbParameter
p2 = mycmd.CreateParameter()
p2.ParameterName = "@tot"
p2.Direction = ParameterDirection.Output
p2.DbType = DbType.Int32
mycmd.Parameters.Add(p2)

mycmd.ExecuteNonQuery()

Label1.text=p2.value

[rohitscripts]

hi swetha
thank you very much.....
your answer helped me a lot!...thanku
but i could not know the difference between (ur code n my code)

OleDbParameter p2=new OleDbParameter();
p2.OleDbType=OleDbType.Integer;
p2.Direction= ParameterDirection.Output;
p2=mycmd.Parameters.Add("@tot", OleDbType.Integer);

Dim p2 As OleDbParameter
p2 = mycmd.CreateParameter()
p2.ParameterName = "@tot"
p2.Direction = ParameterDirection.Output
p2.DbType = DbType.Int32
mycmd.Parameters.Add(p2)

could you please explain me .

And, when my output parameter is integer( above example)...your code is working.

But, when my output parameter is varchar, i'm getting the following error at runtime.
"String[2]: the Size property has an invalid size of 0."
This error is pointing -"mycmd.ExecuteNonQuery();" code line.

I'ld be very thankful if you solve this problem too

anyways thank you for your code.
cheers n regards
rohit

[laailalalaa]

hi, just had the same problem
u should specify nvarchar's size in the constructor for the SqlParameter
this should work:)