Link to home
Start Free TrialLog in
Avatar of ggaarryy
ggaarryy

asked on

How to update sql with gridview from button not in gridview

I have a gridview with some checkboxes and i want to update individual rows in sql database from a button that is not part of the gridview.. below is the gridview and c# code behide for the button clcik event. I can get the data from the gridview for updating but I don't know how to do the actual database update.

               <asp:GridView ID="GridViewScheduleEditWinners" runat="server"
                AutoGenerateColumns="False" CaptionAlign="Bottom"
                DataSourceID="SqlDataSource1" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None"
                BorderWidth="1px" CellPadding="4" EmptyDataText="Data is Not Available" ForeColor="Black"
                GridLines="Vertical" AllowPaging="True"
                onrowdatabound="GridView1_RowDataBound" Font-Size="Small"
                DataKeyNames="Team1Id,Team2Id,ScheduleId">
                <RowStyle BackColor="#F7F7DE" />
                <Columns>
                     <asp:BoundField DataField="Team1Id" HeaderText="Team1Id"
                         ShowHeader="False" SortExpression="Team1Id" Visible="False" />
                     <asp:BoundField DataField="Team2Id" HeaderText="Team2Id"
                         SortExpression="Team2Id" ShowHeader="False" Visible="False" />
                     <asp:BoundField DataField="date" HeaderText="Date" SortExpression="date"
                        DataFormatString="{0:MMMM dd}" />
                               
               
                    <asp:BoundField DataField="date" HeaderText="Time" SortExpression="date"
                        DataFormatString="{0:h:mm tt}" />
                    <asp:BoundField DataField="Name" HeaderText="Field" SortExpression="Name" />
                    <asp:BoundField DataField="Team1Name" HeaderText="Team1Name"
                         SortExpression="Team1Name" />
                     <asp:TemplateField HeaderText="Won">
                         <ItemTemplate>
                             <asp:CheckBox ID="Team1Won" runat="server" />
                         </ItemTemplate>
                     </asp:TemplateField>
                     <asp:BoundField DataField="Team2Name" SortExpression="Team2Name"
                         HeaderText="Team2Name" />
                     <asp:TemplateField HeaderText="Won">
                         <ItemTemplate>
                             <asp:CheckBox ID="Team2Won" runat="server" />
                         </ItemTemplate>
                     </asp:TemplateField>
                </Columns>
                <FooterStyle BackColor="#CCCC99" />
                <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
                <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
                <AlternatingRowStyle BackColor="White" />
            </asp:GridView>

            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CISoftballConnectionString %>"
             
                SelectCommand="SELECT Leagues.Year, Leagues.LeagueId, ScheduleWeekTimeField.date, Fields.FieldId, Fields.Name, Teams.TeamName AS Team1Name, Teams_1.TeamName AS Team2Name, ScheduleTeams.Team1Id, ScheduleTeams.Team2Id, ScheduleTeams.ScheduleId FROM Leagues INNER JOIN ScheduleWeekTimeField ON Leagues.Year = ScheduleWeekTimeField.year AND Leagues.LeagueId = ScheduleWeekTimeField.LeagueId INNER JOIN Fields ON ScheduleWeekTimeField.fieldId = Fields.FieldId INNER JOIN ScheduleTeams ON ScheduleWeekTimeField.ScheduleId = ScheduleTeams.ScheduleId INNER JOIN Teams ON Leagues.Year = Teams.Year AND Leagues.LeagueId = Teams.LeagueId AND ScheduleTeams.Team1Id = Teams.TeamNumber INNER JOIN Teams AS Teams_1 ON Leagues.Year = Teams_1.Year AND Leagues.LeagueId = Teams_1.LeagueId AND ScheduleTeams.Team2Id = Teams_1.TeamNumber WHERE (Leagues.Year = '2011') AND (Leagues.LeagueId = @LId) AND (ScheduleWeekTimeField.week &gt;= @wk) ORDER BY ScheduleWeekTimeField.date, Fields.FieldId DESC"

                UpdateCommand="UPDATE ScheduleTeams SET ScheduleId =, TeamIdWon = @TeamWonId, TeamIdLost = @TeamLostId WHERE (ScheduleId = @ScheduleId)">
                <SelectParameters>
                    <asp:ControlParameter ControlID="DropDownList1" DefaultValue="1" Name="LId"
                        PropertyName="SelectedValue" />
                    <asp:ControlParameter ControlID="DropDownList2" DefaultValue="1" Name="wk"
                        PropertyName="SelectedValue" />
                </SelectParameters>
               
                <UpdateParameters>
                    <asp:Parameter Name="TeamWonId" />
                    <asp:Parameter Name="TeamLostId" />
                    <asp:Parameter Name="ScheduleId" />
                </UpdateParameters>
               
            </asp:SqlDataSource>



 protected void ButtonUpdateWins_Click(object sender, EventArgs e)
    {
        CheckBox cb;
        string ScheduleId;
        string Team1WonId;
        string Team2LostId;
        Boolean gotacheck = false;
        // Iterate through the Products.Rows property
        foreach (GridViewRow row in GridViewScheduleEditWinners.Rows)
        {
            // Access the CheckBoxs
            cb = (CheckBox)row.FindControl("Team1Won");
           
            if (cb != null && cb.Checked)
            {
                gotacheck = true;
                Team1WonId = GridViewScheduleEditWinners.DataKeys[row.RowIndex].Values[0].ToString();
                Team2LostId = GridViewScheduleEditWinners.DataKeys[row.RowIndex].Values[1].ToString();
                ScheduleId = GridViewScheduleEditWinners.DataKeys[row.RowIndex].Values[2].ToString();
            }

            cb = (CheckBox)row.FindControl("Team2Won");

            if (cb != null && cb.Checked)
            {
                gotacheck = true;
                Team1WonId = GridViewScheduleEditWinners.DataKeys[row.RowIndex].Values[1].ToString();
                Team2LostId = GridViewScheduleEditWinners.DataKeys[row.RowIndex].Values[0].ToString();
                ScheduleId = GridViewScheduleEditWinners.DataKeys[row.RowIndex].Values[2].ToString();
            }

What do i do here to update the database?
       }

Avatar of TonyReba
TonyReba
Flag of United States of America image

Whenever you have here:

   UpdateCommand="UPDATE ScheduleTeams SET ScheduleId =, TeamIdWon = @TeamWonId, TeamIdLost = @TeamLostId WHERE (ScheduleId = @ScheduleId)">


is what you are sending to the database to update  ,  what fields are you tring to update?
After  taking out values
     
       if (cb != null && cb.Checked)
        {
                gotacheck = true;
          Team1WonId = GridViewScheduleEditWinners.DataKeys[row.RowIndex].Values[0].ToString();
            Team2LostId = GridViewScheduleEditWinners.DataKeys[row.RowIndex].Values[1].ToString();
            ScheduleId = GridViewScheduleEditWinners.DataKeys[row.RowIndex].Values[2].ToString();
        }

you can update it
I didn't get ur point
I am also noticing that you left ScheduleId =    without parameters in the below code,......


UpdateCommand="UPDATE ScheduleTeams SET ScheduleId =, TeamIdWon = @TeamWonId, TeamIdLost = @TeamLostId WHERE (ScheduleId = @ScheduleId)">

your update should be
UpdateCommand="UPDATE ScheduleTeams SET ScheduleId = @ScheduleId, TeamIdWon = @TeamWonId, TeamIdLost = @TeamLostId WHERE (ScheduleId = @ScheduleId)">


Avatar of ggaarryy
ggaarryy

ASKER

Thanks Tony
My question is how do i send the update command to the database to do the update?
ASKER CERTIFIED SOLUTION
Avatar of ggaarryy
ggaarryy

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
no comment
My last comment was the only one that answered by problem