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:
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:
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.
I have my table adapter with a FillBy query:
VB.NET:
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:
VB.NET:
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.