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_availabilit y_check
@userid varchar(25),
@tot int output
as
select * from myuserinfo where (myuserinfo.use rid=@userid)
set @tot=@@rowcount

c# code::

(here con is my connection to the database)

OleDbCommand mycmd = new OleDbCommand();
mycmd.CommandTy pe = CommandType.Sto redProcedure;
mycmd.CommandTe xt = "uid_availabili ty_check";
mycmd.Connectio n = con;

OleDbParameter p1 = new OleDbParameter( );
p1 = mycmd.Parameter s.Add("@userid" , OleDbType.VarCh ar);
p1.Value = enteruserid.Tex t;
p1.Direction = ParameterDirect ion.Input;

OleDbParameter p2=new OleDbParameter( );
p2.OleDbType=Ol eDbType.Integer ;
p2.Direction= ParameterDirect ion.Output;
p2=mycmd.Parame ters.Add("@tot" , OleDbType.Integ er);

con.Open();
mycmd.ExecuteNo nQuery();
con.close();

label1.Text = p2.Value.ToStri ng();

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.Param eters.Add("@tot ",
OleDbType.Integ er);", the out parameter stored a null value.

And when i included the above,the error is- the stored procedure
"uid_availabili ty_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.CreatePar ameter()
p2.ParameterNam e = "@tot"
p2.Direction = ParameterDirect ion.Output
p2.DbType = DbType.Int32
mycmd.Parameter s.Add(p2)

mycmd.ExecuteNo nQuery()

[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.CreatePar ameter()
p2.ParameterNam e = "@tot"
p2.Direction = ParameterDirect ion.Output
p2.DbType = DbType.Int32
mycmd.Parameter s.Add(p2)

mycmd.ExecuteNo nQuery()

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=Ol eDbType.Integer ;
p2.Direction= ParameterDirect ion.Output;
p2=mycmd.Parame ters.Add("@tot" , OleDbType.Integ er);

Dim p2 As OleDbParameter
p2 = mycmd.CreatePar ameter()
p2.ParameterNam e = "@tot"
p2.Direction = ParameterDirect ion.Output
p2.DbType = DbType.Int32
mycmd.Parameter s.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.ExecuteN onQuery();" 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:)