?
Solved

Search date in database

Posted on 2006-05-09
8
Medium Priority
?
419 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:Supporter
8 Comments
 
LVL 5

Expert Comment

by:t_itanium
ID: 16645643
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
 
LVL 3

Accepted Solution

by:
yokkui earned 1000 total points
ID: 16646123
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
 

Author Comment

by:Supporter
ID: 16646130
How can I do it. Now, my value textbox is textbox3.text. How I convert it. Thanks
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Supporter
ID: 16646160
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
 
LVL 7

Expert Comment

by:dttri
ID: 16646349
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
 

Author Comment

by:Supporter
ID: 16646385
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
 

Expert Comment

by:newmancroos_s
ID: 16646794
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
 

Assisted Solution

by:newmancroos_s
newmancroos_s earned 1000 total points
ID: 16646798
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Introduction to Processes

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question