The redoubtable Eilon Lipton came up with another nice nugget of data-source-control goodness in answer to a question at work today. Here's the scenario. You are using a SqlDataSource control to run a query like this:
Select FirstName, LastName From Employees Where EmployeeId = ?
(Here, the EmployeeId field is an integer.) You want to be able to pass the EmployeeId to the SqlDataSource control directly from the query string. No problem; the data source configuration wizard walks you right through setting that up. It ends up looking something like this:<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:EmployeesConnectionString %>"
SelectCommand="SELECT [FirstName], [LastNamee] FROM [Employees]
WHERE ([EmployeeID] = @EmployeeID)">
<SelectParameters>
<asp:QueryStringParameter Name="BlogID"
QueryStringField="ID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
But what if someone (those darn users) passes in a query string value that's not an integer? You get one of those "Input string not in correct format" errors.
So you want to validate that the query string value is an integer. You can do this by handling the SqlDataSource control's Selecting event. There, you can extract the parameter value, test it, and cancel the event (and thus the query) if something's awry. Here's one way you might check that the query string value is an integer (or integer-able, anyway):Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
Dim idString As String = e.Command.Parameters(0).Value.ToString()
Dim id As Integer
If Int32.TryParse(idString, id) = False Then
labelStatus.Text = "Invalid ID in query string"
e.Cancel = True
End If
End Sub
Pretty straightforward, eh? Problem is that it doesn't work. Or more precisely, even with this handler, you'll still see the invalid-format error.
Eilon had the workaround: in the declaration for the SqlDataSource control's parameter collection, leave the data type blank. So it looks like this:<SelectParameters>
<asp:QueryStringParameter Name="BlogID"
QueryStringField="ID" />
</SelectParameters>
Look, ma, no data type. And now it works. Note that we're converting the EmployeeID to an integer in the Selecting handler, so the type is ok when the query ultimately runs. But if the parameter declaration has no type, the SqlDataSource control won't try to force the parameter value into a particular type, and no error.