How to bind updated data

See more:

Gridview

Hi,
How to bind updated data in the dropdown for each and every grid view rows.
I have two tables A and B.Gridview dropwdown values coming from B table.
finally when i click save button i need to update all the dropdown values in Table A.
while page load i need to fetch the updated data from table A.
pls. give me the solution for this.
see my code below ASPX Page
---------------------------

<asp:GridView ID="GridView1" runat="server" align="center" AllowPaging="True" DataKeyNames="Priority"
                                 AutoGenerateColumns="False" class="tabulardata" DataSourceID="SqlDataSource1" 
                                 HorizontalAlign="Center" Width="100%" OnRowCommand="GridView1_RowCommand" 
                                 OnRowDataBound="GridView1_RowDataBound">
                                 <columns>
                                     <asp:TemplateField HeaderText="Sl.No" ItemStyle-HorizontalAlign="Center" ItemStyle-Width="2%">
                                         <itemtemplate>
                                             <asp:Label ID="Sno" runat="server">
                                         </itemtemplate>
                                         <itemstyle horizontalalign="Center" />
                                     
                                     <asp:BoundField HeaderText="Designaton Code" Visible="true" DataField="DesigCode"
                                         ItemStyle-HorizontalAlign="center" ReadOnly="true" SortExpression="DesigCode">
                                         <itemstyle horizontalalign="Center" width="3%" />
                                     
                                      <asp:BoundField HeaderText="Current Designation" Visible="true" DataField="Designation"
                                         ItemStyle-HorizontalAlign="center" ReadOnly="true" SortExpression="Designation">
                                         <itemstyle horizontalalign="Center" width="25%" />
                                     
                                      <asp:TemplateField HeaderText="Professional Role">
                                            <itemtemplate>
                                                <asp:DropDownList runat="server" ID="ddlProfRole" width="87%" DataValueField="ProfRoleCode" DataSourceID="SqlDataSource2" DataTextField="ProfRoleDescription">
                                                
                                            </itemtemplate>
                                            <itemstyle horizontalalign="Center" width="25%" />
                                       
                                 </columns>
                                 <alternatingrowstyle backcolor="#FFF5F5" />
                             
                         
                     
                 
            
	     
	
    <tr>
         <td colspan="2" align="center">
                   <asp:ImageButton ID="ImgbtnSave" runat="server" ImageUrl="images/Save.gif"/>
                   <asp:ImageButton ID="ImgbtnCancel" runat="server" ImageUrl="images/Cancel.gif"/>
          </td>
    </tr>
 
 <asp:SqlDataSource ID="SqlDataSource1" runat="server"   
    ConnectionString="<%$ConnectionStrings:MPP%>"
    SelectCommand="select Designation,DesigCode,Priority,ProfRole from Designation order by Priority">
<%--<deleteparameters>
     <asp:Parameter Name="emp_number" Type="String" />
</deleteparameters>--%>
    
    <asp:SqlDataSource ID="SqlDataSource2" runat="server"   
    ConnectionString="<%$ConnectionStrings:MPP%>"
    SelectCommand="select -1 as ProfRoleCode, 'Select' as ProfRoleDescription,null as DeptID union select ProfRoleCode,ProfRoleDescription,DeptCode from mppProfRoleMapping order by ProfRoleCode">

ASPX.Vb Code
-------------

Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand
        Dim StrQry As String
        Dim DesigCode As String = Convert.ToInt32(e.CommandArgument)

        If e.CommandName = "Save" Then
            'update  weeklyload table Values for the PojectNumber
            Conn.Open()
            StrQry = ""
            'StrQry = "update mppProfRoleMapping set DesignCode='" &  & "' where ProfRoleCode ='" &  & "'"
            Cmd.Connection = Conn
            Cmd.CommandText = StrQry
            Cmd.ExecuteNonQuery()
            Cmd.Dispose()
            Conn.Close()
        End If
    End Sub
    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
        Dim sno As New Label
        Dim SrNo As Integer
        If e.Row.RowType = DataControlRowType.DataRow Then
            SrNo = (GridView1.PageIndex + 1) * 10 - 9
            sno = e.Row.FindControl("Sno")
            sno.Text = e.Row.RowIndex + SrNo
            sno.DataBind()
        End If
        'Dim drdList As DropDownList
        'For Each grdRow As GridViewRow In GridView1.Rows
        '' Nested DropDownList Control reference is passed to the DrdList object. 
        ''This will allow you access the properties of dropdownlist placed inside the GridView Template column.
        'drdList = DirectCast(GridView1.Rows(grdRow.RowIndex).Cells(1).FindControl("ddlProfRole"), DropDownList)
        '' DataBinding of nested DropDownList Control for each row of GridView.
        'drdList.DataSource = getdataset()
        ''drdList.DataValueField = "ProfRoleCode"
        ''drdList.DataTextField = "ProfRoleDescription"
        ''drdList.DataBind()
        'Next
    End Sub
    Private Function getdataset() As Data.DataSet
        For Each r As GridViewRow In GridView1.Rows
            Dim DesigCode1 As String = GridView1.Rows(r.RowIndex).Cells(1).Text
            Dim query As String = "select * from mppProfRoleMapping LEFT OUTER JOIN designation on mppProfRoleMapping.ProfRoleCode=designation.ProfRoleCode where DesigCode='" & GridView1.Rows(r.RowIndex).Cells(1).Text & "' "
            Dim ad As New SqlDataAdapter(Query, Conn)
            Dim ds As New Data.DataSet()
            ad.Fill(ds)
            Return ds
        Next
    End Function
    Protected Sub ImgbtnSave_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImgbtnSave.Click
        'update  weeklyload table Values for the PojectNumber
        Dim ProfRole As String
        For Each r As GridViewRow In GridView1.Rows
            ProfRole = CType(r.FindControl("ddlProfRole"), DropDownList).Text
            Dim DesigCode As String = GridView1.Rows(r.RowIndex).Cells(1).Text
            If ProfRole = -1 Then
                'Do not do anything
            Else
                Conn.Open()
                StrQry = ""
                StrQry = "update Designation set ProfRoleCode='" & ProfRole & "' where DesigCode ='" & DesigCode & "' and DeptCode='" & Session("Dept") & "' "
                Cmd.Connection = Conn
                Cmd.CommandText = StrQry
                Cmd.ExecuteNonQuery()
                Cmd.Dispose()
                Conn.Close()
            End If
1:
        Next
        ' Rebind the Grid to repopulate the original values table.
        GridView1.DataBind()
    End Sub

Regards,
Ganesh.S