How to Search data by textbox and date in vb.net

Question

5.00/5 (1 vote)

See more:

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

VB

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..

Posted 29-Oct-11 7:56am

caello

Updated 29-Oct-11 8:04am

Sander Rossel

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sander Rossel · Accepted Answer · 2011-10-29T08:18:00

Solution 1

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:

VB

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 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.

Posted 29-Oct-11 8:18am

Sander Rossel

v2

Comments

caello 30-Oct-11 3:58am

thank you for the response
but after I tried out the following message "object reference not set to an instance of an object".
sorry...i am very newbie..
please... explain to detail..

Thanks....very thanks..

Sander Rossel 30-Oct-11 6:27am

Easy, but I'd have to see your code for that.
You are using an Object that you have not instantiated.
For example:
Dim cmd As OleDbCommand ' No New keyword.
cmd.DoSomething ' NullReferenceException because the cmd cannot do something if it is not instantiated.

Dim cmd As New OleDbCommand ' With New keyword.
cmd.DoSomething ' No Exception, the Object exists.

caello 30-Oct-11 9:18am

<pre lang="vb">Public Class FrSearch
Dim con As New OleDbConnection 'Dim MyConnection As New AccessData.DatabaseConnection
Dim com As OleDbCommand
Dim da As OleDbDataAdapter
Dim objdatareader As OleDbDataReader
Dim strSQL As String
Dim ds As New DataSet
Dim dt As New DataTable
Private Sub FrSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
con.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0; data source = ..\ASKI.mdb"

con.Open()
con.Close()
end sub
Private Sub dataDes()

con.Open()
Dim dt As New DataTable
Dim ds As New DataSet
ds.Tables.Add(dt)

'If cbaktif.Checked = True Then
Dim com As New OleDbCommand("Select * from AFINITY where Description = @Description and Tanggal between @Datetgl1 and @Datetgl2 ", con)
com.Parameters.AddWithValue("@Description", TextBox1.Text)
com.Parameters.AddWithValue("@datetgl1", datetgl1.Value)
com.Parameters.AddWithValue("@Datetgl2", datetgl2.Value)
Dim da As New OleDbDataAdapter(com)

'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
Private Sub search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles search.Click

Try
con.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0; data source = ..\ASKI.mdb"

dataDes()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
con.Close()

End Sub</pre>

Sander Rossel 30-Oct-11 9:47am

Where does the Exception occur? Go to Debug -> Exceptions... and tick Common Language Runtime Exceptions Thrown. You should now be able to see at which line exactly the exception occurs. Perhaps datetgl1/2.Value is nothing. The code above looks fine. Although you do create an Object called 'com' at the Class level and then create another Object called 'com' in dataDes. You add Parameters to com and you pass com to your DataAdapter. If it takes the wrong com then that might also be the cause of your troubles.
Same goes for 'da'. You have multiple Objects with the same name defined at different levels. You also instantiate dt and ds at Class level and then create new Objects in dataDes. Lots of redundancy and unused Objects...

caello 30-Oct-11 10:11am

yes because I do not know if my code is correct or not, because I learn a new one this month, it was learned from there and ask questions here.
btw can you give examples of how the code is correct as my case? ..
thanks very much your advice n correction in my code..

Sander Rossel 30-Oct-11 10:37am

You can simply remove all fields except for 'con'.
The con.Close should definately be in a Finally block (I have written an article on proper use of Try Catch Finally. You can find it in my articles).

Public Class FrSearch
Private con As New OleDbConnection

Private Sub FrSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
con.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0; data source = ..\ASKI.mdb"
End Sub

Private Sub dataDes()

Try
con.Open()
Dim dt As New DataTable

Dim com As New OleDbCommand("Select * from AFINITY where Description = @Description and Tanggal between @Datetgl1 and @Datetgl2", con)
com.Parameters.AddWithValue("@Description", TextBox1.Text)
com.Parameters.AddWithValue("@datetgl1", datetgl1.Value)
com.Parameters.AddWithValue("@Datetgl2", datetgl2.Value)
Dim da As New OleDbDataAdapter(com)

da.Fill(dt)
DatagridView1.DataSource = dt
Finally
con.Close()
End Try
End Sub

Private Sub search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles search.Click
Try
dataDes()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
End Class

Try that.

caello 30-Oct-11 11:20am

Thanks Very much...@Naerling
your code is done...maybe next time i will ask in this Forum
my case is close

Sander Rossel 30-Oct-11 11:22am

No problem. Don't forget to vote and accept the answer that helped you :)