利用這個作法的話，最近一筆會出現在最上面，順序會反過來
/*取得使用者的查詢結果*/
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 >= [...]
含有 ‘example’標籤(tag)的文章們
17 十一月
SQL Server 2000 RowID的作法
27 十月
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());
}
27 十月
利用Regex檢查是否為數值(ASP.NET)
public bool CheckIsNumber(string val)
{
Regex reNum = new Regex(@”^\d+$”);
bool isNumeric = reNum.Match(val).Success;
return isNumeric;
}
19 十月
JavaScript CalSubTotal 頁面計算小計功能
//頁面計算小計金額
function calcSubTotal()
{
var objNum = document.getElementById(‘Qty’);
var objUnitPrice = document.getElementById(‘UnitPrice’);
var objSubTotal = document.getElementById(‘SubTotal’);
if (objNum!=null && objUnitPrice != null && objSubTotal!=null)
{
if(objNum.value == “” || objUnitPrice == “”)
{
objSubTotal.value = “0″;
}
else
{
var qty = parseInt(objNum.value); if (isNaN(qty)) qty = 0;
var unitprice = parseInt(objUnitPrice.value); if (isNaN(unitprice)) unitprice = 0;
var result = qty * unitprice;
objSubTotal.value = result;
}
}
}
5 十月
ASP Connection to Sql Server Example
‘Create DB Connection
set Conn = Server.CreateObject(“ADODB.Connection”)
Conn.Open “Provider=SQLOLEDB;Data Source=GBUTEST-DB;Initial Catalog=Northwind;User Id=sa;Password=XXXXX;”
‘Create ADO SQL RecordSet
set rs = Server.CreateObject(“ADODB.recordset”)
rs.Open “SELECT [ShipperID],[CompanyName],[Phone] FROM [Northwind].[dbo].[Shippers]“, Conn
response.write(“<TABLE BORDER=1> <TR BGCOLOR=lightyellow>”)
‘Print Table Header
For each h in rs.fields
response.write “<TD>” & h.Name & “</TD>”
next
response.write(“</TR>”)
‘Extract Data From RecordSet
rs.MoveFirst
While Not rs.EOF
response.write(“<TR>”)
for each x in rs.fields
‘response.write(x.name)
‘response.write(” = “)
response.write “<td>” & x.value & “</td>”
next
rs.MoveNext
response.write(“</TR>”)
Wend
response.write(“</TR></TABLE>”)
Conn.Close
Set Conn=Nothing
6 九月
Update From Statement Example
/*資料更新回訂單主檔*/
UPDATE dbo.CN_Order_Header
SET DeliveryDate=ISNULL(hl.DeliveryDate,h.DeliveryDate),
Consignee=ISNULL(hl.Consignee,h.Consignee),
TelNum=ISNULL(hl.TelNum,h.TelNum),
DeliveryAddress=ISNULL(hl.DeliveryAddress,h.DeliveryAddress),
CheckAccount=ISNULL(hl.CheckAccount,h.CheckAccount)
FROM dbo.CN_Order_Header as h
JOIN dbo.CN_Order_Header_Log as hl
ON h.OrderID = hl.OrderID and
hl.ModifySNo = (SELECT MAX(ModifySNo)
FROM dbo.CN_Order_Header_Log
WHERE OrderID=h.OrderID)
and hl.ModifyStatus = ‘1′
and hl.OrderID=@OrderID
/*資料更新回訂單明細檔,可一次更新多筆單身*/
UPDATE dbo.CN_Order_Detail
SET ProductID=ISNULL(dl.ProductID,d.ProductID),
Unit=ISNULL(dl.Unit,d.Unit),
Qty=ISNULL(dl.Qty,d.Qty),
UnitPrice=ISNULL(dl.UnitPrice,d.UnitPrice),
Memo=ISNULL(dl.Memo,d.Memo)
FROM [...]
