cross tab reporting

since I will need this, I just copy this from SQL Server Tutorial:

-----------------------------------------------------
Accessing and Changing Relational Data
Cross-Tab Reports
Sometimes it is necessary to rotate results so that columns are presented horizontally and rows are presented vertically. This is known as creating a PivotTable®, creating a cross-tab report, or rotating data.
Assume there is a table Pivot that has one row per quarter. A SELECT of Pivot reports the quarters vertically:Year Quarter Amount
---- ------- ------
1990 1 1.1
1990 2 1.2
1990 3 1.3
1990 4 1.4
1991 1 2.1
1991 2 2.2
1991 3 2.3
1991 4 2.4
A report must be produced with a table that contains one row for each year, with the values for each quarter appearing in a separate column, such as:
Year
Q1
Q2
Q3
Q4
1990
1.1
1.2
1.3
1.4
1991
2.1
2.2
2.3
2.4
These are the statements used to create the Pivot table and populate it with the data from the first table:USE Northwind
GO
CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO
This is the SELECT statement used to create the rotated results:SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO
This SELECT statement also handles a table in which there are multiple rows for each quarter. The GROUP BY combines all rows in Pivot for a given year into a single row in the output. When the grouping operation is being performed, the CASE functions in the SUM aggregates are applied in such a way that the Amount values for each quarter are added into the proper column in the result set and 0 is added to the result set columns for the other quarters.
If the results of this SELECT statement are used as input to a spreadsheet, it is easy for the spreadsheet to calculate a total for each year. When the SELECT is used from an application it may be easier to enhance the SELECT statement to calculate the yearly total. For example:SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
GO
Both GROUP BY with CUBE and GROUP BY with ROLLUP compute the same sort of information as shown in the example, but in a slightly different format.
See Also
SELECT
©1988-2004 Microsoft Corporation. All Rights Reserved.

change DNS record

you can manually change your DNS record in the "hosts" file at "C:\windows\system32\drivers\etc" folder.

format like this:
192.168.1.12 defender.buckeye-express.com

when I type "defender.buckeye-express.com" in the address bar, I acctually goes to alpha server (192.168.1.12)

design view change html content

I had trouble to edit a page in the design view: every time when I save this page after updating the path of the image, the style of TD and TR are all gone.
I looked into the html code, found that those style attribute is not valid in TD tag, then I copy & paste following lines from other pages into the part of this page, every thing is fine now.

<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">

dynanically select top %n records from table

I have to use query instead of stored procedure in order to dynamically select top %n record from table, b/c stored procedure can't take a parameter and used it like "select top @number ...". Finally, this is what I implemented:

SqlCommand cmdSql = new SqlCommand();
cmdSql.Connection = cnnSql;
cmdSql.CommandType = CommandType.Text;
string Query = String.Format(@"SELECT TOP {0}
[HelpID],
[CategoryID],
[Question],
[Answer],
[Hits]
FROM dbo.HelpContent ORDER BY [Hits] DESC",Number.ToString());
cmdSql.CommandText = Query;

T-SQL statement

I found T-SQL is boring, and have no clue of those functions in it, here are two examples:

(1) raise error statements
format ---- RAISERROR ({msg_id msg_str }{,severity ,state }
RaisError('duplicate user name',10,1);

(2). if something is not exists
if not exists (select username from users where username = @username)

sure I need to use begin and end after if or else, just like VB, :(

web.config at NUNIT

when using Nunit test your project, it will not read web.config file from the web application project. Actually, I need to copy the web.config file to the bin\debug folder under the test project, and rename it to [testproject].dll.config. In my case, since my test project's name is sprintborad.test, the name of the config file changes to springboard.text.dll.config.

resource ref: http://weblogs.asp.net/psperanza/archive/2004/02/24/79596.aspx

EditItemTemplate column

one advantage of using EditItemTemplate is that you can put some thing in the normal template column and another format in the edit template column. for example, show some texts in normal view, but change to a drop down menu when editing.
example:



<%#DataBinder.Eval(Container.DataItem,"SmtpServerType")%>




ISP Hosted
Exchange
Sendmail
Domino
Groupwise
IMail
Mailsite
Other - UNIX
Other - Windows
Unknown




when use textbox in the edittemplate column, I need to assign "Text" attribute in textbox, althought it seems like there is no "Text" attribute select from the list
for example:

'>

DataBinder.Eval

DataBinder.Eval(Container.DataItem," "), will return a object, but you can't not apply ToString() method directly after it. it will generate error,
an alternative way to do so is to use Convert.ToString();

datetime format

I have always encouter the problem of when coverting datetime object to the correct format of the string, here is the completed reference of that:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemglobalizationdatetimeformatinfoclasstopic.asp

javascript incompatible issue with IE and firefox

I had some incompatible problems in javascript with firefox, apprently the obj.parentElement is not working in firefox, but I had to use obj.parentNode.
However, for the same issue in datagrid, I had to change this.childNodes[1].childNodes[0] to this.cells[1].firstChild, so that I could access to the first control in each row of the datagrid, which is a checkbox

indented in ListItems

I can't not add indented listitems to a dropdown menu, even I add many spaces, like this
dropAction.Items.Add(new ListItem(Server.HtmlDecode(" To Recipient"),"2"));
it won't work! because when converted to html, thes space will despear like I add many usefulless spaces when edit html file. I know I need to use to deal with this, but after I add this it will not give me what I want:

OK, here is the real solution:
dropAction.Items.Add(new ListItem(Server.HtmlDecode(" To Recipient"),"2"));

highlight row in datagrid

resource link:
http://www.dotnetjohn.com/articles.aspx?articleid=12

because datagrid is not like repeater, its row element is not accessbile at design time. so I can't not set onmouseover event to the TD or TR at design time. But I could use datagrid's OnItemDataBound event handler to deal with this.

if (e.Item.ItemType == ListItemType.Item e.Item.ItemType == ListItemType.AlternatingItem)
{
DataRowView row = (DataRowView) e.Item.DataItem;
#region Set MouseOver/MouseOut
e.Item.Attributes.Add("onmouseover", "this.style.backgroundColor='#D6DEEC'");
e.Item.Attributes.Add("onmouseout", "this.style.backgroundColor='#ffffff'");
#endregion
......

in this way, I can even have the datagrid highlight same color but change back to different color when mouseout based on this row's ItemType (Item or AlternatingItem).

Using the enter key to submit a form

resource web page from:
http://www.allasp.net/enterkey.aspx

experience and example:

(1). add onkey down in the html part of an aspx page
onkeydown="if ((event.which && event.which == 13) (event.keyCode && event.keyCode == 13)) {document.forms[0].elements['Button'].click();return false;} else return true;" Runat="server" CssClass="FormText">

'Button' is the ID of the button control I want to fire onclick event,

but this won't work if the text box and button are in a usercontrol, cause final clientid will be changed, and user control id will be added in front of each sub control such as textbox and button, so I need to use the second solution,

(2). add onkeydown attribute in the code behide of the aspx page

string keydownAction = "if ((event.which&&amp;amp;event.which == 13)
(event.keyCode&&event.keyCode == 13)){document.forms[0].elements['"
+btnCreateAlias.ClientID+"'].click();return false;} else return true;";
txtAlias.Attributes.Add("onkeydown",keydownAction);

btnCreateAlias is the button I want to fire onclick event on, when key entered in the textbox.
btnCreateAlias.ClientID is clientid defined by the asp.net at run time, so this solution will be working at page level or inside a user control

resources

This summary is not available. Please click here to view the post.

my first blog

finally decide to have a blog of myself, to write down some experience gained during the programming of ASP.NET

I don't think I can keep pace with this god-damn constantly changing world, but I am still young and working in this shiting IT field, what else can I do?