Stored procedure->ASP.NET application

[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
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.776 / Virus Database: 523 - Release Date: 12.10.2004 a.

[Ansil MCAD]

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" wrote:

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
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.776 / Virus Database: 523 - Release Date: 12.10.2004 a.

[fd123456]

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" <An*******@discussions.microsoft.com> wrote in message news:<EB**********************************@microso ft.com>...

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" wrote:

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
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.776 / Virus Database: 523 - Release Date: 12.10.2004 a.

[Viktor Popov]

Thank you for the replies!
It works now:)

Regards,

Viktor



"Ansil MCAD" <An*******@discussions.microsoft.com> wrote in message
news:EB**********************************@microsof t.com...

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" wrote:

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
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.776 / Virus Database: 523 - Release Date: 12.10.2004 a.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.776 / Virus Database: 523 - Release Date: 12.10.2004 a.