I have an ASP.NET HTML website in which I want to update my SQLDataSource SELECT command using a drop-down menu. The user can select to sort by Date, Duration or Player, and the GridView will update showing the new, sorted results from my mdf database. I'm really not sure at all how to approach this as I am fairly new to ASP.NET. I could just do with some pointers on how to go about it, and what method to use. I can google tutorials, the problem is knowing what to google. Any advice greatly appreciated :) My code:
<form id="form1" runat="server">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="RunsData">
<Columns>
<asp:BoundField DataField="PlayerId" HeaderText="PlayerId" SortExpression="PlayerId" />
<asp:BoundField DataField="Duration" HeaderText="Duration" SortExpression="Duration" />
<asp:BoundField DataField="VersionId" HeaderText="VersionId" SortExpression="VersionId" />
<asp:BoundField DataField="DateUploaded" HeaderText="DateUploaded" SortExpression="DateUploaded" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="RunsData" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [PlayerId], [Duration], [VersionId], [DateUploaded] FROM [Run] ORDER BY [Duration]"></asp:SqlDataSource>
</form>
<div>
<label class="label" for="version">Version: </label>
<select name="version" id="version">
<option value="lev1">Level 1</option>
<option value="lev1to3">Level 1-3</option>
<option value="lev8">Level 8</option>
<option value="lev17">Level 17</option>
</select>
<label class="label" for="sortby">Sort By: </label>
<select name="sortby" id="sortby">
<option value="duration">Duration</option>
<option value="date">Date</option>
<option value="player">Player</option>
</select>
</div>
</div>
CodePudding user response：
Ok, first up, I would think it is better to have the filters at the top (above) the grid, and not below? (just a idea).
and in fact, even better idea would be to drop in the two combo boxes RIGHT into the grid heading - even better!!!
but, lets take this one step at a time.
First up, I recommend we dump (remove) the data source in the web page. The can be handy, wizards generate them - thank you much - all good. However, EVEN when I often use a wizard to create the GV, I THEN blow out the data source, and use code. And when is a GOOD idea to use code to fill the GV?
Why of course when you want to filter it!!!
So, lets do this:
Delete/remove the Sqldata source - we not going to use it.
Remove this from GV - DataSourceID="RunsData"
Also, you clear used the wizards to create and setup that GV, but then now use select html? Why not use DropDown lists? The wizards not only can build them for you (like the GV, they also have a rich .net event model. And you can shove, drive the dropdown list just like the GV (they are data source friendly).
So, we going to assume this markup now:
<div style="float:left">
<label class="label" for="version">Version: </label>
<asp:DropDownList ID="cboVersion" runat="server" Width="150px">
<asp:ListItem></asp:ListItem>
<asp:ListItem Value="1">Level 1</asp:ListItem>
<asp:ListItem Value="1-3">Level 1-3</asp:ListItem>
<asp:ListItem Value="8">Level 8</asp:ListItem>
<asp:ListItem Value="17">Level 17</asp:ListItem>
</asp:DropDownList>
</div>
<div style="float:left;padding-left:25px">
<label class="label" for="sortby">Sort By:</label>
<asp:DropDownList ID="cboSortBy" runat="server" Width="150px">
<asp:ListItem></asp:ListItem>
<asp:ListItem Value="duration">Duration</asp:ListItem>
<asp:ListItem Value="DateUploaded">Date</asp:ListItem>
<asp:ListItem>Player</asp:ListItem>
</asp:DropDownList>
</div>
<div style="clear:both;height:10px"></div> <%-- Start new line for grid --%>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="RunsData">
<Columns>
<asp:BoundField DataField="PlayerId" HeaderText="PlayerId" />
<asp:BoundField DataField="Duration" HeaderText="Duration" />
<asp:BoundField DataField="VersionId" HeaderText="VersionId" />
<asp:BoundField DataField="DateUploaded" HeaderText="DateUploaded" />
</Columns>
</asp:GridView>
</form>
So, note that just like GV the dropdown list as a edit items option, like this:
And then you get this:
Also, when you want level say 1-3 is that column a number type?
And as noted, I put the filters tat the top of the grid. Since your placing the filers below the grid? You have to explain that goal - not seen filters on the bottom of a grid in about 20 years now - maybe some desktop apps? But SUPER rare. Since that choice is SUPER RARE? Then you may well have a good reason, but I placed the filter at the top. In fact, we would/could consider placing the dropdowns in the header of the GV (and that is allowed!!!). But, hey, one step at a time.
I also don't grasp, see the need for two forms on the page? (again, might be a reason, but you better have one heck of a good and great reason for that choice).
So, we now have this:
<div style="float:left">
<label class="label" for="version">Version: </label>
<asp:DropDownList ID="cboVersion" runat="server" Width="150px" AutoPostBack="true">
<asp:ListItem></asp:ListItem>
<asp:ListItem Value="1">Level 1</asp:ListItem>
<asp:ListItem Value="1,3">Level 1-3</asp:ListItem>
<asp:ListItem Value="8">Level 8</asp:ListItem>
<asp:ListItem Value="17">Level 17</asp:ListItem>
</asp:DropDownList>
</div>
<div style="float:left;padding-left:25px">
<label class="label" for="sortby">Sort By:</label>
<asp:DropDownList ID="cboSortBy" runat="server" Width="150px" AutoPostBack="true" >
<asp:ListItem></asp:ListItem>
<asp:ListItem Value="duration">Duration</asp:ListItem>
<asp:ListItem Value="DateUploaded">Date</asp:ListItem>
<asp:ListItem>Player</asp:ListItem>
</asp:DropDownList>
</div>
<div style="clear:both;height:10px"></div> <%-- Start new line for grid --%>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
Width="30%" CssClass="table">
<Columns>
<asp:BoundField DataField="PlayerId" HeaderText="PlayerId" />
<asp:BoundField DataField="Duration" HeaderText="Duration" />
<asp:BoundField DataField="VersionId" HeaderText="VersionId" />
<asp:BoundField DataField="DateUploaded" HeaderText="DateUploaded" />
</Columns>
</asp:GridView>
Note careful we added/allow/have a BLANK choice for the two combo boxes.
Ok, so now our code to load up the grid like this:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
loadgrid()
End If
End Sub
Sub loadgrid()
Using conn As New SqlConnection(My.Settings.TEST4)
Using cmdSQL As New SqlCommand("SELECT PlayerId, Duration, VersionId, DateUploaded FROM [Run]", conn)
' add filter
Dim strWhere As String = ""
If cboVersion.Text <> "" Then
If InStr(cboVersion.SelectedItem.Value, "-") = 0 Then
' one value
cmdSQL.CommandText &= " WHERE VersionID = @id"
cmdSQL.Parameters.Add("@id", SqlDbType.Int).Value = cboVersion.SelectedItem.Value
Else
' we have range
Dim MyRnage() As String = Split(cboVersion.SelectedItem.Value, "-")
cmdSQL.CommandText &= " WHERE VersionID is between @lower and @upper"
cmdSQL.Parameters.Add("@lower", SqlDbType.Int).Value = MyRnage(0)
cmdSQL.Parameters.Add("@upper", SqlDbType.Int).Value = MyRnage(1)
End If
End If
' add order by
If cboSortBy.Text = "" Then
cmdSQL.CommandText &= " ORDER BY Duration"
Else
cmdSQL.CommandText &= " ORDER BY " & cboSortBy.SelectedItem.Value
End If
Dim rstData As New DataTable
conn.Open
rstData.Load(cmdSQL.ExecuteReader)
GridView1.DataSource = rstData
GridView1.DataBind()
End Using
End Using
End Sub
Since we have auto post-back, then for both the sort by combo, filter by, then we have two event stubs here.
Protected Sub cboVersion_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboVersion.SelectedIndexChanged
loadgrid()
End Sub
Protected Sub cboSortBy_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboSortBy.SelectedIndexChanged
loadgrid()
End Sub