Stored procedure->ASP.NET application

  • Thread starter Thread starter Viktor Popov
  • Start date Start date
V

Viktor Popov

Hi,

I would like to ask you do you know how to return a resultset and int value
from Stored Procedure.
If we have a table
Teachers
=========
ID INT PK
NAME VARCHAR(25)
ADDR VARCHAR(75)

I would like to write a SP which must return the @COUNT of all teachers in
the table Teachers and
also the resultset from SELECT NAME FROM TEACHERS WHERE NAME="ADAM". Do you
know how to write this SP?
It must b e something like this:
CREATE STORED PROCEDURE
@NAME VARCHAR(25),
@COUNT INT OUTPUT
AS
SELECT @COUNT=COUNT(*) FROM TEACHERS
SELECT ADDR FROM TEACHERS WHERE NAME='ADAM'
return @COUNT

In the ASP.NET/C# application I do this:
dad.SelectCommand.Parameters.Add(new SqlParameter("@NAME",
SqlDbType.VarChar,25));
dad.SelectCommand.Parameters["@USRNAM"].Value =
Session["usrName"].ToString();
dad.SelectCommand.Parameters.Add(new SqlParameter("@Count", SqlDbType.Int));
dad.SelectCommand.Parameters["@Count"].Direction =
ParameterDirection.Output;
int count = (int)dad.SelectCommand.Parameters["@Count"].Value;


But there is error: Null Reference in the last line.
How can I catch the resultset from the sql query and also the OUTPUT VALUE?

Thank you!
Viktor
 
hi

hope this will work

CREATE PROCEDURE spGetTeachers
@NAME VARCHAR(25),
@COUNT INT OUTPUT
AS
SET NOCOUNT ON
SELECT @COUNT=COUNT(*) FROM TEACHERS
SELECT ADDR FROM TEACHERS WHERE NAME=@NAME
GO



cnn.Open();
SqlDataAdapter cmd=new SqlDataAdapter("spGetTeachers",cnn);
cmd.SelectCommand.CommandType =CommandType.StoredProcedure;
SqlParameter param=new SqlParameter ("@NAME",SqlDbType.VarChar,50);
param.Value ="Adams";

SqlParameter param2=new SqlParameter ("@COUNT",SqlDbType.Int,8);
param2.Direction =ParameterDirection.Output ;
param2.Value =-1;


cmd.SelectCommand.Parameters.Add(param) ;
cmd.SelectCommand.Parameters.Add(param2) ;

DataSet ds=new DataSet ();
cmd.Fill(ds);

param2.Value will give the count


regards
Ansil
 
Hi Viktor,

Please also note that using the Fill keyword will return the number of
affected rows : graphically create a DataAdapter, and a Dataset
modeled after your teachers table, and then :

dsTeachers myTeachers = new dsTeachers();
int NumberOfAffectedRecords = daTeachers.Fill(myTeachers);



HTH,

Michel


Ansil MCAD said:
hi

hope this will work

CREATE PROCEDURE spGetTeachers
@NAME VARCHAR(25),
@COUNT INT OUTPUT
AS
SET NOCOUNT ON
SELECT @COUNT=COUNT(*) FROM TEACHERS
SELECT ADDR FROM TEACHERS WHERE NAME=@NAME
GO



cnn.Open();
SqlDataAdapter cmd=new SqlDataAdapter("spGetTeachers",cnn);
cmd.SelectCommand.CommandType =CommandType.StoredProcedure;
SqlParameter param=new SqlParameter ("@NAME",SqlDbType.VarChar,50);
param.Value ="Adams";

SqlParameter param2=new SqlParameter ("@COUNT",SqlDbType.Int,8);
param2.Direction =ParameterDirection.Output ;
param2.Value =-1;


cmd.SelectCommand.Parameters.Add(param) ;
cmd.SelectCommand.Parameters.Add(param2) ;

DataSet ds=new DataSet ();
cmd.Fill(ds);

param2.Value will give the count


regards
Ansil

Viktor Popov said:
Hi,

I would like to ask you do you know how to return a resultset and int value
from Stored Procedure.
If we have a table
Teachers
=========
ID INT PK
NAME VARCHAR(25)
ADDR VARCHAR(75)

I would like to write a SP which must return the @COUNT of all teachers in
the table Teachers and
also the resultset from SELECT NAME FROM TEACHERS WHERE NAME="ADAM". Do you
know how to write this SP?
It must b e something like this:
CREATE STORED PROCEDURE
@NAME VARCHAR(25),
@COUNT INT OUTPUT
AS
SELECT @COUNT=COUNT(*) FROM TEACHERS
SELECT ADDR FROM TEACHERS WHERE NAME='ADAM'
return @COUNT

In the ASP.NET/C# application I do this:
dad.SelectCommand.Parameters.Add(new SqlParameter("@NAME",
SqlDbType.VarChar,25));
dad.SelectCommand.Parameters["@USRNAM"].Value =
Session["usrName"].ToString();
dad.SelectCommand.Parameters.Add(new SqlParameter("@Count", SqlDbType.Int));
dad.SelectCommand.Parameters["@Count"].Direction =
ParameterDirection.Output;
int count = (int)dad.SelectCommand.Parameters["@Count"].Value;


But there is error: Null Reference in the last line.
How can I catch the resultset from the sql query and also the OUTPUT VALUE?

Thank you!
Viktor
Click to expand...
Click to expand...
 
Thank you for the replies!
It works now:)

Regards,

Viktor







Ansil MCAD said:
hi

hope this will work

CREATE PROCEDURE spGetTeachers
@NAME VARCHAR(25),
@COUNT INT OUTPUT
AS
SET NOCOUNT ON
SELECT @COUNT=COUNT(*) FROM TEACHERS
SELECT ADDR FROM TEACHERS WHERE NAME=@NAME
GO



cnn.Open();
SqlDataAdapter cmd=new SqlDataAdapter("spGetTeachers",cnn);
cmd.SelectCommand.CommandType =CommandType.StoredProcedure;
SqlParameter param=new SqlParameter ("@NAME",SqlDbType.VarChar,50);
param.Value ="Adams";

SqlParameter param2=new SqlParameter ("@COUNT",SqlDbType.Int,8);
param2.Direction =ParameterDirection.Output ;
param2.Value =-1;


cmd.SelectCommand.Parameters.Add(param) ;
cmd.SelectCommand.Parameters.Add(param2) ;

DataSet ds=new DataSet ();
cmd.Fill(ds);

param2.Value will give the count


regards
Ansil

Viktor Popov said:
Hi,

I would like to ask you do you know how to return a resultset and int value
from Stored Procedure.
If we have a table
Teachers
=========
ID INT PK
NAME VARCHAR(25)
ADDR VARCHAR(75)

I would like to write a SP which must return the @COUNT of all teachers in
the table Teachers and
also the resultset from SELECT NAME FROM TEACHERS WHERE NAME="ADAM". Do you
know how to write this SP?
It must b e something like this:
CREATE STORED PROCEDURE
@NAME VARCHAR(25),
@COUNT INT OUTPUT
AS
SELECT @COUNT=COUNT(*) FROM TEACHERS
SELECT ADDR FROM TEACHERS WHERE NAME='ADAM'
return @COUNT

In the ASP.NET/C# application I do this:
dad.SelectCommand.Parameters.Add(new SqlParameter("@NAME",
SqlDbType.VarChar,25));
dad.SelectCommand.Parameters["@USRNAM"].Value =
Session["usrName"].ToString();
dad.SelectCommand.Parameters.Add(new SqlParameter("@Count", SqlDbType.Int));
dad.SelectCommand.Parameters["@Count"].Direction =
ParameterDirection.Output;
int count = (int)dad.SelectCommand.Parameters["@Count"].Value;


But there is error: Null Reference in the last line.
How can I catch the resultset from the sql query and also the OUTPUT VALUE?

Thank you!
Viktor
Click to expand...
Click to expand...
 
Back
Top