473,230 Members | 4,185 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,230 software developers and data experts.

stored procedure in sqlexpress

jed
I have created this example in sqlexpress
ALTER PROCEDURE [dbo].[gettaxbracket]

@annualtax FLOAT

AS
BEGIN

SELECT begin1,end1,deductedamount,pecentageextra
FROM tax
WHERE @annualtax BETWEEN begin1 AND end1
END
can u please tell me how to gain access to the @annualtax parameter in
C#. An then i want to assign a textbox value to it.thanks

Mar 29 '07 #1
2 4052
I think you are asking about talking in the value of the annualtax via a
input box and pass that parameter to the SQL SP via your code and get teh
result back to your code right?

The answer to your question depend on what technique you used to communicate
with your database. Whether you use a datareader or dataset etc.. but in
generatl assuming that you are using SQL Helper / Data Reader approach. It
does provide a method to Execute a SqlCommand against the database providing
as a parameters.

If you google a bit you may easily find a sample code.. I cannot give any
right now since yoru question is too broad

Nirosh.

<je*@auto-soft.co.zawrote in message
news:11**********************@l77g2000hsb.googlegr oups.com...
>I have created this example in sqlexpress
ALTER PROCEDURE [dbo].[gettaxbracket]

@annualtax FLOAT

AS
BEGIN

SELECT begin1,end1,deductedamount,pecentageextra
FROM tax
WHERE @annualtax BETWEEN begin1 AND end1
END
can u please tell me how to gain access to the @annualtax parameter in
C#. An then i want to assign a textbox value to it.thanks

Mar 29 '07 #2
I copied this from http://www.codeproject.com/useritems/simplecodeasp.asp

Stored Procedures are a set of sql commands which are compiled and are
stored inside the database. Every time you execute a sql command, the
command is parsed, optimization is done and then the command is
executed. Parsing and optimization the command each time you run the
query is very expensive. To solve this we have a set of commands
collectively called as stored procedure, which are already parsed and
optimized and are executed when ever we call them. This article
describes about how to call the stored procedures through Ado.net and
how to handle the output parameters of the called stored procedures.

Initially create a object of SqlConnection class which is available in
System.Data.SqlClient namespace. You has to provide the connection
string as a parameter which includes the Data Source name, the
database name and the authentication credentials. Open the connection
using the Open() method.

SqlConnection con = new SqlConnection("Data Source= ;
initial catalog= Northwind ; User Id= ; Password=
'");

con.open();
Create the following stored procedure on the Region table in the
Northwind database which accepts two parameters and does not have any
output parameters.

CREATE PROCEDURE RegionUpdate (@RegionID INTEGER,
@RegionDescription NCHAR(50)) AS
SET NOCOUNT OFF
UPDATE Region
SET RegionDescription = @RegionDescription
Create a SqlCommand object with the parameters as the name of the
stored procedure that is to be executed and the connection object con
to which the command is to be sent for execution.

SqlCommand command = new SqlCommand("RegionUpdate",con);
Change the command objects CommandType property to stored
procedure.

command.CommandType = CommandType.StoredProcedure;
Add the parameters to the command object using the Parameters
collection and the SqlParameter class.

command.Parameters.Add(new
SqlParameter("@RegionID",SqlDbType.Int,0,"RegionID "));

command.Parameters.Add(new
SqlParameter("@RegionDescription",SqlDbType.NChar,
50,"RegionDescription"));

Specify the values of the parameters using the Value property of the
parameters

command.Parameters[0].Value=4;

command.Parameters[1].Value="SouthEast";

Excecute the stored procedure using the ExecuteNonQuery method which
returns the number of rows effected by the stored procedure.

int i=command.ExecuteNonQuery();
Now let us see how to execute stored procedures which has output
parameters and how to access the results using the output parameters.

Create the following stored procedure which has one output parameter.

ALTER PROCEDURE RegionFind(@RegionDescription NCHAR(50)
OUTPUT,
@RegionID INTEGER )AS

SELECT @RegionDescription =RegionDescription from Region
where RegionID=@RegionID

The above stored procedure accepts regionID as input parameter and
finds the RegionDescription for the RegionID input and results it as
the output parameter.

SqlCommand command1 = new SqlCommand("RegionFind",con);
command1.CommandType = CommandType.StoredProcedure;
Add the paremeters to the command1

command1.Parameters.Add(new SqlParameter
("@RegionDescription",SqlDbType.NChar ,
50,ParameterDirection.Output,false,
0,50,"RegionDescription",DataRowVersion.Default,nu ll));
command1.Parameters.Add(new SqlParameter("@RegionID" ,
SqlDbType.Int,
0 ,
"RegionID" ));
Observe that the parameter RegionDescription is added with the
ParameterDirection as Ouput.

specify the value for the input parameter RegionID.

command1.Parameters["@RegionID"].Value = 4;
Assign the UpdatedRowSource property of the SqlCommand object to
UpdateRowSource.OutputParameters to indicate that data will be
returned from this stored procedure via output parameters.

command1.UpdatedRowSource =
UpdateRowSource.OutputParameters;
Call the stored procedure and access the RegionDescription for the
RegionID 4 using the value property of the parameter.

command1.ExecuteNonQuery();
string newRegionDescription =(string)
command1.Parameters["@RegionDescription"].Value;

Close the sql connection.

con.Close();
In the same way you can call the stored procedure that returns a set
of rows by defining the parameters as appropriate and executing the
command using ExecuteReader() that is used to traverse the records
returned by the command.

Regards

http://www.auratius.co.za

Auratius

Mar 29 '07 #3

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

Similar topics

1
by: den 2005 | last post by:
Hi everybody, I created several stored procedure in a local sql server 2005 express database, now when I call/execute them in the asp.net 2.0 web page, it returns an error message of "Cannot...
10
by: J. S. EDV | last post by:
Hello, I have got a little problem with stored procedures and C#. I have got a stored procedure which should only insert something in a table. For example: ALTER PROCEDURE DBO.PROC1 AS...
0
by: Jon Paal | last post by:
error : "Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'." using VWD 2005 with sqlexpress 2005 I am trying to create membership roles and users......
2
by: jed | last post by:
I have created a stored procedure in SQLExpress management.I need to retrieve a numeric value that the stored procedure creates and use it in a C# application.Please help thanks. USE GO /******...
2
by: poops2468 | last post by:
I have a very simple Insert Stored Procedure. When I run it from the Server Explorer, it inserts the records just fine. However, when I try to call it from my form, it does not update the table. The...
4
by: smartin | last post by:
Hi, I'm having problem retrieving data from an SQL stored procedure. I tried debugging but it wont give a the reason for the error. it just throws an exception after executing cmd.ExecuteNonQuery...
3
by: leesquare | last post by:
Hello, I need some help getting output values from my stored procedures when using adodbapi. There's an example testVariableReturningStoredProcedure in adodbapitest.py, and that works for my...
3
by: cmrhema | last post by:
Hi, Kindly excuse if I am posting in the wrong place. I am using Visual Studio 2008, .net framework 3.5, asp.net , c# and sql server 2005. I am supposed to pass stored procedures from client...
2
by: qwedster | last post by:
Folk! How to programattically check if null value exists in database table (using stored procedure)? I know it's possble in the Query Analyzer (see last SQL query batch statements)? But how...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

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.