Its been two and a half years since my last blog post, mainly due to being either busy or lazy. I've decided to try and make more of an effort to post and as part of this, I've also decided to move my blog over to Wordpress to see what its like over there. --  https://nicksoan.wordpress.com

SQL Conditional filter with DropDownList

The following can be used to filter a parameterised SQL query using a drop down list to provide the parameter, or if left blank, will return all results. To keep this quick, im not returning/binding the data to a grid etc...
There are 3 parts required:

• A DropDownList 
• SQLDataSource 
• SQLQuery/Stored Procedure

DropDownList

This DropDownList will be populated from a list of Countries provided by a SQL table and will be used to filter the results set

The important things to note for the DropDown are:


AppendDataBoundItems="true" - Lets you append additional items into a SQL result set
<asp:ListItem Text="" Value="" /> = Creates a blank row at the top of the databound results set

<asp:DropDownList ID="ddlCountryList" runat="server" DataSourceID="dsCountryList"

        DataTextField="BKCountryName" DataValueField="BKCountryName" Width="150px"  
       AutoPostBack="True" AppendDataBoundItems="true">
        <asp:ListItem Text="" Value="" />
</asp:DropDownList>




SQLDataSource
The DataSource is a standard SQLDataSource that is connecting through to my Test Database and wil try and execute a stored Procedure called "usp_ReturnTestData". this proc takes an arguement "Country", which is being provided by the DropDownList.

There are two key things with the DataSource:

1. The property ConvertEmptyStringToNull="true". This ensures that the empty value that were passing from the DropDown will reach the database as a NULL value, and not an empty string.
2. The propery CancelSelectOnNullParameter="false". This will make sure the SQLDatasource doesnt stop as soon as it finds a null value as the parameter

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:cnTestDatabase %>"                SelectCommand="usp_ReturnTestData" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="false">

                <SelectParameters>

                    <asp:ControlParameter ControlID="ddlCountryList" Name="Country" PropertyName="SelectedValue"  

                             ConvertEmptyStringToNull="true" Type="String" />

                </SelectParameters>

</asp:SqlDataSource>

SQL Query
The final part is the SQL Query. In a normal SQL query where we want to return only the value specified in the parameter we could use :

SELECT * FROM Products

WHERE Country = @Country

But in this example, we want to be able to bring back all records if a country is not specified. To do this, we can use the SQL ISNULL() method to evaluate the parameter.

SELECT * FROM Products
WHERE Country = ISNULL(@Country,Country)

The ISNULL function will check to see if the parameter pass is null and if so, return all rows and if if is not, it will filter by the value.

DevExpress AspxGridview - Disable/Hide edit button based on column values

The following will allow the disabling/hiding of an edit button on an AspxGridview based on the value in one of the fields. In this snippet, it checks to see if the rowValue value is populated and if so, it will disable editing

Protected Sub GridSigners_CommandButtonInitialize(sender As Object, e As DevExpress.Web.ASPxGridView.ASPxGridViewCommandButtonEventArgs) Handles GridSigners.CommandButtonInitialize
 
        

If e.Button.ButtonType = DevExpress.Web.ASPxGridView.ColumnCommandButtonType.Edit Then

Dim rowValue As Object = DirectCast(sender, ASPxGridView).GetRowValues(e.VisibleIndex, "columnName")

                If (rowValue Is DBNull.Value) Then
                    e.Visible = True
                Else
                    e.Visible = False
                End If
End If

Show/Hide Divs on Checkbox check in jQuery

This small example shows how you can show/hide content contained in a DIV dependent on the 'checked' property of an asp CheckBox control.

jQuery

<script type="text/javascript">
       $(document).ready(function () {
 
           var chkControl = document.getElementById('<%= chkBox.ClientID %>');
           //Hide div as default
           $("#pnlContent").css("display", "none");
 
           // Handles checkbox 'click' event
           $(chkControl).click(function () {
               if ($(chkControl).is(":checked")) {
                   $("#pnlContent").show("slow");
               }
               else {
                   $("#pnlContent").hide("slow");
               }
           });
       });
 </script>

asp.net

<asp:CheckBox ID="chkBox" runat="server" Text="Check Me" />
  
  <div id="pnlContent">
      <p>Content appears as if by magic!</p>
  </div>


I've found this really useful for building forms where certain fields need to be shown or hidden if a criteria hasn't been met. This is also achievable from the server side where we could create a control that would contain the content, but it would require a postback to the server. This solution is very simple, lightweight and the jQuery animation gives makes it that much more professional looking, rather than forcing the user into a screen refresh and playing spot the difference when something had been added or removed. 


Ive added some basic CSS to make the content easier to see for this example


#pnlContent 
{
    background-color:Gray;
    width:400px;
    height:200px;
    padding: 10px 10px 10px 10px; 
    text-align:center;
}
 
#pnlContent p 
{
  color:White;
  font-size:xx-large;
}

JQuery - BlockUI & asp:Button controls

Using the BlockUI Jquery library to create a Yes/No prompt on the click event of an asp:Button control

* There were problems copying/pasting the jQuery function into here - So I had to screenshot for now

<div><asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" /></div>

<div id="question" style="display:none; cursor: default"> <h1>Would you like to contine?.</h1> <input type="button" id="yes" value="Yes" /> <input type="button" id="no" value="No" /> </div>

SSIS Web Service Task, Variables and Setting up the SQL Agent

One of the things i've found the most difficult to find any sort of good help on, is SSIS.
I intend to write up a few of the important things that ive found and that are not necessarily clear in any official Microsoft atricles or KBs.

I'll start off with one of the things that took me the longest to find - If you have an SSIS package, that contains a Web Service Task that expects parameters, how do you pass parameters to it in SSMS?

SQL Server Agent

Setting the Parameters/Variables to pass to the Web Service
 
In the Set Values tab in the Job Step Properties of the package, you can set the value of the package variables, which will be used to be passed as the parameters into the Web Service. In property path, add :

\Package.Variables[VarName].Value (Where VarName is the name of the variable defined in BIDS - In this case the variable I want to pass is UserID) into the Property Path column and and value you want to pass in the Value column

Access Schema Information in SQL

SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM sysobjects


http://brainof-dave.blogspot.com/2006/11/xtype-values-in-sysobjects.html