0

I'm building a search page in Visual Studio 2012 that instead of using individual toolstrips for each parameter, takes as many parameters as the user inputs. The trouble comes when using two datetime parameters and allowing them to be null. I've gotten great direction from How to pass a Null date variable to SQL Server database and Dealing with DateTime and Null values and so I think somehow using Dbnull.value is the way to go, however, I don't understand where the code goes on my page. Here's the button sub and the corresponding query code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try  
          Me.Claims_IncidentsTableAdapter.FillByAllOptions(Me.IncidentSearchDataSet.Claims_Incidents, LastNameTextBox.Text, ClaimNumberTextBox.Text, InsuredNameTextBox.Text, FirstNameTextBox.Text,
          New System.Nullable(Of Date)(CType(DOLTextBox.Text, Date)), New System.Nullable(Of Date)(CType(DRTextBox.Text, Date)), AssertedCheckBox.CheckState, CWSCheckBox.CheckState)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
End Sub

query

SELECT        Claim_Number, Insured_Name, Policy_Number, lastname, firstname, dateofloss, datereceived, asserted, movedtocws
FROM            dbo.Claims_Incidents
WHERE        (@lastname IS NULL OR
                         lastname LIKE '%' + @lastname + '%') AND (@Claim_Number IS NULL OR
                         Claim_Number LIKE '%' + @Claim_Number + '%') AND (@Insured_Name IS NULL OR
                         Insured_Name LIKE @Insured_Name + '%') AND (@firstname IS NULL OR
                         firstname LIKE '%' + @firstname + '%') AND (@dateofloss IS NULL OR
                         dateofloss = @dateofloss) AND (@datereceived IS NULL OR
                         datereceived = @datereceived) AND (@asserted IS NULL OR
                         asserted = 1) AND (@movedtocws IS NULL OR
                         movedtocws = 1)

Currently, I get the error message "Conversion from string "" to type 'Date' is not valid." if I leave the date fields blank. I get the idea, but where do I put the code? If it's to go in a function, where do I put the function code? I don't understand the proper place for it.

Community
  • 1
  • 1
SQLHound
  • 546
  • 11
  • 24

1 Answers1

1

Cause

Here is your issue:

New System.Nullable(Of Date)(CType(TEXTBOX.Text, Date))

If the TEXTBOX is empty (""), this cannot be converted to a date. In fact, the System.Nullable(Of Date) will never be null because a empty TEXTBOX has the value String.Empty, not Nothing.

Solution

Replace with:

If(String.IsNullOrEmpty(TEXTBOX.Text), New Nullable(Of Date), New Nullable(Of Date)(Date.Parse(TEXTBOX.Text)))
Bjørn-Roger Kringsjå
  • 9,849
  • 6
  • 36
  • 64