I want to display data from two different database tables inside each datalist item, specifically details about a job application, and then using the ID of the application's owner to display some of the owner's details alongside it. Could anyone help me with an approach to doing this.
This is the asp.net view of the datalist:
<asp:DataList ID="applicationsDataList" runat="server" RepeatColumns="4">
<ItemTemplate >
<div class="applicationCard">
<table>
<tr>
<td>
<asp:Label ID="lblJobIndustry" runat="server" Text=<%#Eval("jobIndustry")%>/>
</td>
</tr>
<tr>
<td>
<asp:Image runat="server" ImageUrl="/Images/temp.jpg" />
</td>
</tr>
<tr>
<td>
<asp:Label ID="LblJobPosition" runat="server" Text= <%#Eval("jobPosition")%>/>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label3" runat="server" Text=<%#Eval("firstName")%>/>
<asp:Label ID="Label5" runat="server" Text=<%#Eval("lastName")%>/>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label4" runat="server" Text=<%#Eval("jobLocation")%>/>
</td>
</tr>
</table>
</div>
</ItemTemplate>
</asp:DataList>
This is the code behind, where I was not sure how to do this, but gives an idea of my approach:
string CS = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
OleDbConnection Connection = new OleDbConnection(CS);
OleDbConnection connection2 = new OleDbConnection(CS);
string sqlCom1 = "SELECT [jobSeekerID], [jobPosition], [jobIndustry] FROM [SeekerJobApplication] WHERE ([postingID] = 0)";
OleDbCommand oleCom1 = new OleDbCommand(sqlCom1, Connection);
Connection.Open();
OleDbDataReader applications = oleCom1.ExecuteReader();
while (applications.Read())
{
string jobSeekerID = applications[0].ToString();
string SqlCom2 = "SELECT [firstName], [lastName] FROM [JobSeeker] WHERE ([jobSeekerID] = " jobSeekerID ");";
OleDbCommand oleCom2 = new OleDbCommand(SqlCom2, connection2);
OleDbDataReader name = oleCom1.ExecuteReader();
while (name.Read())
{
string firstName = name[0].ToString();
string lastName = name[1].ToString();
}
string jobPosition = applications[1].ToString();
string jobIndustry = applications[2].ToString();
applicationsDataList.DataSource = name;
applicationsDataList.DataBind();
connection2.Close();
}
OleDbDataAdapter da = new OleDbDataAdapter(oleCom1);
DataSet dataSet = new DataSet();
da.Fill(dataSet, "SeekerJobApplication");
applicationsDataList.DataSource = applications;
applicationsDataList.DataBind();
Thanking you geniuses in advance!
CodePudding user response：
Well, it not clear if you want to display data from the first table as some kind of grid, and then when you click on a row of that grid, display details for that row selection?
or, do you want the main data child data both displayed in the same grid or view?
I mean, we could even say have some rows of data, and say you hit a " " button to expand and display the child data?
I find that trying to nest two gridviews is a bit messy. But a listview (main table), and a child gridview works rather nice.
So we have this markup. Not a "large" amount, but it does give rise to some nice formatting
So this:
<div style="width:44%;margin-left:30px">
<asp:ListView ID="ListView1" runat="server" DataKeyNames="ID" >
<ItemTemplate>
<tr style="">
<td><asp:Button ID="cmdView" runat="server" Text=" " OnClick="cmdView_Click" /></td>
<td><asp:Label ID="HotelNameLabel" runat="server" Text='<%# Eval("HotelName") %>' /></td>
<td><asp:Label ID="CityLabel" runat="server" Text='<%# Eval("City") %>' /></td>
<td><asp:Label ID="ProvinceLabel" runat="server" Text='<%# Eval("Province") %>' /></td>
<td><asp:Label ID="DescriptionLabel" runat="server" Text='<%# Eval("Description") %>' /></td>
</tr>
<tr>
<td colspan="5">
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"
DataKeyNames="ID" CssClass="table table-hover" style="display:none;margin-left:20px" >
<Columns>
<asp:BoundField DataField="Firstname" HeaderText="Firstname" />
<asp:BoundField DataField="LastName" HeaderText="LastName" />
<asp:BoundField DataField="City" HeaderText="City" />
</Columns>
</asp:GridView>
</td>
</tr>
</ItemTemplate>
<LayoutTemplate>
<table id="itemPlaceholderContainer" runat="server" class = "table table-hover" >
<tr runat="server" style="">
<th runat="server">View</th>
<th runat="server">HotelName</th>
<th runat="server">City</th>
<th runat="server">Province</th>
<th runat="server">Description</th>
</tr>
<tr id="itemPlaceholder" runat="server">
</tr>
</table>
</LayoutTemplate>
</asp:ListView>
</div>
Ok, our code to display this is thus:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
LoadGrid();
}
public void LoadGrid()
{
ListView1.DataSource = MyRst("SELECT * FROM tblHotels ORDER BY HotelName");
ListView1.DataBind();
}
And we now have this:
So, now all we need is the code for the " " button to display the child data.
That button code looks like this:
protected void cmdView_Click(object sender, EventArgs e)
{
Button cmd = (Button)sender;
ListViewDataItem gVRow = (ListViewDataItem)cmd.Parent;
GridView gChild = (GridView)gVRow.FindControl("GridView2"); // pluck out the grid for this row
if (gChild.Style["display"] == "normal")
{
// if grid is already display, then hide it, and exit
gChild.Style["display"] = "none";
return;
}
gChild.Style["display"] = "normal";
// only re-load if never loaded
if (gChild.Rows.Count == 0)
{
int HotelPK = (int)ListView1.DataKeys[gVRow.DataItemIndex]["ID"];
gChild.DataSource = MyRst("SELECT * from tblPeople where hotel_id = " HotelPK);
gChild.DataBind();
}
}
And now when we click on a we see this:
And really cool?
The listview "remembers" the sate of the style we set (to hide/show).
thus, if you click on a expanded , it will hide.
So, I can freely expand more rows, say like this:
And of course I have a "helper" routine that returns a data table for a given sql (was getting tired of typing that code over and over). So MyRst() is this:
public DataTable MyRst(string strSQL)
{
var rst = new DataTable();
using (OleDbConnection con = new OleDbConnection(Properties.Settings.Default.AccessDB))
{
using (OleDbCommand cmdSQL = new OleDbCommand(strSQL, con))
{
con.Open();
// fill items table
rst.Load(cmdSQL.ExecuteReader());
}
}
return rst;
}
So, we write VERY little amounts of code.
And we did not even really have much mark-up.
But the results are quite amazing - a expanding collapsing grid, and all with VERY little code.
Now, I suppose you could have the grid start out expanded, but with above, we have VERY good performance, since we ONLY pull display the child rows when the " " button is clicked. And even better, since the list view does remember its state? Then I don't re-load if we expand a row that already previous was expanded. So this setup is very good performance wise.
Note that I did assume that bootstrap was included, and thus included the class = "table table-hover". This makes the two grids look VERY nice.