May 25, 2011

Insert Data in DB using ADO.NET

I have a Student table in DB (id (AutoIncrement),FirstName,LastName)

Below is the sample code to insert data using SqlCommand object's Execute Method

AddItemsInDatabase .aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AddItemsInDatabase.aspx.cs" Inherits="AddItemsInDatabase" %>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
     <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox><br />
     <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox><br />
     
     <asp:Button ID="btnAdd" runat="server" Text="Button" onclick="btnAdd_Click" />
     
    
    </div>
   
    </form>
</body>
</html>

AddItemsInDatabase .aspx.cs


using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;


public partial class AddItemsInDatabase : System.Web.UI.Page
{


    SqlConnection connObj = new SqlConnection();
    DataSet ds;
    SqlCommand objCommand;
    SqlDataAdapter objAdapter;
    protected void Page_Load(object sender, EventArgs e)
    {
        connObj.ConnectionString = ConfigurationManager.ConnectionStrings["DotNetTrainingConnectionString"].ToString();
    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {




        connObj.Open();
        SqlCommand comm = new SqlCommand("insert into student(FirstName,Lastname) values(@FirstName,@Lastname)", connObj);




        comm.Parameters.Add("@LastName", SqlDbType.VarChar, 255).Value = txtFirstName.Text;
        comm.Parameters.Add("@FirstName", SqlDbType.VarChar, 255).Value = txtLastName.Text;




        int result = comm.ExecuteNonQuery();
        if (result != 0)
            Response.Write(" added");
        else
            Response.Write("Error");




    }
}

Below is the DotNetTrainingConnectionString in Web.config File


<add name="DotNetTrainingConnectionString" connectionString="Data Source=.\sqlexpress;Initial Catalog=DotNetTraining;Integrated Security=True"
   providerName="System.Data.SqlClient" />

3 comments:

  1. Hi

    Thk u for the code. 2 interesting.

    I have a problem with. I have an error at this line:
    ConfigurationManager.ConnectionStrings["DotNetTrainingConnectionString"].ToString();

    The :DotNetTrainingConnectionString

    I tried to replace it with my connexionString ( Maybe I'm not replacing it with right connexion string ).

    Where could I find my connexionSTRING??

    Thx

    ReplyDelete
  2. Hi,
    Thx for the conde but,

    I have a problem at this line:ConfigurationManager.ConnectionStrings["DotNetTrainingConnectionString"].ToString();

    I don't know If I'm replacing the connexion string with the right one. Where Could I find my right connexion String

    ReplyDelete
  3. Hi
    This is the format for connection string if the DB Sever is in windows authentication mode




    Change your DB Server Name and Database Name in the above format . If your SqlSever is installed in SQLSERVER Authentication mode then use the below format

    Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;

    ReplyDelete