SQL Server 2000 RowID的作法

利用這個作法的話，最近一筆會出現在最上面，順序會反過來

/*取得使用者的查詢結果*/
DECLARE @ChooseSYSID TABLE (SYS_ID INT);
INSERT INTO @ChooseSYSID(SYS_ID)
SELECT SYS_ID FROM dbo.NETBAR
WHERE (@INFORM_ID = ‘ ‘       OR INFORM_ID LIKE @INFORM_ID)
ORDER BY SYS_ID DESC

/*產生ROWID*/
DECLARE @RowNumber TABLE(ROW_ID INT , SYS_ID INT);
INSERT INTO @RowNumber(ROW_ID , SYS_ID)
SELECT
(SELECT COUNT(*) FROM @ChooseSYSID as e2 WHERE e2.SYS_ID >= e1.SYS_ID) as ROW_ID,
e1.SYS_ID
FROM @ChooseSYSID e1

/*回傳所需筆數*/
SELECT C.ROW_ID,A.SYS_ID,INFORM_ID
FROM dbo.NETBAR_HEAD A , @RowNumber C
WHERE A.SYS_ID = C.SYS_ID
AND (C.ROW_ID >= @startIndex AND C.ROW_ID < (@startIndex + @pageSize ))
ORDER BY A.SYS_ID DESC

取得目前最大的IDENTITY KEY VALUE

這個回傳的值是己經加1的資料

SELECT IDENT_CURRENT(‘[SALES].[dbo].[Manual_Chapter]‘)

[HTML]Firebug 網頁程式設計人員的好工具

http://getfirebug.com/

撰寫網頁時,十分方便的除錯工具….
它的功能簡單介紹如下:

• 友善可調整的編輯方式.
• 偵測並編輯HTML
• 調整CSS至完善
• CSS呈現結果視覺化
• 監控網路活動
• JavaScript除錯與微調
• 快速地找尋錯誤
• 瀏覽DOM結構
• 以命令列方式執行JavaScript
• JavaScript紀錄器

語系代碼

Language	ISO Code
Abkhazian	ab
Afar	aa
Afrikaans	af
Albanian	sq
Amharic	am
Arabic	ar
Armenian	hy
Assamese	as
Aymara	ay
Azerbaijani	az
Bashkir	ba
Basque	eu
Bengali (Bangla)	bn
Bhutani	dz
Bihari	bh
Bislama	bi
Breton	br
Bulgarian	bg
Burmese	my
Byelorussian (Belarusian)	be
Cambodian	km
Catalan	ca
Cherokee
Chewa
Chinese (Simplified)	zh
Chinese (Traditional)	zh
Corsican	co
Croatian	hr
Czech	cs
Danish	da
Divehi
Dutch	nl
Edo
English	en
Esperanto	eo
Estonian	et
Faeroese	fo
Farsi	fa
Fiji	fj
Finnish	fi
Flemish
French	fr
Frisian	fy
Fulfulde
Galician	gl
Gaelic (Scottish)	gd
Gaelic (Manx)	gv
Georgian	ka
German	de
Greek	el
Greenlandic	kl
Guarani	gn
Gujarati	gu
Hausa	ha
Hawaiian
Hebrew	he, iw
Hindi	hi
Hungarian	hu
Ibibio
Icelandic	is
Igbo
Indonesian	id, in
Interlingua	ia
Interlingue	ie
Inuktitut	iu
Inupiak	ik
Irish	ga
Italian	it
Japanese	ja
Javanese	jv
Kannada	kn
Kanuri
Kashmiri	ks
Kazakh	kk
Kinyarwanda (Ruanda)	rw
Kirghiz	ky
Kirundi (Rundi)	rn
Konkani
Korean	ko
Kurdish	ku
Laothian	lo
Latin	la
Latvian (Lettish)	lv
Limburgish ( Limburger)	li
Lingala	ln
Lithuanian	lt
Macedonian	mk
Malagasy	mg
Malay	ms
Malayalam	ml
Maltese	mt
Maori	mi
Marathi	mr
Moldavian	mo
Mongolian	mn
Nauru	na
Nepali	ne
Norwegian	no
Occitan	oc
Oriya	or
Oromo (Afan, Galla)	om
Papiamentu
Pashto (Pushto)	ps
Polish	pl
Portuguese	pt
Punjabi	pa
Quechua	qu
Rhaeto-Romance	rm
Romanian	ro
Russian	ru
Sami (Lappish)
Samoan	sm
Sangro	sg
Sanskrit	sa
Serbian	sr
Serbo-Croatian	sh
Sesotho	st
Setswana	tn
Shona	sn
Sindhi	sd
Sinhalese	si
Siswati	ss
Slovak	sk
Slovenian	sl
Somali	so
Spanish	es
Sundanese	su
Swahili (Kiswahili)	sw
Swedish	sv
Syriac
Tagalog	tl
Tajik	tg
Tamazight
Tamil	ta
Tatar	tt
Telugu	te
Thai	th
Tibetan	bo
Tigrinya	ti
Tonga	to
Tsonga	ts
Turkish	tr
Turkmen	tk
Twi	tw
Uighur	ug
Ukrainian	uk
Urdu	ur
Uzbek	uz
Venda
Vietnamese	vi
Volapük	vo
Welsh	cy
Wolof	wo
Xhosa	xh
Yi
Yiddish	yi, ji
Yoruba	yo
Zulu	zu

ShowMessage on Client(ASP.NET)

protected void ShowMessage(string strMessage)
{
StringBuilder strScript = new StringBuilder();
strScript.Append(“<script language=’javascript’> “);
strScript.Append(“alert(‘” + strMessage + “‘)”);
strScript.Append(“</script>”);

//UpdatePanel內呼叫要用下面的寫法，否則Script不會寫到前端去
ScriptManager.RegisterClientScriptBlock(this.Page, this.GetType(), “testScript”, strScript.ToString(), false);

//一般ServerSide內呼叫
//ClientScript.RegisterStartupScript(this.GetType(), “SetValueScript”, strScript.ToString());

}

protected void ShowMessage(string strMessage)
{
StringBuilder strScript = new StringBuilder();
strScript.Append(“<script language=’javascript’> “);
strScript.Append(“alert(‘” + strMessage + “‘)”);
strScript.Append(“</script>”);

 

//UpdatePanel內呼叫，否則Script不會寫到前端去
ScriptManager.RegisterClientScriptBlock(this.Page, this.GetType(), “testScript”, strScript.ToString(), false);

//一般ServerSide內呼叫
//ClientScript.RegisterStartupScript(this.GetType(), “SetValueScript”, strScript.ToString());

}

利用Regex檢查是否為數值(ASP.NET)

public bool CheckIsNumber(string val)
{
Regex reNum = new Regex(@”^\d+$”);
bool isNumeric = reNum.Match(val).Success;
return isNumeric;
}

DownLoad File From Asp.Net

….

if (file.Exists)
{
//Method-1 將檔案資料讀入Stream,這時檔案已經可以刪除了..之後再把檔案內容輸出即可
//Read File Content
sr = new StreamReader(path);
Response.Clear();
Response.AddHeader(“Content-Disposition”, “attachment; filename=ExcelExport.xls“); //下載的檔案名稱
Response.AddHeader(“Content-Length”, file.Length.ToString());  //下載的檔案大小
Response.ContentType = “application/vnd.ms-excel“; //下載的檔案類型
Response.Write(sr.ReadToEnd());
Response.End();

//Method-2 這是第二個方法,直接把檔案內容寫到前端,此時是不允許將檔案給刪除的,因為檔案是讀取的目標
//Response.Clear();
//Response.AddHeader(“Content-Disposition”, “attachment; filename=ExcelExport.xls”);
//Response.AddHeader(“Content-Length”, file.Length.ToString());
//Response.ContentType = “application/vnd.ms-excel”;
//Response.WriteFile(file.FullName);
//Response.End();

}

….

DownLoad Excel MultiSheet

這裡是asp.net Code的部份，這裡作了一個Excel檔案。

protected string ExportExeclByXSLT_Direct(DataSet dsReportData)
{
string fileName = “././DownLoadFile/ExcelExport.xls”;
string xslPath = “././Xsl_Template/Excel.xslt”;
string xlsPath = “././DownLoadFile/ExcelExport.xls”;
StreamWriter oExcelWriter = System.IO.File.CreateText(Server.MapPath(xlsPath));
try
{
XmlDataDocument xddData = new XmlDataDocument(dsReportData);
XslCompiledTransform xt = new XslCompiledTransform();
xt.Load(Server.MapPath(xslPath));
xt.Transform(xddData, null, oExcelWriter);
return fileName;
}
catch (Exception ex)
{
return “”;
}
finally
{
oExcelWriter.Close();
}
}

這裡是Excel下載的頁面(DownLoad.aspx)

protected void Page_Load(object sender, EventArgs e)
{
NameValueCollection coll = Request.QueryString;
String strRequest = coll["file"];
if (strRequest != “”)
{
String path = Server.MapPath(strRequest);
FileInfo file = new FileInfo(path);
StreamReader sr = null;
try
{
if (file.Exists)
{
//Method-1 將檔案資料讀入Stream,這時檔案已經可以刪除了..之後再把檔案內容輸出即可
//Read File Content
sr = new StreamReader(path);
Response.Clear();
Response.AddHeader(“Content-Disposition”, “attachment; filename=ExcelExport.xls”);
Response.AddHeader(“Content-Length”, file.Length.ToString());
Response.ContentType = “application/vnd.ms-excel”;
Response.Write(sr.ReadToEnd());
Response.End();

//Method-2 這是第二個方法,直接把檔案內容寫到前端,此時是不允許將檔案給刪除的,因為檔案是讀取的目標
//Response.Clear();
//Response.AddHeader(“Content-Disposition”, “attachment; filename=ExcelExport.xls”);
//Response.AddHeader(“Content-Length”, file.Length.ToString());
//Response.ContentType = “application/vnd.ms-excel”;
//Response.WriteFile(file.FullName);
//Response.End();

}

}
catch
{

}
finally
{
if (sr != null) sr.Close(); //Close Reader 注意:要記得把連接關閉
if (file.Exists)file.Delete(); //Delete Temp DownLoad File
}
}
}

這裡是XSLT的部份，負責將DataSet轉換成Excel的XML資料

<xsl:stylesheet version=”1.0″
xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”
xmlns:msxsl=”urn:schemas-microsoft-com:xslt”
xmlns:o=”urn:schemas-microsoft-com:office:office”
xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet” >

<xsl:template match=”*”>
<ss:Workbook xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”>
<ss:Worksheet ss:Name=”Main”>
<ss:Table>
<!–資料標題–>
<ss:Row>
<xsl:for-each select=”Main[position() = 1]/*”>
<ss:Cell>
<ss:Data ss:Type=”String”>
<xsl:value-of select=”local-name()”/>
</ss:Data>
</ss:Cell>
</xsl:for-each>
</ss:Row>
<xsl:for-each select=”Main”>
<!–資料內容–>
<ss:Row>
<xsl:for-each select=”*”>
<ss:Cell>
<ss:Data ss:Type=”String”>
<xsl:value-of select=”.”/>
</ss:Data>
</ss:Cell>
</xsl:for-each>
</ss:Row>
</xsl:for-each>
</ss:Table>
</ss:Worksheet>
<ss:Worksheet ss:Name=”HQ_Detail”>
<ss:Table>
<!–資料標題–>
<ss:Row>
<xsl:for-each select=”HQ_Detail[position() = 1]/*”>
<ss:Cell>
<ss:Data ss:Type=”String”>
<xsl:value-of select=”local-name()”/>
</ss:Data>
</ss:Cell>
</xsl:for-each>
</ss:Row>
<xsl:for-each select=”HQ_Detail”>
<ss:Row>
<xsl:for-each select=”*”>
<ss:Cell>
<ss:Data ss:Type=”String”>
<xsl:value-of select=”.”/>
</ss:Data>
</ss:Cell>
</xsl:for-each>
</ss:Row>
</xsl:for-each>
</ss:Table>
</ss:Worksheet>
<ss:Worksheet ss:Name=”NL_Detail”>
<ss:Table>
<!–資料標題–>
<ss:Row>
<xsl:for-each select=”NL_Detail[position() = 1]/*”>
<ss:Cell>
<ss:Data ss:Type=”String”>
<xsl:value-of select=”local-name()”/>
</ss:Data>
</ss:Cell>
</xsl:for-each>
</ss:Row>
<xsl:for-each select=”NL_Detail”>
<ss:Row>
<xsl:for-each select=”*”>
<ss:Cell>
<ss:Data ss:Type=”String”>
<xsl:value-of select=”.”/>
</ss:Data>
</ss:Cell>
</xsl:for-each>
</ss:Row>
</xsl:for-each>
</ss:Table>
</ss:Worksheet>
<ss:Worksheet ss:Name=”CH_Detail”>
<ss:Table>
<!–資料標題–>
<ss:Row>
<xsl:for-each select=”CH_Detail[position() = 1]/*”>
<ss:Cell>
<ss:Data ss:Type=”String”>
<xsl:value-of select=”local-name()”/>
</ss:Data>
</ss:Cell>
</xsl:for-each>
</ss:Row>
<xsl:for-each select=”CH_Detail”>
<ss:Row>
<xsl:for-each select=”*”>
<ss:Cell>
<ss:Data ss:Type=”String”>
<xsl:value-of select=”.”/>
</ss:Data>
</ss:Cell>
</xsl:for-each>
</ss:Row>
</xsl:for-each>
</ss:Table>
</ss:Worksheet>

</ss:Workbook>
</xsl:template>

</xsl:stylesheet>

TemplateField Add Number Format

前端的寫法：
<asp:templatefield>
	<itemtemplate>
		<asp:label id="Label1" runat="server" text='<%# Eval("MyDateField", "{0:d}") %>' />
	</itemtemplate>
</asp:templatefield>

這個很好用，一定要記起來
增加千分位請用　"{0:N0}"
日期格式請用　"{0:d}"

後端的寫法：
lblGVTotal.Text = Total.ToString("N0");

公司資料備份的程序

下載免費備份程式
FreeFileSync 3.0 免安裝中文版

匯出批次檔設定
從備份程式中匯出設定檔

自動排定工作的參數
“C:\Program Files\FreeFileSync\FreeFileSync.exe” C:\Documents” and Settings\kl.cheng\My Documents\Calendar_Backup.ffs_batch

排程測試…大功告成…