Codespill

MS Word: Remove All Hyperlinks

2011-09-17T12:50:00.000-05:00

Sub RemoveAllHyperlinks()

Dim i As Integer
For i = ActiveDocument.Hyperlinks.Count To 1 Step -1
ActiveDocument.Hyperlinks(i).Range.Text = _
ActiveDocument.Hyperlinks(i).TextToDisplay
Next i

End Sub

Delete Objects by Prefix

2011-08-28T11:56:00.000-05:00

DECLARE @SQL VARCHAR(1000)
DECLARE @tempName VARCHAR(1000)
DECLARE @count VARCHAR(1000)
select @count = COUNT(*) from dbo.sysobjects where type = 't' and name like '[Table_Prefix]%'
while @count > 0
Begin
Select @tempName = name from dbo.sysobjects where type = 't' and name like '[Table_Prefix]%'
exec('drop table ' + @tempName)
set @count = @count - 1
END

DECLARE @SQL VARCHAR(1000)
DECLARE @tempName VARCHAR(1000)
DECLARE @count VARCHAR(1000)
select @count = COUNT(*) from dbo.sysobjects where type = 'p' and name like '[Stored_Procedure_Prefix]%'
while @count > 0
Begin
Select @tempName = name from dbo.sysobjects where type = 'p' and name like '[Stored_Procedure_Prefix]%'
exec('drop table ' + @tempName)
set @count = @count - 1
END

SQL Server Trigger Sample

2011-08-11T10:20:00.000-05:00

CREATE TRIGGER TriggerName ON [TableName]
AFTER UPDATE
AS
IF UPDATE ([Column1]) OR UPDATE ([Column2])
UPDATE [TableName]
SET [Column3] = CAST(YEAR([ThingMonth]) AS VARCHAR(4))+ '.' + DBO.LPAD(CAST(MONTH([ThingMonth]) AS VARCHAR(4)),2,0)
WHERE [Column3] = '.'

SQL Server: LPAD Function

2011-08-11T09:53:00.007-05:00

CREATE FUNCTION [dbo].[LPAD](@string VARCHAR(255), @desired_length INTEGER, @pad_character CHAR(1))
RETURNS VARCHAR(255) AS
BEGIN
-- Prefix the required number of spaces to bulk up the string and then replace the spaces with the desired character
RETURN ltrim(rtrim(
+++CASE
++++++WHEN LEN(@string) < @desired_length
++++++THEN REPLACE(SPACE(@desired_length - LEN(@string)), ' ', @pad_character) + @string
+++ELSE @string
+++END
+++))
END

Shrink SQL Server Logfile

2011-03-03T18:27:00.003-06:00

EXEC sp_dboption 'MCB', 'trunc. log on chkpt.', 'TRUE';
CHECKPOINT;
DBCC SHRINKFILE (MCB_Log, 200);

SharePoint: Hide a Field on SharePoint NewForm.aspx

2010-07-22T13:10:00.001-05:00

Place after <content contentplaceholderid="PlaceHolderMain" runat="server">

SharePoint: Populate People Picker With Logged In User

2010-07-22T13:05:00.000-05:00

"+" used to preserve code indention, replace with spaces or tabs...

MS Access: Simple Proper Case Function

2010-04-19T12:00:00.000-05:00

"+" used to preserve code indention, replace with spaces or tabs...

Function ProperCase(strAnyText As String) As String
' RETURN TEXT IN PROPER CASE.

Dim intCounter As Integer
Dim strOneChar As String

If IsNull(strAnyText) Then
++++Exit Function
End If

' FIRST CONVERT TO INITIAL CAP, FOLLOWED BY ALL LOWERCASE.
strAnyText = UCase$(Left$(strAnyText, 1)) & LCase$(Mid$(strAnyText, 2, 255))

' LOOK AT EACH CHARACTER, STARTING AT THE SECOND CHARACTER.
For intCounter = 2 To Len(strAnyText)
++++strOneChar = Mid$(strAnyText, intCounter, 1)
++++' IF CURRENT CHARACTER (ONECHAR) IS A SPACE OR HYPHEN...
++++If strOneChar = " " Or strOneChar = "-" Or strOneChar = "/" Then
++++++++'...CONVERT THE CHARACTER AFTER SPACE/HYPHEN TO UPPERCASE.
++++++++strAnyText = Left$(strAnyText, intCounter) & UCase$(Mid$(strAnyText, intCounter + 1, 1)) & Mid$(strAnyText, intCounter + 2, 255)
++++End If
Next

ProperCase = strAnyText

End Function

Stored Procedure: Simple Select

2009-11-15T12:00:00.004-06:00

/* SELECTS * FROM COMPANIES VIA COMPANY NAME */

CREATE PROCEDURE [SEARCH_COMPANYNAME]
@strCompanyName nvarchar(50)

AS
SELECT * FROM COMPANIES WHERE COMPANYNAME =@strCompanyName
GO

Update Record from Form With Same Field Names

2009-11-14T12:00:00.000-06:00

Updates a database record with the same field names and returns the record to a specified page; "+" used to preserve code indention, replace with spaces or tabs...

SQL = "UPDATE [TABLENAME] "
For Each strElement IN request.form
' SKIP THESE FORM FIELDS; SKIP SUMBIT BUTTONS.
+++If strElement <> "Submit.y" AND & _
++++++strElement <> "Submit.x" AND & _
++++++strElement <> "submit" AND & _
++++++strElement <> "Field1" AND & _
++++++strElement <> "Field2" AND & _
++++++strElement <> "Field3" AND & _
++++++strElement <> "Field4" AND & _
++++++strElement <> "Field5"
+++Then
++++++' DON'T SKIP THESE; ASSUMES SAME FIELD NAMES.
++++++SQL = SQL & strElement & " = '" & request.form(strElement) & "', "
+++End If
Next

SQL = Left(SQL, Len(SQL)-2) ' TRIM ENDING COMMA AND SPACE.
' ASSUMES THAT THE TABLE HAS AN 'UPDATED' FIELD.
SQL = SQL & ", Updated = '" & Now() &"' WHERE id = " & intID
Set rst = oConn.Execute(SQL)

' ASSUMES THAT THE TABLE HAS AN 'ID' FIELD.
strSqlGetId = "SELECT MAX(id) AS id FROM [TABLENAME]"
Set rstGetId = oConn.Execute(SQL)
Do While Not rstGetId.EOF
+++intId = rstGetID("id")
Loop
rstGetId.Close
Set rstGetId = Nothing

response.redirect("[PAGENAME]?id=" & intId)

Remove Annoying Adds from the Bottom of Chat Programs

2009-11-13T12:00:00.002-06:00

Place all commands in a batch file and run with Yahoo Messenger closed.

@ECHO OFF
TITLE Remove ads from Yahoo Messenger 8-9

ATTRIB -R "%PROGRAMFILES%\Yahoo!\Messenger\Cache\urls.xml"
ECHO "" >"%PROGRAMFILES%\Yahoo!\Messenger\Cache\urls.xml"
ATTRIB +R "%PROGRAMFILES%\Yahoo!\Messenger\Cache\urls.xml"
cacls "%PROGRAMFILES%\Yahoo!\Messenger\Cache\urls.xml" /E /P %username%:N

SET CONTENTPATH=""
IF ERRORLEVEL 1 SET CONTENTPATH="*"

> %TEMP%.\noYMads.reg ECHO REGEDIT4
>>%TEMP%.\noYMads.reg ECHO.
>>%TEMP%.\noYMads.reg ECHO [HKEY_CURRENT_USER\Software\Yahoo\Pager\YUrl]
>>%TEMP%.\noYMads.reg ECHO "Messenger Ad"="*"
>>%TEMP%.\noYMads.reg ECHO "Webcam Upload Ad"="*"
>>%TEMP%.\noYMads.reg ECHO "Webcam Viewer Ad"="*"
>>%TEMP%.\noYMads.reg ECHO "Webcam Viewer Ad Big"="*"
>>%TEMP%.\noYMads.reg ECHO "Webcam Viewer Ad Medium"="*"
>>%TEMP%.\noYMads.reg ECHO "Change Room Banner"="*"
>>%TEMP%.\noYMads.reg ECHO "Conf Adurl"="*"
>>%TEMP%.\noYMads.reg ECHO "Chat Adurl"="*"
>>%TEMP%.\noYMads.reg ECHO "Y Content"=%CONTENTPATH%
>>%TEMP%.\noYMads.reg ECHO [HKEY_CURRENT_USER\Software\Yahoo\Pager\Locale]
>>%TEMP%.\noYMads.reg ECHO "Enable Messenger Ad"="0"
REGEDIT /S %TEMP%.\noYMads.reg
DEL %TEMP%.\noYMads.reg

For Live Messenger - edit the hosts file located under C:\Windows\System32\drivers\etc\hosts and add these lines to the bottom:

127.0.0.1 rad.msn.com
127.0.0.1 rad.live.com

Database Schema Show Me

2009-11-12T12:00:00.002-06:00

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (Table_Name <> 'dtproperties') AND (Table_Name <> 'sysconstraints') AND (Table_Name <> 'syssegments')
ORDER BY Table_Name

The short form.

SELECT TABLE_NAME, DATA_TYPE, COLUMN_NAME, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (Table_Name <> 'dtproperties') AND (Table_Name <> 'sysconstraints') AND (Table_Name <> 'syssegments')
ORDER BY Table_Name

Show me a group of tables with a prefix or on one table only.

SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION
FROM INFORMATION_SCHEMA.columns
WHERE (TABLE_NAME LIKE '[TABLENAME]%')
ORDER BY TABLE_NAME

Hide the Submit Button on Single Click

2009-11-11T12:00:00.000-06:00

Stop Config Inheritance in a Child App

2009-11-10T12:00:00.000-06:00

Stops inheritance from the top level web.config, place in child app web.config; "+" used to preserve code indention, replace with spaces or tabs...

DOS: Shut Down/Start Up Oracle Service 9i

2009-11-09T12:00:00.001-06:00

net stop "OracleOraHome92Agent"
net stop "OracleService[DBNAME]" /y
net stop "OracleOraHome92TNSListener"
net stop "OracleOraHome92HTTPServer"
net stop "OracleMTSRecoveryService"

net start "OracleOraHome92Agent"
net start "OracleService[DBNAME]" /y
net start "OracleOraHome92TNSListener"
net start "OracleOraHome92HTTPServer"
net start "OracleMTSRecoveryService"

DOS: Barebones File List to a File

2009-11-08T00:28:00.000-06:00

dir /b /s > c:\files.txt

Append to the file instead...

dir /b /s >> c:\files.txt

DOS: Map a Sticky Drive

2009-11-04T12:00:00.000-06:00

net use [drive]: \\[server]\[folder] [password] /user:[domain]\[userid] /persistent:yes

MS Access: How Many Occurances of a String in a String?

2009-10-31T14:00:00.000-05:00

How many occurrences of a character are in a string; "+" used to preserve code indention, replace with spaces or tabs...

Function HowManyThisChar(string1 As String, stringtofind As String) As Long
+++HowManyThisChar = Nz(Len(string1)) - Nz(Len(Replace(string1, stringtofind, "")))
End Function

MS Access: Find the Max Value in Array

2009-10-30T14:00:00.000-05:00

Returns the maximum value in an array; "+" used to preserve code indention, replace with spaces or tabs...

Function MaxInArray(varArray As Variant) As Variant
' RETURN THE MAXIMUM VALUE FROM AN ARRAY

' FROM "VBA DEVELOPER'S HANDBOOK"
' BY KEN GETZ AND MIKE GILBERT
' COPYRIGHT 1997; SYBEX, INC. ALL RIGHTS RESERVED.

' IN: VARARRAY: A VALID ARRAY, CHECKED BY THE ISARRAY FUNCTION
' OUT: RETURN VALUE: THE MAXIMUM VALUE IN THE ARRAY

Dim varItem As Variant
Dim varMax As Variant
Dim intI As Integer

If IsArray(varArray) Then
++++If UBound(varArray) = -1 Then
++++++++MaxInArray = Null
++++Else
++++++++varMax = varArray(UBound(varArray))
++++++++For intI = LBound(varArray) To UBound(varArray)
++++++++++++varItem = varArray(intI)
++++++++++++If varItem > varMax Then
++++++++++++++++varMax = varItem
++++++++++++End If
++++++++Next intI
++++++++MaxInArray = varMax
++++End If
Else
++++MaxInArray = Null
End If
End Function

Use C# and VB in the Same Project

2009-10-29T14:32:00.000-05:00

"+" used to preserve code indention, replace with spaces or tabs...

<system.web>
+++<customErrors mode="Off"/>
+++<compilation debug="true" strict="false" explicit="true">
++++++<codeSubDirectories>
+++++++++<add directoryName="VB" />
+++++++++<add directoryName="CS" />
++++++</codeSubDirectories>
++++++<assemblies>
+++++++++<add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>
+++++++++<add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
++++++</assemblies>
+++</compilation>
<system.web>

C:\Visual Studio 2005\WebSites\You\App_Code\VB
C:\Visual Studio 2005\WebSites\You\App_Code\CS
C:\Visual Studio 2005\WebSites\You\App_Code\VB\YourPrettyVb.vb
C:\Visual Studio 2005\WebSites\You\App_Code\CS\YourPrettyCs.cs

MS Access: Find the Min Value in Array

2009-10-29T14:00:00.000-05:00

Returns the minimum value in an array; "+" used to preserve code indention, replace with spaces or tabs...

Function MinInArray(varArray As Variant) As Variant
' RETURN THE MINIMUM VALUE FROM AN ARRAY

' FROM "VBA DEVELOPER'S HANDBOOK"
' BY KEN GETZ AND MIKE GILBERT
' COPYRIGHT 1997; SYBEX, INC. ALL RIGHTS RESERVED.

' IN: VARARRAY: A VALID ARRAY, CHECKED BY THE ISARRAY FUNCTION
' OUT: RETURN VALUE: THE MINIMUM VALUE IN THE ARRAY

Dim varItem As Variant
Dim varMin As Variant
Dim intI As Integer

If IsArray(varArray) Then
++++If UBound(varArray) = -1 Then
++++++++MinInArray = Null
++++Else
++++++++varMin = varArray(LBound(varArray))
++++++++For intI = LBound(varArray) To UBound(varArray)
++++++++++++varItem = varArray(intI)
++++++++++++If varItem < varMin Then
++++++++++++++++varMin = varItem
++++++++++++End If
++++++++Next intI
++++++++MinInArray = varMin
++++End If
Else
++++MinInArray = Null
End If
End Function

MS Access: Add Text Suffix to Numbers

2009-10-28T14:00:00.000-05:00

Adds text suffix to numbers...1st, 2nd, 3rd, etc.; "+" used to preserve code indention, replace with spaces or tabs...

Function NumSuffix(MyNum As Variant) As String
' ADD VERBAL SUFFIXES TO NUMBERS EX: 1ST.
' CREATED BY RICHARD A. HUNT
' REVIEWED 05/21/2009

Dim n As Integer: n = Right(MyNum, 2)
Dim x As Integer: x = n Mod 10
Dim strSuf As String: strSuf = Switch(n <> 11 And x = 1, "st", n <> 12 And x = 2, "nd", n <> 13 And x = 3, "rd", n = 0 And x = 0, "-", True, "th")
NumSuffix = LTrim(Str(MyNum)) & strSuf

End Function

MS Access: Does an Object Exist?

2009-10-27T14:00:00.000-05:00

Check to see if an MS Access object exists; "+" used to preserve code indention, replace with spaces or tabs..

Function ObjectExists(ByVal strObjectType As String, ByVal strObjectName As String) As Boolean
' CHECKS TO SEE IF THE SPECIFIED OBJECT EXISTS.
' CREATED BY RICHARD A. HUNT
' REVIEWED 05/21/2009

+++++Dim dbs As DAO.Database
+++++Dim tbl As DAO.TableDef
+++++Dim qry As DAO.QueryDef
+++++Dim i As Integer

+++++Set dbs = CurrentDb()
+++++ObjectExists = False

+++++If strObjectType = "Table" Then
++++++++++For Each tbl In dbs.TableDefs
+++++++++++++++If tbl.Name = strObjectName Then
++++++++++++++++++++ObjectExists = True
++++++++++++++++++++Exit Function
+++++++++++++++End If
++++++++++Next tbl
+++++ElseIf strObjectType = "Query" Then
++++++++++For Each qry In dbs.QueryDefs
+++++++++++++++If qry.Name = strObjectName Then
++++++++++++++++++++ObjectExists = True
++++++++++++++++++++Exit Function
+++++++++++++++End If
++++++++++Next qry
+++++ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
++++++++++For i = 0 To dbs.Containers(strObjectType & "s").Documents.Count - 1
++++++++++++++If dbs.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
++++++++++++++++++++ObjectExists = True
++++++++++++++++++++Exit Function
+++++++++++++++End If
++++++++++Next i
+++++ElseIf strObjectType = "Macro" Then
++++++++++For i = 0 To dbs.Containers("Scripts").Documents.Count - 1
+++++++++++++++If dbs.Containers("Scripts").Documents(i).Name = strObjectName Then
++++++++++++++++++++ObjectExists = True
++++++++++++++++++++Exit Function
+++++++++++++++End If
++++++++++Next i
+++++Else
++++++++++MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
+++++End If
End Function

MS Access: Is the Form Loaded?

2009-10-26T14:00:00.000-05:00

Check to see if an MS Access form is currently loaded (i.e. open); "+" used to preserve code indention, replace with spaces or tabs.

Function IsLoaded(ByVal strFormName As String) As Integer
' CHECK TO SEE IF THE FORM IS LOADED.
' CREATED BY RICHARD A. HUNT
' REVIEWED: 05/29/2009

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
+++If Forms(strFormName).CurrentView <> conDesignView Then
++++++IsLoaded = True
+++End If
End If

End Function

MS Access: Display an Image in an Image Control from Drive Path

2009-10-25T19:30:00.000-05:00

Displays an image from your hard drive in an standard MS Access Image Control; confirmed to work with Access 97/2000/2003/2007; "+" used to preserve code indention, replace with spaces or tabs.

Sample Call: Me!txtImageNote = DisplayImage(Me!txtPortrait, Me!txtPortraitPath)

Public Function DisplayImage(ctlImageControl As Control, strImagePath As Variant) As String
On Error GoTo Exceptions
' ORIGINALLY OBTAINED FROM HTTP://SUPPORT.MICROSOFT.COM/SEARCH/PREVIEW.ASPX?SCID=KB;EN-US;Q285820
' MODIFIED BY RICHARD A. HUNT
' REVIEWED 06/03/2009

Dim strDatabasePath As String
Dim intSlashLocation As Integer

With ctlImageControl
+++If IsNull(strImagePath) Or CEmptyStr(strImagePath) = "" Then
+++++++.Visible = False
+++++++.Visible = True
+++++++.Picture = "Icons\NoPicture.jpg"
+++++++DisplayImage = "No image name specified."
+++Else
+++++++If InStr(2, strImagePath, ":") = 0 Then
+++++++++++' PATH IS RELATIVE.
+++++++++++strDatabasePath = CurrentProject.FullName
+++++++++++intSlashLocation = InStrRev(strDatabasePath, "\", Len(strDatabasePath))
+++++++++++strDatabasePath = Left(strDatabasePath, intSlashLocation)
+++++++++++strImagePath = strDatabasePath & strImagePath
+++++++End If
+++++++.Visible = True
+++++++.Picture = strImagePath
+++++++DisplayImage = "Image found and displayed."
+++End If
End With

Exceptions:
+++Select Case Err.Number
+++++++Case 2220
+++++++++++' CANNOT FIND THE PICTURE.
+++++++++++ctlImageControl.Visible = False
+++++++++++DisplayImage = "Can't find image in the specified name."
+++End Select
End Function

Function CEmptyStr(varCheckValue As Variant) As String
On Error GoTo Exceptions
' CONVERT NULL TO EMPTY.

Select Case IsNull(varCheckValue)
++++Case True
++++++++CEmptyStr = ""
++++Case False
++++++++CEmptyStr = varCheckValue
End Select

Exceptions:
++++Select Case Err.Number
++++++++Case 0
++++++++++++' DO NOTHING.
++++End Select
End Function

Blogger: CSS Variable Photo Caption Code

2009-10-25T18:00:00.000-05:00

Code for placement of captions in Blogger pictures; example shown; placement in the Blogger template shown at the bottom and change CSS as desired:

Bill Gates

Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Ut wisi enim ad minim veniam, quis nostrud exerci tation ullamcorper suscipit lobortis nisl ut aliquip ex ea commodo consequat. Duis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi. Nam liber tempor cum soluta nobis eleifend option congue nihil imperdiet doming id quod mazim placerat facer possim assum.

.picture {
background-color:$photoBackground;
border: 1px solid $photoBorderOuter;
padding: 3px; /* BORDER AROUND PHOTO */
font:$photoCaptionFontStyle;
color:$photoCaptionFontColor
}
.picture img {
border: 1px solid $photoBorderInner;
vertical-align: middle;
margin-bottom: 0px;
}
.right {
margin: 0.5em 0pt 0.5em 0.8em;
float:right;
}
.left {
margin: 0.5em 0.8em 0.5em 0;
float:left;
}
]]>\/b:skin

Variable name declarations in the Blogger template; place with other Blogger variables and change CSS as desired; variables can also be replaced with hard-coded CSS if preferred:

Blogger: CSS For Magazine Style Pull Quotes

2009-10-25T17:30:00.000-05:00

For use in div tags to display magazine style pull quotes; example shown; placement in the Blogger template shown at the bottom (remove the "\" escape character); can be enhanced with further CSS modifications such as borders, etc.:

Investigationes demonstraverunt lectores legere me lius quod ii legunt saepius.

Duis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi. Nam liber tempor cum soluta nobis eleifend option congue nihil imperdiet doming id quod mazim placerat facer possim assum. Typi non habent claritatem insitam; est usus legentis in iis qui facit eorum claritatem.

.pullquoteleft
{
width: 200px;
margin: 5px 15px 5px 0;
font-size: 1.4em;
text-align: center;
float: left;
}
.pullquoteright
{
width: 200px;
margin: 5px 0 5px 15px;
font-size: 1.4em;
text-align: center;
float: right;
}
]]>\/b:skin

Blogger: CCS For Displaying Code

2009-10-25T15:00:00.000-05:00

Code for displaying code in Blogger posts; example shown; placement in the Blogger template shown at the bottom (remove the "\" escape character):

.code
{
color: #006AB0;
border : 1px solid #DADADA;
padding : 5px 5px;
font-family : Consolas, "Courier New", Courier, mono, serif;
font-size : 12px;
background-color : #fAfAfA;
width : 90%;
overflow : auto;
margin : 10px 0 10px 10px;
border-left : 20px solid #DADADA;
max-height : 500px;
min-height : 16px;
line-height : 16px;
}
.code:hover
{
background-color : #f3f3f3;
}
]]>\/b:skin