old.jamesandjudy.com snippet database

GetAbs

(ASP)

Function GetAbs(sURL)

if Request.ServerVariables("HTTPS") = "off" then
GetAbs = "http://" & Request.ServerVariables("SERVER_NAME") & "/" & sURL
else
GetAbs = "https://" & Request.ServerVariables("SERVER_NAME") & "/" & sURL
end if
end Function

Grant Execute on all stored procs to user

(MSSQL Script)

declare @sql nvarchar(4000)
declare @db sysname ; set @db = DB_NAME()
declare @u sysname ; set @u = QUOTENAME('LPeCommWrite')

set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @u + ''' FROM INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
exec master.dbo.xp_execresultset @sql,@db

Fixes "orphaned users" in sql server database after restoring database on a new server.

(MSSQL Script)

EXEC sp_change_users_login 'auto_fix', 'Read';
EXEC sp_change_users_login 'auto_fix', 'Write';

Random quote generator (javascript)

//quote array

quoteList = new Array();

quoteList[0] = "CodePunk is all good";

quoteList[1] = "Where have all the flowers gone?";

quoteList[2] = "I love money.";

quoteList[3] = "Just a general random quote.";

quoteList[4] = "Drink more generic beer.";

quoteList[5] = "Deer are tasty as well as cute.";

quoteList[6] = "Mules are stubborn.";

quoteList[7] = "JavaScript is cool.";

quoteList[8] = "See how this works?";

quoteList[9] = "You can use file URLs instead of text to make random images.";

//randomization

var now = new Date();

var secs = now.getSeconds();

var raw_random_number = Math.random(secs);

var random_number = Math.round(raw_random_number * (quoteList.length));

if (random_number == quoteList.length){random_number = 0}

//set quote

var quote = quoteList[random_number];

< div>
You do not have the Flash plugin installed, or your browser does not support Javascript (you should enable it, perhaps?)
< /div>
< script>
// <![CDATA[

var version = com.deconcept.FlashObjectUtil.getPlayerVersion();
if (document.getElementById && (version['major'] > 0)) {
document.getElementById('flashversion').innerHTML = "You have Flash player "+ version['major'] +"."+ version['minor'] +"."+ version['rev'] +" installed.";
}

// ]]>
< /script>

Create a user and grant dbreader/dbwriter to them in a specific database

(MSSQL Script)

-- Create a new SQL Server Login
exec sp_addlogin @loginame = 'MyUser', @passwd = 'MyPassword'
-- Grant the SQL login access to your database.
-- Create a database user called

Use AlphaPublicMembership
GO
exec sp_grantdbaccess 'MyUser'
-- Create a user-defined database role.
-- exec sp_addrole 'DBWriter'
-- Add the database user to the new database role.
exec sp_addrolemember 'db_datareader', 'MyUser'
exec sp_addrolemember 'db_datawriter', 'MyUser'

Parse string code into Web Control

(ASP.NET)

oCtl = Page.ParseControl("<asp:textboxtxtName"" value=""It worked dude!"" runat=""server""></asp:textbox>")

phForm.Controls.Add(oCtl)

telnet www.example.com 25

which opens a TCP connection from the sending machine to the MTA listening on port 25 on host www.example.com.

S: 220 www.example.com ESMTP Postfix
C: HELO mydomain.com
S: 250 Hello mydomain.com
C: MAIL FROM:<sender@mydomain.com>
S: 250 Ok
C: RCPT TO:<friend@example.com>
S: 250 Ok
C: DATA
S: 354 End data with <CR><LF>.<CR><LF>
C: Subject: test message
C: From: sender@mydomain.com
C: To: friend@example.com
C:
C: Hello,
C: This is a test.
C: Goodbye.
C: .
S: 250 Ok: queued as 12345
C: QUIT
S: 221 Bye

GenerateGUID (VBScript)

Excel - Insert Module and the column formula = "=GenerateGUID()"

Handling 32-bit PNG files in <=IE6 (using AlphaImage, Serverside, cross-browser friendly)

Private Sub panelTShirtHollow_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles panelTShirtHollow.Load

Dim panelTShirtHollow As HtmlGenericControl = sender

If (Request.Browser.IsBrowser("IE") And Request.Browser.MajorVersion < 7) Then

' Old IE browsers cannot handle 32-bit PNG files as backgrounds without the following hack

panelTShirtHollow.Style.Item("filter") = "progid:DXImageTransform.Microsoft.AlphaImageLoader(src='../../images/tshirt/TShirtBorder.png', sizingMethod='scale')"

Else

' Normal way...

panelTShirtHollow.Style.Item("background-image") = "url('../../images/tshirt/TShirtBorder.png')"

End If

End Sub

Set TypeLib = CreateObject("Scriptlet.TypeLib")
strGUID = TypeLib.GUID
DTSDestination("myTableID") = strGUID

Replace quote in string with html entity

(MS-SQL)

UPDATE ClearClaims
Set CompanyName = REPLACE(CompanyName, CHAR(39), '&#39;')
WHERE (CompanyName LIKE '%' + CHAR(39) + '%')

If (TypeOf (moCtl) Is TemplateBase) Then
Dim oCtl As TemplateBase = moCtl
oCtl.AfterSubmitParentMethod()
End If

.Net(C#) - Name of current function

public static void TraceContext(string FormattedMessage)
{
   Trace.WriteLine(string.Format(FormattedMessage,
      new System.Diagnostics.StackFrame(1).GetMethod().Name));
}

Verify DNS issue

C> nslookup
Default Server: sv2.pronetco.com
Address: 192.168.5.5

> www.buildmyownsite.com
Server: sv2.pronetco.com
Address: 192.168.5.5

Non-authoritative answer:
Name: buildmyownsite.com
Address: 207.97.206.34
Aliases: www.buildmyownsite.com

> www.womenscareofwi.com
Server: sv2.pronetco.com
Address: 192.168.5.5

DNS request timed out.
timeout was 2 seconds.
*** Request to sv2.pronetco.com timed-out
> exit

Dim aCookie As HttpCookie
aCookie = New HttpCookie("myEmail")
aCookie.Value = sEmail
aCookie.Expires = DateTime.Now.AddYears(10)
Response.Cookies.Add(aCookie)

If Not Request.Cookies("myEmail") Is Nothing Then
ViewState("myEmail") = Request.Cookies("myEmail").Value
End If

Sub SaveAttachments(ByVal oResponseID As System.Guid)

Try

Dim oDS As DataSet

Dim oRow As DataRow

Dim oData As New mySiteDesigner.dExpress.dxDataObject("bmos_FormBuilder:Attachment")

oDS = oData.Fill("", "_new", "")

Dim oDSRel As DataSet

Dim oRowRel As DataRow

Dim oDataRel As New mySiteDesigner.dExpress.dxDataObject("bmos_FormBuilder:ResponseAttachment")

oDSRel = oDataRel.Fill("", "_new", "")

Dim hfc As HttpFileCollection = Request.Files

For i As Integer = 0 To hfc.Count - 1

' Scan the files uploaded...

Dim hpf As HttpPostedFile = hfc(i)

Dim oAttachmentID As System.Guid

Dim sFilename As String ' Filename only - no path

Dim sFileExt As String ' File extension

Dim bytImage As Byte() = New Byte(hpf.ContentLength) {}

If hpf.ContentLength > 0 Then

' Initialize the save...

sFilename = System.IO.Path.GetFileName(hpf.FileName)

sFileExt = System.IO.Path.GetExtension(hpf.FileName)

oAttachmentID = System.Guid.NewGuid

' Set the attachment values

oRow = oDS.Tables(0).NewRow ' Create a new row

oDS.Tables(0).Rows.Add(oRow) ' Add row to dataset...otherwise it doesn't get saved.

oRow("AttachmentID") = oAttachmentID

oRow("AttachmentName") = sFilename

oRow("OriginalFileName") = sFilename

oRow("OriginalFileExt") = sFileExt

oRow("ContentType") = hpf.ContentType

oRow("ContentLength") = hpf.ContentLength

hpf.InputStream.Read(bytImage, 0, hpf.ContentLength)

oRow("BinaryDoc") = bytImage

oData.Save(oDS) ' Save the file to the database, as an attachment

' Set the relationship between attachment and responses

oRowRel = oDSRel.Tables(0).NewRow ' Create a new row

oDSRel.Tables(0).Rows.Add(oRowRel) ' Add row to dataset...otherwise it doesn't get saved.

oRowRel("AttachmentID") = oAttachmentID

oRowRel("ResponseID") = oResponseID

oDataRel.Save(oDSRel)

' Save to the file system...

' hpf.SaveAs(Server.MapPath(Me.Page.ResolveUrl("~/Uploads")) & "\" & System.IO.Path.GetFileName(hpf.FileName))

End If

Next

Catch ex As Exception

Throw New ApplicationException("Failed to SaveAttachments(). " & ex.Message, ex)

End Try

End Sub

Sub SendAttachmentToBrowser(ByVal oAttachmentID As System.Guid)
Dim oDS As DataSet
oDS = LoadAttachmentData(oAttachmentID)
If (oDS.Tables(0).Rows.Count > 0) Then
Dim oRow As DataRow = oDS.Tables(0).Rows(0)
Dim bytImage As Byte() = New Byte(oRow("ContentLength")) {}
If Not (oRow("AttachmentID") Is Nothing) Then
' Send down the attachment file...
Response.Clear()
Response.BufferOutput = True
Response.ContentType = oRow("ContentType")
'Response.AddHeader("Content-Disposition", "attachment;filename = " & oRow("OriginalFilename"))
Response.AddHeader("Content-Disposition", "inline;filename = " & oRow("OriginalFilename"))
Response.AppendHeader("Content-Length", oRow("ContentLength"))
Response.OutputStream.Write(CType(oRow("BinaryDoc"), Byte()), 0, Convert.ToInt32(oRow("ContentLength")))
Response.Flush()
Response.End()
End If

End If

End Sub

SELECT name
FROM syscolumns
WHERE (id =
(SELECT id
FROM sysobjects
WHERE type = 'U' AND [NAME] = 'quote'))
ORDER BY colorder

INSERT INTO workforceapp2.dbo.Application_Form
(First_Name, Last_Name)
SELECT First_Name, Last_Name
FROM Application a1
WHERE (AppID = 921)

NOTE: Yes, you do have to specify each column name.

Restore a database to use a different filename (MSSQL)

Declare @DBServerName nvarchar(256),
@DBStarterKitRootPath nvarchar(256),
@BackupDataName nvarchar(256),
@BackupLogName nvarchar(256),
@FromPath nvarchar(256),
@FromBackupFilename nvarchar(256),
@DBNewDatabasePath nvarchar(256),
@ToDataPath nvarchar(256),
@ToLogPath nvarchar(256),
@NewDBName nvarchar(256)

-- Parms...
Set @NewDBName = 'sk-bmos-v205a'

Set @DBServerName = CONVERT(nvarchar(256), SERVERPROPERTY('MachineName') )
Set @DBStarterKitRootPath = 'C-DRIVE\Data\BMOS\Dev\StarterKits\sk-bmos-v205a\'
Set @BackupDataName = 'BMOS_Websitev200_UmbCMS_Data'
Set @BackupLogName = 'BMOS_Websitev200_UmbCMS_Log'
Set @FromBackupFilename = 'sk-bmos-v205_mastersite_engage (v205a starting point to move back to localhost).bak'
Set @FromPath = @DBStarterKitRootPath + @FromBackupFilename
Set @DBNewDatabasePath = 'C-DRIVE\Data\_AppData\SQLDBs\'
Set @ToDataPath = (@DBNewDatabasePath + @NewDBName + '.mdf')
Set @ToLogPath = (@DBNewDatabasePath + @NewDBName + '.ldf')

RESTORE DATABASE @NewDBName
FROM DISK = @FromPath WITH FILE = 1,
MOVE @BackupDataName TO @ToDataPath,
MOVE @BackupLogName TO @ToLogPath,
NOUNLOAD, STATS = 10