GridView Update Problem

Hi all,

I am using MS Visual Studio 2010, VB Language.

I am trying to add, Update, and delete records using GridView Control. Inside the GridView I have a DropDownList control (MaintenanceTermID ) as a session. I can add and delete the records successfully, but the update is not working.

Once I edit the data and click the Update button, it deletes the row from the GridView! In the database they are available but the MaintenanceTermID (session) becomes NULL!!!

I have no idea why this is happening !

I need your help please as soon as possible

Source Code:

<asp:SqlDataSource ID="SqlDataSourceMaintenance" runat="server"
                    ConnectionString="<%$ ConnectionStrings:maintenanceDBConnectionString %>"
                    SelectCommand="SELECT tbl_MaintenanceSubTemp.Quantity, tbl_MaintenanceSubTemp.Total, tbl_MaintenanceSubTemp.MaintenanceSubTempID, tbl_MaintenanceTerms.MaintenanceTermNo FROM tbl_MaintenanceSubTemp INNER JOIN tbl_MaintenanceTerms ON tbl_MaintenanceSubTemp.MaintenanceTermsID = tbl_MaintenanceTerms.MaintenanceTermsID"
                    InsertCommand="Insert into tbl_MaintenanceSubTemp (MaintenanceTermsID, Quantity, Total) values (@MaintenanceTermsID, @Quantity, @Total)"
                    UpdateCommand="Update tbl_MaintenanceSubTemp SET Quantity=@Quantity, MaintenanceTermsID=@MaintenanceTermsID, Total=@Total where (MaintenanceSubTempID=@MaintenanceSubTempID)"
                    DeleteCommand="Delete from tbl_MaintenanceSubTemp where (MaintenanceSubTempID=@MaintenanceSubTempID)"
                    oninserting="Sqldatasource1_Inserting" OnInserted="Sqldatasource1_Inserted">
                    <DeleteParameters>
                        <asp:Parameter Name="MaintenanceSubTempID" />
                    </DeleteParameters>
                    <InsertParameters>
                        <asp:Parameter Name="MaintenanceTermsID" />
                        <asp:Parameter Name="Quantity" />
                        <asp:Parameter Name="Total" />
                    </InsertParameters>
                    <UpdateParameters>
                        <asp:Parameter Name="MaintenanceTermsID" />
                        <asp:Parameter Name="Quantity" />
                        <asp:Parameter Name="Total" />
                        <asp:Parameter Name="MaintenanceSubTempID" />
                    </UpdateParameters>

                </asp:SqlDataSource>

<asp:GridView ID="GridViewMaintenance" runat="server" dir="rtl"
                    AutoGenerateColumns="False" DataKeyNames="MaintenanceSubTempID" 
                    DataSourceID="SqlDataSourceMaintenance" ShowFooter="True" 
                    RowStyle-BorderColor="beige" 
                    RowStyle-BorderWidth="1" BorderWidth="2px" BorderColor="Silver" 
                    Font-Names="Tahoma" Font-Size="Small">
                    <Columns>
                        <asp:TemplateField HeaderText="" 
                            SortExpression="MaintenanceSubTempID">
                            <ItemTemplate>
                                <%# Eval("MaintenanceSubTempID")%>  
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText=" " 
                            SortExpression="MaintenanceTermsID">
                            <ItemTemplate>
                                <%# Eval("MaintenanceTermNo")%> 
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:DropDownList ID="EditTermNo" runat="server" AutoPostBack="True" 
                                    DataSourceID="SqlDataSourcetermNo" DataTextField="MaintenanceTermNo" 
                                    DataValueField="MaintenanceTermsID" AppendDataBoundItems="True" 
                                    onselectedindexchanged="EditTermNo_SelectedIndexChanged">
                                    <asp:ListItem Selected="True" Value="0">choose</asp:ListItem>
                                </asp:DropDownList>
                                <a href="PopupTerms.aspx" onclick="return popitup('PopupTerms.aspx')"></a>
                            </EditItemTemplate>
                            <FooterTemplate>
                                <asp:DropDownList ID="FooterTermNo" runat="server" AutoPostBack="True" 
                                    DataSourceID="SqlDataSourcetermNo" DataTextField="MaintenanceTermNo" 
                                    DataValueField="MaintenanceTermsID" AppendDataBoundItems="True">
                                    <asp:ListItem Selected="True" Value="0">choose</asp:ListItem>
                                </asp:DropDownList>
                                <a href="PopupTerms.aspx" onclick="return popitup('PopupTerms.aspx')"></a>
                            </FooterTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="" SortExpression="Quantity">
                            <ItemTemplate>
                                <%# Eval("Quantity")%> 
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBoxEditQty" runat="server" Text='<%# Bind("Quantity") %>'></asp:TextBox>
                            </EditItemTemplate>
                            <FooterTemplate>
                                <asp:TextBox ID="TextBoxInsertQty" runat="server" Text='<%# Bind("Quantity") %>'></asp:TextBox>
                            </FooterTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="" SortExpression="Total">
                            <ItemTemplate>
                                <%# Eval("Total")%> 
                            </ItemTemplate>                            
                            <EditItemTemplate>
                                <asp:TextBox ID="TextBoxEditTotal" runat="server" Text='<%# Bind("Total") %>' 
                                    style="margin-right: 0px"></asp:TextBox>
                            </EditItemTemplate>
                            <FooterTemplate>
                                <asp:TextBox ID="TextBoxInsertTotal" runat="server" Text='<%# Bind("Total") %>' 
                                    ReadOnly="True"></asp:TextBox>
                            </FooterTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField>
                            <ItemTemplate>
                                <asp:Button ID="Edit" runat="server" CommandName="Edit" Text="" 
                                    Font-Names="Tahoma" Font-Size="Small"/>
                                <asp:Button ID="Delete" runat="server" CommandName="Delete" Text="" 
                                    Font-Names="Tahoma" Font-Size="Small" />
                            </ItemTemplate>                            
                            <EditItemTemplate>
                                <asp:Button ID="ButtonMultiplyEdit" runat="server" CommandName="MultiplyEdit" 
                                    Font-Names="Tahoma" Font-Size="Small" Text="x" />
                                <asp:Button ID="Update" runat="server" CommandName="Update" Text="" 
                                    Font-Names="Tahoma" Font-Size="Small" />
                                <asp:Button ID="Cancel" runat="server" CommandName="Cancel" Text="" 
                                    Font-Names="Tahoma" Font-Size="Small" />
                            </EditItemTemplate>
                            <FooterTemplate>
                                <asp:Button ID="ButtonMultiplyFooter" runat="server" Font-Size="Small" 
                                    Text="x" Font-Names="Tahoma" 
                                    CommandName="MultiplyFooter" />
                                <asp:Button ID="Insert" runat="server" CommandName="InsertNew" Text="" 
                                    Font-Names="Tahoma" Font-Size="Small"/>
                            </FooterTemplate>
                        </asp:TemplateField>
                    </Columns>
                    <EmptyDataTemplate>
                        &nbsp;<table class="style1" style="font-family: tahoma; font-size: small">
                            <tr>
                                <td>
                                     :</td>
                                <td>
                                    <asp:DropDownList ID="TermNoEmpty" runat="server" AutoPostBack="True" 
                                        DataSourceID="SqlDataSourcetermNo" DataTextField="MaintenanceTermNo" 
                                        DataValueField="MaintenanceTermsID" dir="rtl" AppendDataBoundItems="True">
                                        <asp:ListItem Selected="True" Value="0">choose</asp:ListItem>
                                    </asp:DropDownList>
                                </td>
                                <td>
                                    <a href="PopupTerms.aspx" onclick="return popitup('PopupTerms.aspx')"> </a>
                                </td>
                                <td>
                                    :</td>
                                <td>
                                    <asp:TextBox ID="EmptyQty" runat="server" dir="rtl" Width="45px"></asp:TextBox>
                                </td>
                                <td>
                                    :</td>
                                <td>
                                    <asp:TextBox ID="TextBoxTotal" runat="server" dir="rtl" ReadOnly="True" 
                                        Width="45px"></asp:TextBox>
                                </td>
                                <td>
                                    <asp:Button ID="ButtonMultiply" runat="server" Font-Size="Small" 
                                        Text="x" CommandName="MultiplyEmpty" Font-Names="Tahoma" />
                                    <asp:Button ID="NoDataInsert" runat="server" CommandName="NoDataInsert" 
                                        Text="" Font-Names="Tahoma" Font-Size="Small" />
                                </td>
                                <td class="style2">
                                    &nbsp;</td>
                                <td class="style2">
                                    &nbsp;</td>
                            </tr>
                        </table>
                    </EmptyDataTemplate>

<RowStyle BorderColor="Beige" BorderWidth="1px"></RowStyle>
                </asp:GridView>

Page_Load Code:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        LabelDate.Text = Convert.ToDateTime(DateTime.Now).ToString("dd/MMM/yyyy")
        If Not IsPostBack Then
            BindDropDownList()
        End If
        Dim connectionString As String
        connectionString = "Data Source=MOPWSQLDXB\MOPWDB;Initial Catalog=maintenanceDB;Integrated Security=True"
        Dim con As SqlConnection = New SqlConnection(connectionString)

        If GridViewMaintenance.Rows.Count = 0 Then
            Dim testddl As DropDownList = GridViewMaintenance.Controls(0).Controls(0).FindControl("TermNoEmpty")
            Session("TermID") = testddl.SelectedValue

        ElseIf GridViewMaintenance.Rows.Count > 0 Then
            Dim testddl2 As DropDownList = GridViewMaintenance.FooterRow.FindControl("FooterTermNo")
            Session("TermID") = testddl2.SelectedValue

        End If
        Dim cmd As SqlCommand = New SqlCommand("SELECT Price FROM tbl_MaintenanceTerms Where MaintenanceTermsID='" + Session("TermID") + "'", con)
        con.Open()

        Dim reader = cmd.ExecuteReader()
        If reader.HasRows Then
            reader.Read()
            TextBoxPrice.Text = reader.GetValue(0).ToString()
            reader.Close()
        End If
    End Sub

On Row Command:

    Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs) Handles GridViewMaintenance.RowCommand
        If e.CommandName = "MultiplyEmpty" Then
            Dim txtPrice As Integer = TextBoxPrice.Text
            Dim txtQty As TextBox = GridViewMaintenance.Controls(0).Controls(0).FindControl("EmptyQty")
            Dim txtTotal As TextBox = GridViewMaintenance.Controls(0).Controls(0).FindControl("TextBoxTotal")

            Dim itxtQty As Integer = Convert.ToInt32(txtQty.Text)

            txtTotal.Text = (txtQty.Text * txtPrice)

        ElseIf e.CommandName = "MultiplyFooter" Then
            Dim txtPrice As Integer = TextBoxPrice.Text
            Dim txtQtyFooter As TextBox = GridViewMaintenance.FooterRow.FindControl("TextBoxInsertQty")
            Dim txtTotalFooter As TextBox = GridViewMaintenance.FooterRow.FindControl("TextBoxInsertTotal")

            Dim itxtQtyFooter As Integer = Convert.ToInt32(txtQtyFooter.Text)
            txtTotalFooter.Text = (txtQtyFooter.Text * txtPrice)

        ElseIf e.CommandName = "MultiplyEdit" Then
            Dim txtQtyEdit As TextBox = CType(GridViewMaintenance.FindControl("TextBoxEditQty"), TextBox)
            Dim txtTotalEdit As TextBox = CType(GridViewMaintenance.FindControl("TextBoxEditTotal"), TextBox)
            Dim txtPrice As Integer = TextBoxPrice.Text

            txtTotalEdit.Text = (txtQtyEdit.Text * txtPrice)

        End If

        If e.CommandName = "NoDataInsert" Then
            Dim MaintenanceTermsID As DropDownList = TryCast(GridViewMaintenance.Controls(0).Controls(0).FindControl("TermNoEmpty"), DropDownList)
            Dim Quantity As TextBox = TryCast(GridViewMaintenance.Controls(0).Controls(0).FindControl("EmptyQty"), TextBox)
            Dim Total As TextBox = TryCast(GridViewMaintenance.Controls(0).Controls(0).FindControl("TextBoxTotal"), TextBox)


            Dim iMaintenanceTermsID As New SqlParameter("@MaintenanceTermsID", SqlDbType.VarChar, 30)
            iMaintenanceTermsID.Direction = ParameterDirection.Input
            iMaintenanceTermsID.Value = MaintenanceTermsID.Text
            insertParameters.Add(iMaintenanceTermsID)

            Dim iQuantity As New SqlParameter("@Quantity", SqlDbType.VarChar, 30)
            iQuantity.Direction = ParameterDirection.Input
            iQuantity.Value = Quantity.Text
            insertParameters.Add(iQuantity)

            Dim iTotal As New SqlParameter("@Total", SqlDbType.VarChar, 30)
            iTotal.Direction = ParameterDirection.Input
            iTotal.Value = Total.Text
            insertParameters.Add(iTotal)

            SqlDataSourceMaintenance.Insert()

        ElseIf e.CommandName = "InsertNew" Then

            Dim MaintenanceTermsID As DropDownList = TryCast(GridViewMaintenance.FooterRow.FindControl("FooterTermNo"), DropDownList)
            Dim Quantity As TextBox = TryCast(GridViewMaintenance.FooterRow.FindControl("TextBoxInsertQty"), TextBox)
            Dim Total As TextBox = TryCast(GridViewMaintenance.FooterRow.FindControl("TextBoxInsertTotal"), TextBox)


            Dim iMaintenanceTermsID As New SqlParameter("@MaintenanceTermsID", SqlDbType.VarChar, 30)
            iMaintenanceTermsID.Direction = ParameterDirection.Input
            iMaintenanceTermsID.Value = MaintenanceTermsID.Text
            insertParameters.Add(iMaintenanceTermsID)

            Dim iQuantity As New SqlParameter("@Quantity", SqlDbType.VarChar, 30)
            iQuantity.Direction = ParameterDirection.Input
            iQuantity.Value = Quantity.Text
            insertParameters.Add(iQuantity)

            Dim iTotal As New SqlParameter("@Total", SqlDbType.VarChar, 30)
            iTotal.Direction = ParameterDirection.Input
            iTotal.Value = Total.Text
            insertParameters.Add(iTotal)
            SqlDataSourceMaintenance.Insert()

        End If

    End Sub

    Protected Sub Sqldatasource1_Inserting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)
        e.Command.Parameters.Clear()
        For Each p As SqlParameter In insertParameters
            e.Command.Parameters.Add(p)
        Next
    End Sub
    Protected Sub Sqldatasource1_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)
        insertParameters.Clear()
    End Sub

SelectIndexChange (Update Session):

   Protected Sub EditTermNo_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
        Dim connectionString As String
        connectionString = "Data Source=MOPWSQLDXB\MOPWDB;Initial Catalog=maintenanceDB;Integrated Security=True"
        Dim con As SqlConnection = New SqlConnection(connectionString)

        Dim gridViewRow As GridViewRow = CType(((CType(sender, Control)).NamingContainer), GridViewRow)

        Dim ddl1 As DropDownList = CType(gridViewRow.FindControl("EditTermNo"), DropDownList)
        Session("TermID") = ddl1.SelectedValue

        Dim cmd As SqlCommand = New SqlCommand("SELECT Price FROM tbl_MaintenanceTerms Where MaintenanceTermsID='" + Session("TermID") + "'", con)
        con.Open()
        Dim reader = cmd.ExecuteReader()
        If reader.HasRows Then
            reader.Read()
            TextBoxPrice.Text = reader.GetValue(0).ToString()
            reader.Close()
        End If

    End Sub

Re: GridView Update Problem

Any help please??

Re: GridView Update Problem

Hi,

Don't worry.

You have to gover proper order to the update parameters and remove braces from the where clause.

UpdateCommand="Update tbl_MaintenanceSubTemp SET Quantity=@Quantity, MaintenanceTermsID=@MaintenanceTermsID, Total=@Total where (MaintenanceSubTempID=@MaintenanceSubTempID)"
Change this to :
                    UpdateCommand="Update tbl_MaintenanceSubTemp SET Quantity=@Quantity, MaintenanceTermsID=@MaintenanceTermsID, Total=@Total where MaintenanceSubTempID=@MaintenanceSubTempID"

<UpdateParameters>

                        <asp:Parameter Name="Quantity" />
                        <asp:Parameter Name="MaintenanceTermsID" />
                        <asp:Parameter Name="Total" />
                        <asp:Parameter Name="MaintenanceSubTempID" />
                    </UpdateParameters>

tHis will work for you.

Re: GridView Update Problem

lizareay

Hi,

Don't worry.

You have to gover proper order to the update parameters and remove braces from the where clause.

UpdateCommand="Update tbl_MaintenanceSubTemp SET Quantity=@Quantity, MaintenanceTermsID=@MaintenanceTermsID, Total=@Total where (MaintenanceSubTempID=@MaintenanceSubTempID)"
Change this to :
                    UpdateCommand="Update tbl_MaintenanceSubTemp SET Quantity=@Quantity, MaintenanceTermsID=@MaintenanceTermsID, Total=@Total where MaintenanceSubTempID=@MaintenanceSubTempID"

<UpdateParameters>

                        <asp:Parameter Name="Quantity" />
                        <asp:Parameter Name="MaintenanceTermsID" />
                        <asp:Parameter Name="Total" />
                        <asp:Parameter Name="MaintenanceSubTempID" />
                    </UpdateParameters>

tHis will work for you.

Thank you for your reply,

but still I have the same problem, the DropDownList becomes NULL in the database :(

Re: GridView Update Problem

Hi,

If you are lost the data in the gridview when you update it you can try to rebind it with the database.

You can learn the samples in the below links. I think they will give you some help.

http://www.codeproject.com/Articles/24741/Nesting-the-DropDownList-to-Gridview-in-ASP-NET-2

http://www.codeproject.com/Articles/23471/Editable-GridView-in-ASP-NET-2-0

http://msdn.microsoft.com/en-us/library/ms178294.aspx

Re: GridView Update Problem

Hi all,

The problem is, once I click on the Edit button, it refresh the dropdownlist from the selected value ex. 10034 to "Choose"

How can I solve this problem.??

the dropdownlist should keep the same selected value

Re: GridView Update Problem

Can anyone help me pleaase!!!

Re: GridView Update Problem

I want to clarify something, when I click on the edit button, all colomns retrive the saved data. but the dropdownlist colomn retrives the first value which is "Choose"

How can I solve this problem?

Please I need your help