ASP.NET - Display pdf From BLOB

Imports System.IO

Private Sub DisplayPdfFromBLOB(ByVal adt As DataTable)

        Dim blob() As Byte

        Try

            blob = CType(adt(0)("form_document"), Byte())

            Dim ms As New MemoryStream(blob)

            Response.ContentType = "application/pdf"

           ms.WriteTo(Response.OutputStream)

            Response.Flush()

        Catch ex As Exception

            Throw ex

        End Try

    End Sub

SQL - Get Left Part of String From 2nd Space

If you have some text value and you need to get the left part of the string from the 2nd space, here is a technique I figured out that works.

declare @some_text as nvarchar(20) = 'through 123 some text';

select ltrim(rtrim(left(@some_text, charindex(' ', @some_text, charindex(' ', @some_text)+1))))

You could apply the same logic to go from the nth space in the occurrence, it would become more complex.

VB.NET - Count Number of Characters in a String

Public Function CharCount(ByVal OrigString As String, ByVal Chars As String, Optional ByVal CaseSensitive As Boolean = False) As Integer

        '**********************************************

        'PURPOSE: Returns Number of occurrences of a character or

        'or a character sequencence within a string

        'PARAMETERS:

        'OrigString: String to Search in

        'Chars: Character(s) to search for

        'CaseSensitive (Optional): Do a case sensitive search

        'Defaults to false

        'RETURNS:

        'Number of Occurrences of Chars in OrigString

        'EXAMPLES:

        'Debug.Print CharCount("FreeVBCode.com", "E") -- returns 3

        'Debug.Print CharCount("FreeVBCode.com", "E", True) -- returns 0

        'Debug.Print CharCount("FreeVBCode.com", "co") -- returns 2

        ''**********************************************

        Dim intLen As Integer

        Dim intCharLen As Integer

        Dim intAns As Integer

        Dim strInput As String

        Dim strChar As String

        Dim intCtr As Integer

        Dim intEndOfLoop As Integer

        Dim bytCompareType As Byte

        strInput = OrigString

        If strInput = "" Then Exit Function

        intLen = Len(strInput)

        intCharLen = Len(Chars)

        intEndOfLoop = (intLen - intCharLen) + 1

        bytCompareType = CByte(IIf(CaseSensitive, vbBinaryCompare, vbTextCompare))

        For lCtr = 1 To intEndOfLoop

            strChar = Mid(strInput, lCtr, intCharLen)

            If StrComp(strChar, Chars, CType(bytCompareType, CompareMethod)) = 0 Then _

                intAns = intAns + 1

        Next

        CharCount = intAns

    End Function

** Originally Retrieved From: http://www.freevbcode.com/ShowCode.asp?ID=1025 (I modified to be an int)

SQL - Remove Duplicates from a Delimited String

The following is a function to remove duplicates from a delimited string.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mga_distinct_list]
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
VARCHAR(MAX)
AS
BEGIN
DECLARE @ParsedList TABLE
(
Item VARCHAR(MAX)
)
DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)
SET @list = LTRIM(RTRIM(@list)) + @Delim
SET @pos = CHARINDEX(@delim, @list, 1)
WHILE @pos > 0
BEGIN
SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))
IF @list1 <> ''
INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX)))
SET @list = SUBSTRING(@list, @pos+1, LEN(@list))
SET @pos = CHARINDEX(@delim, @list, 1)
END
SELECT @rlist = COALESCE(@rlist+',','') + item
FROM (SELECT DISTINCT Item FROM @ParsedList) t
RETURN @rlist
END

SQL - Format List with Commas & And

USE [######]

GO

/****** Object:  UserDefinedFunction [dbo].[format_list]    Script Date: 05/17/2012 09:39:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ===========================================================================================

-- Author:        jefz

-- Create date: 5/17/2012

-- Description:   Use to turn a list that looks like this:

--                     

--                            Bob| Joe| Sally| Billy

--                     

--                      Into a list that looks like this:

--

--                            Bob, Joe, Sally and Billy

--

--                      A '|' is used to delimit the original list incase any of the names (or any

--                      string value used) includes a comma.  When the original list is created, it

--                      should be created with a '|'

-- ===========================================================================================

CREATE FUNCTION [dbo].[format_list]

(

      @list as nvarchar(max)

)

RETURNS nvarchar(max)

AS

BEGIN

      IF len(@list)-len(replace(@list, '|', '')) > 0

                 

                        BEGIN

                              select @list = reverse(@list)

                              select @list = left(@list, CHARINDEX ('|' ,@list)-1) + 'dna ' + replace(right(@list, len(@list)-CHARINDEX ('|' ,@list)), '|', ' ,')

                              select @list = reverse(@list)

                        END

                 

                  ELSE

                 

                        BEGIN

                              select @list = @list

                        END

     

                 

          RETURN @list

END

SQL - Return Multiple Records on One Line

If you have multiple records you want to appear on one line delimited by some character such as a comma, see below.

This would make:

Jones
Smith
Williams

Look like this:

Jones, Smith, Williams

DECLARE @all_names varchar(2000);

SELECT @all_names = COALESCE(@all_names + ', ', '') +

a.last_name 

FROM customers_table a

--WHERE .... (this is optional)

SELECT @all_names as 'last_names'

SQL - Zero Pad a Number

declare @somenumber int = 46;

select RIGHT(REPLICATE('0',3) + CAST(@somenumber AS NVARCHAR(3)),3)

This will take the number '46' and format it as a nvarchar to look like '046'. The number '3' in the expression specifies the number of digits you want the number to have.