Link Excel spreadsheet to SQL Server 2005

Create "New Linked Server" in the linked servers tree
* Name it
* Provider: Microsoft Jet 4.0 OLE DB Provider
* Product Name: Excel
* Data Source: c:\xxxx\xxxx\xx.xls
* Provider String: Excel 8.0

Then choose OK - make sure you have the file in the proper directory.

display all fields from one database record

Here is a quick way, when debugging, to display all fields from one record
(courtesy of Bill W from 4GuysfromRolla)

strSQL = "SELECT * FROM requests WHERE ID = " & rID & ";"
Set RS = yourAlreadyOpenConnectionObject.Execute( strSQL )
For fnum = 0 TO RS.Fields.Count-1
Set fld = RS.Fields(fnum)
Response.Write fld.name & "=" & fld.value & "(begin tag)br/(end tag)" & vbNewLine
Next

Displaying all form fields submitted from page

I like this simple code when debugging or starting a new project where I need to see all the form fields submitted at once:

For Each fld In Request.Form
Response.Write fld & "=" & Request.Form(fld) & "(begin tag)br/(end tag)>" & vbNewLine
Next
Response.Write "(begin tag)hr(end tag)"

IDC Servco SCAM

Administrator at the office got a call on the phone today from someone claiming to be from Hewlett Packard and "needed to talk about the problem with the HP printer" The administrator sent me the call and when I picked up, the person on the line said "I'm calling for Hewlett Packard, this is about your HP2015 printer, we're sorry we were supposed to call about the price change on the toner it is going up to 748 dollars from its regular price of 698 dollars, we didn't get this information to you out in time." I'm thinking hmmm I don't even have a HP2015 printer and anyway, this is a lot of money for toner so I said "how much toner?", she said "a box" I am thinking this must be a carton of about 6 or something, I said "one box?" and she said "yes, now we will complete the order and ship it out at the lower cost" I said "what company are you?" and she said "IDC Servco", then I said "how come I have never heard of you and why are you quoting prices to me as if you were shipping it out?", the next thing she did was hang up.

I checked on the internet and found this is a common scam attempt from this company:
http://www.complaints.com/2007/september/14/IDCSERVCO_152345.htm That is a post from 2007 so this stuff has been happening for a long time.

Please tell your people in charge of the printing supplies to watch out for this, they are fast talkers....

Free pdf OCR solution using Microsoft tools

Came across an issue where I had to convert a poorly scanned pdf document to word. The steps I took were to print the pdf to the "Microsoft Office Document Image Writer", saved as a .mdi file, then open it up in Microsfot Office Document Imaging (in the tools section), then choose to OCR the document and save text to Word. Worked fairly nicely and simply had to clean up the text.

loop through dates, skip days

Had an issue where I needed to loop from a start date to an end date but for certain weekdays, (like fridays or mondays, information coming from a series of checkboxes; 1 for each weekday) I needed to skip whichever the user checked off....

Solution:

For dt = StartDate To EndDate
If InStr( shiftdays, CStr(Weekday(dt)) ) > 0 Then
... process this dt ...
End If
Next

COALESCE

This function was hard for me to first understand, but I have found it very useful:
COALESCE(expression1,expression2)
means if expression1 is null, then expression2

This was useful when a column originally contains null values and I needed to concatenate a value to it, not knowing if the value was still null

COALESCE(column1,'') means if column1 is null, then blank, otherwise column1