I have a gridview that shows data from sql using a query. The query find the current month and year and displays cases for the current month. I have added two dropdownlists to my page. These are unbound. The first dropdownlist has the months in number format (01, 02, 03 etc). The second dropdownlist has the year which is worked with vb.net to add current year and three years previous (2007, 2006, 2005, 2004). I would like to be able to set the two dropdownlists to a month and year and have the gridview automatically change and show the cases for that month and year.
I cant figure out how to tie the dropdownlists to the query for unbound dropdownlists.
The code i have just now is -
VB.NET
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
lblmonth.Text = DateTime.Now.ToString("MMM
M yyyy")
If Not Page.IsPostBack Then
Call Populate_MonthList()
Call Populate_YearList()
End If
End Sub
Sub Populate_MonthList()
'Add each month to the list
drpmonth.Items.Add("01")
drpmonth.Items.Add("02")
drpmonth.Items.Add("03")
drpmonth.Items.Add("04")
drpmonth.Items.Add("05")
drpmonth.Items.Add("06")
drpmonth.Items.Add("07")
drpmonth.Items.Add("08")
drpmonth.Items.Add("09")
drpmonth.Items.Add("10")
drpmonth.Items.Add("11")
drpmonth.Items.Add("12")
'Make the current month selected item in the list
drpmonth.Items.FindByValue
(DateTime.
Now.Month)
.Selected = True
End Sub
Sub Populate_YearList()
Dim intYear As Integer
'Year list can be changed by changing the lower and upper
'limits of the For statement
For intYear = DateTime.Now.Year - 3 To DateTime.Now.Year
drpyear.Items.Add(intYear)
Next
'Make the current year selected item in the list
drpyear.Items.FindByValue(
DateTime.N
ow.Year).S
elected = True
End Sub
End Class
ASP.NET
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="
http://www.w3.org/1999/xhtml"
>
<head runat="server">
<title>Untitled Page</title>
</head>
<body style="text-align: left">
<form id="form1" runat="server">
<div style="text-align: center"> <asp:Label ID="lblmonth" runat="server" Font-Names="Verdana" Font-Size="14pt"></asp:Lab
el>
<br />
<br />
<asp:DropDownList ID="drpmonth" runat="server" Font-Names="Verdana" Font-Size="12pt" AutoPostBack="True">
</asp:DropDownList>
<asp:DropDownList ID="drpyear" runat="server" Font-Names="Verdana" Font-Size="12pt" AutoPostBack="True">
</asp:DropDownList><br />
<br />
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:BC_BE_Ac
cessConnec
tionString
%>"
SelectCommand="SELECT Lender.Company, COUNT(CaseProcessing.Statu
sID) AS Completed, SUM(vw_Case_CommissionTota
l.Commissi
onIn) AS [Comm In], SUM(vw_Case_CommissionTota
l.Commissi
onOut) AS [Comm Out], SUM(vw_Case_CommissionTota
l.Commissi
onNet) AS [Comm Net] FROM CaseDetails INNER JOIN CaseProcessing ON CaseDetails.CaseID = CaseProcessing.CaseID INNER JOIN CaseUnderWriting ON CaseDetails.CaseID = CaseUnderWriting.CaseID INNER JOIN Lender ON CaseUnderWriting.LenderID = Lender.ID INNER JOIN vw_Case_CommissionTotal ON CaseProcessing.CaseID = vw_Case_CommissionTotal.ca
seid WHERE (MONTH(CaseProcessing.Stat
usDate) = MONTH(GETDATE())) AND (YEAR(CaseProcessing.Statu
sDate) = YEAR(GETDATE())) AND (CaseDetails.LoanTypeID = 8) AND (CaseProcessing.StatusID = 22) GROUP BY Lender.Company ORDER BY Lender.Company">
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False
" DataSourceID="SqlDataSourc
e1"
Width="850px">
<Columns>
<asp:BoundField DataField="Company" HeaderText="Company" HtmlEncode="False" SortExpression="Company">
<ItemStyle HorizontalAlign="Left" VerticalAlign="Middle" Width="330px" />
</asp:BoundField>
<asp:BoundField DataField="Completed" HeaderText="Completed" ReadOnly="True" SortExpression="Completed"
>
<ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" Width="130px" />
</asp:BoundField>
<asp:BoundField DataField="Comm In" DataFormatString="{0:C}" HeaderText="Comm In"
HtmlEncode="False" ReadOnly="True" SortExpression="Comm In">
<ItemStyle HorizontalAlign="Right" VerticalAlign="Middle" Width="130px" />
</asp:BoundField>
<asp:BoundField DataField="Comm Out" DataFormatString="{0:C}" HeaderText="Comm Out"
HtmlEncode="False" ReadOnly="True" SortExpression="Comm Out">
<ItemStyle HorizontalAlign="Right" VerticalAlign="Middle" Width="130px" />
</asp:BoundField>
<asp:BoundField DataField="Comm Net" DataFormatString="{0:C}" HeaderText="Comm Net"
HtmlEncode="False" ReadOnly="True" SortExpression="Comm Net">
<ItemStyle HorizontalAlign="Right" VerticalAlign="Middle" Width="130px" />
</asp:BoundField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
