469,338 Members | 8,430 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,338 developers. It's quick & easy.

Stored procedure->ASP.NET application

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.
Nov 16 '05 #1
3 1981
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.

Nov 16 '05 #2
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.

Nov 16 '05 #3
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.
Nov 16 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Dino L. | last post: by
reply views Thread by Ranginald | last post: by
1 post views Thread by a.mustaq | last post: by
12 posts views Thread by brwalias | last post: by
4 posts views Thread by Chris | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.