Web Design | o7th Web Design

RSS

Archive for the ‘Web Design’ Category

Dynamic Parameterized Queries in VB.Net

01 Jun

I have a bit of code which can be found here. I use this all the time when I am developing classic ASP web application.

I recently found that I have a need for the same methodolgy was needed in a .Net application I recently started developing, so I came up with the following:

Code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
 
 
Namespace ZipCM
 
    Public Class Database
 
#Region "Properties"
        Public ConnString As String
        Public CmdType As Integer
        Public Query As String
        Public Values As Object
        Public ParamNames As Object
        Public ReturnValues As Object
        Public FieldCount As Long
        Public TotalRecords As Long
        Public AffectedRows As Long
        Public ReturnValue As Object
        Public HasReturn As Boolean
        Public ReturnParamName As String
        Public ReturnDataType As Object
 
        Private i As Integer
#End Region
 
#Region "ExecuteDataReader"
        Public Sub ExecuteDataReader()
            Try
                Dim objConn As New SqlConnection(ConnString)
                Dim objCmd As New SqlCommand(Query, objConn)
                Dim objDA As New SqlDataAdapter()
                Dim objDS As New DataSet()
                If IsArray(Values) And IsArray(ParamNames) Then
                    If UBound(Values) = UBound(ParamNames) Then
                        ‘Prepare our parameters
                        For Me.i = 0 To UBound(Values)
                            objCmd.Parameters.AddWithValue(ParamNames(i), Values(i))
                        Next
                        objCmd.CommandType = CmdType
                        objCmd.Connection.Open()
                        objDA.SelectCommand = objCmd
                        objDA.Fill(objDS, "Tbl")
                        objCmd = Nothing
                        objConn.Close() : objConn = Nothing
                        Dim DT As DataTable = objDS.Tables("Tbl")
                        FieldCount = DT.Columns.Count
                        TotalRecords = DT.Rows.Count
                        Dim c As Integer
                        Dim r As Integer
                        Dim tmpRetVals(FieldCount – 1, TotalRecords – 1) As Array
                        For c = 0 To FieldCount – 1
                            For r = 0 To TotalRecords – 1
                                tmpRetVals(c, r) = New String() {DT.Columns.Item(c).ColumnName, DT.Rows(r)(c).ToString()}
                            Next
                        Next
                        ReturnValues = tmpRetVals
 
                    End If
                End If
            Catch ex As Exception
 
            End Try
        End Sub
 
#End Region
 
#Region "Execute Non Data Reader"
 
        Public Sub ExecuteNDQuery()
            Try
                Dim objConn As New SqlConnection(ConnString)
                Dim objCmd As New SqlCommand(Query, objConn)
                Dim objParam As SqlParameter
                If IsArray(Values) And IsArray(ParamNames) Then
                    If UBound(Values) = UBound(ParamNames) Then
                        ‘Prepare our parameters
                        If HasReturn Then
                            objParam = New SqlParameter(ReturnParamName, ReturnDataType)
                            objParam.Direction = ParameterDirection.Output
                            objCmd.Parameters.Add(objParam)
                            objParam = Nothing
                        End If
                        For Me.i = 0 To UBound(Values)
                            objCmd.Parameters.AddWithValue(ParamNames(i), Values(i))
                        Next
                        objCmd.CommandType = CmdType
                        objCmd.Connection.Open()
                        If HasReturn Then
                            objCmd.ExecuteNonQuery()
                            ReturnValue = objCmd.Parameters(ReturnParamName).Value
                        Else
                            objCmd.ExecuteNonQuery()
                        End If
                        objCmd.Connection.Close() : objConn = Nothing
 
                    End If
                End If
            Catch ex As Exception
 
            End Try
        End Sub
 
#End Region
 
    End Class
End Namespace

Bookmark It

No Comments

Posted in Web Design

Optimizing Scripts

22 Jan

I’ve found that by optimizing my javascript and CSS files a bit, I can decrease the load times of my pages by quite a bit.

Combining script files, and removing a few elements decreased the total filesize by 36%.

Here is how I do it!

Using it!

<!–#include virtual="/includes/server/classes/compress.asp"–>
<%
Set objCompress = New clsCompress
        Dim FilePath : FilePath =       Split(Request.QueryString("file"), ",")
        objCompress.strFile =           FilePath
        objCompress.ReturnScripts()
Set objCompress = Nothing
%>

{please note, you will have to change the location of the include file.}

Include File

<%
Class clsCompress
        Public strFile
        Private objFSO, objFile, objRegExp, strCompressedFile, strFileType
        Private Sub Class_Initialize()
                strCompressedFile = ""
        End Sub
        Public Sub ReturnScripts()
                Set objFSO = CreateObject("Scripting.FileSystemObject")
                        If IsArray(strFile) Then
                                For i = 0 To UBound(strFile)
                                        Set objFile = objFSO.OpenTextFile(Server.MapPath(strFile(i)))
                                                If Not(objFile.AtEndOfStream) Then
                                                        strCompressedFile = strCompressedFile & VbCrLf & objFile.ReadAll
                                                End If
                                        Set objFile = Nothing
                                        tmp = Right(strFile(i), 3)
                                        strFileType = Replace(tmp, ".", "")
                                Next : i = Null
                                Erase strFile
                        End If
                Set objFSO = Nothing
                Select Case UCase(strFileType)
                        Case "CSS" ‘CSS
                                Set objRegExp = New RegExp
                                        objRegExp.IgnoreCase = True
                                        objRegExp.Global = True
                                        objRegExp.Pattern = "/\*[^*]*\*+([^/][^*]*\*+)*/"
                                        strCompressedFile = objRegExp.Replace(strCompressedFile, "")
                                Set objRegExp = Nothing                         
                                ‘Strip Line Breaks
                                Set objRegExp = New RegExp
                                        objRegExp.IgnoreCase = True
                                        objRegExp.Global = True
                                        objRegExp.Pattern = "\n+"
                                        strCompressedFile = objRegExp.Replace(strCompressedFile, "")
                                Set objRegExp = Nothing                         
                                ‘Strip Tabs
                                Set objRegExp = New RegExp
                                        objRegExp.IgnoreCase = True
                                        objRegExp.Global = True
                                        objRegExp.Pattern = "\t+"
                                        strCompressedFile = objRegExp.Replace(strCompressedFile, "")
                                Set objRegExp = Nothing
                                ‘Strip Spaces Greater than 2 long
                                Set objRegExp = New RegExp
                                        objRegExp.IgnoreCase = True
                                        objRegExp.Global = True
                                        objRegExp.Pattern = "\s{2}"
                                        strCompressedFile = objRegExp.Replace(strCompressedFile, "")
                                Set objRegExp = Nothing
                                Response.ContentType = "text/css"
                                Response.Write(strCompressedFile)
                                Response.Flush
                                Response.Clear
                        Case "JS" ‘Javascript
                                ‘Strip New Lines
                                strCompressedFile = Replace(strCompressedFile, VbCr, "")
                                ‘Strip Spaces Greater than 2 long
                                strCompressedFile = Replace(strCompressedFile, "  ", "")
                                Response.ContentType = "text/javascript"
                                Response.Write(strCompressedFile)
                                Response.Flush
                                Response.Clear
                End Select
        End Sub
End Class
%>

Bookmark It

No Comments

Posted in Web Design

Couple of JQuery Ajax Loaders

22 Dec

I had a couple of instances where I needed some nice, clean AJAX content loaders. Once to slide a page, one to page, and one with no effects.

Here is the code for them… enjoy (p.s. these require you include the JQuery library, also make sure to change any path inside to the path of your loading images)

function loadPageFade(strPageToLoad, strWhereToLoadIt) {
        $.ajax({
                url: strPageToLoad,
                beforeSend: function(){
                        $(strWhereToLoadIt).queue( function() {
                                $(this).html(‘<p style="text-align:center;"><img src="/icons/loading_bar.gif" alt="Loading…" /></p>’);
                                $(this).animate({opacity:0}, 500);
                                $(this).dequeue();
                        });
                },
                cache: false,
                success: function(html){
                        $(strWhereToLoadIt).queue( function() {
                                $(this).html(html).animate({opacity:1}, 500);
                                $(this).dequeue();
                        });
                },
                error: function(){
                        $(strWhereToLoadIt).queue( function() {
                                $(this).html(‘<div class="err_message">There was an error processing your request.</div>’).animate({opacity:1}, 500);
                                $(this).dequeue();
                        });
                }
                });
}
function loadPageSlide(strPageToLoad, strWhereToLoadIt) {
        $.ajax({
                url: strPageToLoad,
                beforeSend: function(){
                        $(strWhereToLoadIt).slideUp(300);
                },
                cache: false,
                success: function(html){
                        $(strWhereToLoadIt).html(html).slideDown(500);
                },
                error: function(){
                        $(strWhereToLoadIt).html(‘<div class="err_message">There was an error processing your request.</div>’).hide().slideDown(500);
                }
                });
}
function loadPage(strPageToLoad, strWhereToLoadIt) {
        $.ajax({
                url: strPageToLoad,
                cache: false,
                success: function(html){
                        $(strWhereToLoadIt).html(html);
                },
                error: function(){
                        $(strWhereToLoadIt).html(‘<div class="err_message">There was an error processing your request.</div>’).hide().fadeIn(1000);
                }
                });
}

Bookmark It

No Comments

Posted in Web Design

Dynamic Parameterized Queries w/ Paging

16 Dec

I did this because it was said that it could not be done.

Here we have a method to utilize the ADO.Command object, ADO.GetRows(), and true recordset paging, all wrapped in one neat little class.

Usage.asp

<!–#include virtual="/cls_db.asp"–>
<%
‘*****************************************************************************  
‘*****************************************************************************  
‘  
‘ This code can be used anywhere you like, all I ask is that you keep this  
‘ notice here, so people know who actually made it! =D  Thanks!  
‘  
‘ This code was developed by Kevin Pirnie, c/o o7th Web Design  
‘ support@07th.com :: http://www.07th.com  
‘  
‘*****************************************************************************  
‘***************************************************************************** 
‘——————————————————————————
‘——————————————————————————
‘Dimension our variables
Dim objData, intPage
Dim intCols, intRows, intTotPages, intTotRecs, strDisplayPaging, strConnString
‘——————————————————————————
‘——————————————————————————
‘Get the page we are on, if any
intPage = Request.QueryString("p")
‘If we don’t have a page, set it to 1
If Not(IsNumeric(intPage)) Then intPage = 1
‘——————————————————————————
‘——————————————————————————
‘Set our data object to the class
Set objData = New DBv1
        With objData ‘just because i don’t feel like typing out objData everytime I need it
                ‘Set our command type: 1 = Inline SQL Statement, 4 = Stored Procedure
                .intCommandType =               1
                ‘Pass our Connection String … if we have one, uncomment the line below, and comment the … section
                ‘.strConnString =               strConnString
                ‘Or we can simply build it here, just uncomment the lines in between …
                ‘ …
                .intDBType =                    3
                ‘Supported database types:
                ‘       1 = SQL 2000
                ‘       2 = SQL 2005
                ‘       3 = SQL 2005 Express
                ‘       4 = MS Access
                ‘       6 = MS Access 2007
                ‘       7 = MySQL
                ‘       8 = Borland Interbase ‘<- requires the proper driver installed on the server
                .strDBServer =                  "The Address to your Database Server"
                .strDBUser =                    "The Database Username"
                .strDBPassword =                "The Database Password"
                .strDBDatabase =                "The default database"
                ‘ …
                ‘Pass the class our SQL statement, use ? in the WHERE clause
                ‘You can also use this to fire off a stored procedure
                .strQry =                               "Select Field1, Field2 From Table Where Field3 = ? Or Field4 = ?"
                ‘Pass an array of values to look for in our WHERE clause
                .arrParamValues =               Array(Val1, Val2) ‘If none, use ""
                ‘Pass an array of Data Types for our values (These are ADO DataTypes, and the list of them can be found here -> http://www.w3schools.com/ADO/ado_datatypes.asp)
                ‘Only use the numeric value for this, we also only support the following:
                ’2, 3, 4, 5, 6, 7, 11, 14, 72, 128, 129, 200, 203, 204
                .arrParamDataTypes =    Array(200, 200) ‘If none, use ""
                ‘If we fire a stored procedure, and it has an output variable
                ‘.intRetDataType =              3 ‘Set the return value data type
                ‘.intRetSize =                  4 ‘Set the return value size
                ‘Do we want to page the results?
                .boolUsePaging =                True ‘or False
                ‘How many records per page do we want to display?
                .intRecPerPage =                10 ‘any numeric value
                ‘What page number are we on?
                .intPageNumber =                intPage
                ‘What page are we displaying these results?
                .strPagingPage =                "usage.asp?a=a" ‘We use a=a because the paging method expects a querystring to already be available
                ‘What should we use to display for the left arrows?
                .strPagingLeft =                "&laquo;"
                ‘What should we use to display for the right arrows?
                .strPagingRight =               "&raquo;"
                ‘Execute our query, and store the resulting 2d array in a variable for use later
                ‘If we fire a stored procedure and it has an output variable, this variable will equal that value
                tmpArray =                              .ExecuteQry()
                ‘Get the total number of pages returned from the recordset
                intTotPages =                   .intTotalPages
                ‘Get the total number of records returned from the recordset
                intTotRecs =                    .intTotalRecords
                ‘Get our paging links for the recordset
                strDisplayPaging =              .RecordPaging()
        End With ‘End our With block
‘Clean up the object
Set objData = Nothing
‘——————————————————————————
‘——————————————————————————
‘Now that we have done all this, let’s see what the results are.
‘First we check to see if we have an array
If IsArray(tmpArray) Then
        ‘We have an array, so let’s work with it
        ‘I’d like to put in the info about the paging, total records & total pages
        Response.Write("<strong>" & intTotRecs & "</strong> total records in <strong>" & intTotPages & "</strong> pages.")
        ‘Now I want to display my paging links
        Response.Write(strDisplayPaging)
        ‘Now Let’s display the results of the recordset…
        Response.Write("<table width=""100%"" cellpadding=""2"" cellspacing=""0"" border=""1"">" & VbCrLf)
        ‘Since I know how many columns, I will simply put the table header here
        Response.Write("        <thead>" & VbCrLf)
        Response.Write("                <tr>" & VbCrLf)
        Response.Write("                        <th>ISO CC</th>" & VbCrLf)
        Response.Write("                        <th>ISO CC3</th>" & VbCrLf)
        Response.Write("                        <th>Country Number Code</th>" & VbCrLf)
        Response.Write("                        <th>Country Name</th>" & VbCrLf)
        Response.Write("                </tr>" & VbCrLf)
        Response.Write("        </thead>" & VbCrLf)
        ‘Now let’s start the body of our table
        Response.Write("        <tbody>" & VbCrLf)
        ‘Let’s start a FOR loop to get and display all available rows
        For intRows = 0 To UBound(tmpArray, 2) ‘The 2 is representative of the rows in the recordset
                ‘If we didn’t know what columns we have, we would do another FOR loop inside this using:
                ‘UBound(tmpArray, 1)
                Response.Write("                <tr>" & VbCrLf)
                Response.Write("                        <td>" & tmpArray(1, intRows) & "</td>" & VbCrLf)
                Response.Write("                        <td>" & tmpArray(3, intRows) & "</td>" & VbCrLf)
                Response.Write("                        <td>" & tmpArray(4, intRows) & "</td>" & VbCrLf)
                Response.Write("                        <td>" & tmpArray(2, intRows) & "</td>" & VbCrLf)
                Response.Write("                </tr>" & VbCrLf)
        Next
        intRows = Null ‘Clear intRows out
        Response.Write("        </tbody>" & VbCrLf)
        Response.Write("</table>" & VbCrLf)
        ‘Id like to display the record paging again, in case the list is long
        Response.Write(strDisplayPaging)
        ‘I’d also like to display that paging info again
        Response.Write("<strong>" & intTotRecs & "</strong> total records in <strong>" & intTotPages & "</strong> pages.")
        Erase tmpArray ‘Releases the array from memory
Else
        ‘We have no array, so let’s display a message stating this!
        Response.Write("There are no records for that query.")
End If
%>

cls_db.asp

<%
‘*****************************************************************************  
‘*****************************************************************************  
‘  
‘ This code can be used anywhere you like, all I ask is that you keep this  
‘ notice here, so people know who actually made it! =D  Thanks!  
‘  
‘ This code was developed by Kevin Pirnie, c/o o7th Web Design  
‘ support@07th.com :: http://www.07th.com  
‘  
‘*****************************************************************************  
‘***************************************************************************** 
Class DBv1
    ‘Private Declarations
    Private i, p, pp, strDataLength, objCmd, objRS, objConn, objError
    Private intCurrPage, ini, fim
    ‘Public Declarations
    Public intDBType, strDBUser, strDBPassword, strDBServer, strDBDatabase
        Public strConnString, intCommandType
    Public strQry, arrParamValues, arrParamDataTypes, intRetDataType, intRetSize
    Public boolUsePaging, intTotalPages, intTotalRecords
    Public intRecPerPage, intPageNumber, strPagingPage, strPagingLeft, strPagingRight
    ‘Initialize
    Private Sub Class_Initialize()
        intDBType = 0
        intCommandType = 0
        strDBServer = Null
        strDBUser = Null
        strDBPassword = Null
        strDBDatabase = Null
        strQry = Null
        arrParamValues = Null
        arrParamDataTypes = Null
        boolUsePaging = False
        intTotalPages = 0
        intTotalRecords = 0
        intRecPerPage = 0
        intPageNumber = 0
        strPagingPage = Null
        strPagingLeft = " &lt; "
        strPagingRight = " &gt; "
    End Sub
    ‘Terminate
    Private Sub Class_Terminate()
        intDBType = 0
        intCommandType = 0
        strDBServer = Null
        strDBUser = Null
        strDBPassword = Null
        strDBDatabase = Null
        strQry = Null
        arrParamValues = Null
        arrParamDataTypes = Null
        boolUsePaging = False
        intTotalPages = 0
        intTotalRecords = 0
        intRecPerPage = 0
        intPageNumber = 0
        strPagingPage = Null
        strPagingLeft = " &lgt; "
        strPagingRight = " &rgt; "
    End Sub
    ‘Execute the Query
    Public Function ExecuteQry()
        Set objConn = CreateObject("ADODB.Connection")
            objConn.Open strConnectionString
                Set objCmd = CreateObject("ADODB.Command")
                    objCmd.CommandText = strQry
                    objCmd.CommandType = intCommandType
                    If IsArray(arrParamValues) And IsArray(arrParamDataTypes) Then
                        If UBound(arrParamValues) = UBound(arrParamDataTypes) Then
                            For i = 0 To UBound(arrParamValues)
                                Select Case arrParamDataTypes(i)
                                    Case 2 ‘Small Integer
                                        strDataLength = 2
                                    Case 3 ‘Integer
                                        strDataLength = 4
                                    Case 4 ‘Single
                                        strDataLength = 4
                                    Case 5 ‘Float
                                        strDataLength = 8
                                    Case 6 ‘Currency
                                        strDataLength = 8
                                    Case 7 ‘Date
                                        strDataLength = 8
                                    Case 11 ‘Bit
                                        strDataLength = 1
                                    Case 14 ‘Decimal
                                        strDataLength = 9
                                    Case 72 ‘GUID
                                        strDataLength = 16
                                    Case 128 ‘Binary
                                        strDataLength = 50
                                    Case 129 ‘Char
                                        If Not ReqValue(arrParamValues(i)) Then
                                            strDataLength = 1
                                        Else
                                            strDataLength = Len(arrParamValues(i))
                                        End If
                                    Case 200 ‘VarChar
                                        If Not ReqValue(arrParamValues(i)) Then
                                            strDataLength = 1
                                        Else
                                            strDataLength = Len(arrParamValues(i))
                                        End If
                                    Case 203 ‘NText
                                        If Not ReqValue(arrParamValues(i)) Then
                                            strDataLength = 1
                                        Else
                                            strDataLength = Len(arrParamValues(i))
                                        End If
                                    Case 204 ‘VarBinary
                                        strDataLength = 50
                                    Case Else ‘Hmm…guess
                                        If Not ReqValue(arrParamValues(i)) Then
                                            strDataLength = 1
                                        Else
                                            strDataLength = Len(arrParamValues(i))
                                        End If
                                End Select
                                If arrParamDataTypes(i) = 14 Then
                                    Set p = objCmd.CreateParameter(, CInt(arrParamDataTypes(i)), , CInt(strDataLength), InputCleaner(arrParamValues(i)))
                                    p.NumericScale = 2
                                    p.Precision = 10
                                    objCmd.Parameters.Append p
                                Else
                                    objCmd.Parameters.Append (objCmd.CreateParameter(, CInt(arrParamDataTypes(i)), , CInt(strDataLength), InputCleaner(arrParamValues(i))))
                                End If
                            Next
                            i = Null
                            Erase arrParamValues
                            Erase arrParamDataTypes
                        Else
                            ExecuteQry = "Your values and data type arrays need to be the same length."
                        End If
                    End If
                                        ‘Debug the parameters if necessary
                                        ‘For each Item In objCmd.Parameters
                                        ‘       Write("Name:" & Item.Name & "-Type:" & Item.Type & "-Value:" & Item.Value & "<br />")
                                        ‘Next   
                    Set objCmd.ActiveConnection = objConn
                        Select Case intCommandType
                            Case 1
                            If InStr(1, UCase(strQry), "SELECT") > 0 Then
                                Set objRS = CreateObject("Adodb.RecordSet")
                                    If boolUsePaging Then
                                        objRS.PageSize = intRecPerPage
                                        objRS.CacheSize = intRecPerPage
                                        objRS.CursorType = 3
                                    End If
                                    objRS.Open objCmd
                                    If Not (objRS.EOF) Then
                                        If boolUsePaging Then
                                            If Not (validNumber(intPageNumber)) Then
                                                objRS.AbsolutePage = 1
                                            Else
                                                objRS.AbsolutePage = intPageNumber
                                            End If
                                            ExecuteQry = objRS.GetRows(intRecPerPage)
                                            intTotalPages = objRS.PageCount
                                            intTotalRecords = objRS.RecordCount
                                        Else
                                            ExecuteQry = objRS.GetRows()
                                        End If
                                    Else
                                        ExecuteQry = "There are no records."
                                        Exit Function
                                    End If
                                Set objRS = Nothing
                                Exit Function
                            ElseIf InStr(1, UCase(strQry), "INSERT") > 0 Then
                                If InStr(1, UCase(strQry), "@@IDENTITY") > 0 Or InStr(1, UCase(strQry), "NEWID()") > 0 Then
                                    Set objRS = objCmd.Execute()
                                        If Not (objRS.EOF) Then
                                            ExecuteQry = objRS(0)
                                        End If
                                    Set objRS = Nothing
                                Else
                                    objCmd.Execute
                                    ExecuteQry = "Your command has been executed."
                                End If
                            ElseIf (InStr(1, UCase(strQry), "DELETE") > 0 Or InStr(1, UCase(strQry), "UPDATE") > 0 Or Left(UCase(strQry), 2) = "SP") Then
                                If Left(UCase(strQry), 2) = "SP" And intRetDataType > "" Then
                                    objCmd.Parameters.Append (objCmd.CreateParameter("@ret", intRetDataType, 2, , intRetSize))
                                    objCmd.Execute
                                    ExecuteQry = objCmd.Parameters("@ret")
                                Else
                                    ExecuteQry = objCmd.Execute
                                End If
                            End If
                        Case 4
                            If intRetDataType > "" Then
                                objCmd.Parameters.Append (objCmd.CreateParameter("@ret", intRetDataType, 2, , intRetSize))
                                objCmd.Execute
                                ExecuteQry = objCmd.Parameters("@ret")
                            Else
                                If boolUsePaging Then
                                    Set objRS = CreateObject("Adodb.RecordSet")
                                        objRS.PageSize = intRecPerPage
                                        objRS.CacheSize = intRecPerPage
                                        objRS.CursorType = 3
                                        objRS.CursorLocation = 3
                                        objRS.Open objCmd
                                        If Not (objRS.EOF) Then
                                            If boolUsePaging Then
                                                If Not (validNumber(intPageNumber)) Then
                                                    objRS.AbsolutePage = 1
                                                Else
                                                    objRS.AbsolutePage = intPageNumber
                                                End If
                                                ExecuteQry = objRS.GetRows(intRecPerPage)
                                                intTotalPages = objRS.PageCount
                                                intTotalRecords = objRS.RecordCount
                                            Else
                                                ExecuteQry = objRS.GetRows()
                                            End If
                                        Else
                                            objCmd.Execute
                                        End If
                                    Set objRS = Nothing
                                Else
                                    objCmd.Execute
                                End If
                            End If
                        End Select
                    Set objCmd.ActiveConnection = Nothing
                Set objCmd = Nothing
            objConn.Close
        Set objConn = Nothing
    End Function
    ‘Paging Links
    Public Function RecordPaging()
        tmpString = ""
        tmpString = tmpString & "<div class=""paging_links"">" & vbCrLf
        If Not (validNumber(intPageNumber)) Then
            CurrentPage = 1 ‘We’re On the first page
            NumPerPageOf = 1
        Else
            CurrentPage = CInt(intPageNumber)
            NumPerPageOf = ((CurrentPage * NumPerPage) – NumPerPage) + 1
        End If
        If CurrentPage > 1 Then
            If CurrentPage > 5 And intTotalPages > 10 Then
                tmpString = tmpString & "   <span><a href=""" & strPagingPage & "&amp;p=1"">1</a></span> "
                tmpString = tmpString & "<span class=""prevChunk""> &laquo; </span>"
            End If
            If intTotalPages > 10 Then
                If CurrentPage > 5 Then
                    If intTotalPages > (CurrentPage + 5) Then
                        ini = (CurrentPage – 4)
                        fim = (CurrentPage + 5)
                    Else
                        ini = (intTotalPages – 9)
                        fim = intTotalPages
                    End If
                Else
                    ini = 1
                    fim = 10
                End If
            Else
                ini = 1
                fim = intTotalPages
            End If
            For a = ini To fim
                If a = CInt(intPageNumber) Then
                    tmpString = tmpString & " <span class=""curPage"">" & a & "</span> "
                Else
                    tmpString = tmpString & "   <span><a href=""" & strPagingPage & "&amp;p=" & a & """>" & a & "</a></span> "
                End If
            Next: a = Null
        Else
            If intTotalPages = 1 Then
                tmpString = tmpString & ""
            Else
                tmpString = tmpString & "<span class=""curPage"">1</span> "
            End If
            If intTotalPages > 10 Then     ‘id=161&MWC=Layouts
                fim = 10
            Else
                fim = intTotalPages
            End If
            For a = 2 To fim
                If a = CInt(intPageNumber) Then
                    tmpString = tmpString & "<span class=""curPage"">" & a & "</span> "
                Else
                    tmpString = tmpString & "   <span><a href=""" & strPagingPage & "&amp;p=" & a & """>" & a & "</a></span> "
                End If
            Next: a = Null
        End If
        If CurrentPage < intTotalPages – 5 And intTotalPages > 10 Then
            tmpString = tmpString & "<span class=""lastChunk""> &raquo; </span>"
                        tmpString = tmpString & "   <span><a href=""" & strPagingPage & "&amp;p=" & intTotalPages & """>" & intTotalPages & "</a></span> "
        End If
        tmpString = tmpString & "</div>" & vbCrLf
        RecordPaging = tmpString
        tmpString = ""
    End Function
    ‘Get our connection string
    Private Function strConnectionString()
        If ReqValue(strConnString) Then
            strConnectionString = strConnString
        Else
            Select Case intDBType
                Case 1 ‘SQL 2000
                    strConnectionString = "Provider=SQLOLEDB.1;Password=" & strDBPassword & ";User ID=" & strDBUser & ";Initial Catalog=" & strDBDatabase & ";Data Source=" & strDBServer & ""
                Case 2 ‘SQL 2005
                    strConnectionString = "Provider=SQLNCLI;Server=" & strDBServer & ";Database=" & strDBDatabase & ";Uid=" & strDBUser & ";Pwd=" & strDBPassword & ";DataTypeCompatibility=80;"
                Case 3 ‘SQL 2005 Express
                    strConnectionString = "Provider=SQLOLEDB;Data Source=" & strDBServer & ";Persist Security Info=True;Password=" & strDBPassword & ";User ID=" & strDBUser & ";Initial Catalog=" & strDBDatabase & ";DataTypeCompatibility=80"
                Case 4 ‘MS Access
                    strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBDatabase & ";User Id=" & strDBUser & ";Password=" & strDBPassword & ";"
                Case 6 ‘MS Access 2007
                    strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBDatabase & ";Persist Security Info=False;"
                Case 8 ‘Borland Interbase – requires the SIBProvider to be installed on the server
                    strConnectionString = "provider=sibprovider;location=" & strDBServer & ":;data source=" & strDBDatabase & ";user id=" & strDBUser & ";Password=" & strDBPassword & ";"
                Case 7 ‘MySQL
                    strConnectionString = "Driver={MySQL ODBC 3.51 Driver};Server=" & strDBServer & ";Database=" & strDBDatabase & "; User=" & strDBUser & ";Password=" & strDBPassword & ";Option=3;"
            End Select
        End If
    End Function
    ‘Input cleaning … just in case
    Private Function InputCleaner(ByVal strStringToClean)
        If Not (ReqValue(strStringToClean)) Then
            If InStr(1, strStringToClean, "’") > 0 Then strStringToClean = Replace(strStringToClean, "’", "&#39;")
            If InStr(1, strStringToClean, Chr(34)) > 0 Then strStringToClean = Replace(strStringToClean, Chr(34), "&#34;")
            If InStr(1, strStringToClean, "@") > 0 Then strStringToClean = Replace(strStringToClean, "@", "&#64;")
            If InStr(1, strStringToClean, "|") > 0 Then strStringToClean = Replace(strStringToClean, "|", "&#124;")
            If InStr(1, strStringToClean, "*") > 0 Then strStringToClean = Replace(strStringToClean, "*", "&#42;")
            If InStr(1, strStringToClean, "–") > 0 Then strStringToClean = Replace(strStringToClean, "–", "&#45;&#45;")
            If InStr(1, strStringToClean, "(") > 0 Then strStringToClean = Replace(strStringToClean, "(", "&#40;")
            If InStr(1, strStringToClean, ")") > 0 Then strStringToClean = Replace(strStringToClean, ")", "&#41;")
        End If
        InputCleaner = strStringToClean
    End Function
    ‘Required Value?
    Private Function ReqValue(ByVal strValue)
        ReqValue = True ‘by default
        If strValue = "" Then ReqValue = False
        If IsNull(strValue) Then ReqValue = False
        If Len(strValue) <= 0 Then ReqValue = False
        If IsEmpty(strValue) Then ReqValue = False
    End Function
    ‘Valid Number?
    Private Function validNumber(ByVal strValue)
        If ReqValue(strValue) Then
            validNumber = False ‘Default
            Set objRegExp = New RegExp
                objRegExp.Pattern = "^(?:-?(?:[0-9]+\.?|[0-9]*(?:\.[0-9]+){1}))$"
                validNumber = objRegExp.Test(strValue)
            Set objRegExp = Nothing
        End If
    End Function
End Class
%>

Bookmark It

No Comments

Posted in Web Design

When it comes down to choosing the perfect domain for your website, there are a few important factors that will come into play with helping you keep traffic coming to your site. Here I’m going to discuss some of these factors in detail and explain each one, so sit tight and have yourself a good read!

While your sitting down planning out your site and you have the perfect niche you want to create your site in and the perfect ideas to make your site amusing, you have to think carefully when it comes to the name and the domain. Choose as short of a domain as possible, but easy to remember for your viewers.

Pick a keyword that describes your site and play around with different terms to make it work and be easily remembered. I’ll use one of my own projects as an example, TalkDeveloping. The site is a community and discussion board for developers of programming / coding and design talents, with that being said the site is obviously about developing and the users talk with one another of the subjects, so TalkDeveloping makes perfect sense and is the best route to go for the site. Another example would be Good Tutorials. The domain is good-tutorials.com and matches the site perfectly. Why? It has the main aspect of the site in the domain (tutorials) and is very easy to remember. It makes perfect sense for this domain as it’s a large tutorial listing site where you can list your tutorials free of charge and get great traffic in return.

You want your name to be as unique as possible. One of the obvious ways to find out is when you’re doing the domain search it’ll list if it’s taken of available. If it’s not you’re best bet is choosing a different name and branding it as your own to go with your project.

Another large factor in careful domain choosing is the all great search engines. If your domain is descriptive and short, the search engines will love it and visit your site numerous times which will increase the likeliness of your content going higher to the top of popular search terms for content you may have on your site, and at the same time could increase your sites PageRank (common with Google).

Take all of what I said into consideration and choosing your next project domain should be a great pay-off in the end, and remember to always think of the user. Will the user easily remember my domain? Will the search engines pick up on it?

Bookmark It

No Comments

Posted in Web Design

Prevent Image “Leaching”

10 Dec

I came across a situation where people where “leaching” images off my family photo site. “File Leaching” means that instead of the person downloading the file, and simply serving it up from their server, they were simply linking to my version of it.

While this may seem trivial, it happened on a site that has 10,000 family pictures, and was causing serious bandwidth issues for me.

So… being the good developer I think I am, I came up with a way to prevent that.

The basics of it are, simply store your files above the websites root directory, and stream them to the user upon request.

I found using ADO.Stream this is possible, and very efficient.

Hope you find it as useful as I do… Enjoy!

Usage:

<%  
‘*****************************************************************************  
‘*****************************************************************************  
‘  
‘ This code can be used anywhere you like, all I ask is that you keep this  
‘ notice here, so people know who actually made it! =D  Thanks!  
‘  
‘ This code was developed by Kevin Pirnie, c/o o7th Web Design  
‘ support@07th.com :: http://www.07th.com  
‘  
‘*****************************************************************************  
‘***************************************************************************** 
Dim strPath : strPath = "C:\WebStorage\thefile.zip" ‘physical path to the file
Dim strFileName : strFileName = "WhatIWantToCallIt.zip" ‘whatever I feel like naming the file
Call downloadFile(strPath, strFileName)
%>

Code:

<%
‘Force File Download
Sub DownloadFile(ByVal strFileName, ByVal strFileNamePass, ByVal intFileType)
     Response.Buffer = True
     Response.Clear
     Set objStream = CreateObject("ADODB.Stream")
     objStream.Open
     objStream.Type = 1
     Set objFilesystem = CreateObject("Scripting.FileSystemObject")
                 If Not objFilesystem.FileExists(strFilename) Then
                          Write("<h1>Error</h1>: " & strFilename & " does not exist<p>")
                          Response.End
                 End If
                 Set objFilestream = objFilesystem.GetFile( strFilename )
                         intFilelength = objFilestream.size
                         objStream.LoadFromFile( strFilename )
                         If Err Then
                                  Write("<h1>Error: </h1>" & Err.Description & "<p>")
                                  Response.End
                         End If
                         If Len( Trim(strDownloadFilename) ) > 0 Then
                                  strFileNamePass = Trim( strFileNamePass )
                         Else
                                  strFileNamePass = objFilestream.Name
                         End If
                         Select Case intFileType
                                Case 1 ‘CSV
                                        Response.ContentType = "text/csv"
                                Case 2 ‘XML
                                        Response.ContentType = "text/xml"
                         End Select
                         Response.AddHeader "Content-Disposition", "attachment; filename=" & strFileNamePass
                         Response.AddHeader "Content-Length", intFilelength
                         Response.Charset = "UTF-8"
                         For i = 0 To objStream.Size
                                  i = i + 128000
                                  Response.BinaryWrite(objStream.Read(128000))
                                  Response.Flush
                         Next : i = Null
                 Set objFilestream = Nothing
        Set objFilesystem = Nothing
End Sub
%>

Bookmark It

No Comments

Posted in Web Design

Recordset Paging With .GetRows()

10 Dec

I constantly use a .GetRows() array when displaying a recordset from a database. However, I found that I needed to be able to page the records ont he page, so that so many records won’t display all at once potentially crashing the page.

Here is a function that I use to display a Google like recordset paging system.

<%
‘Usage = Response.Write(PagingLinks(TheArrayNameFromRecordset, HowManyPerPage, URLOfThePageWeAreOn, 1stLinkDisplay, PrevLinkDisplay, NextLinkDisplay, LastLinkDisplay, StartingPage#Display, EndingPage#Display))
Function PagingLinks(ByVal strArrName, ByVal strNumPerPage, ByVal strPageName, ByVal strFirst, ByVal strPrev, ByVal strNext, ByVal strLast, ByVal iStart, ByVal iStop)
        PagingLinks = ""
        iStart = Request.QueryString("s")
        iOffset = Request.QueryString("o")
        If Not IsNumeric(iStart) Then
                iStart = 0
        Else
                iStart = CLng(iStart)
        End If
        If Not IsNumeric(iOffset) Then
                iOffset = strNumPerPage
        Else
                iOffset = CLng(iOffset)
        End If
        If UBound(strArrName, 2) < strNumPerPage Then
                iRows = (UBound(strArrName,2) + 1)
        Else
                iRows = UBound(strArrName,2)
        End If
        If iRows > (iOffset + iStart) Then
                iStop = iOffset + iStart – 1
        Else
                iStop = iRows
        End If
        If iStop > iRows – iOffset Then
                iStop = iStop – 1
        End If
        If Not(IsNumeric(iOffset)) Then iOffset = 1
        pages = RoundUp(iRows / iOffset)
        pgNum = RoundUp(iStart / iOffset) + 1
        PagingLinks = PagingLinks & "<a href="" strpagename="" &s="0&o=&quot;" ioffset="" &="">" & strFirst & "</a> "
        If iStart > 0 Then
                PagingLinks = PagingLinks & "<a href="" strpagename="" &="" &s=" & iStart – iOffset & " &o=" & iOffset & ">" & strPrev & "</a> "
        End If
        Dim MaxPagesToShow, MaxLBound, MaxUBound
        MaxPagesToShow = 10
        MaxLBound = pgNum – Int(MaxPagesToShow/2)
        If MaxLBound < 1 Then MaxLBound = 1
        MaxUBound = MaxLBound + MaxPagesToShow
        If MaxUBound > pages Then
                MaxUBound = pages
                MaxLBound = MaxUBound – MaxPagesToShow
        End If
        If MaxLBound < 1 Then MaxLBound = 1
        If (iRows – iOffset) < 0 Then
                l = 0
        Else 
                l = (iRows – iOffset)
        End If
        For z = MaxLBound To MaxUBound
                pgOffset = z * iOffset – iOffset
                If z = pgNum Then
                        PagingLinks = PagingLinks & ("<b>" & z & "</b> ")
                Else
                        PagingLinks = PagingLinks & ("<a href="" strpagename="" &="" &s=" & pgOffset & " &o=" & iOffset & ">" & z & "</a> ")
                End If
        Next
        If iStop + 1 < iRows Then
                PagingLinks = PagingLinks & "<a href="" strpagename="" &="" &s=" & iStart + iOffset & " &o=" & iOffset & ">" & strNext & "</a> "
        End If
        PagingLinks = PagingLinks & "<a href="" strpagename="" &="" &s=" & l & " &o=" & iOffset & ">" & strLast & "</a><br>"
End Function
%>

Bookmark It

No Comments

Posted in Web Design

Importance Of Browser Compatibility

10 Dec

One of the most important aspects you need to think of when you’re coding your website is to make it work across all browsers. If you don’t and a user visits your website from a browser you haven’t tested, and it’s broken, chances are they won’t be returning.

Like most things when developing your website, you need to take into consideration the different browsers and how you will be coding each one. If your website is supposed to look a certain way and you let a few alignment errors go in a certain browser, if could affect the users take on your site. You may not think it to be a big deal losing a few viewers, but you need to think of the larger picture. If there are a few viewers using the certain browser where your site appears broken, there’s a high guess that a lot of viewers using the same browser will see the exact same problems and possibly might not return again because of it.

Always thoroughly test your site in the most popular browsers then work your way down the list. Having your site work in each browser can be one of the big key aspects of making your website a success.

Happy coding!

Bookmark It

No Comments

Posted in Web Design

Regular Expression String Validation

10 Dec

When developing forms for a website it is always a good idea to implement server-side validation for your strings.

While client-side validations are handy and pretty, they can also be easily compromised.

Here are some server-side validation checks that utilize Regular Expressions. They return a boolean value if the string passes the test or not.

<%
‘Valid Zip Code
Function ValidZipCode(ByVal strValue)
        ValidZipCode = False
        If Not(ReqValue(strValue)) Then
                ValidZipCode = False
        Else
                Set objRegExp = New RegExp
                        objRegExp.Pattern = "^\d{5}$|^\d{5}-\d{4}$"
                        ValidZipCode = objRegExp.Test(strValue)
                Set objRegExp = Nothing
        End If
End Function
‘Valid Date
Function ValidDate(ByVal strValue)
        ValidDate = False
        If Not(ReqValue(strValue)) Then
                ValidDate = False
        Else
                Set objRegExp = New RegExp
                        objRegExp.Pattern = "^[0,1]?\d{1}\/(([0-2]?\d{1})|([3][0,1]{1}))\/(([1]{1}[9]{1}[9]{1}\d{1})|([2-9]{1}\d{3}))$"
                        ValidDate = objRegExp.Test(strValue)
                Set objRegExp = Nothing
                If Not(ValidDate) Then
                        ValidDate = IsDate(strValue)
                End If
        End If
End Function
‘Valid Email email@domain.com
Function ValidEmail(ByVal strValue)
        ValidEmail = False‘default
        If Not(ReqValue(strValue)) Then
                ValidEmail = False
        Else
                Set objRegExp = New RegExp
                        objRegExp.Pattern = "^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$"
                                ValidEmail = objRegExp.Test(strValue)
                Set objRegExp = Nothing
        End If
End Function
‘Valid Letters
Function ValidLetters(ByVal strValue)
        ValidLetters = False‘Default
        If Not(ReqValue(strValue)) Then
                ValidLetters = False
        Else
                Set objRegExp = New RegExp
                        objRegExp.Pattern = "^([a-zA-Z])"
                                ValidLetters = objRegExp.Test(strValue)
                Set objRegExp = Nothing
        End If
End Function
‘Valid Characters A-Z/a-z/0-9
Function ValidChars(ByVal strValue)
        ValidChars = False‘Default
        If Not(ReqValue(strValue)) Then
                ValidChars = False
        Else
                Set objRegExp = New RegExp
                        objRegExp.Pattern = "^([a-zA-Z0-9])"
                                ValidChars = objRegExp.Test(strValue)
                set objRegExp = Nothing
        End If
End Function
‘Valid Number
Function ValidNumber(ByVal strValue)
        ValidNumber = False‘Default
        If Not(ReqValue(strValue)) Then
                ValidNumber = False
        Else
                Set objRegExp = New RegExp
                        objRegExp.Pattern = "^([0-9])"
                                ValidNumber = objRegExp.Test(strValue)
                set objRegExp = Nothing
        End If
End Function
‘Valid Phone
Function ValidPhone(ByVal strValue)
        ValidPhone = False‘default
        If Not(ReqValue(strValue)) Then
                ValidPhone = False
        Else
                Set objRegExp = New RegExp
                        objRegExp.Pattern = "^([0-1]([\s-./\\])?)?(\(?[2-9]\d{2}\)?|[2-9]\d{3})([\s-./\\])?(\d{3}([\s-./\\])?\d{4}|[a-zA-Z0-9]{7})$"
                                ValidPhone = objRegExp.Test(strValue)
                Set objRegExp = Nothing
        End If
End Function
‘Valid GUID
Function ValidGUID(ByVal strValue)
        ValidGUID = False‘default
        If Not(ReqValue(strValue)) Then
                ValidGUID = False
        Else
                Set objRegExp = New RegExp
                        objRegExp.Pattern = "^(?:{[0-9A-F]{8}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{12}})$"
                                ValidGUID = objRegExp.Test(strValue)
                Set objRegExp = Nothing
        End If
End Function
‘Valid Credit Card
Function ValidCreditCard(ByVal strValue)
        ValidCreditCard = False‘default
        If Not(ReqValue(strValue)) Then
                ValidCreditCard = False
        Else
                Set objRegExp = New RegExp
                        objRegExp.Pattern = "^((?:4\d{3})|(?:5[1-5]\d{2})|(?:6011)|(?:3[68]\d{2})|(?:30[012345]\d))[ -]?(\d{4})[ -]?(\d{4})[ -]?(\d{4}|3[4,7]\d{13})$"
                        ValidCreditCard = objRegExp.Test(strValue)
                Set objRegExp = Nothing
        End If
End Function
%>

Bookmark It

No Comments

Posted in Web Design

SQL Format Name Function

10 Dec

I was working for an ASP (application service provider) awhile back and got sick of formatting our members names in code at the time of rendering the page.

I asked our DBA for advice if he thought it would be better performance to simply do this in the SQL calls to the database, of course his answer was yes, however what he suggested was to simply concatenate the name parts into an alias field.

Needless to say, it ended up bombing the page out around 1000 records.

After searching online for a few hours I came across a SQL Function that does exactly what was needed, and WHAMMMOOO!!! No more page bombing.

Here it is, with usage to share with all (because it took so long to find it)

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FormatName](@NameString varchar(100), @NameFormat varchar(20))
returns varchar(100) AS
begin
–FormatName parses a NameString into its component parts and returns it in a requested format.
–
–@NameString is the raw value to be parsed.
–@NameFormat is a string that defines the output format.  Each letter in the string represents
–a component of the name in the order that it is to be returned.
–    [H] = Full honorific
–    [h] = Abbreviated honorific
–    [F] = First name
–    [f] = First initial
–    [M] = Middle name
–    [m] = Middle initial
–    [L] = Last name
–    [l] = Last initial
–    [S] = Full suffix
–    [s] = Abbreviated suffix
–    [.] = Period
–    [,] = Comma
–    [ ] = Space
 
–Example: select dbo.Formatname(‘Reverend Gregory Robert Von Finzer Junior’, ‘L, h. F m. s.’)
–Result: ‘Von Finzer, Rev. Gregory R. Jr.’
 
–Test variables
– declare    @NameString varchar(50)
– declare    @NameFormat varchar(20)
– set    @NameFormat = ‘L, h. F m. s.’
– set    @NameString = ‘Reverend Gregory Robert Von Finzer Junior’
 
Declare    @Honorific varchar(20)
Declare    @FirstName varchar(20)
Declare    @MiddleName varchar(30)
Declare    @LastName varchar(30)
Declare    @Suffix varchar(20)
Declare    @TempString varchar(100)
Declare    @IgnorePeriod char(1)
 
–Prepare the string
–Make sure each period is followed by a space character.
SET    @NameString = rtrim(ltrim(REPLACE(@NameString, ‘.’, ‘. ‘)))
–Eliminate double-spaces.
while  charindex(‘  ‘, @NameString) &gt; 0 SET @NameString = REPLACE(@NameString, ‘  ‘, ‘ ‘)
–Eliminate periods
while  charindex(‘.’, @NameString) &gt; 0 SET @NameString = REPLACE(@NameString, ‘.’, ”)
 
–If the lastname is listed first, strip it off.
SET    @TempString = rtrim(LEFT(@NameString, charindex(‘ ‘, @NameString)))
IF    @TempString IN (‘VAN’, ‘VON’, ‘MC’, ‘Mac’, ‘DE’) SET @TempString = rtrim(LEFT(@NameString, charindex(‘ ‘, @NameString, len(@TempString)+2)))
IF    RIGHT(@TempString, 1) = ‘,’ SET @LastName = LEFT(@TempString, len(@TempString)-1)
IF    len(@LastName) &gt; 0 SET    @NameString = ltrim(RIGHT(@NameString, len(@NameString) - len(@TempString)))
 
–Get rid of any remaining commas
while  charindex(‘,’, @NameString) &gt; 0 SET @NameString = REPLACE(@NameString, ‘,’, ”)
 
–Get Honorific and strip it out of the string
SET    @TempString = rtrim(LEFT(@NameString, charindex(‘ ‘, @NameString + ‘ ‘)))
IF    @TempString IN (‘MR’, ‘MRS’, ‘MS’, ‘DR’, ‘Doctor’, ‘REV’, ‘Reverend’, ‘SIR’, ‘HON’, ‘Honorable’, ‘CPL’, ‘Corporal’, ‘SGT’, ‘Sergeant’, ‘GEN’, ‘General’, ‘CMD’, ‘Commander’,  ‘CPT’, ‘CAPT’, ‘Captain’, ‘MAJ’, ‘Major’, ‘PVT’, ‘Private’, ‘LT’, ‘Lieutenant’, ‘FATHER’, ‘SISTER’) SET @Honorific = @TempString
IF    len(@Honorific) &gt; 0 SET    @NameString = ltrim(RIGHT(@NameString, len(@NameString) - len(@TempString)))
 
–Get Suffix and strip it out of the string
SET    @TempString = ltrim(RIGHT(@NameString, charindex(‘ ‘, Reverse(@NameString) + ‘ ‘)))
IF    @TempString IN (‘Jr’, ‘Sr’,  ‘II’, ‘III’, ‘Esq’, ‘Junior’, ‘Senior’) SET @Suffix = @TempString
IF    len(@Suffix) &gt; 0 SET @NameString = rtrim(LEFT(@NameString, len(@NameString) - len(@TempString)))
 
IF @LastName IS NULL
begin
–Get LastName and strip it out of the string
SET    @LastName = ltrim(RIGHT(@NameString, charindex(‘ ‘, Reverse(@NameString) + ‘ ‘)))
SET    @NameString = rtrim(LEFT(@NameString, len(@NameString) - len(@LastName)))
–Check to see if the last name has two parts
SET    @TempString = ltrim(RIGHT(@NameString, charindex(‘ ‘, Reverse(@NameString) + ‘ ‘)))
IF    @TempString IN (‘VAN’, ‘VON’, ‘MC’, ‘Mac’, ‘DE’)
begin
SET @LastName = @TempString + ‘ ‘ + @LastName
SET @NameString = rtrim(LEFT(@NameString, len(@NameString) - len(@TempString)))
end
end
 
–Get FirstName and strip it out of the string
SET    @FirstName = rtrim(LEFT(@NameString, charindex(‘ ‘, @NameString + ‘ ‘)))
SET    @NameString = ltrim(RIGHT(@NameString, len(@NameString) - len(@FirstName)))
 
–Anything remaining is MiddleName
SET    @MiddleName = @NameString
 
–Create the output string
SET    @TempString = ”
while len(@NameFormat) &gt; 0
begin
IF @IgnorePeriod = ‘F’ OR LEFT(@NameFormat, 1) &lt;&gt; ‘.’
begin
SET @IgnorePeriod = ‘F’
SET @TempString = @TempString +
case ascii(LEFT(@NameFormat, 1))
when ’72′ then case @Honorific
when ‘Dr’ then ‘Doctor’
when ‘Rev’ then ‘Reverend’
when ‘Hon’ then ‘Honorable’
when ‘Maj’ then ‘Major’
when ‘Pvt’ then ‘Private’
when ‘Lt’ then ‘Lieutenant’
when ‘Capt’ then ‘Captain’
when ‘Cpt’ then ‘Captain’
when ‘Cmd’ then ‘Commander’
when ‘Gen’ then ‘General’
when ‘Sgt’ then ‘Sergeant’
when ‘Cpl’ then ‘Corporal’
else isnull(@Honorific, ”)
end
when ’70′ then isnull(@FirstName, ”)
when ’77′ then isnull(@MiddleName, ”)
when ’76′ then isnull(@LastName, ”)
when ’83′ then case @Suffix
when ‘Jr’ then ‘Junior’
when ‘Sr’ then ‘Senior’
when ‘Esq’ then ‘Esquire’
else isnull(@Suffix, ”)
end
when ’104′ then case @Honorific
when ‘Doctor’ then ‘Dr’
when ‘Reverend’ then ‘Rev’
when ‘Honorable’ then ‘Hon’
when ‘Major’ then ‘Maj’
when ‘Private’ then ‘Pvt’
when ‘Lieutenant’ then ‘Lt’
when ‘Captain’ then ‘Capt’
when ‘Cpt’ then ‘Capt’
when ‘Commander’ then ‘Cmd’
when ‘General’ then ‘Gen’
when ‘Sergeant’ then ‘Sgt’
when ‘Corporal’ then ‘Cpl’
else isnull(@Honorific, ”)
end
when ’102′ then isnull(LEFT(@FirstName, 1), ”)
when ’109′ then isnull(LEFT(@MiddleName, 1), ”)
when ’108′ then isnull(LEFT(@LastName, 1), ”)
when ’115′ then case @Suffix
when ‘Junior’ then ‘Jr’
when ‘Senior’ then ‘Sr’
when ‘Esquire’ then ‘Esq’
else isnull(@Suffix, ”)
end
when ’46′ then case RIGHT(@TempString, 1)
when ‘ ‘ then ”
else ‘.’
end
when ’44′ then case RIGHT(@TempString, 1)
when ‘ ‘ then ”
else ‘,’
end
when ’32′ then case RIGHT(@TempString, 1)
when ‘ ‘ then ”
else ‘ ‘
end
else ”
end
IF ((ascii(LEFT(@NameFormat, 1)) = 72 AND @Honorific IN (‘FATHER’, ‘SISTER’))
OR (ascii(LEFT(@NameFormat, 1)) = 115 AND @Suffix IN (‘II’, ‘III’)))
SET @IgnorePeriod = ‘T’
end
SET @NameFormat = RIGHT(@NameFormat, len(@NameFormat) - 1)
end
 
RETURN @TempString
end

Bookmark It

No Comments

Posted in Web Design

o7th Web Design

Archive for the ‘Web Design’ Category

Dynamic Parameterized Queries in VB.Net

Optimizing Scripts

Couple of JQuery Ajax Loaders

Dynamic Parameterized Queries w/ Paging

Domain Branding

Prevent Image “Leaching”

Recordset Paging With .GetRows()

Importance Of Browser Compatibility

Regular Expression String Validation

SQL Format Name Function

Pages

Archives

Categories