如何在下拉列表中根据所选月份将数据从数据库显示到gridview [英] How to display the data from database to gridview based on selected month in drop down list
本文介绍了如何在下拉列表中根据所选月份将数据从数据库显示到gridview的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个月的下拉列表。我需要通过选择Month来在网格视图中显示数据。我应该写什么编码。我很无能，因为我是ASP.Net的新手。你能帮助我吗？
例如，我选择了Month = January。然后gridview必须显示日期= 12/01/2014
我的更新代码：
< pre lang =vb> ' ---从下降的负载月份---
Dim monthName As String = 我 .cmbMonth.SelectedValue
' --- LOAD TAHUN ---
Dim 年作为 整数 = DateTime.Now.Year
Dim monthNo As 整数 =
Convert.ToDateTime（ 01 - & monthName& - &年）。月
' ---获取第一个和最后一个月---
Dim FirstDate As 新 DateTime（ year，Convert.ToInt32（ Me .cmbMonth.SelectedValue.ToString）， 1 ）
< span class =code-keyword> Dim LastDate As DateTime =
FirstDate.AddMonths（ 1 ）。AddDays（-1）
sql = SELECT Row_Number（ ）OVER（ORD BY b.Date）AS ROWNUMBER，a.Name，b.MatricNo，b.Date，b.KaunselingID，b .Status _
& FROM DbStudent.dbo.SMP01_Personal AS _
& INNER JOIN dbo.Kaunseling AS b ON a.MatricNo = b.MatricNo _
&安培; WHERE Status ='2'和Date> ='& FirstDate& 'AND Date< ='& LastDate& '
' ---运行查询并将结果数据绑定到GRIDVIEW控件---
Dim ds As DataSet = GetData（sql）
如果（ ds.Tables.Count> 0 ）然后
gvList.DataSource = ds
gvList.DataBind（）
结束 如果
< td style = width：58px > 月< / td >
< asp：DropDownList < span class =code-attribute> ID = cmbMonth runat = server AutoPostBack = true 宽度 = 100 >
< span class =code-keyword>< asp：ListItem 值 = 1 > 1月< / asp：ListItem >
< asp：ListItem 值 = 2 > 二月< / asp：ListItem >
< asp：ListItem 值 = 3 > March < / asp：ListItem >
< asp：ListItem 值 = 4 > 四月< / asp ：ListItem >
< asp：ListItem 值 = 5 &g t; 可以< / asp：ListItem >
< asp：ListItem 值 = 6 > 六月< / asp ：ListItem >
< asp：ListItem 值 = 7 > 7月< / asp：ListItem >
< asp：ListItem 值 = 8 > 八月< / asp：ListItem >
< asp：ListItem 值 = 9 > 九月< / asp：ListItem >
< asp：ListItem 值 = 10 > 十月< / asp：ListItem >
< asp：ListItem 值 = 11 > November < / asp：ListItem >
< asp：ListItem 值 = 12 > 12月< / asp：ListItem >
< / asp：DropDownList >
< / td >
<％ - 表格网格视图 - ％>
< 表 class = TableContent style = width： 100％ >
< tr >
< td valign = top >
< asp：gridView id = gvSenaraiP CssClass = gridViewTable autogeneratecolumns = False allowpaging = True 宽度 = 100％ EmptyDataText = 无记录
OnRowCommand = gvSenaraiP_RowCommand runat = 服务器 PageSize = 20 >
< FooterStyle CssClass = gridViewFooter / >
< < span class =code-leadattribute> HeaderStyle CssClass = gridViewHeader / >
< AlternatingRowStyle CssClass = gridViewAlternating / >
< 列 >
< asp：BoundField DataField = ROWNUMBER HeaderText = 否。 SortExpression = 否。 HeaderStyle-Width = 2％ > < / asp：BoundField >
< asp：BoundField DataField = 名称 HeaderText = 学生姓名 < span class =code-attribute> SortExpression = 学生姓名 HeaderStyle-Width = 20％ > < / asp：BoundField >
< asp：BoundField DataField = MatricNo HeaderText = Matric No. SortExpression = Matric No. HeaderStyle-Width = 15％ > < / asp：BoundField >
< asp：BoundField DataField = 日期 HeaderText = 日期 DataFormatString = {0：dd / MM / yyyy} < span class =code-attribute> SortExpression = 日期 HeaderStyle-Width = 15％ > < / asp：BoundField >
< asp：BoundField DataField = KaunselingID HeaderText = 隐藏 >
< ItemStyle CssClass = 隐藏 VerticalAlign = Top / >
< HeaderStyle CssClass = hidden / >
< FooterStyle CssClass = 隐藏 / >
< / asp：BoundField >
< /列 >
< FooterStyle < span class =code-attribute> CssClass = gridViewFooter / >
< HeaderStyle ForeColor = 白色 CssClass = gridViewHeader / >
< PagerSettings FirstPageText = < < LastPageText = > > 模式 = NextPreviousFirstLast NextPageText = > PreviousPageText = < / >
< / asp：gridView >
解决方案
一些简单的浏览就可以得出结论在MSDN：
ASP的GridView示例.NET 2.0 [ ^ ]
一个选项是使用DataView RowFilter属性c $ c>。
您没有提供有关您的实施的更多信息，所以我必须在这里猜测。
// 此代码仅用于我的调试
// 您可能已经填充了数据表。
DataTable dt = new DataTable（）;
dt.Columns.Add（ date， typeof运算（DateTime的））;
dt.Rows.Add（ new DateTime（ 2014 ， 09 ， 01 ））;
dt.Rows.Add（ new DateTime（ 2014 ， 10 ， 01 ））;
dt.Rows.Add（ new DateTime（ 2014 ， 10 ， 11 ））;
dt.Rows.Add（ new DateTime（ 2014 ， 11 ， 01 ））;
dt.Rows.Add（ new DateTime（ 2014 ， 11 ， 04 ））;
dt.Rows.Add（ new DateTime（ 2014 ， 11 ， 30 ））;
dt.Rows.Add（ new DateTime（ 2014 ， 12 ， 01 ））;
DateTime date = DateTime.Now;
DataView dv = dt.DefaultView;
int month = 10 ; // 这是DropDownList中选定的月份
int endDay = 0 ;
switch （月）
{
case 2 ：endDay = 28 ; break ; // 二月，闰年除外。在此示例中未解决
case 4 ：endDay = 30 ; break ; // 四月
案例 6 ：endDay = 30 ; break ; // 6月
案例 9 ：endDay = 30 ; break ; // 9月
案例 11 ：endDay = 30 ; break ; // 11月
默认 ：endDay = 31 ; break ; // 其余月份
}
DateTime startDate = new DateTime（DateTime.Now.Year，month， 1 ）;
DateTime endDate = new DateTime（DateTime.Now.Year，month，endDay）;
// 假设您的数据表中有一个名为date的列，
// 否则更改为相应的列名
dv.RowFilter = 字符串 .Format（ date> ='{ 0}'和日期< ='{1}'，startDate，endDate）;
// 然后将dv分配给GridView
另一个也许更好的选择是在选择新月时向数据库发送新请求。
然后你在SELECT语句中使用WHERE子句。
我不太擅长SQL，所以我把那部分留给你了。
SQL的一个地方是： w3schools：SQL Tutorial [ ^ ]
特别是 SQL WHERE子句 [ ^ ]
使用上面代码中的startDate和endDate创建，您可以尝试以下代码。
string sqlCo mmand = 字符串 .Format（ @ SELECT [column1] ，[column2] FROM [table] WHERE Date BETWEEN'{0}'和'{1}'，startDate.ToString（ MM / dd / yyyy），endDate.ToString（ MM /日/年））;
// 请注意，日期格式字符串必须与数据库中的格式匹配。
由于此代码在c＃中，您可能需要查看下面的链接才能转换为VB。
差异应该是最小的。
String.Format方法 [ ^ ]
和
http://msdn.microsoft.com/en-us/library/system.string.format(v = vs.110）.aspx [ ^ ]
其他人可能会向您展示更好的SQL查询。
''' /你需要得到年份也可以包含
'''/months of many years ie.2014,2013,2012,2011,....
'''/fetch month from drop down
Dim monthName As String = \"January\"
'''/fetch year $b$ b Dim year As Integer = 2014
Dim monthNo As Integer = Convert.ToDateTime((Convert.ToString(\"01-\") & monthName) + \"-\" + year).Month
'''////get first and last date
Dim FirstDate As New DateTime(year, monthNo, 1)
Dim lastDate As DateTime = FirstDate.AddMonths(1).AddDays(-1)
'''////use select query with BETWEEN operator
'''////samples found in below link
'''/http://74.125.224.72/#q=sql+between+two+dates
'''////good luck ;-)
'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik
'Facebook: facebook.com/telerik
'=======================================================
---replace ur sql value like below
sql = \"SELECT a.Name, b.MatricNo, b.Date, b.KaunselingID FROM DbStudent.dbo.Personal AS a INNER JOIN dbo.Kaunseling AS b ON a.MatricNo = b.MatricNo WHERE Date >= (datevalue('\" & FirstDate & \"') + timevalue('\" & FirstDate & \"')) and Date <= (datevalue('\" & lastDate & \"') + timevalue('\" & lastDate & \"')) \"
---u should try to use parameterized query for a secured way
-----http://stackoverflow.com/questions/22976492/how-to-select-data-between-two-date-ranges-using-datepicker-c-sharp[^]
Sub Lo adList() '---LOAD DATA TO GRIDVIEW---
'---LOAD LIST---
dbconn = New OleDb.OleDbConnection(strCon)
dbconn.Open()
'---LOAD MONTH FROM DROP DOWN---
Dim monthName As String = Me.cmbBulan.SelectedValue
'---LOAD YEAR---
Dim year As Integer = 2014
Dim monthNo As Integer =
Convert.ToDateTime(\"01-\" & monthName & \"-\" & year).Month
'---GET FIRST AND LAST DATE---
Dim FirstDate As New DateTime(year, Convert.ToInt32(Me.cmbBulan.SelectedValue.ToString), 1)
Dim LastDate As DateTime =
FirstDate.AddMonths(1).AddDays(-1)
sql = \"SELECT Row_Number() OVER(ORDER BY b.Date) AS ROWNUMBER, a.Name, b.MatricNo, b.Date, b.KaunselingID \" _
& \"FROM DbStudent.dbo.Personal AS a \" _
& \"INNER JOIN dbo.Kaunseling AS b ON a.MatricNo = b.MatricNo \" _
& \"WHERE convert(datetime, Date, 103) >= '\" & FirstDate & \"' AND convert(datetime, Date, 103) <= '\" & LastDate & \"'\"
'---RUN THE QUERY AND BIND THE RESULTING DATASET TO THE GRIDVIEW CONTROL---
Dim ds As DataSet = GetData(sql)
If (ds.Tables.Count > 0) Then
gvList.DataSource = ds
gvList.DataBind()
End If
dbconn.Close()
dbconn = Nothing
End Sub
I have a drop down list for Month. I need to display the data in grid view by selecting the Month. What coding should I write. I'm clueless because I'm new in ASP.Net. Can you help me.
For example, I've selected Month=January. Then the gridview must display the date=12/01/2014
My updated code :
'---LOAD MONTH FROM DROP DOWN---
Dim monthName As String = Me.cmbMonth.SelectedValue
'---LOAD TAHUN---
Dim year As Integer = DateTime.Now.Year
Dim monthNo As Integer =
Convert.ToDateTime("01-" & monthName & "-" & year).Month
'---GET FIRST AND LAST DATE---
Dim FirstDate As New DateTime(year, Convert.ToInt32(Me.cmbMonth.SelectedValue.ToString), 1)
Dim LastDate As DateTime =
FirstDate.AddMonths(1).AddDays(-1)
sql = "SELECT Row_Number() OVER(ORDER BY b.Date) AS ROWNUMBER, a.Name, b.MatricNo, b.Date, b.KaunselingID, b.Status " _
& "FROM DbStudent.dbo.SMP01_Personal AS a " _
& "INNER JOIN dbo.Kaunseling AS b ON a.MatricNo = b.MatricNo " _
& "WHERE Status = '2' And Date >= '"& FirstDate &"' AND Date <= '"& LastDate &"'"
'---RUN THE QUERY AND BIND THE RESULTING DATASET TO THE GRIDVIEW CONTROL---
Dim ds As DataSet = GetData(sql)
If (ds.Tables.Count > 0) Then
gvList.DataSource = ds
gvList.DataBind()
End If
<td style="width: 58px">Month</td>
<asp:DropDownList ID="cmbMonth" runat="server" AutoPostBack="true" Width="100">
<asp:ListItem Value="1">January</asp:ListItem>
<asp:ListItem Value="2">February</asp:ListItem>
<asp:ListItem Value="3">March</asp:ListItem>
<asp:ListItem Value="4">April</asp:ListItem>
<asp:ListItem Value="5">May</asp:ListItem>
<asp:ListItem Value="6">June</asp:ListItem>
<asp:ListItem Value="7">July</asp:ListItem>
<asp:ListItem Value="8">August</asp:ListItem>
<asp:ListItem Value="9">September</asp:ListItem>
<asp:ListItem Value="10">October</asp:ListItem>
<asp:ListItem Value="11">November</asp:ListItem>
<asp:ListItem Value="12">December</asp:ListItem>
</asp:DropDownList>
</td>
<%--Table Grid View--%>
<table class="TableContent" style="width:100%">
<tr>
<td valign="top">
<asp:gridView id="gvSenaraiP" CssClass="gridViewTable" autogeneratecolumns="False" allowpaging="True" Width="100%" EmptyDataText="No Record"
OnRowCommand="gvSenaraiP_RowCommand" runat="server" PageSize="20">
<FooterStyle CssClass="gridViewFooter" />
<HeaderStyle CssClass="gridViewHeader" />
<AlternatingRowStyle CssClass="gridViewAlternating" />
<Columns>
<asp:BoundField DataField="ROWNUMBER" HeaderText="No." SortExpression="No." HeaderStyle-Width="2%" ></asp:BoundField>
<asp:BoundField DataField="Name" HeaderText="Student Name" SortExpression="Student Name" HeaderStyle-Width="20%"></asp:BoundField>
<asp:BoundField DataField="MatricNo" HeaderText="Matric No." SortExpression="Matric No." HeaderStyle-Width="15%"></asp:BoundField>
<asp:BoundField DataField="Date" HeaderText="Date" DataFormatString="{0:dd/MM/yyyy}" SortExpression="Date" HeaderStyle-Width="15%"></asp:BoundField>
<asp:BoundField DataField="KaunselingID" HeaderText="Hidden">
<ItemStyle CssClass="hidden" VerticalAlign="Top" />
<HeaderStyle CssClass="hidden" />
<FooterStyle CssClass="hidden" />
</asp:BoundField>
</Columns>
<FooterStyle CssClass="gridViewFooter" />
<HeaderStyle ForeColor="White" CssClass="gridViewHeader" />
<PagerSettings FirstPageText="<<" LastPageText=">>" Mode="NextPreviousFirstLast" NextPageText=">" PreviousPageText="<" />
</asp:gridView> 解决方案
Some simple browsing gives this result at MSDN:
GridView Examples for ASP.NET 2.0[^]
One option is to use theRowFilterproperty of theDataView.
You don't give much information about your implementation, so I have to guess here.
// This code is just for my debugging // You have probably already a filled data table. DataTable dt = new DataTable(); dt.Columns.Add("date", typeof(DateTime)); dt.Rows.Add(new DateTime(2014, 09, 01)); dt.Rows.Add(new DateTime(2014, 10, 01)); dt.Rows.Add(new DateTime(2014, 10, 11)); dt.Rows.Add(new DateTime(2014, 11, 01)); dt.Rows.Add(new DateTime(2014, 11, 04)); dt.Rows.Add(new DateTime(2014, 11, 30)); dt.Rows.Add(new DateTime(2014, 12, 01)); DateTime date = DateTime.Now; DataView dv = dt.DefaultView; int month = 10; // This is the selected Month from the DropDownList int endDay = 0; switch (month) { case 2: endDay = 28; break; // February, Except in leap years. Not solved in this example case 4: endDay = 30; break; // April case 6: endDay = 30; break; // June case 9: endDay = 30; break; // September case 11: endDay = 30; break; // November default: endDay = 31; break; // The rest of the months } DateTime startDate = new DateTime(DateTime.Now.Year, month, 1); DateTime endDate = new DateTime(DateTime.Now.Year, month, endDay); // Assuming you have a column named 'date' in your data table, // otherwise change to the appropriate column name dv.RowFilter = String.Format("date >= '{0}' AND date <= '{1}'", startDate, endDate); // Then assign dv to your GridView
Another, and maybe better option, would be to send a new request to the database when a new month is selected.
Then you use the WHERE clause in the SELECT statement.
I am not so good at SQL, so I leave that part to you.
One place to start with SQL is here: w3schools: SQL Tutorial[^]
Especially The SQL WHERE Clause[^]
Using the creation of startDate and endDate from the code above you can try the code below.
string sqlCommand = String.Format(@"SELECT [column1], [column2] FROM [table] WHERE Date BETWEEN '{0}' AND '{1}'", startDate.ToString("MM/dd/yyyy"), endDate.ToString("MM/dd/yyyy")); // Note that the date format string must match the format in your database.
As this code is in c#, you might want to look at the links below in order to convert to VB.
The difference should be minimal.
String.Format Method[^]
and
http://msdn.microsoft.com/en-us/library/system.string.format(v=vs.110).aspx[^]
Someone else can probably show you a better SQL query.
'''/u need to get year also coz ur database may contain '''/months of many years ie.2014,2013,2012,2011,.... '''/fetch month from drop down Dim monthName As String = "January" '''/fetch year Dim year As Integer = 2014 Dim monthNo As Integer = Convert.ToDateTime((Convert.ToString("01-") & monthName) + "-" + year).Month '''////get first and last date Dim FirstDate As New DateTime(year, monthNo, 1) Dim lastDate As DateTime = FirstDate.AddMonths(1).AddDays(-1) '''////use select query with BETWEEN operator '''////samples found in below link '''/http://74.125.224.72/#q=sql+between+two+dates '''////good luck ;-) '======================================================= 'Service provided by Telerik (www.telerik.com) 'Conversion powered by NRefactory. 'Twitter: @telerik 'Facebook: facebook.com/telerik '=======================================================
---replace ur sql value like below
sql = "SELECT a.Name, b.MatricNo, b.Date, b.KaunselingID FROM DbStudent.dbo.Personal AS a INNER JOIN dbo.Kaunseling AS b ON a.MatricNo = b.MatricNo WHERE Date >= (datevalue('" & FirstDate & "') + timevalue('" & FirstDate & "')) and Date <= (datevalue('" & lastDate & "') + timevalue('" & lastDate & "')) "
---u should try to use parameterized query for a secured way
-----http://stackoverflow.com/questions/22976492/how-to-select-data-between-two-date-ranges-using-datepicker-c-sharp[^]
Sub LoadList() '---LOAD DATA TO GRIDVIEW--- '---LOAD LIST--- dbconn = New OleDb.OleDbConnection(strCon) dbconn.Open() '---LOAD MONTH FROM DROP DOWN--- Dim monthName As String = Me.cmbBulan.SelectedValue '---LOAD YEAR--- Dim year As Integer = 2014 Dim monthNo As Integer = Convert.ToDateTime("01-" & monthName & "-" & year).Month '---GET FIRST AND LAST DATE--- Dim FirstDate As New DateTime(year, Convert.ToInt32(Me.cmbBulan.SelectedValue.ToString), 1) Dim LastDate As DateTime = FirstDate.AddMonths(1).AddDays(-1) sql = "SELECT Row_Number() OVER(ORDER BY b.Date) AS ROWNUMBER, a.Name, b.MatricNo, b.Date, b.KaunselingID " _ & "FROM DbStudent.dbo.Personal AS a " _ & "INNER JOIN dbo.Kaunseling AS b ON a.MatricNo = b.MatricNo " _ & "WHERE convert(datetime, Date, 103) >= '" & FirstDate & "' AND convert(datetime, Date, 103) <= '" & LastDate & "'" '---RUN THE QUERY AND BIND THE RESULTING DATASET TO THE GRIDVIEW CONTROL--- Dim ds As DataSet = GetData(sql) If (ds.Tables.Count > 0) Then gvList.DataSource = ds gvList.DataBind() End If dbconn.Close() dbconn = Nothing End Sub
这篇关于如何在下拉列表中根据所选月份将数据从数据库显示到gridview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
