[WordPress Contact Form 7] Add Numeric Type Field

This is about adding a numeric type "num" in Contact Form 7 (CF7, version 3.0.1) with minimum effort. This type only allows input to be numbers. Basically we will reuse a lot of the existing code in \wp-content\plugins\contact-form-7\modules\text.php It doesn't matter where do you put the code as long as they are in the same file.

1.
Register two shortcode:

wpcf7_add_shortcode( 'num', 'wpcf7_text_shortcode_handler', true );
wpcf7_add_shortcode( 'num*', 'wpcf7_text_shortcode_handler', true );

2.
Register the validation filter and implement the filter:

add_filter( 'wpcf7_validate_num', 'wpcf7_num_validation_filter', 10, 2 );
add_filter( 'wpcf7_validate_num*', 'wpcf7_num_validation_filter', 10, 2 );

function wpcf7_num_validation_filter( $result, $tag ) {
    $type = $tag['type'];
    $name = $tag['name'];

    $_POST[$name] = trim( strtr( (string) $_POST[$name], "\n", " " ) );

    if ( 'num' == $type || 'num*' == $type ) {
        if ( 'num*' == $type && '' == $_POST[$name] ) {
            $result['valid'] = false;
            $result['reason'][$name] = wpcf7_get_message( 'invalid_required' );
        } elseif ( '' != $_POST[$name] && ! is_numeric( $_POST[$name] ) ) {
            $result['valid'] = false;
            $result['reason'][$name] = 'Numbers are required.';
        }
    }

    return $result;
}

3.
You can try to add your Tag Generator for the new type, but I just don't bother. To use the new field, it is the same as using [text] field, e.g. [num mobile /10 class:mobile]

[WordPress Contact Form 7] Populate Select Dropdown from Database

Contact Form 7 (CF7) is a popular plugin for the WordPress CMS. It supports a few basic form components that allows user building a form quickly. One particular problem I had is to populate a drop down selection box from content stored in the database (e.g., a list of vacant positions). To dynamically populate dropdown options is not an uncommon task for site builders. And if you know something about programming, this wouldn't be hard either.

With CF7, you can only fill your options by hand. I have looked around for solutions, and found this post on the forum. However, it only appears to be working but not quite -- every time it rebuilds the options from database regardless user input, and nothing will be sent on submission (see the last comment in this post).

I then had to worked out my way; I believe this is original, so copy right reserved *_^. Here is how. (By the way, the version I am using is Contact Form 7 3.0.1).

In the wp-content\plugins\contact-form-7\modules\ folder, find the select.php. This is the file to be modified. (In general, I do NOT like to hack into the plugin source code. This will make upgrade difficult.)

1.
In function wpcf7_select_shortcode_handler, $values, $labels and $defaults need to be redefined for your customised dropdown. They are arrays for dropdown values, dropdown texts and default selection respectively. I have the following snippet right after $options are parsed (after line 50). Here my select field ID is "position".

if ($name == 'position') {
  // query the positions
  $positions = new WP_Query('YOUR_QUERY');
  
  while( $positions->have_posts() ) : $positions->the_post();
    array_push( $values, get_the_ID() );     // post ID as option value
    array_push( $labels, get_the_title() );  // post title as option text
  endwhile;
  
  // set the key of the default selection
  array_push( $defaults, 1 + array_search($YOUR_DEFAULT_VALUE, $values) );
} 

2.
In function wpcf7_select_validation_filter, you need to re-fill the dropdown option values before the post operation because they cannot be found in the shortcode. They will be needed in $_POST later. I have the following snippet right after the local variable definitions. The code is very similar.

if ($name == 'position') {
  // query the positions
  $positions = new WP_Query('YOUR_QUERY');
  while( $positions->have_posts() ) : $positions->the_post(); 
    array_push( $values, get_the_ID() );
  endwhile;
} 

And that is it.

LINQ Speedup

"From 10 minutes to 10 seconds" -- this is true experience I had. The brief story is, I had to insert a lot of records into the database. The way LINQ does (if you look at the generated query by using some SQL profiler) was to insert the record one-by-one surrounded with some safety check. Pretty straightforward; and it works fine in a small scale. In case you have cascaded data structure with a lot of Foreign keys floating around (which is normal for normal forms) in your database, the time it takes can be arbitrarily long -- Imagine how many tables it requires to lock before any one operation.

The way I found which works faster when inserting a lot of data in one go needs just a few extras.

1. Cache/Serialise the tables to local data structures, if they are read repeatedly and not very large. This can save a lot of SELECT statements generated by LINQ. Preferably, perform a sorting on your local data structure, which can support faster BinarySearch(). (e.g.)
2. Write a stored-procedure that handles Bulk-insert to multiple tables, in which you must have safety check and error handling as well. After all, stored procedures are first class citizen in handling the database operations. (e.g.)
3. Instead of calling LINQ insert, call the stored procedure (registered as function import in .dbml). (e.g.)

All the above are not tricky to be done. But the result can be astonishing and worthwhile.

ASP Show Update Progress

Just to remind myself: To properly show an UpdateProgress, we need
1. asp:ScriptManager
2. asp:UpdateProgress + asp:UpdatePanel work together

Example like this.http://ajax.net-tutorials.com/controls/updateprogress-control/

SQL Server 2005 Frustration

Yes, MS SQL server takes character cases casually -- 'Everything' = 'eVERYthING'
Yes, MS SQL server are not friendly to empty string -- LEN('') = 1
But this is ridiculous: The following statements returns true.

• LEN('TEST      ') = 4
• 'TEST      ' = 'Test'

SQL Bulk Insert Environment setting

SET NUMERIC_ROUNDABORT OFF
GO
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO

Formatting SQL output string

Step 1: Putting a record into a sentence.
You can build a sentence off a row using SELECT. For example,

SELECT [Who] + ' is going to do ' + [Item] + 'on '+ CONVERT(VARCHAR, [due], 105) + '.'
        FROM [Actions]

Step 2: Format the sentence.
When you want to putting line break or even a tab in your output, you may need CHAR function in SQL.

Tab	char(9)
Line feed	char(10)
Carriage return	char(13)

Details see http://msdn.microsoft.com/en-us/library/ms187323.aspx

Step 3: Form an essay with multiple records.
You may need to use CURSOR to iterate the rows from the result table.
 

iisapp.vbs

In the Task Manager, you can only find w3wp.exe running per ASP.NET website instance. To find out which process is which website, use a vbscript called iisapp.vbs in command line for IIS 6.0.
http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/b8721f32-696b-4439-9140-7061933afa4b.mspx?mfr=true

Paste Special in Excel.

Controlling Column Widths in Excel
Ever notice that when you copy data from one worksheet to another, the column widths don't copy correctly? Try this method.

• Copy the data and paste it into another spreadsheet. Leave the data highlighted. Go to Edit/Paste Special and put a tick mark in the radio button that says Column Widths.

Excel Auto CalculationsTo setup your spreadsheet for more examples, add the numbers 10 through 100 in cells A1:A10 in increments of 10. Shortcut:Note that you can type 10 in cell A1 and 20 in cell A2. Select both cells and use the Fill Handle to drag down to cell A10. Since Excel recognized the series of 10, 20, etc., you should now have 10 through 100 in cells A1 through A10. Remember that you only need to enter enough numbrs for Excel to see the pattern, then select them all and drag with the Fill Handle and Excel will continue the series, ad infinitum.

• In cell B1, type 100 and hit enter. Now return to cell B1 and copy it. Then highlight cells A1:A10 and go to the Edit menu and choose Paste Special.
  
• In the center section of the Paste Special box where it says Operation, put a tick mark in the radio button besideMultiply and click OK.

Excel will multiply all the numbers in A1:A10 by 100 (which you copied from cell B1). Try doing the same thing, but choosing Add, Subtract, or Divide. You will see that this is a very quick way to perform the same, simple mathematics on multiple cells.

Removing an Excel FormulaHere's one to use when you want to keep a value in a cell but remove the formula that produced this value.

• Add this formula to cell C1: =A1+B1. 
  
• Copy it and leave it selected. Go to the Edit menu and choose Paste Special. 
  
• This time, put a tick mark in the radio button in the Paste section that says Values and click OK. 

Changing the Data Layout in ExcelThis one is handy to know when you inherit a spreadsheet that someone else made and you want to change the layout of the data quickly.

• Highlight cells A1:A10 again and copy them. 
  
• Now click into cell D1 and go to the Edit menu and choose Paste Special. 
  
• This time, put a check in the box at the bottom that says Transpose and click OK.

You'll see that Excel will pasted your values across the columns, instead of down the rows. 

Skip Blanks in an Excel Data SeriesThis is a great one to use when you want to copy new data over old, but don't want to replace existing data in a cells where there is no new data.

• In cells C5:C9, enter the numbers 10, 20, , 40, ( meaning do not put anything in cells C7 and C9). 
  
• Now, in cells D5:D9, enter 50, 60, 70, 80, and 90. 
  
• Highlight cells C5:C9 and copy them. Click in cell D5 and go to the Edit menu and choose Paste Special. This time, put a check in the box that says Skip blanks and click OK.

You will see that cells D5:D9 now show 10, 20, 70, 40, 90, because Excel did not paste blank values over existing data. 

Linking Data in Excel

• Add another simple formula to your spreadsheet (again, two simple numbers and a sum to add them up will do). 
  
• Now copy the cell with the formula in it and go to another sheet in the workbook. Click on any blank cell. Go to Edit/Paste Special and click at the button where it says Paste Link.

You will see your number is in the cell and the formula bar shows that it relates to another sheet. 

• Go back to that sheet and change the SUM formula to an AVERAGE formula. 
  
• Return to the sheet where you pasted it and you will see it is updated there also.

Pasting a link means the destination cell will always be updated when you change the original cell. You can also do this between workbooks.

Pasting Web Pages into WordFirst, copy some text from a Web Page and paste it into Word and see if you have problems. If you go to a website, you will see my text is white on a dark background. If you copy white text and paste it into Word, you won't see anything! So, let's look at that Paste Special box:

• You see that by default, Word wants to copy this text in HTML Format, which would include the white font formatting.
  
• Try Paste Special/Unformatted Text. You will get just the text, in whatever default font you have set in Word.
  
• Also, sometimes when you copy text from an Email into Word, all of the margins are messed up. Try Paste Special/Unformatted Text for that one too.

Now try copying a picture from a Web Page or from an Email (right click on the graphic and choose Copy). Because graphics come in many formats, you may want to convert yours into a format that is smaller in file size or more compatible with your computer. Often graphics embedded in Emails only allow you to save them as bmp (bitmap) files, which are HUGE. People think they have to go into a graphics program to convert this file to something more manageable. Not true. Try Word's Paste Special:

• Note that you have various choices for different graphic formats, such as gif, jpeg, and png, which are all smaller and more manageable than bmp files.
  
• Try pasting your graphic in different formats and see the different choices you then have for making changes to your graphic. Also, notice the drastic difference in your file size when you make different choices.

Also, notice that Word's Paste Special box has the same Paste Link feature mentioned in the Excel section above. Though it's not always available (depends on what you are pasting and where you are pasting it from), when it's not grayed out you can use it to automatically update whatever you pasted when the original is changed.

Three ways to make something invisible on the WebForm

Suppose having a web control called "myContrl". In the code behind, how to make it invisible at run time?

1. myContrl.Visible = false;

2. myContrl.Attributes["style"] = "display:none";

3. myContrl.Height = Unit.Pixel(0);

How to append multiple files into one

Some suggests using the syntax:
for <variablename> in (<directorylisting>) do <command> <variablename>
I use
copy  *.txt  Aggregate.File

SQL updatet with inner join

SQL update statement doesn't like to include an inner join clause -- although it is doable with a little modification. (Goo it with the keywords.) -- it definitely doesn't like to include an inner join and an aggregate clause at the same time. For example, the following does NOT work.
Update tableA
Set Flag = 1
From tableA
Inner Join tableB on tableA.ID = tableB.ID
Where ...
Group By tableB.ID

In this case, the option left is to use nested select statement.

Finally my Discrete Math class pay off

I am writing a long and complicated stored procedure. Basically it is like
"if A is true, then
  SELECT blabla FROM bla WHERE (B = true)
otherwise,
  SELECT blabla FROM bla WHERE (B = false)"

I don't want to write CASE WHEN; and I don't want to repeat the common SELECT stuff. So this is what I do:
"SELECT blabla FROM bla WHERE (A = false OR B = true)"
 This is because "if X then Y" is logically equivalent to "not X or Y". 

A test on scheduled SSIS job

I have create an SSIS package that monitors an FTP and imports incoming data sets as flat files to the SQL server. (BTW, importing directly to an SQL destination is much faster than importing through ADO connection.Guess why?) It is deployed as a scheduled job on the server. The problem is: what should be schedule interval -- given that the job should be run as often as possible while the timespan to run an importing is indefinite.

I was afraid that the scheduled job would be pre-empted when a new schedule starts. That would cause a disaster -- 'cause no one knows what will happen when importing is stopped in the middle. So I conduct a test: I create a 2-min importing job, but schedule it as run every 1 minute. The result is: the next schedule will start only when the current one finishes. Mercy.

Different Options for Importing Data into SQL Server

ProblemMoving data into SQL Server is something that most DBAs or Developers are faced with probably on a daily basis.  One simple way of doing this is by using the Import / Export wizard, but along with this option there are several other ways of loading data into SQL Server tables. Another common technique would be to use either DTS (SQL 2000) or SSIS (SQL 2005).  In this tip we take a look at some of these other options for importing data into SQL Server.

SolutionIn addition to using the Import / Export wizards and/or DTS or SSIS to move data into SQL Server there are also a few other options for doing this that are built into SQL Server.  Some these other options include bcp, BULK INSERT, OPENROWSET as well as others.  The following examples show you some of these different options for importing data and how you can use some of these inline with your T-SQL code as well as others that can be run from the command line.

---

BCP
This is one of the options that is mostly widely used.  One reason for this is that it has been around for awhile, so DBAs have come quite familiar with this command.  This command allows you to both import and export data, but is primarily used for text data formats.  In addition, this command is generally run from a Windows command prompt, but could also be called from a stored procedure by using xp_cmdshell or called from a DTS or SSIS package.

Here is a simple command for importing data from file C:\ImportData.txt into table dbo.ImportTest.

bcp dbo.ImportTest in 'C:\ImportData.txt' -T -SserverName\instanceName

For more information about bcp click here.

---

BULK INSERT
This command is a T-SQL command that allows you to import data directly from within SQL Server by using T-SQL.  This command imports data from file C:\ImportData.txt into table dbo.ImportTest.

BULK INSERT dbo.ImportTest  FROM 'C:\ImportData.txt'  WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )

For more information about BULK INSERT click here.

---

OPENROWSET
This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server.  By using this command along with an INSERT INTO command we can load data from the specified data source into a SQL Server table.

This command will pull in all data from worksheet [Sheet1$]. By using the INSERT INTO command you can insert the query results into table dbo.ImportTest.

INSERT INTO dbo.ImportTest  SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',  'Excel 8.0;Database=C:\ImportData.xls', [Sheet1$])

Here is another example where data is pulled from worksheet [Sheet1$] by using a SELECT * FROM command. Again, by using the INSERT INTO command you can insert the query results into table dbo.ImportTest.   The query can be any valid SQL query, so you can filter the columns and rows by using this option.

INSERT INTO dbo.ImportTest  SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',  'Excel 8.0;Database=C:\ImportData.xls', 'SELECT * FROM [Sheet1$]')

For more information about OPENROWSET click here.

---

OPENDATASOURCE
This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server. This is similar to the OPENROWSET command.

INSERT INTO dbo.ImportTest  SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',  'Data Source=C:\ImportData.xls;Extended Properties=Excel 8.0')...[Sheet1$]

For more information about OPENDATASOURCE click here.

---

OPENQUERY
Another option is OPENQUERY.  This is another command that allows you to issue a T-SQL command to select data and again with the INSERT INTO option we can load data into our table.  There are two steps with this process, first a linked server is setup and then second the query is issued using the OPENQUERY command.  This option allow you to filter the columns and rows by the query that is issued against your linked data source.

EXEC sp_addlinkedserver 'ImportData',     'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',     'C:\ImportData.xls',     NULL,     'Excel 8.0'  GO INSERT INTO dbo.ImportTest  SELECT *  FROM OPENQUERY(ImportData, 'SELECT * FROM [Sheet1$]')

For more information about OPENQUERY click here.

---

Linked Servers
Here is yet another option with setting up a linked server and then issuing a straight SQL statement against the linked server.  This again has two steps, first the linked server is setup and secondly a SQL command is issued against the linked data source.

EXEC sp_addlinkedserver 'ImportData',     'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',     'C:\ImportData.xls',     NULL,     'Excel 8.0'  GO INSERT INTO dbo.ImportTest  SELECT * FROM ImportData...Sheet1$

For more information about Linked Servers click here.

As you can see right out of the box SQL Server offers many ways of importing data into SQL Server.  Take a look at these different options to see what satisfies your database requirements.

Freezing GridView header. EP3

Finally, there is something works as I desired. (My desire is minimal amount of work, and maximal achievement).

http://johnsobrepena.blogspot.com/2009/09/extending-aspnet-gridview-for-fixed.html

A better explained mechanism can be found here.
http://www.developer.com/lang/jscript/article.php/10939_3696921_4/A-Better-Fixed-GridView-Header-for-ASPNET.htm

Freezing GridView header. EP2

This is a hack: separate the header (as a fixed table) and the data (in a header-less gridview).
The problem is, it is difficult to align the columns, not mentioning that you could not dynamically generate the table as in autogenerate mode.

http://www.aspsnippets.com/Articles/Scrollable-GridView-with-Fixed-Headers-in-ASP.Net.aspx

Freezing GridView header. EP1

Use style sheet only -- put the gridview in a div or a panel that with the container style below. The problem is, it only works for IE. Worst, it may not work correctly in new version of IE. Somehow, it works for most of DataGrid control, as DataGrid is older than GridView.

http://mattberseth.com/blog/2007/09/freezing_gridview_column_heade_1.html


(note: overflow-x or overflow-y are not standard CSS properties. Therefore, MS has their own version of the properties for IE, e.g. -ms-overflow-x:scroll; -ms-overflow-y:scroll)

   

Change web page layout dyanmicaly

For an ASP.NET website, there are two things defining the page layout: masterpage and theme. They can be specified in the page header. For example, 

<%@ Page Title="A Web Page" Theme = "CoolTheme" Language="C#" MasterPageFile="~/SuperMasterPage.master" AutoEventWireup="true" CodeBehind="ExamplePage.aspx.cs" Inherits="WebGUI. ExamplePage" %>

This is static setting of the page layout. The masterpage used is SuperMasterPage.master and the theme is CoolTheme (probably defined in the App_Themes folder by default).

To dynamically/programmatically set them, we shall call upon the Page_PreInit procedure. Notice the order of procedures to be called when loading a page – Page_PreInit takes place before Page_Load. It is the only proper place to declare your masterpage and theme.

In the following example, we set a different masterpage and a different theme for a particular user called “joseph”.

In C#

protected void Page_PreInit(object sender, EventArgs e)
    {
        if (User.Identity.Name == "joseph")

      {
            this.MasterPageFile = "~/NewMasterPage.master";

            this.Theme = "NewTheme";

        }
    }

In VB

    Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreInit
 
        If User.Identity.Name.Equals("joseph") Then

            Me.Page.MasterPageFile = "~/NewMasterPage.master"
            Me.Page.Theme = "NewTheme"

        End If
 
    End Sub

Fixed an Access bug several months later

As title. Here is the story:
Several months ago, I was asked to inspect a weird bug in MS Access. In the beginning, no one knew what went wrong; just the Access crashed when genenerating a weekly staff-project report. Then I figured out that when a staff called "Brian Ye" was included in the summary report, the report generator crashes. Once again, debugging with VBA was not pleasant. In the end, even after I got some one to help, none of us could figure out what went wrong after a couple of hours. Luckily, "Brian" was just an intern. When he left, problem's solved.

Today, the reporting bit crashed again. This time, it took me only 10 minutes to locate the problem which explains the problem before. Thanks to the induction method.  So today it was about reporting with a staff called "Alan Shaw". See the problem? Probably too difficult, 'cause I haven't mentioned that the Access report uses their initials for its header. So the initials for them are "AS" and "BY" -- yes, they are SQL keywords. That's the problem.

(I twisted the names of the above-mentioned staff slightly.)