jzBLOCK - Developer Notes: 2012

Monday, October 22, 2012

SQL - Return Left of String from Specified Char

DECLARE @somestring as nvarchar(50) = 'test,value';

select left(@somestring,CHARINDEX(',',@somestring)-1)

SQL - Return Right of String from Specified Char

DECLARE @somestring as nvarchar(50) = 'test,value';

select right(@somestring,LEN(@somestring)-CHARINDEX(',',@somestring))

SQL - Check if Char Exists

DECLARE @somestring as nvarchar(50) = 'test,value';

select

[test] = CASE

WHEN CHARINDEX(',', @somestring) > 0

THEN 'exists'

ELSE 'does not exist'

END

Friday, October 12, 2012

VB.NET - Loop On XML Data

Sample XML Data:

<ChapterEntry><Number>3</Number><Time>220381</Time><Title>Harrison</Title></ChapterEntry>

<ChapterEntry><Number>4</Number><Time>348865</Time><Title>Billy</Title></ChapterEntry>

<ChapterEntry><Number>6</Number><Time>458876</Time><Title>Chester</Title></ChapterEntry>

<ChapterEntry><Number>8</Number><Time>562816</Time><Title>Donny</Title></ChapterEntry>

<ChapterEntry><Number>9</Number><Time>600409</Time><Title>Steve</Title></ChapterEntry>

<ChapterEntry><Number>10</Number><Time>649318</Time><Title>Jimbo</Title></ChapterEntry>

<ChapterEntry><Number>12</Number><Time>775005</Time><Title>Sammy</Title></ChapterEntry>

</Chapters>

</ChapterContent>

Save the XML file to a location and parse through it with VB code:

Using reader As XmlReader = XmlReader.Create("C:\TEMP\test.xml")

While reader.Read()

If reader.IsStartElement() Then

If reader.Name = "Time" Then

strTime = reader.ReadElementString("Time")

strBillNumber = reader.ReadElementString("Title")

'this will be code to insert into table

sbTemp.Append("Time = " & strTime & " Name = " & strBillNumber & vbCrLf)

End If

End While

End Using

Wednesday, August 29, 2012

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

Friday, August 3, 2012

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.

Wednesday, June 20, 2012

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

CharCount = intAns

End Function

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

Thursday, May 17, 2012

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 [######]

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

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

-- 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)

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

Monday, May 7, 2012

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'

Friday, May 4, 2012

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.

SQL - Strip / Remove Lead Zeroes of a String

Cast the string to an int, then back into a nvarchar:

select CAST(CAST(substring('000422', 3, 4) as int) as nvarchar)

SQL - Capitalize the First Character of a String

UPPER(LEFT(some_string,1)) + RIGHT( some_string ,(LEN( some_string )-1))

Thursday, April 12, 2012

SQL - Count Character Occurrence in a String

Want to count the number of times a specific character appears within a string? The line below will count the number of times the letter 'a' appears within the string value returned from the column 'description'. I display the column for description and a column for the count side by side.

select description, len(description)-len(replace(description,'a','')) as 'count' from table_items

VB.NET - Margin Outside of Printable Area

Problem: You are trying to print something in Microsoft Word through VB.NET but get stopped with a message telling you the margins are outside of the printable area and you have to click to continue printing. You do not want this message to come up but instead go straight to printing.

Solution: You must turn off the the error messaging and backgroud printing, then print the document, then you can turn everything back on the way it was. Here is the code snippet to show you how.

Dim objWordApp As Word.Application

Dim blnWordAlerts As Boolean

'............

'code

'............

' Store current Background Printing setting.

blnWordAlerts = objWordApp.Options.PrintBackground

' Turn off error messaging and Background Printing.

objWordApp.Application.DisplayAlerts = Word.WdAlertLevel.wdAlertsNone

'objWordApp.wdAlertsNone()

objWordApp.Options.PrintBackground = False

' Print the active document.

objWordApp.ActiveDocument.PrintOut(False) 'false allows the print to finish before it moves to any other code

' Turn on error messaging and restore Background Printing

' to original setting.

objWordApp.Application.DisplayAlerts = Word.WdAlertLevel.wdAlertsAll

objWordApp.Options.PrintBackground = blnWordAlerts

Tuesday, March 20, 2012

VB.NET - Office (Word, Excel...) - RPC Server Error

In my application I would seem to randomly get errors that would say something like:

The server threw an exception. (Exception from HRESULT ... (RPC_E_SERVERFAULT))
The RPC server is unavailable. (Exception from HRESULT ...)

I could not discover the root of the problem nor a solution. I found out this issue is typically caused by Microsoft Word (or whichever Office application) not closing correctly. If you interrupt it while running a process and then try to open a specific file again an error may occur. Furthermore this happens when you open multiple instances of Word opening, closing, and running at the same time. The instances of Word will attempt to "piggyback" off one another. When one instance closes but another attempts to run a process, the error is generated.

To solve this issue at the root of the problem you will need to make sure you are instantiating instances of word that are isolated from each other.

If you have done this, another possible source of the error is that you need to enable a template or document that was disabled.

To correct this you need to enable the file that is causing the problem because it has become disabled. Open Microsoft Word (or other Office application), click the office button, click "Word Options". Then under "Add-Ins", click the "Manage" dropdown list and select "Disabled Items". Click "go". Select any items that are disabled and then click "Enable".

Try running your application again and it should work now.

If not, the RPC error is being caused by something else now.

C# - Maintain Position on DataGridView After Column Sort

The following code handles the situation when the user is in a DataGridView and they sort a column while scrolled over. By default, if the user sorts the column it will snap the user back over to the left. This code will allow the user to maintain the position they are in. First whenever the scroll event is activated, a modular level variable stores the stop position of the horizontal scroll bar. Whenever a user sorts a column in the grid the horizontal scroll bar position is set to the modular level variable that was set in the prior event.

int  _cintHorizontalStop;
private void Grid_Scrolled(object sender, ScrollEventArgs e)
{
if (e.ScrollOrientation == ScrollOrientation.HorizontalScroll)
{
   _cintHorizontalStop = e.NewValue;
}
}
private void Grid_Sorted(object sender, EventArgs e)
{
datagridview1.HorizontalScrollingOffset =  _cintHorizontalStop ;
}

Wednesday, March 14, 2012

VB.NET - Maintain Position on DataGridView After Column Sort

Private _cintHorizontalStop As Integer '<-- modular level variable

Private Sub datagridview1_Scroll(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ScrollEventArgs) Handles datagridview1 .Scroll

Try

If e.ScrollOrientation = ScrollOrientation.HorizontalScroll Then

_cintHorizontalStop = e.NewValue

End If

Catch ex As Exception

ErrorHandlerMuni(ex)

End Try

End Sub

Private Sub datagridview1 _Sorted(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles datagridview1 .Sorted

Try

gridDGV.HorizontalScrollingOffset = _cintHorizontalStop

Catch ex As Exception

ErrorHandlerMuni(ex)

End Try

End Sub

Tuesday, March 13, 2012

VB.NET - String Incrementer (For Loops With Excel)

If you need a function that will increment a string, in particular to put in a loop that goes through columns in excel, here is what you are looking for.

I’m using it to increment a string (my purpose is to move through the columns in excel a -> b ; b -> c ; and so on. But then what is also does, it will go from z -> aa ; so it can be used to loop through columns that go past the column z.

''' <summary>

''' jefz - function is to be used to increment a string character. When the string reaches Z it will start

''' counting again from AA. Primary purpose is to be used as a counter for referencing excel cells going across

''' the worksheet.

'''

''' Ex: A -> B

''' M -> N

''' Z -> AA

''' AM -> AN

''' AZ -> BA

''' ZZ -> AAA

''' </summary>

''' <param name="strString">letter to start incrementing (must be in lowercase)</param>

''' <returns></returns>

''' <remarks></remarks>

Private Function IncrementString(ByRef strString As String) As String

Dim intLenString As Integer

Dim strChar As String

Dim intI As Integer

intLenString = Len(strString)

' Start at far right

For intI = intLenString To 0 Step -1

' If we reach the far left then add an A and exit

If intI = 0 Then

strString = "a" & strString

Exit For

End If

' Consider next character

strChar = Mid(strString, intI, 1)

If strChar = "z" Then

' If we find Z then increment this to A

' and increment the character after this (in next loop iteration)

strString = Microsoft.VisualBasic.Left$(strString, intI - 1) & "a" & Mid(strString, intI + 1, intLenString)

Else

' Increment this non-Z and exit

strString = Microsoft.VisualBasic.Left$(strString, intI - 1) & Chr(Asc(strChar) + 1) & Mid(strString, intI + 1, intLenString)

Exit For

End If

Next intI

IncrementString = strString

End Function

Monday, March 12, 2012

VB.NET - Changing Application Icon

If you want to change the icon for your application, the first thing everybody will want to do is go into the properties of the project and change the icon associated with the program under the 'Application' tab. This is good except to also get the new icon to appear on each of the forms and in the task bar of windows, you will need to go to the properties window for each of the forms you want to associate the icon with. Within the properties window the form, go to the 'Icon' property and also set the icon there. You should now get the icon to show on the upper left of the form as well as in the task bar.

Monday, March 5, 2012

VB.NET - Check to see if "X" Button was Clicked

If you want to be able to check if the red "X" button was clicked on the form, you must add this code to the code of the main form:

Private Const WM_SYSCOMMAND As Int32 = &H112

Private Const SC_CLOSE As Int32 = &HF060

'*Do not try to step into this procedure, once you're in, you're usually stuck.

Protected Overrides Sub WndProc(ByRef m As Message)

If m.Msg = WM_SYSCOMMAND Then

Select Case m.WParam.ToInt32()

Case SC_CLOSE

'MsgBox("Form gets closed.")

cGenBlnClickedX = True

End Select

End If

MyBase.WndProc(m)

End Sub

I use this code with my DataGridView validation. I have row validation with my DataGridView, however if the user clicks the "X" button I do not want my grid to go through validation so I have the following code inside of a function to check to see if validation is needed when entering the RowValidating event handler:

If cGenBlnClickedX = True Then

Exit Function

End If

*Note about this code - when stepping through code this will appear to show up at random times. You cannot step line by line your way out of it. There are a few tricks to getting around this. 1) the easiest thing to do is instead of stepping line by line, "step out" of the function and you will continue on as usual. 2) when debugging, comment this code out and then put it back in when done debugging. 3) you can go to the line of code you were on before you stepped into this code. Go to the line of code after it and "run to cursor"

jzBLOCK - Developer Notes

Blog Archive

Monday, October 22, 2012

SQL - Return Left of String from Specified Char

SQL - Return Right of String from Specified Char

SQL - Check if Char Exists

Friday, October 12, 2012

VB.NET - Loop On XML Data

Wednesday, August 29, 2012

ASP.NET - Display pdf From BLOB

Friday, August 3, 2012

SQL - Get Left Part of String From 2nd Space

Wednesday, June 20, 2012

VB.NET - Count Number of Characters in a String

Thursday, May 17, 2012

SQL - Remove Duplicates from a Delimited String

SQL - Format List with Commas & And

Monday, May 7, 2012

SQL - Return Multiple Records on One Line

Friday, May 4, 2012

SQL - Zero Pad a Number

SQL - Strip / Remove Lead Zeroes of a String

SQL - Capitalize the First Character of a String

Thursday, April 12, 2012

SQL - Count Character Occurrence in a String

VB.NET - Margin Outside of Printable Area

Tuesday, March 20, 2012

VB.NET - Office (Word, Excel...) - RPC Server Error

C# - Maintain Position on DataGridView After Column Sort

Wednesday, March 14, 2012

VB.NET - Maintain Position on DataGridView After Column Sort

Tuesday, March 13, 2012

VB.NET - String Incrementer (For Loops With Excel)

Monday, March 12, 2012

VB.NET - Changing Application Icon

Monday, March 5, 2012

VB.NET - Check to see if "X" Button was Clicked

About Me

Followers