如何在vb.net中按文本框和日期搜索数据 [英] How to Search data by textbox and date in vb.net

问题描述

嗨..all
我是VB.net的新手
请帮帮我..
如何通过VB.net中的文本框和datetimepicker Access 2003数据库搜索数据

Exp:我想根据零件名称和两个datetimepicker搜索数据

我尝试使用这样的代码，但结果与预期不符

Hi..all
I''m newbie in VB.net
Please Help me..
How to search data by textbox and datetimepicker access 2003 database in VB.net

Exp : I want to search for data based on the Part name and two datetimepicker

I try with code like this but the results are not in line with expectations

Private Sub SearchData()
con.Open()
        Dim dt As New DataTable
        Dim ds As New DataSet
        ds.Tables.Add(dt)
        Dim da As New OleDbDataAdapter
        'da = New OleDbDataAdapter(com)
        If cbaktif.Checked = True Then
            da = New OleDbDataAdapter("Select * from AFINITY where Description = '" & TextBox1.Text & "' and " & " Datetgl between #" & DateTimePicker1.Text & "# and #" & DateTimePicker2.Text & "# ", con)
        Else
            da = New OleDbDataAdapter("Select * from AFINITY where Description like '%" & TextBox1.Text & "%'", con)
        End If
        da.Fill(dt)
        DatagridView1.DataSource = dt.DefaultView
        con.Close()
End Sub

请更正我的编码

谢谢..

Please Correction in my coding

Thanks..

推荐答案

我不确定您的确切问题是什么.我可以想象日期没有按照您的期望格式化.
首先，我真的建议您将查询参数化(不这样做可能会导致性能问题和SQL注入！).请考虑以下情况:有人在您的文本框中键入D''Artagnan. D''Artagnan中的''将使您的查询混乱，因为这意味着访问中字符串的结尾.
而是使用此:

I am not sure what your exact problem is. I can imagine that the dates are not formatted to your expectations.
First of all I really recommend making your query parameterized (not doing so may result in performance issues and SQL injection!). Consider the following: Someone types D''Artagnan in your TextBox. The '' in D''Artagnan will mess up your query since it means the end of a string in access.
Instead use this:

Dim cmd As New OleDbCommand("Select * from AFINITY where Description = @Description and Datetgl between @Datetgl1 and @Datetgl2 ", con)
cmd.Parameters.AddWithValue("@Description", TextBox1.Text)
cmd.Parameters.AddWithValue("@Datetgl1", DateTimePicker1.Value)
cmd.Parameters.AddWithValue("@Datetgl2", DateTimePicker2.Value)
Dim da As New OleDbDataAdapter(cmd)

.NET现在将自动将@Description替换为TextBox1.Value，但还将确保将其用作整体参数(包括通常会破坏查询的任何内容，例如*/和'').
您的查询现在已参数化，这意味着查询将被缓存并且参数将始终有效.因此，D''Artagnan现在将是您的文本框中的有效条目.
此外，我认为(尽管不能肯定地说)这种方法(并使用DateTimePicker.Value)将解决您的其他问题.

.NET will now automatically replace @Description with the TextBox1.Value, but will also make sure that it is used as parameter as a whole (including anything that would normally break your query like */ and '' ).
Your query is now parameterized, which means queries will be cached and parameters will always be valid. So D''Artagnan will now be a valid entry in your TextBox.
Furthermore I think (though I can''t say with certainty) that this approach (and using DateTimePicker.Value) will solve your other problem.

这篇关于如何在vb.net中按文本框和日期搜索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！