Asp.net with C#.net Update gridview with dropdownlist

Question

I want to edit gridview data with textboxes and dropdownlist in (VendorCode) where VendorCode list items should be available from tblVendor. The data loaded in gridview is from table tblPerson. Can u help to solve the problem?!!!!!!!!!!!!!!


.aspx page 
<%@ Page Title="" Language="C#" MasterPageFile="~/Pages/MainSite.master" AutoEventWireup="true" CodeFile="pgPersonList.aspx.cs" Inherits="Pages_pgPersonList" %>

<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" Runat="Server">
    <style type="text/css">
        .style1
        {
            width: 85px;
            height: 23px;
        }
        .style3
        {
            width: 148px;
            height: 23px;
        }
        .style4
        {
            width: 113px;
            height: 23px;
        }
        .style5
        {
            width: 369px;
            height: 23px;
        }
        .style6
        {
            width: 50px;
            height: 23px;
        }
        .style7
        {
            height: 23px;
        }
    </style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server">
  
    <table style="width:100%;">
        <tr>
            <td class="style1">
                <asp:Label ID="Label1" runat="server" Font-Size="Medium" ForeColor="Black"
                    Text="Type Name "></asp:Label>
            </td>
            <td class="style5">
                <asp:TextBox ID="txtSearchName" runat="server" AutoPostBack="True"
                    Font-Size="Small" Height="21px" ontextchanged="txtSearchName_TextChanged"
                    Width="358px"></asp:TextBox>
           </td>
            <td class="style6">
                <asp:Button ID="btnShow" runat="server"
                    style="margin-left: 0px" Text="Show" onclick="btnShow_Click" />
            </td>
            <td class="style3">
                <asp:Label ID="Label2" runat="server" Font-Size="Medium" ForeColor="Black"
                    Text="Choose Person ID"></asp:Label>
            </td>
            <td class="style4">
                <asp:DropDownList ID="ddlPersonID" runat="server" AutoPostBack="True"
                    Font-Size="Medium" Height="22px"
                    onselectedindexchanged="ddlPersonID_SelectedIndexChanged" Width="108px">
                </asp:DropDownList>
            </td>
            <td class="style7">
                <asp:Button ID="btnRefresh" runat="server"
                    style="margin-left: 0px" Text="Refresh" onclick="btnRefresh_Click" />
                </td>
        </tr>
    </table>
  
    <asp:GridView ID="GridView1" runat="server" CellPadding="3"
        Width="1028px" AllowPaging="True" AllowSorting="True"
       BorderStyle="None"
        onselectedindexchanged="GridView1_SelectedIndexChanged"
        onrowediting="GridView1_RowEditing"
        onrowcancelingedit="GridView1_RowCancelingEdit"
        onrowupdating="GridView1_RowUpdating" BackColor="White"
        BorderColor="#999999" BorderWidth="1px" GridLines="Vertical"
        AutoGenerateColumns="False" DataKeyNames="PersonID"
        style="margin-bottom: 83px; margin-right: 0px;" Height="16px">
    <AlternatingRowStyle BackColor="Gainsboro" />
       
    <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
    <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
    <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
    <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#F1F1F1" />
    <SortedAscendingHeaderStyle BackColor="#0000A9" />
    <SortedDescendingCellStyle BackColor="#CAC9C9" />
    <SortedDescendingHeaderStyle BackColor="#000065" />
   
    <Columns>
      <asp:CommandField ButtonType="Button" ShowEditButton="true" ShowCancelButton="true" />
                   
      <asp:BoundField DataField="PersonID" HeaderText="Person ID" ReadOnly="true" >
               
         <ItemStyle Width="50px" Wrap="True" />
        </asp:BoundField>
               
      <asp:TemplateField HeaderText="Vendor Code" ItemStyle-Width = "150">
            <ItemTemplate>
                <asp:Label ID = "lblVendorCode" runat="server" Text='<%# Eval("VendorCode") %>'></asp:Label>
                <asp:DropDownList ID="ddlVendorCode" runat="server" Visible = "false">
                </asp:DropDownList>
            </ItemTemplate>
        </asp:TemplateField>
           
       
                 
      <asp:TemplateField HeaderText="Person Name">
          <ItemTemplate>
              <%# Eval("PersonName")%>
          </ItemTemplate>
          <EditItemTemplate>
              <asp:TextBox runat="server" ID="txtPersonName" Text='<%# Eval("PersonName")%>' />
          </EditItemTemplate>
          <ControlStyle Width="150px" />
          <ItemStyle Width="150px" />
      </asp:TemplateField>

      <asp:TemplateField HeaderText="Address">
          <ItemTemplate>
              <%# Eval("Address")%>
          </ItemTemplate>
          <EditItemTemplate>
              <asp:TextBox runat="server" ID="txtAddress" Text='<%# Eval("Address")%>' />
          </EditItemTemplate>
          <ControlStyle Width="100px" />
          <ItemStyle Width="100px" />
      </asp:TemplateField>
   
      <asp:TemplateField HeaderText="Contact">
          <ItemTemplate>
              <%# Eval("Contact")%>
          </ItemTemplate>
          <EditItemTemplate>
              <asp:TextBox runat="server" ID="txtContact" Text='<%# Eval("Contact")%>' />
          </EditItemTemplate>
          <ControlStyle Width="100px" />
          <ItemStyle Width="100px" />
      </asp:TemplateField>
   
      <asp:TemplateField HeaderText="Designation">
          <ItemTemplate>
              <%# Eval("Designation")%>
          </ItemTemplate>
          <EditItemTemplate>
              <asp:TextBox runat="server" ID="txtDesignation" Text='<%# Eval("Designation")%>' />
          </EditItemTemplate>
          <ControlStyle Width="100px" />
          <ItemStyle Width="100px" />
      </asp:TemplateField>
   
      <asp:TemplateField HeaderText="Email">
          <ItemTemplate>
              <%# Eval("Email")%>
          </ItemTemplate>
          <EditItemTemplate>
              <asp:TextBox runat="server" ID="txtEmail" Text='<%# Eval("Email")%>' />
          </EditItemTemplate>
          <ControlStyle Width="100px" />
          <ItemStyle Width="100px" />
      </asp:TemplateField>
       
    </Columns>
           
</asp:GridView>
  
</asp:Content>




.aspx.cs page


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 Pages_pgPersonList : System.Web.UI.Page
{
    //private string drv;ab;
    string ab;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindDataToGrid();
            showPersonID();
        }
    }
    void showPersonID()
    {
        Class1 cls = new Class1(Session["sdbpath"].ToString());
        cls.con.Open();
        string qry = "select PersonID from tblPerson";
        SqlDataReader rd = cls.showDataFromTable(qry);
        ddlPersonID.Items.Clear();
        while (rd.Read())
        {
            ddlPersonID.Items.Add(rd[0].ToString());

        }//end while

    }//end of function showcustomerID
    void BindDataToGrid()
    {
        Class1 cls = new Class1(Session["sdbpath"].ToString());
        cls.con.Open();
        string qry = "select * from tblPerson";
        DataTable dt = cls.GridDataTable(qry);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }

    protected void ddlPersonID_SelectedIndexChanged(object sender, EventArgs e)
    {
        BindGridByPersonID();
    }
    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        BindGridByPersonID();
    }
    //click on cancel
    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        e.Cancel = true;
        GridView1.EditIndex = -1;
        BindDataToGrid();
    }
    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
       
        GridViewRow row = GridView1.Rows[e.RowIndex];

        TextBox txtPersonID = (TextBox)row.FindControl("txtPersonID");
        //DropDownList ddlVendorCode = (DropDownList)row.FindControl("ddlVendorCode");

//        GridViewRow gvr =
//this.GridView1.Rows[this.GridView1.EditIndex];
        DropDownList ddlVendorCode =
        (DropDownList)row.FindControl("ddlVendorCode");



        //ddlVendorCode.DataSource = GetDataItem();
           
        //ddlVendorCode.DataValueField = "VendorCode";
       // ddlVendorCode.DataBind();
       // e.NewValues["VendorCode"] = ddlVendorCode.SelectedValue;
//ListItem li =
//ddl.Items.FindByValue(drv["Country"].ToString());
//li.Selected = true;

        TextBox txtPersonName = (TextBox)row.FindControl("txtPersonName");
        TextBox txtAddress = (TextBox)row.FindControl("txtAddress");
        TextBox txtContact = (TextBox)row.FindControl("txtContact");
        TextBox txtDesignation = (TextBox)row.FindControl("txtDesignation");
        TextBox txtEmail = (TextBox)row.FindControl("txtEmail");
       
        String PersonID = GridView1.DataKeys[e.RowIndex].Value.ToString();
        string VendorCode = ddlVendorCode.SelectedValue;
        string PersonName = txtPersonName.Text;
        string Address = txtAddress.Text;
        string Contact = txtContact.Text;
        string Designation = txtDesignation.Text;
        string Email = txtEmail.Text;
    //update to database
        Class1 cls1 = new Class1(Session["sdbpath"].ToString());//connectio class define
        cls1.con.Open();
        string qry = "update tblPerson set VendorCode = '" + ddlVendorCode.SelectedValue + "' ,PersonName = '" + txtPersonName.Text + "' ,Address = '" + txtAddress.Text + "' ,Contact='" + txtContact.Text + "' ,Designation='" + txtDesignation.Text + "' ,Email='" + txtEmail.Text + "' where PersonID = '" + PersonID + "'";
        cls1.SaveEditDeleteData(qry);

        string msg = "alert('Person information has been Upated sucessfully ');";
        Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "", msg, true);

        GridView1.EditIndex = -1;
        BindDataToGrid();
    }
    protected void txtSearchName_TextChanged(object sender, EventArgs e)
    {
        BindDataToGridByName();
    }
    void BindDataToGridByName()
    {
        Class1 cls = new Class1(Session["sdbpath"].ToString());
        cls.con.Open();
        string qry = "select * from tblPerson where PersonName like '" + txtSearchName.Text + "%'";
        DataTable dt = cls.GridDataTable(qry);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
    void BindGridByPersonID()
    {
        Class1 cls = new Class1(Session["sdbpath"].ToString());
        cls.con.Open();
        string qry = "select * from tblPerson where PersonID = '" + ddlPersonID.Text + " '";
        DataTable dt = cls.GridDataTable(qry);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
    protected void btnShow_Click(object sender, EventArgs e)
    {
        BindDataToGridByName();
    }
    protected void btnRefresh_Click(object sender, EventArgs e)
    {
        BindDataToGrid();
    }
    protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            Class1 cls = new Class1(Session["sdbpath"].ToString());
            cls.con.Open();
            string cmd = "SELECT DISTINCT(VendorCode) FROM tblVendor";
            DropDownList ddlVendorCode = (e.Row.FindControl("ddlVendorCode") as DropDownList);
            ddlVendorCode.DataSource = cls.showDataFromTable(cmd);
            ddlVendorCode.DataTextField = "VendorCode";
            ddlVendorCode.DataValueField = "VendorCode";
            ddlVendorCode.DataBind();
            string country = (e.Row.FindControl("lblVendorCode") as Label).Text;
            ddlVendorCode.Items.FindByValue("ddlVendorCode").Selected = true;
        }
    }
}