Search date in database

I don't know why my code doesn't work:

If TextBox3.Text = "" Then
            MessageBox.Show("Enter Search Criteria", "yourProject", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If
        If WinComboBox1.Text = "Candidate Code" Then
            DataView1.RowFilter = "Candidate_Code like '%" + TextBox3.Text + "%'"
        End If
        If WinComboBox1.Text = "Candidate Name" Then
            DataView1.RowFilter = "Candidate_Name like '%" + TextBox3.Text + "%'"
        End If
        If WinComboBox1.Text = "Candidate DOB" Then
            DataView1.RowFilter = "Date_Of_Birth like '%" + TextBox3.Text + "%'"
        End If

I search the Candidate Code and Candidate Name wells. But when I search Candidate DOB, it show me the error

DataView1.RowFilter = "Date_Of_Birth like '%" + TextBox3.Text + "%'" <~~~ please explain to me, and show me the ways to solve this problem
I'm using vb.net 2003 and sqlserver 2000
SupporterAsked:
Who is Participating?
 
yokkuiCommented:
Replace the last line of code with the following and try again.

DataView1.RowFilter = "Date_Of_Birth = Convert(datetime,'" + TextBox3.Text + "',101)"

The last parameter of the Convert function is the datetime mask code which follows the following.

0 or 100  mon dd yyyy hh:miAM (or PM)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
9 or 109  mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
13 or 113  dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
20 or 120  yyyy-mm-dd hh:mi:ss(24h)
21 or 121  yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM
0
 
t_itaniumCommented:
hi Supporter

your searching for the date but your are giving a string variable as parameter...

try to convert it in your code to a datetime or date type then pass it to the query..

cheers
0
 
SupporterAuthor Commented:
How can I do it. Now, my value textbox is textbox3.text. How I convert it. Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
SupporterAuthor Commented:
I met an error

An unhandled exception of type 'System.Data.EvaluateException' occurred in system.data.dll

Additional information: Invalid number of arguments: function Convert().
0
 
dttriCommented:
Hello Supporter,
As yokkui suggested, you should CONVERT your text box value to a date time format. You set the third parameter to CONVERT depend on your input. Please give us a sample input of your program.
0
 
SupporterAuthor Commented:
Yeah. Here is my code

If TextBox3.Text = "" Then
            MessageBox.Show("Enter Search Criteria", "yourProject", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If
        If WinComboBox1.Text = "Candidate Code" Then
            DataView1.RowFilter = "Candidate_Code like '%" + TextBox3.Text + "%'"
        End If
        If WinComboBox1.Text = "Candidate Name" Then
            DataView1.RowFilter = "Candidate_Name like '%" + TextBox3.Text + "%'"
        End If
        If WinComboBox1.Text = "Candidate DOB" Then
            DataView1.RowFilter = "Date_Of_Birth like '%" + TextBox3.Text + "%'"
        End If

When I choose "Candidate Code" and "Candidate Name", my dataview works well. It search my database immediately
but not with "Candidate DOB".
Here, textbox3.text is my textbox to enter value. Thanks all so much for helping me.
0
 
newmancroos_sCommented:
hi,

Like function to data type field will not work so you can convert the date into varchar in the query itself and then try.

        oCOn = New SqlConnection(sConstr)
        Dim sSql As String = "Select  Lastname,firstname,convert(varchar(10),birthdate) as bb from Employees "
        Dim oadp As New SqlDataAdapter(sSql, oCOn)
        Dim oData As New DataSet
        oadp.Fill(oData)
        Dim oView As DataView
        oView = oData.Tables(0).DefaultView
        oView.RowFilter = "bb like '% " & CStr("3/4/1995") & "%'"



Newmancroos
0
 
newmancroos_sCommented:
hi,

Like function to date type field will not work so you can convert the date into varchar in the query itself and then try.

        oCOn = New SqlConnection(sConstr)
        Dim sSql As String = "Select  Lastname,firstname,convert(varchar(10),birthdate) as bb from Employees "
        Dim oadp As New SqlDataAdapter(sSql, oCOn)
        Dim oData As New DataSet
        oadp.Fill(oData)
        Dim oView As DataView
        oView = oData.Tables(0).DefaultView
        oView.RowFilter = "bb like '% " & CStr("3/4/1995") & "%'"



Newmancroos
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.