473,554 Members | 3,224 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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 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.

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
2068
by: Shaun Stuart | last post by:
I've got a webpage that calls some stored procedures with input variables. The procedures return recordsets and also some output variables. We're trying to get the values of the output variables. I've done this using the method I found in MSDN, as shown in the code below. The problem is that we believe doing it this way involves the use of the...
0
6683
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft Visual Basic .NET version of this article, see 308049. For a Microsoft Visual C++ .NET version of this article, see 310071. For a Microsoft Visual J#...
4
4953
by: Robin Tucker | last post by:
Hi, I'm trying to determine with my program whether or not a given database supports a given feature set. To do this I'm querying for certain stored procedures in the sysobjects table and if they are present, making the assumption the database will support the given feature. The problem is I can't find a certain stored procedure in the...
2
5441
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
0
827
by: Ranginald | last post by:
Is there way to execute a certain stored procedure based on the logged in user? Could you store the storedprocedure name using Profiles, and then pull the data when the user is logged in? Thanks!
1
1129
by: a.mustaq | last post by:
Hi All, I have some doubts regarding to caching. 1.Where cached objects are stored. 2.What is public caching and private caching. 3.Is caching user specific or application specific Please help me.
12
2234
by: Light | last post by:
Hi all, I posted this question in the sqlserver.newusers group but I am not getting any response there so I am going to try it on the fine folks here:). I inherited some legacy ASP codes in my office. The original code's backend is using the SQL Server 2000 and I am testing to use it on the Express edition. And I run into the following...
1
6117
by: sheenaa | last post by:
Hello Members, I m creating my application forms in ASP.Net 2005 C# using the backend SQL Server 2005. What i have used on forms :: ? On my first form i have used some label,textboxs,dropdownlists,radiobutton and checkbox asp standard controls. On the click event of the command button the data gets stored into the...
12
4691
by: brwalias | last post by:
Hi, using .net 2 sql server 2005 Here is my situation: I'm passing a variable in the url from a selection on Page A and need to display the results on the Results page be based on that variable in the url. I would like that variable to trigger a stored and
4
1232
by: Chris | last post by:
Hi, i have a formview in Insert mode. when a new record is inserted, the text of a textbox becomes: "ok". Instead of using the ItemInserted or ItemInserting, i use my own procedure like this: Protected Sub myproc((ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArgs) TextBox1.Text = "ok"
0
7775
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8008
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7526
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7854
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6113
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5411
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3525
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1992
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1107
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.