Stone Coast Web Design's Weblog

Navigation

Search

Blogroll

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

| |

E-mail

Total Posts: 112
This Year: 50
This Month: 0
This Week: 0
Comments: 0

Thursday, February 07, 2008

TRIM Function in SQL Server

Thursday, February 07, 2008 10:52:21 AM (Eastern Standard Time, UTC-05:00) ( )

I was surprised to learn that both SQL Server 2000/2005 don't offer a built in TRIM function to remove leading or trailing whitespace from a string. What is possible, however, is to use a user defined function (UDF) to do the same thing.

SQL Server 2000:

CREATE FUNCTION dbo.TRIM(@string VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
RETURN LTRIM(RTRIM(@string))
END

SQL Server 2005:

CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string))
END
GO

Then you call the function like this

SELECT DISTINCT(dbo.TRIM(Industry)) AS Industry FROM TCompanies
WHERE Industry IS NOT NULL

Comments [0] | Trackback |

Saturday, February 02, 2008

SQL Server Split Function

Saturday, February 02, 2008 8:18:09 PM (Eastern Standard Time, UTC-05:00) ( )

Using a comma delimited string with id's as input parameter for a SQL query

I just recently found out about a custom Split function for SQL Server 2000/2005 while I was reading this article: Designing Reports with SQL Server Reporting Services 2005. It mentioned a custom Split function made in T-SQL that could take in a delimited string with id's.

Today I had the need of such a functionality in my current project and luckily remembered where I saw it in the first place. If you check several checkboxes and press the button you get, for those selected records, to see everything that's in the database for those.

Split function:

IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'Split' 
)
   DROP FUNCTION Split
GO
CREATE FUNCTION dbo.Split
(
    @ItemList NVARCHAR(4000), 
    @delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))  
AS      

BEGIN    
    DECLARE @tempItemList NVARCHAR(4000)
    SET @tempItemList = @ItemList

    DECLARE @i INT    
    DECLARE @Item NVARCHAR(4000)

    SET @tempItemList = REPLACE (@tempItemList, ' ', '')
    SET @i = CHARINDEX(@delimiter, @tempItemList)

    WHILE (LEN(@tempItemList) > 0)
    BEGIN
        IF @i = 0
            SET @Item = @tempItemList
        ELSE
            SET @Item = LEFT(@tempItemList, @i - 1)
        INSERT INTO @IDTable(Item) VALUES(@Item)
        IF @i = 0
            SET @tempItemList = ''
        ELSE
            SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
        SET @i = CHARINDEX(@delimiter, @tempItemList)
    END 
    RETURN
END  
GO

This is how to call the split function using a stored procedure:

CREATE PROCEDURE USP_RetrieveInformationForSelectedEmployees 
@p_selectedEmployees NVARCHAR(50) 
AS 
BEGIN SELECT * FROM Employees 
WHERE EmployeeID in (SELECT Item FROM split(@p_selectedEmployees, ',')) 
END

Lastly, this is how it's all put together:

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web.Configuration" %>
<%@ Import Namespace="System.Data" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  
<script runat="server">
  
   protected void Button1_Click(object sender, EventArgs e)
  {
  RetrieveInformationForSelectedEmployees();
  }
   
   private void RetrieveInformationForSelectedEmployees()
  {
  StringBuilder sb = new StringBuilder();
   
   // First loop through the GridView and see which
   // employees were selected. I use the StringBuilder
   // since the list could be a very long list.
   foreach (GridViewRow row in GridView1.Rows)
  {
   if (((CheckBox)row.FindControl("CheckBox1")).Checked)
  {
  sb.Append(GridView1.DataKeys[row.RowIndex].Value.ToString() + ',');
  }
  }
   
   using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings
  ["NorthwindConnectionString"].ConnectionString))
  {
   using (SqlCommand cmd = new SqlCommand("USP_RetrieveInformationForSelectedEmployees", conn))
  {
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.AddWithValue("@p_selectedEmployees", sb.ToString());
   
  DataSet ds = new DataSet();
  SqlDataAdapter da = new SqlDataAdapter(cmd);
  da.Fill(ds);
   
  GridViewResult.DataSource = ds;
  GridViewResult.DataBind();
  }
  }
  }
   
  </script>
   
  <html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
   <title>Untitled Page</title>
  </head>
  <body>
   <form id="form1" runat="server">
   <div>
   <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
   DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">
   <Columns>
   <asp:TemplateField>
   <ItemTemplate>
   <asp:CheckBox ID="CheckBox1" runat="server" />
   </ItemTemplate>
   </asp:TemplateField>
   <asp:BoundField DataField="LastName" HeaderText="LastName" 
   SortExpression="LastName" />
   <asp:BoundField DataField="FirstName" HeaderText="FirstName" 
   SortExpression="FirstName" />
   <asp:BoundField DataField="Title" HeaderText="Title" 
   SortExpression="Title" />
   </Columns>
   </asp:GridView>
   <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
   ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
   SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [Title]
  FROM [Employees] ORDER BY [LastName], [FirstName]">
   </asp:SqlDataSource>
   
   <asp:Button ID="Button1" runat="server" Text="Retrieve data" OnClick="Button1_Click" />
   <p>
   <asp:GridView runat="server" ID="GridViewResult" />
   </p>
   </div>
   </form>
  </body>
 </html>

Comments [0] | Trackback |

Wednesday, January 30, 2008

A Look at ASP.NET 2.0's URL Mapping

Wednesday, January 30, 2008 11:26:30 AM (Eastern Standard Time, UTC-05:00) ( )

I've been working on a project at work which involves hosting PHP pages in a IIS 6.0 server. The site uses querystring parameters to pass to single PHP page which in turn calls the requested include pages. The site works well but Google has difficulty crawling the site because none of the pages use "friendly-URLs". We found some third-party products to install on IIS like http://www.micronovae.com/ModRewrite/ModRewrite.html but there they're a pain to get working and cost $$.

I started looking more into URLRewritting and was happy to see that this is built into the 2.0 Framework. Here's a nice overview of it from Scott Mitchell:

http://aspnet.4guysfromrolla.com/articles/011007-1.aspx

Comments [0] | Trackback |

Monday, January 28, 2008

VS2008 includes shiny new images to use in our apps

Monday, January 28, 2008 8:30:52 PM (Eastern Standard Time, UTC-05:00) ( )

http://blogs.devsource.com/devlife/content/net_general/vs2008_includes_shiny_new_images_to_use_in_our_apps_1.html

Comments [0] | Trackback |

Saturday, January 26, 2008

Conditional Logic on TemplateField

Saturday, January 26, 2008 9:59:05 PM (Eastern Standard Time, UTC-05:00) ( )

I recently ran into a challenge where I needed to change the way a asp:Hyperlinkfield control worked based on another value in my database. In my case below. I needed the Gridview to show a link to companyprofile.aspx using the HyperlinkField control if the company was in the Fortune 1000. If the company was not the in the Fortune 1000, then I just needed to display a regular bound field in the Gridview.

To summarize:

If company is in the Fortune 1000, show this field.

<asp:HyperLinkField 
DataTextField="CompanyName"
SortExpression="CompanyName"   
DataNavigateURLFields="CompID" 
DataNavigateURLFormatString="companyprofile.aspx?CompID={0}"
HeaderText="Name" >
</asp:HyperLinkField>

Otherwise, show this field:

<asp:BoundField HeaderText="Name" DataField="CompanyName" SortExpression="CompanyName" />

The way I ended up solving this was to create both a HyperLink control and a Label control and stick them within a TemplateField. With both of the controls within the field, then you can apply a conditional statement within the Visible property of each control. Then, only one control will display at a time, depending on the conditional result. In the code example below, I show both the Hyperlink control and a label control populated with the company name. I then make the 'visible' property conditional on the value of IndexFortune making sure to use logic so that both would never be shown at the same time.

<asp:templatefield headertext="Name" sortexpression="CompanyName">
    <itemtemplate>
        <asp:hyperlink id="HyperLink1" runat="server" navigateurl='<%# Eval("CompID", "companyprofile.aspx?CompID={0}") %>'
            text='<%# Eval("CompanyName") %>' visible='<%# Convert.ToInt32(Eval("IndexFortune")) < 1001 %>' />
        <asp:label id="label1" runat="server" text='<%# Eval("CompanyName") %>' visible='<%# Convert.ToInt32(Eval("IndexFortune")) >= 1001 %>' />
    </itemtemplate>
</asp:templatefield>

This approach worked well except for one glitch. When the IndexFortune value was null, I would get an error trying to convert this value to an Int32. I addressed this part in my stored procedure using the ISNULL function in SQL Server. When IndexFortune is null, give it a default value of 1001 so it shows up in my label control in the Gridview without a hyperlink.

SELECT ISNULL(IndexFortune, 1001) AS IndexFortune

Comments [0] | Trackback |

Monday, January 21, 2008

Mass Delete Using Gridview with Checkboxes

Monday, January 21, 2008 3:42:52 PM (Eastern Standard Time, UTC-05:00) ( )


   1:  <%@ Import Namespace="System.Data" %>

   2:  <%@ Import Namespace="System.Data.SQLClient" %>

   3:  <%@ Page Language="VB" %>

4:

   5:  <script language="VB" Runat="server">

   6:  Dim ConnString as String

   7:      Sub Page_Load(Source as Object, E as EventArgs)

   8:          ConnString=ConfigurationManager.ConnectionStrings("YourNorthwindString").ConnectionString

   9:          if not page.IsPostBack then

  10:              BindGrid

  11:          end if

  12:      End Sub

13:

  14:      Sub DeleteRecord(intProd as Integer)

  15:          Dim Conn As New SqlConnection(ConnString)

  16:          Dim cmd As New SqlCommand("Delete from Products where productID=@ProductID", Conn)

  17:          cmd.Parameters.Add(New SqlParameter("@ProductID", intProd))

  18:          Conn.Open()

  19:          cmd.ExecuteNonQuery()

  20:          Conn.Close

  21:      End Sub

22:

23:

  24:      Sub DoDelete()

  25:          Dim i As Integer

  26:          For i = 0 To gvProducts.Rows.Count - 1

  27:              Dim dgItem As GridViewRow = gvProducts.Rows(i)

  28:              Dim lblid As Label = CType(dgItem.FindControl("lblid"), Label)

  29:              Dim cb As CheckBox = CType(dgItem.FindControl("chk1"), CheckBox)

  30:              If cb.Checked Then

  31:                  DeleteRecord(CInt(lblid.Text))

  32:              End If

  33:          Next i

  34:          BindGrid()

  35:      End Sub

36:

  37:      Sub BindGrid()

  38:            Dim Conn As New SqlConnection(ConnString)

  39:            Dim dr as SqlDataReader

  40:            Dim mySQL as String

  41:            mySQL="SELECT Top 10 [ProductID], [ProductName], [QuantityPerUnit], " & _

  42:                "[UnitPrice] FROM [Products]"

  43:              Dim cmd As New SqlCommand(mySQL, Conn)

  44:              Conn.Open()

  45:          dr=cmd.ExecuteReader

  46:          gvProducts.DataSource=dr

  47:          gvProducts.DataBind

  48:          Conn.Close

  49:      End Sub

50:

  51:      Protected Sub btnDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs)

  52:          DoDelete()

  53:      End Sub

  54:  </script>

55:

  56:  <head runat="server">

  57:      <title>Mass Deletes Using a Gridview with Checkboxes</title>

  58:  </head>

  59:  <body>

  60:      <form id="form1" runat="server">

  61:          <asp:GridView ID="gvProducts" runat="server" AutoGenerateColumns="False"

  62:              DataKeyNames="ProductID">

  63:              <Columns>

  64:                  <asp:TemplateField HeaderText="Mark for Delete">

  65:                      <ItemTemplate>

  66:                          <asp:CheckBox ID="chk1" runat="server" Checked="False" />

  67:                           <asp:Label ID="lblID" Visible="True" runat="server" Text='<%# Bind("ProductID") %>'></asp:Label>

  68:                      </ItemTemplate>

  69:                  </asp:TemplateField>

70:

  71:                  <asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />

  72:                  <asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit" SortExpression="QuantityPerUnit" />

  73:                  <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" HtmlEncode="False" DataFormatString="{0:C}" SortExpression="UnitPrice" />

  74:              </Columns>

  75:          </asp:GridView>

  76:          <asp:Button ID="btnDelete" runat="server" Text="Delete Checked" OnClick="btnDelete_Click" />

  77:      </form>

  78:  </body>

  79:  </html>

80:

Comments [0] | Trackback |

Monday, January 14, 2008

Using AJAX to Search & Sort a Gridview

Monday, January 14, 2008 2:00:01 PM (Eastern Standard Time, UTC-05:00) ( )

I've been working through examples in several books and blogs and one of the coolest examples I've come across involves searching and sorting by drilling down through a specified column. After you've clicked on a column header to sort the data in a Gridview, you can then enter a search string into the textbox and restrict the search results even further all without refreshing the page.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="filter.aspx.cs" Inherits="Filter" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<style type="text/css">
.highlight {
    background-color: yellow;
}
</style>
    <title>Movie Gridview Filter</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:ScriptManager ID="ScriptManager1" runat="server" />
    <div>
        <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="conditional">
            <ContentTemplate>
        <div>
            Filter Selected Column (<asp:Label ID="lblSelectedColumn" runat="server" />):
                <asp:TextBox ID="FilterText" runat="server" OnTextChanged="FilterText_TextChanged" />               
        </div>
        <p>
            
            <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" AllowPaging="True" 
            AutoGenerateColumns="False" AllowSorting="True" EmptyDataText="There are no records to display"
            Cellpadding="3" GridLines="Vertical" OnRowDataBound="GridView1_RowDataBound"
            OnPageIndexChanged="GridView1_PageIndexChanged" OnSorted="GridView1_Sorted" BackColor="White" 
      BorderColor="#999999" BorderStyle="None" BorderWidth="1px" Font-Names="Verdana,sans-serif" Font-Size="X-Small" 
HorizontalAlign="Center" Width="90%">            
            
            <Columns>                
               <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
               <asp:BoundField DataField="Director" HeaderText="Director" SortExpression="Director" />
               <asp:BoundField DataField="DateReleased" HeaderText="DateReleased" DataFormatString="{0:dd-MMM-yy}" 
               SortExpression="DateReleased" />
               <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />                                      
            </Columns>                
                <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
                <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
                <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
                <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
                <AlternatingRowStyle BackColor="#DCDCDC" />
            </asp:GridView>
            
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ConnectionStrings:MyDatabase %>"
            SelectCommand="SELECT Title,  Director, DateReleased, Description FROM Movies"></asp:SqlDataSource>
            
            </ContentTemplate>
            <Triggers>
                    <asp:AsyncPostBackTrigger ControlID="FilterText" EventName="TextChanged" />
            </Triggers>            
             </asp:UpdatePanel>
        </p>
    </div>
    </form>
   
   <script type="text/javascript">
   Sys.Application.add_load(page_load);
   Sys.Application.add_unload(page_unload);
   
   function page_load() {
    $addHandler($get('FilterText'), 'keydown', onFilterTextChanged);
   }
   
   function page_unload() {
   $removeHandler($get('FilterText'), 'keydown', onFilterTextChanged);
   }
   
   var timeoutID = 0; 
   
   function onFilterTextChanged(e) {
    if (timeoutID) {
            window.clearTimeout(timeoutID);
     }
      timeoutID = window.setTimeout(updateFilterText, 1000);
   }  
   
   function updateFilterText() {
   __doPostBack('FilterText', '');
   }
   </script> 
</body>
</html>

filter.aspx.cs:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Text;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Filter : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
            GridView1.Sort("Title", SortDirection.Ascending);
        lblSelectedColumn.Text = "Title";
    }

    protected void GridView1_Sorted(object sender, EventArgs e)
    {
        UpdateFilter();
    }

    protected void GridView1_PageIndexChanged(object sender, EventArgs e)
    {
        UpdateFilter();
    }

    protected void Filter_Click(object sender, EventArgs e)
    {
        UpdateFilter();
    }

    protected void FilterText_TextChanged(object sender, EventArgs e)
    {
        UpdateFilter();
    }

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType != DataControlRowType.DataRow)
            return;

        if (String.IsNullOrEmpty(SqlDataSource1.FilterExpression))
            return;

        int colIndex = GetColumnIndex(GridView1.SortExpression);
        TableCell cell = e.Row.Cells[colIndex];

        string cellText = cell.Text;
        int leftIndex = cellText.IndexOf(FilterText.Text, StringComparison.OrdinalIgnoreCase);

        int rightIndex = leftIndex + FilterText.Text.Length;

        StringBuilder builder = new StringBuilder();
        builder.Append(cellText, 0, leftIndex);
        builder.Append("<span class=\"highlight\">");
        builder.Append(cellText, leftIndex, rightIndex - leftIndex);
        builder.Append("</span>");
        builder.Append(cellText, rightIndex, cellText.Length - rightIndex);
        cell.Text = builder.ToString();
    }

    private void UpdateFilter()
    {
        string filterExpression = null;

        if (!String.IsNullOrEmpty(FilterText.Text))
            filterExpression = string.Format("[{0}] LIKE '%{1}%'", GridView1.SortExpression, FilterText.Text);
        lblSelectedColumn.Text = GridView1.SortExpression;

        SqlDataSource1.FilterExpression = filterExpression;
    }

    private int GetColumnIndex(string columnName)
    {
        for (int i = 0; i < GridView1.Columns.Count; i++)
        {
            BoundField field = GridView1.Columns[i] as BoundField;
            if (field != null && field.DataField == columnName)
                return i;
        }
        return -1;
    }

Technorati Tags: ASP.NET,AJAX,Gridview,Sorting

Comments [0] | Trackback |

Sunday, January 13, 2008

Why ASP.NET AJAX UpdatePanels are dangerous

Sunday, January 13, 2008 11:04:30 AM (Eastern Standard Time, UTC-05:00) ( ASP.NET | Javascript/AJAX )

An interesting article on the drawbacks of using the UpdatePanel

http://encosia.com/2007/07/11/why-aspnet-ajax-updatepanels-are-dangerous/

Comments [0] | Trackback |

Mostly Web Development

Navigation

Search

Categories

On this page

Archive

Blogroll

SQL Server 2000:

SQL Server 2005: