Return a date on search results

Question

0.00/5 (No votes)

See more:

I have various search boxes in my program (one per form) that is coded to bring results back by various methods and it works beautifully, so long as the returned results are in Integer format. My current problem is I am working a new form and need the search box to bring back results by REPAIR_DATE, which is a DateTime field.

my current code as follows:

VB

<pre>    Private Sub btnSearch_Click(sender As System.Object, e As System.EventArgs) Handles btnSearch.Click
        Cursor = Cursors.WaitCursor
        Dirty()

        If cbColName.Text = "SEARCH BY:" Then
            MeMsgBoxSearchCriteria.ShowDialog()
        Else : lbSearchResults.Items.Clear()
            Select Case MaintenanceDataSet.Maintenance_Table.Columns(cbColName.Text).DataType
                Case GetType(Integer)
                    DV.RowFilter = cbColName.Text & " = " & tbSearchInput.Text.Trim
                Case GetType(Date)
                    DV.RowFilter = cbColName.Text & " = #" & tbSearchInput.Text.Trim & "#"
                Case Else
                    DV.RowFilter = cbColName.Text & " LIKE '*" & tbSearchInput.Text.Trim & "*'"
            End Select

            If DV.Count > 0 Then
                For IX As Integer = 0 To DV.Count - 1
                    lbSearchResults.Items.Add(DV.Item(IX)("ID"))
                Next
                If DV.Count = 1 Then
                    lbSearchResults.SelectedIndex = 0
                    Dim ix As Integer = MaintenanceDataSetBindingSource.Find("ID", CInt(lbSearchResults.SelectedItem.ToString))
                    MaintenanceDataSetBindingSource.Position = ix
                Else
                    lbSearchResults.Visible = True
                    lbSearchResults.BringToFront()
                End If
            Else
                ' Display a message box noting the search criteria is not found.   
                MeMsgBoxNoSearch.ShowDialog()
            End If
        End If
        Cursor = Cursors.Default
    End Sub

    Private Sub lbSearchResults_SelectedIndexChanged(sender As Object, e As EventArgs) Handles lbSearchResults.SelectedIndexChanged
        Dim ix As Integer = MaintenanceDataSetBindingSource.Find("ID", CInt(lbSearchResults.SelectedItem.ToString))
        MaintenanceDataSetBindingSource.Position = ix
        lbSearchResults.Visible = False
    End Sub

I am looking to bring back results based on column REPAIR_DATE.

What I have tried:

I tried redefining "ix as Integer" to "ix as Date" and changing "CInt(lbSearchResults.SelectedItem.ToString)" to "CDate(lbSearchResults.SelectedItem.ToString)" and it ERRORS WITH "value of type 'integer' cannot be converted to 'date'" I am still fairly new to VB and not sure how to properly bring REPAIR_DATE results back as opposed to an integer. I do get the results by date in my listbox (when I use REPAIR_DATE as the sort-by field, but with code set as I have it above, but when clicking on one of the returned results to load that record it causes the program to abort with the above listed error mainly because it is looking to return a number not a date. I understand why it fails, I just do not know how to resolve.

Posted 26-May-17 6:17am

K3JAE

Updated 28-May-17 10:59am

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)

Maciej Los · Answer 1 · 2017-05-28T10:59:00

Quote:
I tried redefining "ix as Integer" to "ix as Date" and changing "CInt(lbSearchResults.SelectedItem.ToString)" to "CDate(lbSearchResults.SelectedItem.ToString)" and it ERRORS WITH "value of type 'integer' cannot be converted to 'date'"

The error message is quite clear. Assuming that ID field is type of integer, you cannot implicity convert it to date/datetime data type.
You have to refer to REPAIR_DATE field in according procedure.

Note that there's few other issues in your code:

ToString() method is redundant:

VB.NET

Dim ix As Integer = MaintenanceDataSetBindingSource.Find("ID", CInt(lbSearchResults.SelectedItem.ToString))

to filter data by date, i'd suggest to change your code:

VB
```
DV.RowFilter = cbColName.Text & " = #" & tbSearchInput.Text.Trim & "#"
```
to:

VB
```
Dim ci As CultureInfo = CultureInfo.InvariantCulture
Dim stringDate As String = tbSearchInput.Text.Trim
DV.RowFilter = String.Format("{0}=#{1}#", cbColName.Text, stringDate.Format("yyyy/MM/dd", ci))
```
It's good programming practice to use ISO datetime format. Depending on situation you may need to add time part.
Have a look here: vb.net - How to use BindingSource.Filter for a Date in visual basic 2012? - Stack Overflow[^]

Good luck!