Tuesday, 7 February 2012

Add Record to Database using Empty Template and Footer Template and in gridview serch data through Autocomplete Extender

Source Code
 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="AutoCompleteInGridView .aspx.cs" Inherits="AutoCompleteInGridView_" %>

<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="asp" %>

<!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>
    <style type="text/css">
        .style1
        {
            width: 100%;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
     <asp:ScriptManager ID="ScriptManager1" runat="server">
    </asp:ScriptManager>
    <div>
  
        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
                <table class="style1">
                    <tr>
                        <td>
                            <asp:GridView ID="gv" runat="server" AllowPaging="True"
                                AutoGenerateColumns="False"
                                onselectedindexchanged="gv_SelectedIndexChanged" ShowFooter="True" >
                          
                                <Columns>
                                    <asp:TemplateField HeaderText="Name">
                                      <ItemTemplate>
                                       
                                              <asp:TextBox ID="TxtName" runat="server"  Enabled="true" Visible="true"></asp:TextBox>                                       
                                           <asp:AutoCompleteExtender ID="TxtName_AutoCompleteExtender" runat="server"
                                               DelimiterCharacters="" Enabled="True" ServiceMethod="GetName" MinimumPrefixLength="1" ServicePath="" TargetControlID="TxtName">
                                           </asp:AutoCompleteExtender>
                                        </ItemTemplate>                                     
                                   </asp:TemplateField>
                                   <asp:TemplateField HeaderText="Roll">
                                   <ItemTemplate>
                                   <asp:TextBox ID="Txtroll" runat="server" Text='<%# Eval("roll") %>' Enabled="true" Visible="true"></asp:TextBox>                                       
                                   </ItemTemplate>
                                 
                                   </asp:TemplateField>
                                  <asp:TemplateField HeaderText="Name">
                                    
                                      <ItemTemplate>
                                          <asp:Label ID="LblRollno" runat="server" Text='<%# Eval("roll") %>' Visible="false"></asp:Label>
                                              <asp:TextBox ID="TxtName1" runat="server" Text='<%# Eval("Name") %>' Enabled="true" Visible="true"></asp:TextBox>                                       
                                       
                                        </ItemTemplate>
                                      <FooterTemplate>
                                          <asp:TextBox ID="TxtName" runat="server"></asp:TextBox>
                                      </FooterTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Age">
                                        <FooterTemplate>
                                            <asp:TextBox ID="Txtage" runat="server"></asp:TextBox>
                                        </FooterTemplate>
                                      <ItemTemplate>
                                       
                                              <asp:TextBox ID="Txtage" runat="server" Text='<%# Eval("age") %>' Enabled="true" Visible="true"></asp:TextBox>                                       
                                       
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField HeaderText="Date">
                                        <FooterTemplate>
                                            <asp:TextBox ID="TxtDate" runat="server"></asp:TextBox>
                                            <asp:CalendarExtender ID="TxtDate_CalendarExtender" runat="server"
                                                Enabled="True" TargetControlID="TxtDate">
                                            </asp:CalendarExtender>
                                        </FooterTemplate>
                                      <ItemTemplate>
                                       
                                              <asp:TextBox ID="Txtinfdate" runat="server" Text='<%# Eval("InfDate") %>' Enabled="true" Visible="true"></asp:TextBox>                                       
                                       
                                              <asp:CalendarExtender ID="Txtinfdate_CalendarExtender" runat="server"
                                                  Enabled="True" TargetControlID="Txtinfdate" Format="dd/MM/yyyy" ClearTime="false">
                                              </asp:CalendarExtender>
                                       
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                    <asp:TemplateField >
                                        <FooterTemplate>
                                            <asp:Button ID="Btn_FInsert" runat="server" onclick="Btn_FInsert_Click"
                                                Text="Save" />
                                        </FooterTemplate>
                                    <ItemTemplate>
                                        <asp:Button ID="btnupdate" runat="server" Text="Update" CommandName="UP" OnClick="btnupdate_Click" />
                                    </ItemTemplate>
                                    </asp:TemplateField>
                                     <asp:TemplateField>
                                    <ItemTemplate>
                                        <asp:Button ID="btndlt" runat="server" Text="Dlt" CommandName="UP"
                                            onclick="btndlt_Click"/>
                                    </ItemTemplate>
                                    </asp:TemplateField>
                                  
                                </Columns>
                             <EmptyDataTemplate>
        <tr style="background-color: Green;">
            <th scope="col">
                 Name
            </th>
            <th scope="col">
                Age
            </th>
            <th scope="col">
               Date
            </th>
            <th scope="col">
                 
            </th>
        </tr>
        <tr>
            <td>
                <asp:TextBox ID="txtName" runat="server" />
            </td>
            <td>
                <asp:TextBox ID="txtage" runat="server" />
            </td>
            <td>
                <asp:TextBox ID="txtdate" runat="server" />
            </td>
            <td>
                <asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="Add" CommandName = "EmptyDataTemplate" />
            </td>
        </tr>
    </EmptyDataTemplate>
                            </asp:GridView>
                         
                        </td>
                    </tr>
                </table>
            </ContentTemplate>
            <Triggers>
            <asp:PostBackTrigger ControlID="gv" />
            </Triggers>
        </asp:UpdatePanel>
    </div>
 
     <asp:Label ID="Label1" runat="server" Text="Success"></asp:Label>
 
    </form>
</body>
</html>

Design

Runtime Design //When Blank Table
 

Runtime Design
 

C# Code
 using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
public partial class AutoCompleteInGridView_ : System.Web.UI.Page
{
    SqlConnection con;
    SqlCommand cmd = new SqlCommand();
    DataSet ds;
    SqlDataAdapter da;
    int roll;
    protected void Page_Load(object sender, EventArgs e)
    {
        Label1.Visible = false;
        if (IsPostBack == false)
        {
            con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            //cmd.Connection = con;
            //cmd.CommandText = "select * from fms.fms_demo";
            con.Open();

            da = new SqlDataAdapter("select max(roll) as roll  from Student", con);
            ds = new DataSet();
            da.Fill(ds);
            if (ds.Tables[0].Rows[0]["roll"].ToString() != "")
            {
                roll = int.Parse(ds.Tables[0].Rows[0]["roll"].ToString());
                con.Close();
            }
            else
            {
                roll = 1;
            }
            fillGridview();
        }
    }

    private void fillGridview()
    {
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        //cmd.Connection = con;
        //cmd.CommandText = "select * from Student";
        con.Open();
        da = new SqlDataAdapter("select roll,Name,age,Convert(varchar(20),InfDate,111) as InfDate  from Student", con);
        ds = new DataSet();
        da.Fill(ds);
        gv.DataSource = ds;
        gv.DataBind();
        con.Close();
    }
    [System.Web.Script.Services.ScriptMethod()]
    [System.Web.Services.WebMethod]
    public static List<string> GetName(string prefixText)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
        con.Open();
        SqlCommand cmd = new SqlCommand("select * from Student ", con);
        cmd.Parameters.AddWithValue("@Name", prefixText);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        List<string> CountryNames = new List<string>();
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            CountryNames.Add(dt.Rows[i]["name"].ToString());
        }
        return CountryNames;
    }
    protected void btnupdate_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
        Button bt = (Button)sender;
        GridViewRow gr = (GridViewRow)bt.NamingContainer;
        int rr = gr.RowIndex;

        Label t1 = (Label)gr.FindControl("LblRollno");
        TextBox t2 = (TextBox)gr.FindControl("TxtName1");
        con.Open();
        SqlCommand com = new SqlCommand("update Student set Name='" + t2.Text + "' where roll=" + t1.Text + "", con);
        com.ExecuteNonQuery();
    }

   protected void Add(object sender, EventArgs e)
   {
    Control control = null;
    if (gv.FooterRow != null)
    {
        control = gv.FooterRow;
    }
    else
    {
        control = gv.Controls[0].Controls[0];
    }
    string nm = (control.FindControl("txtName") as TextBox).Text;
    int ag = int.Parse((control.FindControl("txtage") as TextBox).Text);
    DateTime dt =Convert.ToDateTime((control.FindControl("txtdate") as TextBox).Text);
    string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO Student (Name,age,InfDate) VALUES(@CustomerName, @Customerage, @Customerdob)";
            cmd.Parameters.AddWithValue("@CustomerName", nm);
            cmd.Parameters.AddWithValue("@Customerage", ag);
            cmd.Parameters.AddWithValue("@Customerdob", dt);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
    Response.Redirect(Request.Url.AbsoluteUri);
}
    protected void gv_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
    protected void btndlt_Click(object sender, EventArgs e)
    {

       SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
        Button bt = (Button)sender;
        GridViewRow gr = (GridViewRow)bt.NamingContainer;
        int rr = gr.RowIndex;

        Label t1 = (Label)gr.FindControl("LblRollno");
       // TextBox t2 = (TextBox)gr.FindControl("TxtName1");
        con.Open();
        SqlCommand com = new SqlCommand("delete from Student where roll=" + t1.Text + "", con);
        com.ExecuteNonQuery();
    }
    protected void Btn_FInsert_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
        Button bt = (Button)sender;
        GridViewRow gr = (GridViewRow)bt.NamingContainer;
        TextBox Nm = (TextBox)gr.FindControl("TxtName");
        TextBox ag = (TextBox)gr.FindControl("Txtage");
        TextBox dt = (TextBox)gr.FindControl("TxtDate");
        con.Open();
        SqlCommand com = new SqlCommand("Insert into Student (age,InfDate,Name) values('"+ag.Text+"','"+dt.Text+"','"+Nm.Text+"')",con);
       int x= com.ExecuteNonQuery();
       if (x > 0)
       {
           fillGridview();
           Label1.Visible = true;
       }     
    }
}

Web Config File
<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="ConnectionString" connectionString="server=Satya\S1;database=linku;uid=ma;pwd=li2011"/>
  </connectionStrings>
    <system.web>
   
        <compilation debug="true" targetFramework="4.0">
            <assemblies>
                <add assembly="System.Design, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
                <add assembly="System.Web.Extensions.Design, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
                <add assembly="System.Windows.Forms, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/></assemblies></compilation>
   
    </system.web>
</configuration>

Databse Design







Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)