Gridview Update Dropdownlist

Question

In my aspx+VB+SQL Web

I had added table create script, My aspx page and VB code behind

Data is loaded into gridview and drop down is also working finer. I can edit and update teh data

but when I press the update button my gridview disappears. if I have 3 rows in gridview and want to edit each rows. I have to select the dropdown list each time and update each row.

SQL Script

Aspx Page

<asp:GridView ID="gvCustomers" DataKeyNames="ION_ID" runat="server" AutoGenerateColumns="false"    OnRowEditing="EditCustomer" OnRowDataBound="RowDataBound" OnRowUpdating="UpdateCustomer"    OnRowCancelingEdit="CancelEdit">
            <Columns>
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
                <asp:BoundField DataField="ION_ID" HeaderText="ION_ID" InsertVisible="False"    ReadOnly="True" SortExpression="ION_ID" ><ControlStyle Width="50px" /></asp:BoundField>
                <asp:BoundField DataField="Originator" HeaderText="Originator"               SortExpression="Originator" >          <ControlStyle Width="80px" /></asp:BoundField>
                <asp:BoundField DataField="Originator_ID" HeaderText="Originator_ID"         SortExpression="Originator_ID" >       <ControlStyle Width="100px" /></asp:BoundField>
                <asp:BoundField DataField="Date" HeaderText="Date"   SortExpression="Date" DataFormatString="{0:dd-MMM-yy}">       <ControlStyle Width="80px"  />      </asp:BoundField>
                <asp:BoundField DataField="LPR_NO" HeaderText="LPR_NO"   SortExpression="LPR_NO" >   <ControlStyle Width="100px" />   </asp:BoundField>
                <asp:BoundField DataField="LPR_Dt" HeaderText="LPR_Dt" SortExpression="LPR_Dt"  DataFormatString="{0:dd-MMM-yy}">     <ControlStyle Width="80px" />     </asp:BoundField>
                <asp:BoundField DataField="Product" HeaderText="Product"           SortExpression="Product" >   </asp:BoundField>

 <asp:TemplateField HeaderText="Grant">
            <ItemTemplate>
                <asp:Label ID="lblCity" runat="server" Text='<%# Eval("Grants")%>'></asp:Label>
            </ItemTemplate>
            <EditItemTemplate>
                <asp:DropDownList ID="ddlCities" runat="server">
                </asp:DropDownList>
            </EditItemTemplate>
        </asp:TemplateField>

                <asp:BoundField DataField="AU" HeaderText="AU"                     SortExpression="AU" >        <ControlStyle Width="50px" />  </asp:BoundField>
                <asp:BoundField DataField="Qty_Req" HeaderText="Qty_Req"           SortExpression="Qty_Req" >   <ControlStyle Width="100px" /> </asp:BoundField>     
                <asp:BoundField DataField="Qty_Issue" HeaderText="Qty_Issue"     SortExpression="Qty_Issue" > <ControlStyle Width="100px" />   </asp:BoundField>      
                <asp:BoundField DataField="Qty_NA" HeaderText="Qty_NA"           SortExpression="Qty_NA" >    <ControlStyle Width="100px" />   </asp:BoundField>      
                
                <asp:BoundField DataField="Status" HeaderText="Status"         SortExpression="Status" >           <ControlStyle Width="70px" />    </asp:BoundField>
            </Columns>
        </asp:GridView>

Code Behind

Imports System.Data.SqlClient
Imports System.Data

Partial Class LPR_Initiate
    Inherits System.Web.UI.Page
    Dim Str As String = ConfigurationManager.ConnectionStrings("Hony_Capt_BVeh_BaijuEP").ConnectionString
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


If Not IsPostBack Then

            Dim con2 As New SqlConnection
            con2.ConnectionString = ConfigurationManager.ConnectionStrings("Hony_Capt_BVeh_BaijuEP").ConnectionString
            con2.Open()
            Dim comd4 As New SqlCommand("SELECT  DISTINCT Originator_ID from LP_ION where Status='No'", con2)
            Dim dab4 As New SqlDataAdapter(comd4)
            Dim dsb4 As New DataSet()
            dab4.Fill(dsb4)
            orgid.Items.Clear()
            orgid.DataSource = dsb4
            orgid.DataTextField = "Originator_ID"
            orgid.DataBind()
            orgid.Items.Insert(0, New ListItem("Select"))
        End If

End Sub


Protected Sub orgid_SelectedIndexChanged(sender As Object, e As EventArgs) Handles orgid.SelectedIndexChanged
        Me.BindGrid()
    End Sub

    Private Sub BindGrid()
        Dim sql As String = "SELECT * FROM LP_ION WHERE Originator_ID='" + orgid.Text + "'"
        Dim conString As String = ConfigurationManager.ConnectionStrings("Hony_Capt_BVeh_BaijuEP").ConnectionString
        Using con As SqlConnection = New SqlConnection(conString)
            Using sda As SqlDataAdapter = New SqlDataAdapter(sql, con)
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    gvCustomers.DataSource = dt
                    gvCustomers.DataBind()
                End Using
            End Using
        End Using
    End Sub



    Protected Sub RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
        If e.Row.RowType = DataControlRowType.DataRow AndAlso gvCustomers.EditIndex = e.Row.RowIndex Then
            Dim ddlCities As DropDownList = CType(e.Row.FindControl("ddlCities"), DropDownList)
            Dim sql As String = "SELECT Grants FROM Fund"
            Dim conString As String = ConfigurationManager.ConnectionStrings("Hony_Capt_BVeh_BaijuEP").ConnectionString
            Using con As SqlConnection = New SqlConnection(conString)
                Using sda As SqlDataAdapter = New SqlDataAdapter(sql, con)
                    Using dt As DataTable = New DataTable()
                        sda.Fill(dt)
                        ddlCities.DataSource = dt
                        ddlCities.DataTextField = "Grants"
                        ddlCities.DataValueField = "Grants"
                        ddlCities.DataBind()
                        Dim selectedCity As String = DataBinder.Eval(e.Row.DataItem, "Grants").ToString()
                        ddlCities.Items.FindByValue(selectedCity).Selected = True
                    End Using
                End Using
            End Using
        End If
    End Sub

    Protected Sub EditCustomer(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
        gvCustomers.EditIndex = e.NewEditIndex
        Me.BindGrid()

      
    End Sub

    Protected Sub CancelEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
        gvCustomers.EditIndex = -1
        Me.BindGrid()
    End Sub

    Protected Sub UpdateCustomer(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
        Dim city As String = (TryCast(gvCustomers.Rows(e.RowIndex).FindControl("ddlCities"), DropDownList)).SelectedItem.Value
        Dim ION_ID As String = gvCustomers.DataKeys(e.RowIndex).Value.ToString()
        Dim conString As String = ConfigurationManager.ConnectionStrings("Hony_Capt_BVeh_BaijuEP").ConnectionString
        Using con As SqlConnection = New SqlConnection(conString)
            Dim query As String = "UPDATE LP_ION SET Grants = @Grants WHERE ION_ID = @ION_ID"
            Using cmd As SqlCommand = New SqlCommand(query, con)
                cmd.Parameters.AddWithValue("@ION_ID", ION_ID)
                cmd.Parameters.AddWithValue("@Grants", city)
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
                Response.Redirect(Request.Url.AbsoluteUri)
            End Using
        End Using
    End Sub