Question Optional parameters and dates

[Robert_SF]

Hi everyone. I'm using VS2010 Express and SQL CE 3.5, and I'm trying to create a Query By Example form for a table. I read The Ins and OUTs of Parameterized Queries... and the subsequent discussion.

I have my table adapter with a FillBy query:

SELECT recid, clientid, lawyerid, inre, opened, closed
FROM cases
WHERE (@Pclientid IS NULL OR clientid = @Pclientid)
  AND (@Plawyerid IS NULL OR lawyerid = @Plawyerid)
  AND (@Pinre IS NULL OR inre LIKE @Pinre + '%')
  AND (@Opened IS NULL OR opened = @Opened)

Columns "opened" and "closed" are DateTime types. "Closed" allows nulls but "opened" does not. The "id" columns are Integer, and "inre" is just a text column.

And here I invoke the fill method:

Dim aFindCases As New FindCases
aFindCases.ShowDialog()
If aFindCases.DialogResult = DialogResult.OK Then
    Me.CasesTableAdapter.FillQBE(Me.lawDataSet.cases, _
                                 aFindCases.ClientLookup.SelectedValue, _
                                 aFindCases.LawyerLookup.SelectedValue, _
                                 aFindCases.InRe.Text, _
                                 aFindCases.Opened.Text) 'Is "" or date in "xx/xx/xxxx" format
End If

If a date is given for Opened, it works fine, but not if Opened.Text = "". Then it crashes because Conversion from string "" to type 'Date' is not valid. I tried all sorts of different values, and I tried nullable types, but nothing worked.

It appears that if the Parameter's DBType is DateTime, then the ProviderType must also be set to DateTime, and it's simply not going to allow a non-date value to be put in there. Does this sound right?

If that is the case, what workarounds or alternatives would you suggest? Some possibilities that came to mind are these.

1.- Treat all date queries as ranges. That will need two parameters. If the user enters no date, then supply the values MinDate and MaxDate. If the user does enter a date, then supply that date to both parameters.

2.- Change the column type from DateTime to String in the database. I don't know what the implications of this would be, but it would be giving up the validation the DB does, wouldn't it?

3.- I've read the above is not a problem with stored procedures, but SQL CE doesn't support stored procedures.

Ok, thanks for your thoughts on this.

 

[jmcilhinney]

If your FillQBE method expects a Date then you should be passing a Date, not a String. If you want to pass a NULL value then you don't pass an empty String because that's not a date or NULL. It's a String with no characters. If you want to pass NULL then you use Nothing. You need to set the properties of that parameter appropriately in the DataSet designer to make it accept NULLs first.

 

[Robert_SF]

Thanks, John. Your hint to use Nothing got me back on track.

However, what was really tripping me up is that the data set designer is kind of broken when it comes to query parameters. If you make a change to the dataset, it resets all the AllowDBNull flags in your parameters. The designer also changes some of the parameters DbTypes. Did you know that? Do you know if it's particular to the VS Express editions or what?

 

[jmcilhinney]

I've encountered the same thing in the DataSet designer.

 

[Robert_SF]

Ok, got it to work, and here's some info that might help others in a similar situation.

There are a couple of issues with Dataset Designer that affect you if you're using optional parameters in your SQL; that is, something like WHERE (@Pcol IS NULL OR col = @Pcol) AND more such clauses. One is that it will sometimes rewrite and restructure your SQL if you go into the Query Builder. So don't go into the Query Builder.

The second issue is a little more exasperating. Each query in a table adapter has a collection of parameters and their properties. It seems that the "@Pcol IS NULL" part of your SQL won't work unless the AllowDBNull property for @Pcol is set to true. Unfortunately, the designer sets that property according to whether the column itself, way back in the database, allows null values. That's not workable since whether a parameter is optional has nothing to do with whether the column is optional. Even more unfortunately, the designer keeps changing AllowDBNull back to the default at every opportunity.

The workaround is to be aware of when you save your dataset. After you save it, click on your table adapter, open the parameters collection, and make sure all the AllowDBNull properties are set to True. After that, don't save it unnecessarily. If you open the dataset to look at something, and it prompts you to save when you close it, don't save unless you actually changed something. The designer may also change some DbType properties, but it's kind of random.