Validating data source control parameters

New comments for this entry are disabled.

March 09, 2006 | Validating data source control parameters | 10255 hit(s)

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.

Anonymous 29 Mar 06 - 1:22 PM

Excellent, I was running into this problem yesterday. You might want to wrap that in a try/catch in case someone (those darn users) doesn't put in the querystring field at all and causes a null exception.

What do you think about trapping it in the OnLoad event? More hassle than it's worth?

mike 29 Mar 06 - 2:17 PM

>What do you think about trapping it in the OnLoad event? More hassle than it's worth?

No, not really. There are lots of ways to deal with the validation issue. The point illustrated here is actually a general solution to validating a parameter value (could also be user input) that's about as close to the data "layer" as one can get under the circumstances. The ideal, as per your thought, is to try to ensure that no unvalidated parameter values ever get this far. But if they do ...

DK 06 May 06 - 12:19 PM

Hi, how can I use error handling in SqlDataSource control? I don't see any useful event.

mike 06 May 06 - 12:26 PM

DK: What kind of error are you trying to trap?

DK 06 May 06 - 1:26 PM

for example query timeout, sql server not running, mistake in query ...

mike 06 May 06 - 1:42 PM

Ah, good question. I'll ask and see what the official answer is. One possibly hack-y way to trap errors is to perform databinding manually in (e.g.) PageLoad and to use a try-catch block for that:

    Sub Page_Load()
        If Not IsPostBack Then
            Try
                GridView1.DataSourceID = "SqlDataSource1"
                GridView1.DataBind()
            Catch ex As Exception
                Response.Write("Error data binding")
            End Try
        End If
    End Sub

There are of course other implications to taking over the task of data binding ...

mike 06 May 06 - 1:53 PM

Actually, you can also trap a SqlDataSource control error using Page_Error. In the page with the data binding, do this:

Sub Page_Error()
   Server.Transfer("DataBindError.aspx")
End Sub

And then in the target page, do something like this:

Sub Page_Load()
   Label1.Text = Server.GetLastError.Message
End Sub

As but one example, I mean.

DK 06 May 06 - 2:20 PM

manual databinding looks good. thanks

Douglas Albright 13 Jul 09 - 6:40 AM

Thank You, it took me three hours of Googling and searching boards to find this answer.

I prefered to use the validator controls then evaluate the page



    Protected Sub ObjectDataSourceDashboad_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceSelectingEventArgs) Handles ObjectDataSourceDashboad.Selecting
        If Page.IsPostBack Then
            If Page.IsValid = False Then e.Cancel = True
        End If
    End Sub

mike's web log/comments