Wednesday, September 1, 2010

Insert record in database using stored procedure in asp.net

In this article am explaining very simpe thing insert record in database using stored procedure,that will very help full for entry level programmer.

web.config
 <connectionStrings>  
<add name="conString" connectionString="Data Source=.\SQLEXPRESS; database=Northwind;Integrated Security=true"/>:
</connectionStrings>


Used Stored procedure
CREATE PROCEDURE [dbo].[AddUser]
(
@FName varchar(50),
@LName varchar(50),
@DateOfBirth datetime,
@City varchar(50),
@State varchar(50)
)
AS
BEGIN

SET NOCOUNT ON;
INSERT INTO UserDetails (FName, LName, DateOfBirth, City, State)
VALUES (@FName, @LName, @DateOfBirth, @City, @State)
END


C# code

 String ConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;  
SqlConnection con = new SqlConnection(ConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "AddUser";
cmd.Parameters.Add("@FName",SqlDbType.VarChar).Value = txtFName.Text.Trim();
cmd.Parameters.Add("@LName", SqlDbType.VarChar).Value = txtLName.Text.Trim();
cmd.Parameters.Add("@DateOfBirth", SqlDbType.DateTime).Value = txtDOB.Text.Trim();
cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text.Trim();
cmd.Parameters.Add("@State", SqlDbType.VarChar).Value = txtState.Text.Trim();
cmd.Connection = con;
try
{
con.Open();
cmd.ExecuteNonQuery();
lblMessage.Text = "Record inserted successfully";
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}

33 comments:

  1. The code just work fine: Nice code.

    ReplyDelete
  2. poor.....................code,,,,,,worst i ever seen............

    ReplyDelete
  3. nice work.........

    ReplyDelete
  4. Good coding,
    one who is saying worse, doesn't know OOPs concept

    ReplyDelete
  5. good code,
    it's really healpfull ,
    Give me complete coding All DML operations

    ReplyDelete
  6. its really nice one

    ReplyDelete
  7. can you post the code for searching the records....

    ReplyDelete
  8. it is very helpd me

    ReplyDelete
  9. article flows really awesome....thanks for helping biginers ali.rameez72@yahoo.com

    ReplyDelete
  10. very good code.....keep it up help to helpful people..... thank u so much......god bless u.....

    ReplyDelete
  11. the sp seems to have these errors
    -----------------------------------
    Msg 102, Level 15, State 1, Procedure write_categs, Line 2
    Incorrect syntax near '{'.
    Msg 137, Level 15, State 2, Procedure write_categs, Line 10
    Must declare the scalar variable "@cat1".

    ReplyDelete
  12. thanks....this code helps me

    ReplyDelete
  13. Thanks .. Its working & helps for my exam preparation ..

    ReplyDelete
  14. it is working fine....thanku

    ReplyDelete
  15. nice post..you can visit following link to get code of storing data in database using asp.net c#.

    Click Here

    ReplyDelete
  16. nice post..you can visit following link to get code of how to store data in database using stored procedure and 3-tier architecture.100% verified code.

    Click Here

    ReplyDelete
  17. suppose in stored procedure contain some int values like salary empid then got error plese pass values like that how?

    ReplyDelete