.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal

Post New Resource Bookmark and Share   

My first asp application with stored procedures

Posted By:Gowthammanju       Posted Date: June 29, 2011    Points: 25    Category:    URL: http://www.dotnetspark.com  

These article explains you about adding of datas in to database using stored procedures.

What is stored procedures?

A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, StoredProc, or SP) are actually stored in the database data dictionary.

Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Extensive or complex processing that requires the execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures, by executing one stored procedure from within another.

Benefits of using stored procedures:

The various benefits are

Reduced network usage between clients and servers

Enhanced hardware and software capabilities

Improved security

Reduced development cost and increased reliability

Centralized security, administration, and maintenance for common routines

Stored procedures are created with the CREATE PROCEDURE statement.

The syntax is shown below.

CREATE PROCEDURE procedure_name
@param data_type = default_value,
@param data_type = default_value,
@param data_type = default_value
-- statements for procedure here

Dropping Stored Procedures

DROP PROCEDURE stored_procedure_name

Just follow this steps u will find the correct way

open the sql server  2005 or 2008

click the new query tab

copy and paste below coding for creation of table as

use jobwebsite
create table register(regid int identity(1,1),firstname varchar(100),lastname varchar(50),emailid varchar(50),mobileno varchar(20),qualification varchar(30),username varchar(10),pwd varchar(10),usertype varchar(10),regdte datetime,primary key (regid))
create table logininf(logid int identity(1,1),memregid int references register(regid),lusername varchar(100),lpwd varchar(50),lusertype varchar(50),primary key(lusername) )
create table autoregno(aid int identity(1,1),anumber varchar(20),amemid int references register(regid),primary key(anumber))

Now for creating procedures copy the below codings as

//creating procedure with name

create proc proc_register

//declaring procedure parameters as datatype

@frstnme varchar(100),@lstnme varchar(50),@emailid varchar(50),@mobileno varchar(10),@qualify varchar(30),@usernme varchar(10),@pwd varchar(10),@usertype varchar(10),@dte datetime,@gender varchar(8),@autogenid varchar(20)
as begin
declare @error int,@memberid int //declaring variables inside procedures
begin tran
//insert/update statements

insert into register values
//for retriving particular values

set @memberid=(select regid from register where username=@usernme)

insert into logininf values(@memberid,@usernme,@pwd,@usertype) insert into autoregno values(@autogenid,@memberid,GETDATE())/
//stements for printing errors

set @error=@@ERROR if @error<>0 begin goto errors end
//verifies whether transaction is suces or not

commit tran
//passing errors values

if @error<>0 begin rollback tran end return @error end go
now in design page of aspx inside the form tag use coding as

choose list number
  last name
  email id
  mobile number
  usertype Admin Member

next step is to call the procedures in to ur coding page as

           //calling of procedure name which was created above

cmd = new SqlCommand("proc_register", con);
// basic structure

cmd.CommandType = CommandType.StoredProcedure;
//passing variables to procedures as
// //cmd.Parameters.Add("parametername", SqlDbType.datatype).Value = textboxvalue;

cmd.Parameters.Add("@frstnme", SqlDbType.VarChar).Value = textbox1.text; cmd.Parameters.Add("@lstnme", SqlDbType.VarChar).Value = textbox2.text; cmd.Parameters.Add("@emailid", SqlDbType.VarChar).Value =textbox3.text;
cmd.Parameters.Add("@mobileno", SqlDbType.VarChar).Value =tetxbox4.text; cmd.Parameters.Add("@qualify", SqlDbType.VarChar).Value =textbox5.text; cmd.Parameters.Add("@usernme", SqlDbType.VarChar).Value =textbox6.text; cmd.Parameters.Add("@pwd", SqlDbType.VarChar).Value = textbox7.text; cmd.Parameters.Add("@usertype", SqlDbType.VarChar).Value =textbox8.text;
//to add date time

DateTime dt1 = Convert.ToDateTime(r9); cmd.Parameters.Add("@dte", SqlDbType.DateTime).Value = dt1; cmd.Parameters.Add("@gender", SqlDbType.VarChar).Value = r10; cmd.Parameters.Add("@autogenid", SqlDbType.VarChar).Value = r11; con.Open(); if (cmd.ExecuteNonQuery() > 0) { result = "yes"; } else { result = "no"; } } catch (Exception ex) { error = ex.Message; } finally { con.Close(); }
hence finally run the program 

u will find the solution..

thats it



Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend