Stone Coast Web Design Blog

Navigation

Search

Blogroll

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

| |

E-mail

Total Posts: 240
This Year: 46
This Month: 3
This Week: 0
Comments: 0

Thursday, January 28, 2010

Case Sensitive SQL Query Search

Thursday, January 28, 2010 3:32:08 PM (GMT Standard Time, UTC+00:00) ( SQL )

Case Sensitive SQL Query Search

If Column1 of Table1 has following values ‘CaseSearch, casesearch, CASESEARCH, CaSeSeArCh’, following statement will return you all the four records.

SELECT Column1 FROM Table1 WHERE Column1 = 'casesearch'

To make the query case sensitive and retrieve only one record (“casesearch”) from above query, the collation of the query needs to be changed as follows.
SELECT Column1 FROM Table1 WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'

Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

To change the collation of the any column for any table permanently run following query.
ALTER TABLE Table1 ALTER COLUMN Column1 VARCHAR(20) COLLATE Latin1_General_CS_AS

To know the collation of the column for any table run following Stored Procedure.
EXEC sp_help DatabaseName

Second results set above script will return you collation of database DatabaseName.

Comments [0] | |

Wednesday, January 20, 2010

11 Ways to Increase Your jQuery Performance and jQuery Cheatsheet

Wednesday, January 20, 2010 7:45:40 PM (GMT Standard Time, UTC+00:00) ( jQuery )

Nice article: http://addyosmani.com/blog/11-ways-to-increase-your-jquery-performance/

Comments [0] | |

Tuesday, January 19, 2010

Encrypting SQL Server Data

Tuesday, January 19, 2010 1:58:07 AM (GMT Standard Time, UTC+00:00) ( SQL )

I’m working on a large project for a federal agency and they want to start using some sensitive data on a Web site I’m working on for them. While SSL is being used, they also require that some of the data being stored in the database be encrypted. I found a pretty simple to do it on this article: http://www.sql-server-helper.com/functions/string-encryption.aspx

Here is the code:

Encryption

CREATE FUNCTION [dbo].[Encrypt] ( @pClearString VARCHAR(100) )
RETURNS NVARCHAR(100) WITH ENCRYPTION AS
BEGIN
    
    DECLARE @vEncryptedString NVARCHAR(100)
    DECLARE @vIdx INT
    DECLARE @vBaseIncrement INT
    
    SET @vIdx = 1
    SET @vBaseIncrement = 128
    SET @vEncryptedString = ''
    
    WHILE @vIdx <= LEN(@pClearString)
    BEGIN
        SET @vEncryptedString = @vEncryptedString + 
                                NCHAR(ASCII(SUBSTRING(@pClearString, @vIdx, 1)) +
                                @vBaseIncrement + @vIdx - 1)
        SET @vIdx = @vIdx + 1
    END
    
    RETURN @vEncryptedString

END
GO

Decrypt

CREATE FUNCTION [dbo].[Decrypt] ( @pEncryptedString NVARCHAR(100) )
RETURNS VARCHAR(100) WITH ENCRYPTION AS
BEGIN

DECLARE @vClearString VARCHAR(100)
DECLARE @vIdx INT
DECLARE @vBaseIncrement INT

SET @vIdx = 1
SET @vBaseIncrement = 128
SET @vClearString = ''

WHILE @vIdx <= LEN(@pEncryptedString)
BEGIN
    SET @vClearString = @vClearString + 
                        CHAR(UNICODE(SUBSTRING(@pEncryptedString, @vIdx, 1)) - 
                        @vBaseIncrement - @vIdx + 1)
    SET @vIdx = @vIdx + 1
END

RETURN @vClearString

END
GO

Here are examples of how to use these functions

INSERT INTO USERS values('TestUser1', dbo.ENCRYPT('TestPW1'))

SELECT UserID, dbo.Decrypt(UserPW) AS Password from Users

Comments [0] | |

Friday, January 15, 2010

Search and Highlight a GridView Using jQuery

Friday, January 15, 2010 9:24:10 PM (GMT Standard Time, UTC+00:00) ( Gridview | jQuery )

This cool example allows you to enter some text into a textbox which searches and highlights the results in a Gridview. Here is a working demo. And another example.

<script type="text/javascript" src="http://ajax.Microsoft.com/ajax/jquery/jquery-1.3.2.js"></script>

    <script type="text/javascript">
        $(function() {
            var $txtBox = $('input[id$=txtSearch]');
            $txtBox.keyup(function(e) {
            searchText();
        });
        
        function searchText() {
            var $txt = $txtBox.val().toLowerCase();
                $(".grid td").removeClass("highlight");
                if ($txt) {
                $(".grid > tbody > tr > td:not(:has(table, tr))")
                .filter(function() {
                return $(this).text().toLowerCase().indexOf($txt) != -1;
                }).addClass('highlight');
                }
            }
            searchText();
        });
</script>
    
</head>
<body>

<form runat="server">
Enter Text Here: <asp:TextBox ID="txtSearch" runat="server" /><br /><br />

<asp:gridview runat="server" CellPadding="4" 
ForeColor="#333333" GridLines="None" 
AllowSorting="false" class="grid" 
id="dgMovies" Width="80%" 
HorizontalAlign="Center" 
Font-Names="Tahoma" 
Font-Size="Small" 
EmptyDataText="No records were found">
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />    
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <HeaderStyle BackColor="gray" Font-Bold="True" ForeColor="Black" />
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:gridview>

</form>

Comments [0] | |

Using jQuery to Swap Images

Friday, January 15, 2010 8:47:13 PM (GMT Standard Time, UTC+00:00) ( jQuery )

This would be helpful when expanding and collapsing panels. Here is an example.

<!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>
        <title>jQuery from Microsoft AJAX CDN</title>
        <script type="text/javascript" src="http://ajax.Microsoft.com/ajax/jquery/jquery-1.3.2.js"></script>

        <script type="text/javascript">        
        $(function() {
            $('img[id$=imageSwap]').toggle(
                function() {
                $(this).attr("src", "../images/Hide.gif");
            },
                function() {
                $(this).attr("src", "../images/Show.gif");
                }
                );
            });        
        </script>
    </head>
    <body>


    <h2>Click on the image to swap</h2>
    <br /><br />
    <asp:Image ID="imageSwap" runat="server"
    ImageUrl="~/images/Show.gif" />
    
</body>
</html>

Comments [0] | |

jQuery Grid Plugin

Friday, January 15, 2010 6:54:08 PM (GMT Standard Time, UTC+00:00) ( ASP.NET | jQuery )

Pretty amazing grid plugin for ASP.NET. He has versions for both ASP.NET and PHP.

http://www.trirand.com/blog/?page_id=5

Comments [0] | |

Wednesday, January 13, 2010

Working With Dates and Times in SQL Server

Wednesday, January 13, 2010 3:38:29 AM (GMT Standard Time, UTC+00:00) ( SQL )

Two great articles on this topic

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

http://weblogs.sqlteam.com/jeffs/jeffs/jeffs/jeffs/jeffs/jeffs/archive/2007/01/02/56079.aspx

Comments [0] | |

Monday, January 11, 2010

SQL Practice

Monday, January 11, 2010 9:11:01 PM (GMT Standard Time, UTC+00:00) ( SQL )

A query with an ORDER BY clause cannot be used as a table expression. That is, a view, inline table-valued function,sub0query, derived table
or common table expression. This produces the error message "The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified."

SELECT UserID, Name 
FROM (SELECT UserID, Name FROM TSubscribers ORDER BY Name) AS D

The way to get around this is to use the TOP option.

SELECT UserID, Name
FROM (SELECT TOP 100 Percent UserID, Name, Company FROM TSubscribers ORDER BY Name) AS D

SELECT CASE

SELECT CustomerID, City,
    CASE
        WHEN COUNT(orderID) = 0 Then 'No_Orders'
        WHEN COUNT(orderID) <=2 Then 'Up_to_2_orders'
        WHEN COUNT(orderID) > 2 Then 'More_than_2_orders'
    END AS Category
    FROM Customers
    GROUP BY CustomerID, City

Comments [0] | |

Wednesday, January 06, 2010

Hiding a Submit Button When Clicked Using Javascript

Wednesday, January 06, 2010 6:57:11 PM (GMT Standard Time, UTC+00:00) ( ASP.NET | HTML | Javascript/AJAX )

This is a cool trick which assigns a bit of javascript to a server-side button to hide one button and replace it with another. In this example, the submit button, chkReview, when clicked is hidden from the user and is replaced with another button, btnSaveDisabled, which says “Please wait…Your request is being processed.”

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)   
If Not Page.IsPostBack Then
chkReview.OnClientClick = string.Format("javascript:{0}.style.visibility = 'hidden';{0}.style.display  = 'none';{1}.style.visibility = 'visible'", 
chkReview.ClientID, btnSaveDisabled.ClientID)
End If
End Sub

<asp:Button ID="chkReview" runat="server" onclick="chkSubmitRequest_Click" Text="Submit Request" CssClass="button"  />
<br />
<asp:Button ID="btnSaveDisabled" Enabled="false" runat="server" Text="Please wait...Your request is being processed" 
Style="visibility: hidden;" CssClass="button" />

Comments [0] | |

Removing Duplicate Values from a List

Wednesday, January 06, 2010 6:39:30 PM (GMT Standard Time, UTC+00:00) ( ASP.NET | VB.NET )

This is an example of how to remove duplicate values from a list or string separated by commas. You will also need to import the System.IO namespace to use the StringBuilder method: <%@ Import Namespace="System.IO" %>

'lblIDList contains a list of values separated by commas
'12,58,102,12,99,87,87

Dim values As String
values = lblIDList.Text
                
'Remove duplicates
values = RemoveDuplicates(values)                
                
'Reassign the lblIDList value without the duplicates
lblIDList.Text = values

Public Function RemoveDuplicates(ByVal items As String) As String
            Dim Result As StringBuilder = New StringBuilder()
            Dim newArray As Array
    
            newArray = Split(items, ",")
            For i As Integer = 0 To newArray.Length - 1
                If Result.ToString.IndexOf(newArray(i).ToString()) = -1 Then
                    Result.Append(newArray(i).ToString() & ",")
                End If
            Next
            Return Result.ToString.Substring(0, Result.ToString.LastIndexOf(","))
End Function

Comments [0] | |

How to generate a random number

Wednesday, January 06, 2010 6:32:18 PM (GMT Standard Time, UTC+00:00) ( ASP.NET | VB.NET )

An example of how to generate a random number in VB.NET

intLowerBound = 0
intUpperBound = 1000      
'Get the random number and display it in lblRandomNumber
lblRandomNumber.Text = GetRandomNumberInRange(intLowerBound, intUpperBound)

Function GetRandomNumberInRange(intLowerBound As Integer, intUpperBound As Integer)
                
    Dim RandomGenerator As Random
    Dim intRandomNumber As Integer

    ' Create and init the randon number generator
    RandomGenerator = New Random()

    ' Get the next random number
        intRandomNumber = RandomGenerator.Next(intLowerBound, intUpperBound + 1)
                
    ' Return the random # as the functions return value
    GetRandomNumberInRange = intRandomNumber
                        
End Function

Comments [0] | |

Stone Coast Web Design Blog

Mostly .NET Development

Navigation

Search

Categories

On this page

Archive

Blogroll