Unable to understand how to transfer datatable data to ListView

Question

I am trying to display the output (in the form of multiple data tables) from a stored procedure to a ListView. I have attempted to follow the answer from C# datatable to listview , but the parameters I am passing is not of the correct type.

My ASPX file contains the following:

<asp:WizardStep runat="server">

                <asp:SqlDataSource  ID="GetHighSchoolTeamInfo" runat="server" ConnectionString="<%$ ConnectionStrings:FollowingHSFootballConnectionString %>"
                                    SelectCommand="Select [HighSchoolFootballTeam$].[HighSchoolName], [HighSchoolFootballTeam$].[HighSchoolTeamID] from [HighSchoolFootballTeam$]
                                                        Where [HighSchoolFootballTeam$].[HighSchoolName] = @HighSchoolName">
                    <SelectParameters>
                        <asp:ControlParameter Name="HighSchoolName" ControlID="CheckBoxClassRegion" PropertyName="SelectedValue" />
                    </SelectParameters>
                </asp:SqlDataSource>

                <asp:button id="Submit1" runat="server" Text="Submit" OnClick="CheckBoxClassRegion_btnSubmit" />

                <asp:Label ID="AddressText" runat="server" />

        </asp:WizardStep>

        :
        :
        :

       <asp:ListView ID="ListView1" runat="server">
         <AlternatingItemTemplate>
               <tr style="">
                            <td>
                                <asp:Label Text='<%# Eval("Date") %>' runat="server" ID="DateLabel" /></td>
                            <td>
                                <asp:Label Text='<%# Eval("HighSchoolName") %>' runat="server" ID="HighSchoolNameLabel" /></td>
                            <td>
                                <asp:Label Text='<%# Eval("HighSchoolName1") %>' runat="server" ID="HighSchoolName1Label" /></td>
               </tr>
          </AlternatingItemTemplate>
          <EditItemTemplate>
               <tr style="">
                            <td>
                                <asp:Button runat="server" CommandName="Update" Text="Update" ID="UpdateButton" />
                                <asp:Button runat="server" CommandName="Cancel" Text="Cancel" ID="CancelButton" />
                            </td>
                            <td>
                                <asp:TextBox Text='<%# Bind("Date") %>' runat="server" ID="DateTextBox" /></td>
                            <td>
                                <asp:TextBox Text='<%# Bind("HighSchoolName") %>' runat="server" ID="HighSchoolNameTextBox" /></td>
                            <td>
                                <asp:TextBox Text='<%# Bind("HighSchoolName1") %>' runat="server" ID="HighSchoolName1TextBox" /></td>
               </tr>
           </EditItemTemplate>
                    <EmptyDataTemplate>
                        <table id="Table1" runat="server" style="">
                            <tr>
                                <td>No data was returned.</td>
                            </tr>
                        </table>
                    </EmptyDataTemplate>
                    <InsertItemTemplate>
                        <tr style="">
                            <td>
                                <asp:Button runat="server" CommandName="Insert" Text="Insert" ID="InsertButton" />
                                <asp:Button runat="server" CommandName="Cancel" Text="Clear" ID="CancelButton" />
                            </td>
                            <td>
                                <asp:TextBox Text='<%# Bind("Date") %>' runat="server" ID="DateTextBox" /></td>
                            <td>
                                <asp:TextBox Text='<%# Bind("HighSchoolName") %>' runat="server" ID="HighSchoolNameTextBox" /></td>
                            <td>
                                <asp:TextBox Text='<%# Bind("HighSchoolName1") %>' runat="server" ID="HighSchoolName1TextBox" /></td>
                        </tr>
                    </InsertItemTemplate>
                    <ItemTemplate>
                        <tr style="">
                            <td>
                                <asp:Label Text='<%# Eval("Date") %>' runat="server" ID="DateLabel" /></td>
                            <td>
                                <asp:Label Text='<%# Eval("HighSchoolName") %>' runat="server" ID="HighSchoolNameLabel" /></td>
                            <td>
                                <asp:Label Text='<%# Eval("HighSchoolName1") %>' runat="server" ID="HighSchoolName1Label" /></td>
                        </tr>
                    </ItemTemplate>
                    <LayoutTemplate>
                        <table id="Table2" runat="server">
                            <tr id="Tr1" runat="server">
                                <td id="Td1" runat="server">
                                    <table runat="server" id="itemPlaceholderContainer" style="" border="0">
                                        <tr id="Tr2" runat="server" style="">
                                            <th id="Th1" runat="server">Date</th>
                                            <th id="Th2" runat="server">HighSchoolName</th>
                                            <th id="Th3" runat="server">HighSchoolName1</th>
                                        </tr>
                                        <tr runat="server" id="itemPlaceholder"></tr>
                                    </table>
                                </td>
                            </tr>
                            <tr id="Tr3" runat="server">
                                <td id="Td2" runat="server" style=""></td>
                            </tr>
                        </table>
                    </LayoutTemplate>
                    <SelectedItemTemplate>
                        <tr style="">
                            <td>
                                <asp:Label Text='<%# Eval("Date") %>' runat="server" ID="DateLabel" /></td>
                            <td>
                                <asp:Label Text='<%# Eval("HighSchoolName") %>' runat="server" ID="HighSchoolNameLabel" /></td>
                            <td>
                                <asp:Label Text='<%# Eval("HighSchoolName1") %>' runat="server" ID="HighSchoolName1Label" /></td>
                        </tr>
                    </SelectedItemTemplate>
                </asp:ListView>

The C# code behind file contains the following

>     protected void CheckBoxClassRegion_btnSubmit(object sender, EventArgs e)
>     {
>         int i;
>         AddressText.Text += " <br />";
>         /**********************************************************************/
>         /* The code below will initialize the connection to the database.     */
>         /* As the connection string to the SQL database is defined as conn,   */
>         /* the open method from conn will connect to the database, and the    */
>         /* cmd variable will call on the stored procedure GetSchedule.        */
>         /**********************************************************************/
>         string strcon = WebConfigurationManager.ConnectionStrings["FollowingHSFootballConnectionString"].ConnectionString;
>         using (SqlConnection conn = new SqlConnection(strcon))
>         {
>             SqlCommand cmd = new SqlCommand("GetSchedule", conn);
>             cmd.CommandType = CommandType.StoredProcedure;
>             conn.Open();
>             /**********************************************************************/
>             /* The for loop below will determine which items from the checkbox    */
>             /* were selected from the input and use the High School team name to  */
>             /* pass to the stored procedure 'GetSchedule' to return the dates,    */
>             /* home team and away team each game.                                 */
>             /**********************************************************************/
>             /******************************************************************************************************************************************/
>             foreach (ListItem item in CheckBoxClassRegion.Items)                    /******************************************************************/
>             {                                                                       /* This loop will go through all of the checkboxed items          */
>                 if (item.Selected == true)                                            /******************************************************************/
>                 {                                                                   /*    If this team has been selected                              */
>                     cmd.Parameters.Clear();                                           /*       Pass input parameter "Team Name"                         */
>                     cmd.Parameters.AddWithValue("@TeamName", item.Value);        
> /******************************************************************/
>                     SqlDataAdapter da = new SqlDataAdapter(cmd);
>                     DataTable dt = new DataTable();
>                     da.Fill(dt);
>                     for (i = 0; i <= dt.Rows.Count - 1; i++) 
>                     {
>                         ListViewItem itm = new ListViewItem(dt.Rows[i].ToString());
>                             
>                         ListView1.DataSource.ToString(itm);
>                        
>                     }
> 
>                 }
>             }
>         }
>     }

The correct output is being produced from the stored procedure when da.Fill(dt); is executed, so my confusion is with the loop that follows. Can anyone aid me in understanding what data type or possibly what method is the correct approach?

Thank You

Answer 1

Like I said before, you are trying to use a Windows Forms ListView example, and it doesn't work the same way.

Fortunately, it can be much easier to do. First, create a class to represent your query tuples. I don't know if the types are the same I used, just update it if needed:

// Create a class to store every tuple of your query.
public class QueryTuple
{
    public DateTime Date { get; set; }

    public string HighSchoolName { get; set; }

    public string HighSchoolName1 { get; set; }

    // For NULL values that maps to struct types, use Nullable<T>
    public Nullable<int> AwayScore {get; set; }

    // Nullable types can also be written with a ? after the type
    public int? HomeScore { get; set; }
}

Then, update your foreach code for something like this.

List<QueryTuple> tuples = new List<QueryTuple>();

foreach (ListItem item in CheckBoxClassRegion.Items)
{
    if (item.Selected == true)
    {
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@TeamName", item.Value);

        // Make sure your connection is open before ExecuteReader
        using (var dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                QueryTuple tuple = new QueryTuple
                {
                    Date = (DateTime)dr["Date"],
                    HighSchoolName = (string)dr["HighSchoolName"],
                    HighSchoolName1 = (string)dr["HighSchoolName1"],

                    // Mapping nullable types may need a bit more coding
                    HomeScore = dr["HomeScore"] == DBNull.Value ? new int?() : (int)dr["HomeScore"],
                    AwayScore = dr["AwayScore"] == DBNull.Value ? new int?() : (int)dr["AwayScore"] 
                };

                tuples.Add(tuple);
            }
        }
    }
}

// Finally, you set the DataSource property and call the DataBind method
ListView1.DataSource = tuples;
ListView1.DataBind();