Hi all,
Here is some information which could help people who want to create stored procedures and execute them in their program.
You can create stored procedures in two ways: - Through front end
- or writing procedure from backend SQLServer.
Back End
First we'll cover how to write a stored procedure in back end.
The following example is how to create a store procedure for MSSql Server: - Open enterprise manager,
- Expand the SQL Server Group,
- Expand the Server,
- Select "Databases" folder,
- Expand the database the store procedure will be written for,
- Right click "Stored Procedures",
- Select "New Stored Procedure..."
This will open a dialogue box that will let you create a new Stored Procedure.
In this dialogue box you will enter the commands that will be executed when this store procedure is used.
The following is an example of a store procedure that will add an entry into the "Student" table: -
CREATE PROCEDURE AddStudent @Sno VARCHAR(10),@Sname VARCHAR(20),@Saddress VARCHAR(50)
-
AS
-
Begin
-
INSERT INTO STUDENT(Sno ,Sname ,Saddress ) values (@Sname ,@Sname,@Saddress)
-
End
In this example, "AddStudent " is the procedure name and the parameters needed to execute procedure are: "@Sno", "@Sname", "@Saddress" . "Sno","Sname"," Saddress" are the column names for student table. Using The Store Procedure In Code
After you create your store procedure in your database, you can use it in your code.
The following is an example of how to store student information into the database (using the store procedure created in the steps above) when the "Savebutton " is clicked. -
Private Sub SaveButton_Click() Handles SaveButton.Click
-
Dim conn As SqlConnection = New SqlConnection("data Source=yourserver/systemno;uid="userID";pwd="password";initial catalog=dbname")
-
Try
-
conn.Open()
-
Dim strcmd As SqlCommand = New SqlCommand("stu", conn)
-
strcmd.CommandType = CommandType.StoredProcedure
-
strcmd.Parameters.Add("@sno", TextBox1.Text)
-
strcmd.Parameters.Add("@sname", TextBox2.Text)
-
strcmd.Parameters.Add("@saddress", TextBox3.Text)
-
strcmd.ExecuteNonQuery()
-
conn.Close()
-
Catch ex As Exception
-
-
End Try
-
End Sub
-
End Class
-
This code will take the values from asp page/form and then stored the values in backend SQLDB.
Hope this will help you.
Jyo!
9 8973
hi ,
ya this is good method to writ stored procedure
hi ,
ya this is good method to writ stored procedure
Thanks! For your comment
Frinavale 9,735
Recognized Expert Moderator Expert
Hi jyothi1105!
This is a great post.
Thank you so much for providing us with this resource!
-Frinny
RedSon 5,000
Recognized Expert Expert
Hi jyothi1105!
This is a great post.
Thank you so much for providing us with this resource!
-Frinny
Frin,
Do you want to format this original post a little better and put it in "Frinny's tip o' the week" thread and we can start a sticky thread for all your (and others') tips o' the week?
Frinavale 9,735
Recognized Expert Moderator Expert
Frin,
Do you want to format this original post a little better and put it in "Frinny's tip o' the week" thread and we can start a sticky thread for all your (and others') tips o' the week?
This sounds like a great idea!
Great!
Thank you for posting your codes and knowledge. This will help us..
hi
like this stored procedure concepts...
hw can we store the datas into MS ACCESS files...
and hw can we retrive that.. using c#.....
Frinavale 9,735
Recognized Expert Moderator Expert
hi
like this stored procedure concepts...
hw can we store the datas into MS ACCESS files...
and hw can we retrive that.. using c#.....
Hi Prabur,
You are going to have to use ADO.NET to connect to your MS Access database files using C#. After you have researched ADO.NET and attempted to solve your problem, please post any question or problems in the .NET forum to receive help.
Cheers!
-Frinny
Really very usefull resource
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Dim St Thomas |
last post by:
I am a developer working on a database client program. I am testing this
program on a Windows XP machine (1.5 GHz AMD chip, 480 Mb RAM, 60 Gb disk)
This machine has Oracle 9.2.0.1.0 and RedBrick database software installed.
I am testing the software by creating small test databases.
If I create an Oracle database using the Database Configuration Assistant,
it takes forever just to create the database. If I choose to create a
new database...
|
by: Robin Tucker |
last post by:
Hi there,
I have a database on my test machine that will need to be installed on users
machines. I would like to create the database with the given schema on the
users machine and also with some suitable default values in the tables. I
note that although I can script the schema so that re-creating the structure
of the database is simple on the users machine, I cannot script the contents
of the tables also (automatically). What I would...
|
by: Kent Lewandowski |
last post by:
hi all,
Recently I wrote some stored procedures using java jdbc code
(admittedly my first stab) and then tried to implement the same within
java packages (for code reuse). I encountered problems doing this.
I wanted to implemented a generic "Helper" class like this:
/**
* Helper
|
by: billmiami2 |
last post by:
Perhaps many of you MS Access fanatics already know this, but it seems
that stored procedures and views are possible in Jet. I thought I
would leave this message just in case it would help anyone.
I discovered this the other day while doing some experiments with ADO
and ADO.NET. Basically, I wanted to run a stored MS Access query with
parameters using the syntax
Execute MyProcedure @Param1, @Param2...
|
by: Tim Marshall |
last post by:
I was following the thread "Re: Access Treeview - Is it Safe Yet?" with
interest and on reading the post describing Lauren Quantrell's
SmartTree, I've run into something I don't understand: Stored
Procedures. I thought stored pricedures were an Oracle/MS SQL Server
thing and don't know how they work with Access Jet. I've looked at some
of the help on stored procedures in A2003, but really don't understand
what's going on.
Can someone...
| |
by: mooreit |
last post by:
The purpose for my questions is accessing these technologies from
applications. I develop both applications and databases. Working with
Microsoft C#.NET and Microsoft SQL Server 2000 Production and 2005 Test
Environments.
What is the purpose of a view if I can just copy the vode from a view
and put it into a stored procedure?
Should I be accessing views from stored procedures?
|
by: Cuculain |
last post by:
Anyone had problems with this? The Management Studio just saves my attempts to create stored procedures as query files and doesn't add them to the list of stored procedures.
|
by: Andy |
last post by:
Hi guys
I having a problem creating a report in Access 2003 project talking to
a SQL database through and ODBC connect.
After hours of trying things from Access Help, MSDN and Google I still
can't get it working.
I have a query defined (view) and want the end user to put in a start
date and end date to filter a report.
|
by: Quish |
last post by:
Hey
I am creating a database application that is accessed through a .NET front end.
What I want to do is run a SQL script that will create my DB, create my indexes and enforce my constraints (all of which I have done) but I also want to create my stored procedures in the same script also.
When I merge all my stored procedures (about 16) into one file and run it in SQL Query Analyser I get multiple errors but the one that is coursing me...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |