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

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

Share via

Gridview Update Dropdownlist