查询以从日期时间选择器中选择从访问数据库中检索特定月份或年份的数据 [英] Query to retrieve a particular month or year's data from access database selecting from datetimepicker

问题描述

我在使用datetimepicker从访问数据库中检索特定月份或特定年份的数据时遇到问题.我有特定的日子细节.但是我想计算一个特定的月份或年份帐户.请帮助我


我的代码是:

I have problem in retrieving a particular month or particular year''s data from access database using datetimepicker. I have got a particular days details. But I want to calculate a particular month or particular years account . plz help me


My code is:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
       If RadioButton1.Checked = True Then
           Dim cmb As String
           'cmb = DateTimePicker1.Value.ToString("yyyy -MM-dd")
           cmb = DateTimePicker1.Value.ToString("dd-MM-yyyy")
           ds.Clear()
           da = New OleDb.OleDbDataAdapter("Select * from Sale where Dat=#" & cmb & "#", con)
           da.Fill(ds, "Tab")
           con.Close()
           DataGridView1.DataSource = ds.Tables("Tab").DefaultView
           DataGridView1.Show()
       End If
       If RadioButton2.Checked = True Then
           Dim mnth As String
           mnth = DateTimePicker1.Value.ToString("dd-MM-yyyy")
           mnth = DatePart(DateInterval.Month, DateTimePicker1.Value)
           'MsgBox(mnth)
           ds2.Clear()
           'da = New OleDb.OleDbDataAdapter("Select * from Sale where Dat='" & cmb & "'", con)
           da1 = New OleDb.OleDbDataAdapter("Select * from Sale where Dat=#" & mnth & "#", con)
           da1.Fill(ds2, "Tab")
           con.Close()
           DataGridView1.DataSource = ds2.Tables("Tab").DefaultView
           DataGridView1.Show()
       End If
   End Sub

推荐答案

首先，使用参数化查询而不是一起使用字符串将日期与查询一起构建.

其次，您传递的是整个日期，而不仅仅是月份或年份.

您的查询的WHERE子句不会只显示一个月或一年，而是使用整个日期.因此，您的查询将仅在完全指定的日期匹配，仅在特定日期匹配数据.

我不知道您的Dat列声明为什么以及如何存储数据.但是，如果您使用的是datetime列，并且完整的日期存储在其中，则必须使用DatePart函数修改WHERE子句以仅查看日期的月份.像这样的东西:

First, use a parameterized query instead of concantenting strings together to build the query with the date.

Second, you''re passing in the entire date, not just the month or year number.

Your query''s WHERE clause doesn''t look at just a month or year, it''s using the entire date. So, your query will only match on dates that are exactly as specified, matching only the data on a specific day.

I have no idea what your Dat column is declared as and how your data is stored. But, if you''re using a datetime column and the full date is stored in it, you''ll have to modify the WHERE clause to look at only the month of the date using the DatePart function. Something like:

SELECT * FROM Sale WHERE DatePart("m", Dat)=?

您传入的参数将是OleDbType.Integer的月份数.

The parameter you pass in would be the month number as an OleDbType.Integer.

这篇关于查询以从日期时间选择器中选择从访问数据库中检索特定月份或年份的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！