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.SelectComma nd.Parameters.A dd(new SqlParameter("@ NAME",
SqlDbType.VarCh ar,25));
dad.SelectComma nd.Parameters["@USRNAM"].Value =
Session["usrName"].ToString();
dad.SelectComma nd.Parameters.A dd(new SqlParameter("@ Count", SqlDbType.Int)) ;
dad.SelectComma nd.Parameters["@Count"].Direction =
ParameterDirect ion.Output;
int count = (int)dad.Select Command.Paramet ers["@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.SelectComma nd.CommandType =CommandType.St oredProcedure;
SqlParameter param=new SqlParameter ("@NAME",SqlDbT ype.VarChar,50) ;
param.Value ="Adams";

SqlParameter param2=new SqlParameter ("@COUNT",SqlDb Type.Int,8);
param2.Directio n =ParameterDirec tion.Output ;
param2.Value =-1;
cmd.SelectComma nd.Parameters.A dd(param) ;
cmd.SelectComma nd.Parameters.A dd(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.SelectComma nd.Parameters.A dd(new SqlParameter("@ NAME",
SqlDbType.VarCh ar,25));
dad.SelectComma nd.Parameters["@USRNAM"].Value =
Session["usrName"].ToString();
dad.SelectComma nd.Parameters.A dd(new SqlParameter("@ Count", SqlDbType.Int)) ;
dad.SelectComma nd.Parameters["@Count"].Direction =
ParameterDirect ion.Output;
int count = (int)dad.Select Command.Paramet ers["@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 NumberOfAffecte dRecords = daTeachers.Fill (myTeachers);

HTH,

Michel
"Ansil MCAD" <An*******@disc ussions.microso ft.com> wrote in message news:<EB******* *************** ************@mi crosoft.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.SelectComma nd.CommandType =CommandType.St oredProcedure;
SqlParameter param=new SqlParameter ("@NAME",SqlDbT ype.VarChar,50) ;
param.Value ="Adams";

SqlParameter param2=new SqlParameter ("@COUNT",SqlDb Type.Int,8);
param2.Directio n =ParameterDirec tion.Output ;
param2.Value =-1;
cmd.SelectComma nd.Parameters.A dd(param) ;
cmd.SelectComma nd.Parameters.A dd(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.SelectComma nd.Parameters.A dd(new SqlParameter("@ NAME",
SqlDbType.VarCh ar,25));
dad.SelectComma nd.Parameters["@USRNAM"].Value =
Session["usrName"].ToString();
dad.SelectComma nd.Parameters.A dd(new SqlParameter("@ Count", SqlDbType.Int)) ;
dad.SelectComma nd.Parameters["@Count"].Direction =
ParameterDirect ion.Output;
int count = (int)dad.Select Command.Paramet ers["@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*******@disc ussions.microso ft.com> wrote in message
news:EB******** *************** ***********@mic rosoft.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.SelectComma nd.CommandType =CommandType.St oredProcedure;
SqlParameter param=new SqlParameter ("@NAME",SqlDbT ype.VarChar,50) ;
param.Value ="Adams";

SqlParameter param2=new SqlParameter ("@COUNT",SqlDb Type.Int,8);
param2.Directio n =ParameterDirec tion.Output ;
param2.Value =-1;
cmd.SelectComma nd.Parameters.A dd(param) ;
cmd.SelectComma nd.Parameters.A dd(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.SelectComma nd.Parameters.A dd(new SqlParameter("@ NAME",
SqlDbType.VarCh ar,25));
dad.SelectComma nd.Parameters["@USRNAM"].Value =
Session["usrName"].ToString();
dad.SelectComma nd.Parameters.A dd(new SqlParameter("@ Count", SqlDbType.Int)) ; dad.SelectComma nd.Parameters["@Count"].Direction =
ParameterDirect ion.Output;
int count = (int)dad.Select Command.Paramet ers["@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.