Using the QueryStringParameter with
SQLDataSource
The QueryStringParameter object can represent any query string passed to a
page. The QueryStringParameter class includes all the properties of the base
Parameter class with the addition of the following property:
QueryStringField—The name of the query string that the
QueryStringParameter represents.
This type of parameter is particularly useful when you build Master/Detail
pages.
Required on Details OR (2nd) web page:
<asp:SqlDataSource
id=”srcMovie”
SelectCommand=”SELECT * FROM smaster
WHERE sid=@sid”
ConnectionString=”<%$ ConnectionStrings:smaster %>”
Runat=”server”>
<SelectParameters>
<asp:QueryStringParameter
Name=”sid”
QueryStringField=”Id” />
</SelectParameters>
</asp:SqlDataSource>
Using the SessionParameter with
SQLDataSource
The SessionParameter object enables you to represent any item stored in
Session state. It includes all the properties of the base Parameter class
and the following property:
SessionField—The name of the item stored in Session state that the
SessionParameter represents.
Say, if session variable set to value;
Session[“MovieCategory”] = “Animation”;
We can use SessionParameter as;
<asp:SqlDataSource
id=”srcMovies”
SelectCommand=”SELECT Name As Category,Title,Director
FROM Movies
INNER JOIN MovieCategories
ON CategoryId = [Link]
WHERE Name=@Name”
ConnectionString=”<%$ ConnectionStrings:Movies %>”
Runat=”server”>
<SelectParameters>
<asp:SessionParameter
Name=”Name” SessionField=”MovieCategory” />
</SelectParameters>
</asp:SqlDataSource>
Handling SQL Command Execution Errors
Whenever we build a software application we need to plan for failure.
Databases go down, users enter unexpected values in input fields, and
networks get clogged. It is miraculous that the Internet works at all.
You can handle errors thrown by the SqlDataSource control by handling any
or all of the following four events:
Deleted—Happens immediately after the SqlDataSource executes its delete
command.
Inserted—Happens immediately after the SqlDataSource executes its insert
command.
Selected—Happens immediately after the SqlDataSource executes its select
command.
Updated—Happens immediately after the SqlDataSource executes its delete
command.
For ex:
<asp:SqlDataSource
id=”srcMovies”
SelectCommand=”SELECT * FROM DontExist”
ConnectionString=”<%$ ConnectionStrings:Movies %>”
OnSelected= “M_Selected”
Runat=”server” />
<script runat=”server”>
protected void
M_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
if ([Link] != null)
{
[Link] = [Link];
[Link] = true;
}
}
</script>
Similarly if some kind of error occurs while making insertion
in DB we can also handle it;
<script runat=”server”>
protected void
R_Inserted (object sender, FormsViewInsertedEventArgs e)
{
if ([Link] != null)
{
[Link] = [Link];
[Link] = true;
}
}
</script>
Canceling Command Execution
You can cancel SqlDataSource commands when some criterion is not met. For
example, you might want to validate the parameters that you are using with the
command before executing the command.
You can cancel a command by handling any of the following events exposed by
the SqlDataSource control:
Deleting—Happens immediately before the SqlDataSource executes its delete
command.
Filtering—Happens immediately before the SqlDataSource filters its data.
Inserting—Happens immediately before the SqlDataSource executes its insert
command
Selecting—Happens immediately before the SqlDataSource executes its select
command.
Updating—Happens immediately before the SqlDataSource executes its delete
command.
<asp:SqlDataSource
id=”srcMovies”
SelectCommand=”SELECT * FROM Movies”
UpdateCommand=”UPDATE Movies SET Title=@Title,
Director=@Director,DateReleased=@DateReleased
WHERE Id=@id”
ConnectionString=”<%$ ConnectionStrings:Movies %>”
Runat=”server” OnUpdating=”srcMovies_Updating” />
| <script runat=”server”>
protected void M_Updating(object sender,
SqlDataSourceCommandEventArgs e)
{
foreach (SqlParameter param in [Link])
if ([Link] == null)
{
[Link] = true;
[Link] = “All fields are required!”;
}
}
</script>