Click here to Skip to main content
15,867,771 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Thank you for your help with this.
I have a GridView displaying data from the "Attendees" SQL table.
Column 5 is a TemplateField with a RadioButtonList, RbCompleted, (Yes/No/Pending).
Column 9 is a TemplateField with a TextBox, TbHoursCompleted.

The RadioButton, RbCompleted, in the EditItemTemplate: When Yes is selected, how can I put a value from the "Training" SQL table into the TextBox, TbHoursCompleted? (SELECT Hours FROM Training.dbo.Training WHERE TrainingID = @TrainingID)

This is so that when staff edit an Attendee's record and select that the training has been Completed, the HoursCompleted text box on the same row that's being edited is populated from the Hours column of the Training table where the TrainingID from the selected row of the GridView matches the TrainingID in the Training table. Remember the GridView is showing data from the Attendees table.

Thank you!

ASP.NET
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="AttendeeID" DataSourceID="AttendeeListDataSource1" CssClass="center" CellPadding="4" ForeColor="#333333" GridLines="None">
      <AlternatingRowStyle BackColor="White" />
      <Columns>
          <asp:CommandField ShowEditButton="True" UpdateText="Save" />
          <asp:BoundField DataField="TrainingID" HeaderText="Training ID" InsertVisible="False" ReadOnly="True" SortExpression="TrainingID" Visible="True" />
          <asp:BoundField DataField="AttendeeID" HeaderText="Attendee ID" InsertVisible="False" ReadOnly="True" SortExpression="AttendeeID" Visible="True" />
          <asp:BoundField DataField="DisplayName" HeaderText="Attendee Name" SortExpression="DisplayName" ItemStyle-CssClass="colPad" Visible="True" ReadOnly="True">
          <ItemStyle HorizontalAlign="Left" />
          </asp:BoundField>
          <asp:BoundField DataField="ScheduledDate" HeaderText="Scheduled Date" SortExpression="ScheduledDate" ApplyFormatInEditMode="True" DataFormatString="{0:d}" HtmlEncode="False" ControlStyle-Width="80px" ControlStyle-CssClass="dateField" >
              <ControlStyle CssClass="dateField" Width="80px"></ControlStyle>
          </asp:BoundField>
          <asp:TemplateField HeaderText="Completed" SortExpression="Completed" ControlStyle-CssClass="colPad">
              <EditItemTemplate>
                  <%--<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Completed") %>'></asp:TextBox>--%>
                  <asp:RadioButtonList ID="RbCompleted" runat="server" Text='<%# Bind("Completed") %>' OnSelectedIndexChanged="RbCompleted_SelectedIndexChanged">
                      <asp:ListItem>Yes</asp:ListItem>
                      <asp:ListItem>No</asp:ListItem>
                      <asp:ListItem>Pending</asp:ListItem>
                  </asp:RadioButtonList>
              </EditItemTemplate>
              <ItemTemplate>
                  <asp:Label ID="Label1" runat="server" Text='<%# Bind("Completed") %>'></asp:Label>
              </ItemTemplate>
              <ControlStyle CssClass="colPad"></ControlStyle>
          </asp:TemplateField>
          <asp:BoundField DataField="CompletedDate" HeaderText="Completed Date" SortExpression="CompletedDate" ApplyFormatInEditMode="True" DataFormatString="{0:d}" HtmlEncode="False" ControlStyle-Width="80px" ControlStyle-CssClass="dateField" >
              <ControlStyle CssClass="dateField" Width="80px"></ControlStyle>
          </asp:BoundField>
          <asp:BoundField DataField="RescheduledDate" HeaderText="Rescheduled Date" SortExpression="RescheduledDate" ApplyFormatInEditMode="True" DataFormatString="{0:d}" HtmlEncode="False" ControlStyle-Width="80px" ControlStyle-CssClass="dateField" >
              <ControlStyle CssClass="dateField" Width="80px"></ControlStyle>
          </asp:BoundField>
          <asp:BoundField DataField="Canceled" HeaderText="Canceled Date" SortExpression="Canceled" ApplyFormatInEditMode="True" DataFormatString="{0:d}" HtmlEncode="False" ControlStyle-Width="80px" ControlStyle-CssClass="dateField" >
              <ControlStyle CssClass="dateField" Width="80px"></ControlStyle>
          </asp:BoundField>
          <asp:TemplateField HeaderText="Hours Completed" SortExpression="HoursCompleted">
              <EditItemTemplate>
                  <asp:TextBox ID="TbHoursCompleted" runat="server" Text='<%# Bind("HoursCompleted", "{0:n2}") %>' onkeypress="return num(this);" ></asp:TextBox>
              </EditItemTemplate>
              <ItemTemplate>
                  <asp:Label ID="LbHoursCompleted" runat="server" Text='<%# Bind("HoursCompleted", "{0:n2}") %>'></asp:Label>
              </ItemTemplate>
              <ControlStyle Width="40px" />
          </asp:TemplateField>
      </Columns>
      <EditRowStyle BackColor="#FF9999" />
      <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
      <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
      <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
      <RowStyle BackColor="#EFF3FB" />
      <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
      <SortedAscendingCellStyle BackColor="#F5F7FB" />
      <SortedAscendingHeaderStyle BackColor="#6D95E1" />
      <SortedDescendingCellStyle BackColor="#E9EBEF" />
      <SortedDescendingHeaderStyle BackColor="#4870BE" />
  </asp:GridView>

  <asp:SqlDataSource ID="AttendeeListDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TrainingConnectionString %>"
      SelectCommand="SELECT [TrainingID], [AttendeeID], [DisplayName], [ScheduledDate], ISNULL([Completed],'Pending') AS Completed, [CompletedDate], [RescheduledDate], [Canceled], CASE WHEN CompletedDate IS NULL THEN '0' ELSE HoursCompleted END AS [HoursCompleted] FROM [Attendees] WHERE (ISNULL([Completed],'Pending') LIKE '%' + @Completed + '%') AND TrainingID LIKE '%' + @TrainingName + '%' AND DisplayName LIKE '%' + @AttendeeNameSearch + '%' ORDER BY [DisplayName]"
      DeleteCommand="DELETE FROM [Attendees] WHERE [AttendeeID] = @AttendeeID"
      InsertCommand="INSERT INTO [Attendees] ([DisplayName], [ScheduledDate], [Completed], [CompletedDate], [RescheduledDate], [Canceled], [HoursCompleted]) VALUES (@DisplayName, @ScheduledDate, @Completed, @CompletedDate, @RescheduledDate, @Canceled, @HoursCompleted)"
      UpdateCommand="UPDATE [Attendees] SET [ScheduledDate] = @ScheduledDate, [Completed] = @Completed, [CompletedDate] = @CompletedDate, [RescheduledDate] = @RescheduledDate, [Canceled] = @Canceled, [HoursCompleted] = @HoursCompleted WHERE [AttendeeID] = @AttendeeID">
      <DeleteParameters>
          <asp:Parameter Name="AttendeeID" Type="Int32" />
      </DeleteParameters>
      <InsertParameters>
          <asp:Parameter Name="TrainingID" Type="Int32" />
          <asp:Parameter Name="DisplayName" Type="String" />
          <asp:Parameter DbType="Date" Name="ScheduledDate" />
          <asp:Parameter Name="Completed" Type="String" />
          <asp:Parameter DbType="Date" Name="CompletedDate" />
          <asp:Parameter DbType="Date" Name="RescheduledDate" />
          <asp:Parameter DbType="Date" Name="Canceled" />
          <asp:Parameter Name="HoursCompleted" Type="Decimal" />
      </InsertParameters>
      <SelectParameters>
          <asp:ControlParameter ControlID="Completed" DefaultValue="%%" Name="Completed" PropertyName="SelectedValue" Type="String" />
          <asp:ControlParameter ControlID="TrainingName" DefaultValue="%%" Name="TrainingName" PropertyName="SelectedValue" Type="String" />
          <asp:ControlParameter ControlID="AttendeeNameSearch" DefaultValue="%%" Name="AttendeeNameSearch" PropertyName="Text" Type="String" />
      </SelectParameters>
      <UpdateParameters>
          <asp:Parameter Name="TrainingID" Type="Int32" />
          <asp:Parameter Name="DisplayName" Type="String" />
          <asp:Parameter DbType="Date" Name="ScheduledDate" />
          <asp:Parameter Name="Completed" Type="String" />
          <asp:Parameter DbType="Date" Name="CompletedDate" />
          <asp:Parameter DbType="Date" Name="RescheduledDate" />
          <asp:Parameter DbType="Date" Name="Canceled" />
          <asp:Parameter Name="HoursCompleted" Type="Decimal" />
          <asp:Parameter Name="AttendeeID" Type="Int32" />
      </UpdateParameters>
  </asp:SqlDataSource>


What I have tried:

C#
// I totally don't know what I'm doing here:
    protected void AddHoursCompleted()
    {
        SqlConnection DBConn = new SqlConnection(ConfigurationManager.ConnectionStrings["StaffRosterConnectionString"].ConnectionString);
        string sql = "SELECT TOP 1 Hours FROM Training WHERE TrainingID = @TrainingID";
        SqlCommand DBCmd = new SqlCommand(sql, DBConn);

        try
        {
            DBConn.Open();
            DBCmd.Parameters.Add("@TrainingID", SqlDbType.VarChar).Value = GridView1.SelectedRow.FindControl("TrainingID");   //GridView1.TrainingID Selected Row column Index 1;  ?    //EmployeeName.Text;

            SqlDataReader dr = DBCmd.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    //Session["UID"] = (int)dr["ID"];
                    // = dr["Hours"].ToString();  // In GridView
                    //AssignmentDate.Text = dr.GetDateTime(14).ToString("MM/dd/yyyy");         //Column Index of 14 is Effective Date
                    //HireDate.Text = dr.GetDateTime(6).ToString("MM/dd/yyyy");                    //Column Index of 6 is DOH.
                }

                dr.Close();
            }
            else
            {
                //msgFetch.Text = "No records found.";
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        DBCmd.Dispose();
        DBConn.Close();
    }

    protected void RbCompleted_SelectedIndexChanged(object sender, EventArgs e)
    {
        AddHoursCompleted();
    }
Posted
Updated 15-Dec-22 3:42am
Comments
adriancs 15-Dec-22 9:00am    
You are strongly recommended to use dynamic native HTML + javascript (AJAX) to achieve this. HTML + javascript are considered native to web development world, and it's much easier to accomplish what you need. dynamic native HTML + javascript (AJAX) can also be used in ASP.NET WebForms. Look for the demo pages of "LoadJavascriptTable.aspx" and "LoadJavascriptTable.aspx" for insights at my demo project at github: https://github.com/adriancs2/GridView-Html-Table

Here is the live demo: http://gridview.adriancs.com/

I do not know ASP.NET but C# and your code looks really messy. Therefore, I give you some hints:

1) You should use an own (static or singleton) class to generally communicate with your database wherein you only need 3-4 general methods: Read, Write, WriteMultiple and (optionally) Vacuum.

C#
public static class DB {
    private static string _ConnectionString = "...";

    public static DataTable Read(DBQuery query) { ... }
    public static void Vacuum() { ... }
    public static void Write(DBQuery query) { ... }
    public static void WriteMultiple(DBQueries queries) { ... }
}

You call those methods (exept Vacuum) with the prepared parameterized DB query (or queries) via your entity class(es), see below.

2) For each entity (e.g. "Attendee" and "Trainings") you should have an own entity class where you define the properties and the corresponding database methods you really need, e.g.: MapFromDB, ReadMaxID, ReadAllFromDB, ReadFromDB, RemoveFromDB, SaveToDB.

Within those database methods you build the corresponding SQL queries and parameters, call the method of the DB class and (when using Read...) map the resulting DataTable back to the entity object(s) via the MapFromDB() method.

You should also think about which entities you want to display and if your approach makes sense. As it seems you use a mix of attendees and trainings, you need an own entity type for this which includes properties of both and database methods that affect 2 different SQL tables at once. Furthermore, I doubt it is reasonable to display the total training hours of an attendee within a list where one attendee can occur multiple times as hereby you need to update all those entries at once. It would be better to use a separate sub GridView to display all trainings for a single attendee and display (and update) the total training hours as header info and/or in a GridView which only displays attendees.

3) Do not bind controls (like a GridView) directly to a database source, instead use (Binding)Lists to work directly with the entity objects and their methods. This is the classic approach which forces you to rework your code but always keeps you in full control when anything is read from or updated on the database.

4) The GridView handling is as follows:

Read all entities from the database and map those to a (Binding)List. Then use/bind this list to your GridView source.

When interacting within the GridView (e.g. edit cells, using command functions, etc.) call separate methods for your GridView wherein you use and manipulate the single entity object of the selected GridView row and, if needed, update or read from the database with the database methods you have in your entity class. A much better approach would even be to use separate add and edit dialogues with a "Save"/"Apply"/"OK" button for any data manipulation.

So, when using command functions within your GridView row, get the single entity by using the ID column of your selected row and then finding the entity via this ID in your (Binding)List. Now, work directly with that single entity.

In your case, within the method RbCompleted_SelectedIndexChanged() (which is triggered when you change the radio button) you need to use the corresponding row index to read the contents of the column "AttendeeID" and/or "TrainingID" to get the corresponding single entity of the GridView datasource (which should be a (Binding)List). When you have it, you can manipulate this single entity and use its database method (e.g. ReadTotalTrainingHours() or UpdateTotalTrainingHours()) to update its property.

By updating the entity the GridView should display the updated value automatically. If not, you need to additionally call the GridView.InvalidateRow() method.

Good luck!
 
Share this answer
 
v2
Comments
David Megnin 2022 14-Dec-22 8:34am    
Hi tardezyx,
Thank you for your reply. I don't doubt that my code is a mess. I have never had formal instruction. I've picked up what little I know by Google and trial and error. Mostly error. ;-)
I will study your hints and try to apply what I can understand.
David Megnin 2022 14-Dec-22 8:47am    
After reading; in the third paragraph of your second hint, you state (correctly) "I doubt it is reasonable to display the total training hours of an attendee within a list where one attendee can occur multiple times as hereby you need to update all those entries at once."
What I'm trying to do is to Group By the DisplayName so that there is only one row per DisplayName with the (pseudo code:) SUM(dbo.Training.Hours) WHERE Training.TrainingID = Attendees.TrainingID. The hours will always come from the Training table and never updated in this GridView. That's the goal, anyway. I just wanted to explain what I'm trying to do.
Thank you, again.
David Megnin 2022 14-Dec-22 11:02am    
I don't know how to reference the TextBox in order to put a value into it. Could someone please help me with this?
This can be easily achieved by using pure HTML + javascript (AJAX).

You may have a look at my demo project on this topic at:

GitHub - adriancs2/GridView-Html-Table: A Comparison Doing a Table with GridView and Dynamic HTML Table in ASP.NET WebForms[^]

Look for "LoadJavascriptTable.aspx" and "LoadJavascriptTable.aspx" inside the project, it's a ASP.NET WebForms project.

Or you can also read my codeproject articles:

Part 1 - GridView vs Dynamic HTML Table (ASP.NET WebForms)[^]

Part 2: GridView vs dynamic HTML Table (ASP.NET WebForms)[^]

Here is an insight of how is this possible to be done.

1st create a blank ASP.NET page, for example "apiGetTrainingHours.aspx"
ASP.NET
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="apiGetTrainingHours.aspx.cs" Inherits="Demo.apiGetTrainingHours" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
        </div>
    </form>
</body>
</html>
Delete everything and left only the first line:
ASP.NET
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="apiGetTrainingHours.aspx.cs" Inherits="Demo.apiGetTrainingHours" %> 

Go to code behind, type your coding of getting the hours from database:
C#
protected void Page_Load(object sender, EventArgs e)
{
    int attendeeID = Convert.ToInt32(Request.QueryString["attendeeID"]);
    int trainingId = Convert.ToInt32(Request.QueryString["trainingId"]);

    int hours = GetHoursFromDatabase(attendeeID, trainingId);

    Response.Write(hours.ToString());
}

int GetHoursFromDatabase(int attendeeID, int trainingId)
{
    // load hours from database 
}
At your main page where you want to show the table, creates a placeholder:
ASP.NET
<asp:PlaceHolder ID="ph1" runat="server"></asp:PlaceHolder>
An example of class to hold the data:
C#
class Training
{
    public int AttendeeID { get; set; }
    public int TrainingId { get; set; }

    public Training(int attendeeID, int trainingId)
    {
        AttendeeID = attendeeID;
        TrainingId = trainingId;
    }
}
At the code behind of the main page, build the html table
C#
List<Training> lst = new List<Training>();
lst.Add(new Training(1, 1));
lst.Add(new Training(2, 1));
lst.Add(new Training(3, 2));

StringBuilder sb = new StringBuilder();

sb.Append(@"
<table>
<tr>
<th>AttendeeID</th>
<th>TrainingId</th>
<th>Completed</th>
<th>Hours</th>
</tr>
");

foreach(Training t in lst)
{
    sb.Append($@"
<tr>
<td>{t.AttendeeID}</td>
<td>{t.TrainingId}</td>
<td>
<select onchange='loadHours(this, {t.AttendeeID}, {t.TrainingId});'>
<option value='0'>No</option>
<option value='1'>Yes</option>
<option value='2'>Pending</option>
</select>
</td>
<td><input type='text' id='txtHours_{t.AttendeeID}_{t.TrainingId}' name='txtHours_{t.AttendeeID}_{t.TrainingId}' /></td>
</tr>
");
}

sb.Append("</table>");

ph1.Controls.Add(new LiteralControl(sb.ToString()));
Notice that, in above code, the HTML component of "SELECT", will trigger a javascript function called "loadHours( )".

At the front page, insert the following javascript to load the hours by using AJAX:
JavaScript
function loadHours(selectInput, attendeeID, trainingId) {
    if (selectInput.value != "1")
        return;

    const xhttp = new XMLHttpRequest();
    xhttp.onload = function () {
        let hours = this.responseText;
        let txtHours = document.getElementById(`txtHours_${attendeeID}_${trainingId}`);
        txtHours.value = hours;
    }
    xhttp.open("GET", `apiGetTrainingHours.aspx?attendeeID=${attendeeID}&trainingId=${trainingId}`, true);
    xhttp.send();
}
 
Share this answer
 
v9
Comments
David Megnin 2022 15-Dec-22 9:57am    
Hi adriancs,
Thank you very much for your reply. I didn't explain my problem very well, maybe.
That TextBox in my GridView; I just want to put a number in it. That's all. I don't know how to "address" that TextBox.

GridView1.TbHoursCompleted.Text = n

That line doesn't work in my AddHoursCompleted() method.
What line will work?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900