如何使用日期搜索记录
如何使用日期搜索记录
提问于 2013-10-02 22:05:20
我正在用datagridview显示学生的记录。
我想显示按旅店出生日期过滤的记录。
当我使用自定义格式从datetimepicker中选择11/09来搜索数据时，dd/mm并没有显示任何内容。
如何在忽略年份的同时只匹配dd/mm来显示所有记录？
这是我的代码：
Try
con = New OleDbConnection(cs)
con.Open()
cmd = New OleDbCommand("SELECT HostelerID as [Hosteler ID],HostelerName as [Hosteler Name],DOB,HostelName as [Hostel Name],RoomNo as [Room No],DateOfJoining as [Date Of Joining],Purpose,FatherName as [Father's Name],MobNo1 as [Mobile No],Phone1 as [Phone No],MotherName as [Mother's Name],MobNo2 as [Mobile No 2],City,Address,Email,ContactNo as [Contact No],InstOfcDetails as [Ins/Ofc Details],Phone2 as [Phone No 2],Agreement,GuardianName as [Guardian Name],GuardianAddress as [Guardian Address],MobNo3 as [Guardian Mobile No],Phone3 as [Guardian Phone No],FixedDeposit as [Fixed Deposit],CompletionDate as [Completion Date],Photo,DocsPic as [Docs Pic] from Hostelers where DOB like #" & DateTimePicker1.Text & "# order by HostelerName", con)
Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
Dim myDataSet As DataSet = New DataSet()
myDA.Fill(myDataSet, "Hostelers")
DataGridView6.DataSource = myDataSet.Tables("Hostelers").DefaultView
con.Close()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try回答 3
Stack Overflow用户
回答已采纳
发布于 2013-10-02 22:28:59
尝试将“where”子句更改为：
" WHERE MONTH(DOB) = " & DateTimePicker1.Value.Month &
" AND DAY(DOB) = " & DateTimePicker1.Value.Day 不过，我也强烈建议您使用参数化SQL命令http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx。
这将防止SQL注入(http://technet.microsoft.com/en-us/library/ms161953(v=SQL.105).aspx和其他错误)。
Stack Overflow用户
发布于 2013-10-02 22:36:47
“使用自定义格式dd/mm从日期选择器中选择11/09来搜索数据”
在Access查询中，您可以要求db引擎将Format()应用于您的DOB值，然后进行比较。这将用于静态值'11/09‘.
WHERE Format(DOB, 'dd/mm') = '11/09'为了使其更灵活，您可以使用参数查询并提供DateTimePicker1.Text作为参数值。
WHERE Format(DOB, 'dd/mm') = your_stringStack Overflow用户
发布于 2013-10-02 22:29:15
将查询更改为如下所示：
cmd = New OleDbCommand("
SELECT HostelerID as [Hosteler ID], HostelerName as [Hosteler Name],
DOB, HostelName as [Hostel Name],
RoomNo as [Room No], DateOfJoining as [Date Of Joining],
Purpose, FatherName as [Father's Name],
MobNo1 as [Mobile No], Phone1 as [Phone No],
MotherName as [Mother's Name], MobNo2 as [Mobile No 2],
City, Address, Email, ContactNo as [Contact No],
InstOfcDetails as [Ins/Ofc Details], Phone2 as [Phone No 2],
Agreement, GuardianName as [Guardian Name],
GuardianAddress as [Guardian Address], MobNo3 as [Guardian Mobile No],
Phone3 as [Guardian Phone No], FixedDeposit as [Fixed Deposit],
CompletionDate as [Completion Date], Photo, DocsPic as [Docs Pic]
FROM Hostelers
WHERE (DatePart(\"M\", DOB) = '" + DateTimePicker1.Value.Date.Month + '")
AND (DatePart(\"D\", DOB) = '" + DateTimePicker1.Value.Date.Day + '"))
ORDER BY HostelerName
")页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/19147609
复制相关文章
相似问题
腾讯云开发者
