Simulate key press

SendKeys.Send("{ENTER}")
SendKeys.Send("{DELETE}")

Avoid illegal login

Request.ServerVariables("HTTP_REFERER")
Request.ServerVariables("SERVER_NAME")

compare these two variables

Parse HTML in AxWebBrowser

Dim TheURL As New Uri("URL string")
AxWebBrowser1.Navigate(TheURL)

釋放元件

AxWebBrowser1.Dispose()

以元件名字取得所開啟網頁內的元件

Imports mshtml

Dim e1 As Object = AxWebBrowser1.Document
Dim ebay As Object = e1.all

ebay.item("元件name").value

ebay.item("元件name").click()

…etc

解析HTML

Dim TheLength As Integer = ebay.length
Dim i As Integer
        For i = 0 To TheLength – 1
                If LCase(ebay.item(i).tagname) = "td" Then
                    If ebay.item(i).innertext = "Buyer Email" Then
                        ….
                    End If
                End If

            If LCase(ebay.item(i).tagname) = "b" Then
               Select Case ebay.item(i).uniqueID    ‘每一個TAG都有一個uniqueID, 以TAG的位置順序安排
                    Case "ms__id76"
                        TheEmailAddress = ebay.item(i).innertext
                    Case "ms__id80"
                        TheItemName = ebay.item(i).innertext
                End Select
              End If
        Next

判斷元件是否存在

IsNothing(ebay.item("元件name"))

元件陣列, 當網頁中有數個同名的元件時

ebay.item("元件name", 0).click()    ‘第一個

ebay.item("元件name", 1).click()     ‘第二個

Parse HTML in WebBrowser

Dim ebay As HtmlDocument = WebEBay.Document ‘宣告物件
Dim dhl As HtmlDocument = WebDHL.Document

 

ebay.GetElementById("sellernotes").GetAttribute("value")  ‘取得元素值

ebay.GetElementsByTagName("a").Count ‘文件中<a>的總數

ebay.GetElementsByTagName("a").Item(i).GetAttribute("href") ‘第i個<a>

dhl.GetElementById("txtCompanyName").SetAttribute("value", TheValue) ‘設定元素內的值

 

Dim TheLength As Integer = ebay.All.Count ‘取得整份文件長度

 

範例:某個元素內包含的文字

        If TheEmailAddress = "" Then
            For i = 0 To ebay.GetElementsByTagName("td").Count – 1
                If ebay.GetElementsByTagName("td").Item(i).InnerText = "Buyer Email" Then
                    TheEmailAddress = ebay.GetElementsByTagName("td").Item(i + 1).InnerText
                    Exit For
                End If
            Next
        End If

 

範例:觸發元素內的事件

Dim InputList As HtmlElementCollection = dhl.GetElementsByTagName("input")
        For i = 0 To InputList.Count – 1
            With InputList.Item(i)
                If .GetAttribute("type") = "radio" And .GetAttribute("name") = "dropoff" And .GetAttribute("value") = "1" Then
                    .SetAttribute("checked", "true")
                    .RaiseEvent("onclick")
                    Exit For
                End If
            End With
        Next

 

dhl.InvokeScript("setPickup") ‘呼叫文件內的Script

範列

Dim dhl As HtmlDocument = WebDHL.Document
        If dhl.GetElementById("dbxSvcType").GetAttribute("selectedindex") <> 5 Then
            If MsgBox("Not ship via Ground , Continue?", MsgBoxStyle.YesNo, "Warning") = MsgBoxResult.No Then Exit Sub
        End If
        dhl.InvokeScript("goForward_onNext", New String() {"document.frmShipment"})

將圖片存進DB中

Dim con As New SqlConnection _
       ("Server=YourServer;uid=<username>;pwd=<strong password>;database=northwind")
      Dim da As New SqlDataAdapter _
       ("Select * From MyImages", con)
      Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
      Dim ds As New DataSet()

      da.MissingSchemaAction = MissingSchemaAction.AddWithKey

      Dim fs As New FileStream _
       ("C:\winnt\Gone Fishing.BMP", FileMode.OpenOrCreate, _
        FileAccess.Read)
      Dim MyData(fs.Length) As Byte
      fs.Read(MyData, 0, fs.Length)
      fs.Close()
      con.Open()
      da.Fill(ds, "MyImages")
      Dim myRow As DataRow
      myRow = ds.Tables("MyImages").NewRow()

      myRow("Description") = "This would be description text"
      myRow("imgField") = MyData
      ds.Tables("MyImages").Rows.Add(myRow)
      da.Update(ds, "MyImages")

      fs = Nothing
      MyCB = Nothing
      ds = Nothing
      da = Nothing

      con.Close()
      con = Nothing
      MsgBox ("Image saved to database")

從DB中取出圖片轉存

Dim con As New SqlConnection _
       ("Server=YourServer;uid= ;pwd=;database=northwind")
      Dim da As New SqlDataAdapter _
       ("Select * From MyImages", con)
      Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
      Dim ds As New DataSet()

      con.Open()
      da.Fill(ds, "MyImages")
      Dim myRow As DataRow
      myRow = ds.Tables("MyImages").Rows(0)

      Dim MyData() As Byte
      MyData = myRow("imgField")
      Dim K As Long
      K = UBound(MyData)

      Dim fs As New FileStream _
       ("C:\winnt\Gone Fishing2.BMP", FileMode.OpenOrCreate, _
        FileAccess.Write)
      fs.Write(MyData, 0, K)
      fs.Close()

      fs = Nothing
      MyCB = Nothing
      ds = Nothing
      da = Nothing

      con.Close()
      con = Nothing
      MsgBox ("Image retrieved")

將DB中的圖片讀到記憶體

Dim TheDS As New DataSet
        TheDS = TheDB.SelectRows("select * from Product_picture where PPID=" & ListExistPICID.Items.Item(ListExistPIC.SelectedIndex))
        Dim myRow As DataRow
        myRow = TheDS.Tables(0).Rows(0)
      
        Dim MyData() As Byte
        MyData = myRow("ThePicture")

        Dim bs As Stream = New MemoryStream()

        bs.Write(MyData, 0, MyData.Length)
        PB.Image = Image.FromStream(bs)

        bs = Nothing
        TheDS = Nothing

The latest identity ID

sql="insert into table(name) values(‘Tom’);select @@IDENTITY"
Dim id as Int32 = CType(cmd.ExecuteScalar(conn, CommandType.Text, sql),Int32)

 

if DB is publisher or subscript ==> select SCOPE_IDENTITY()

取得預存程序所回傳的資料集

step 1. 宣告SqlCommand

step 2. 設置SqlCommand執行Stored Procedute

step 3. 使用SqlCommand.ExecuteReader將結果回傳到一個SqlDataReader內

step 4. 宣告DataSet, 使用DataSet.Load將SqlReader的資料讀入DataSet中

step 5. 將DataGridView的DataSource與DataSet連結

DataTable.RowState

你的datatable裡面的每一個ROW都會有一個RowState的屬性
如果該ROW是新增則RowState="Added"
如果該ROW是有被修改過的RowState="Modifyed"
如果該ROW是被刪除的則RowState="Deleted"
如果該ROW都未被異動過的則RowState="UnChange"
然後自己下迴圈去跑Command
再異動DataTable資料的的時候要注意移除資料請用
Rows(i).Delete()

不要使用Rows.Remove(i)
前者為資料狀態改為刪除
後者則為真實將資料列刪除
另外你如果有呼叫使用AcceptChanges()的話則會將資料認可為未異動的狀態RowState="UnChange"

Prohibit Chinese IME

keyCode = 229 時，表示在輸入中文：
<INPUT TYPE="text" onKeyDown="if (event.keyCode == 229){event.returnValue = false;}">

Prohibit to input none-English

<INPUT TYPE="text" onKeyDown="if (!/[a-zA-Z]/.test(String.fromCharCode(event.keyCode))){event.returnValue = false;}">

判斷上傳檔案size

<html> 
<head> 
<meta http-equiv="Content-Language" content="zh-tw"> 
<meta http-equiv="Content-Type" content="text/html; charset=big5"> 
<title>上傳</title> 
</head> 
<script language="JavaScript"> 
<!– 
function checkFile(sizeLimit) { 
var img = new Image(); 
document.MM_returnValue = false; 
img.sizeLimit = sizeLimit; 
img.src = ‘file:///’ + document.frmMain.File1.value; 
img.onload = showImageDimensions; 
}

function showImageDimensions() {
if (this.fileSize > this.sizeLimit) {
alert(‘您所選擇的檔案大小為 ‘+ (this.fileSize/1000) +’ kb，\n超過了上傳上限 ‘ + (this.sizeLimit/1000) + ‘ kb！\n不允許您上傳喔！’);
return;
}
document.MM_returnValue = true;
}
//–>
</script>
</head>
<body oncontextmenu="window.event.returnValue=false">
<table width="100%" height="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<form ACTION="upload.asp" METHOD="POST" name="frmMain" enctype="multipart/form-data" onSubmit="checkFile(100000);return document.MM_returnValue;">
<div align="center">
圖片: <input type="file" name="File1" size="20">
<input type="submit" name="btn1" value="確定上傳">
</div>
</form>
</td>
</tr>
</table>
</body>
</html>

Cursor in TSQL

宣告DECLARE→開啟OPEN→處理→關閉CLOSE→→解除DEALLOCATE
CURSOR的宣告
DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | FAST_FORWARD | STATIC | 
KEYSET | DYNAMIC ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ] lFOR select_statement [ FOR UPDATE ]
開啟: OPEN cursor_name

LOCAL | GLOBAL : 可否適用於建立該CURSOR的程序外
FORWARD_ONLY : 預設值, 只能由第一筆資料往後讀取, 無法回頭, 無法修改資料
FAST_FORWARD : 在唯讀狀態下得到效能最佳化, 只能往後讀取資料, 無法回頭
STATIC : 可前後移動, 但資料內容無法修改 KEYSET : 可前後移動, 可修改, 但無法完全反應出變動 (例如新增一筆資料)
DYNAMIC : 可前後移動, 隨時得到最新資料, 但系統負擔最重
READ_ONLY : cursor內的資料不能更改
SCROLL_LOCKS : 當資料被讀入cursor後即被鎖定, 確保資料異動成功
OPTIMISTIC : 當更新cursor資料時才鎖定, 如果該資料已被異動過, 之後的異動不會成功
TYPE_WARNING : 當程序中內部宣告與使用者要求的型態不同時, 發出警告給使用者

FETCH
[ [ NEXT | PRIOR | FIRST | LAST |
ABSOLUTE {數值|變數} |
RELATIVE {數值|變數} ] FROM ] cursor_name [ INTO 變數#1…] Sequential Cursor的讀取 • FETCH NEXT Scroll Cursor的讀取
FETCH [NEXT | PRIOR | FIRST | LAST
| ABSOLUTE n | RELATIE n ]

異動Cursor所指表格內的資料
宣告Cursor時要加 FOR UPDATE 刪除資料： • DELETE FROM table_name WHERE CURRENT OF cursor_name 更新資料：
UPDATE table_name SET old_value = new_value WHERE CURRENT OF cursor_name

關閉: CLOSE cursor_name 解除: DEALLOCATE [GLOBAL] cursor_name 宣告CURSOR變數: lTYPE 1:
DECLARE @cur1 CURSOR
DECLARE cur2 CURSOR FOR select_statement
SET @cur1 = cur2 lTYPE 2:
DECLARE @cur3 CURSOR
SET @cur3 = CURSOR [L|G][F|F|S|K|D][R|S|O][T]
　　　　　　 FOR select_statement

Case-sensitive

select * from member where login=’" & id & "’ and convert(binary,password)=convert(binary,’" & password & "’)

select fixed records without using “TOP"

set rowcount 3

select name,salary
from salse
order by salary desc

set rowcount 0

Select table/field names

select a.name,b.* from sysobjects a, syscolumns b
where a.xtype=’U’ and a.id=b.id
order by a.name

a.name是指 table name
b.name是指 column name

select a.name as tablename,b.name as columename,c.name as typename, TheNULL=
case b.isnullable
when 1 then ‘YES’
when 0 then ‘NO’
end,
TheDefault=
case b.cdefault
when 0 then “
else (select text from syscomments where id=b.cdefault)
end
from sysobjects a, syscolumns b, systypes c
where a.xtype=’U’ and a.id=b.id and b.xtype=c.xtype
order by a.name

select a.name as tablename,b.name as columename,c.name + ‘(‘ + cast(c.length as varchar) + ‘)’ as typename,
TheDefault=
case b.cdefault
when 0 then
case isnull(b.autoval,0)
when 0 then “
else ‘identity’
end
else (select text from syscomments where id=b.cdefault)
end ,
TheNULL=
case b.isnullable
when 1 then ‘YES’
when 0 then ‘NO’
end

from sysobjects a, syscolumns b, systypes c
where a.xtype=’U’ and a.id=b.id and b.xtype=c.xtype
order by a.name

select a.name as tablename,b.name as columename,c.name + ‘(‘ +
case c.name
when ‘nvarchar’ then cast(b.length/2 as varchar)
else cast(b.length as varchar)
end + ‘)’ as typename,
TheDefault=
case b.cdefault
when 0 then
case isnull(b.autoval,0)
when 0 then “
else ‘identity’
end
else (select text from syscomments where id=b.cdefault)
end ,
TheNULL=
case b.isnullable
when 1 then ‘YES’
when 0 then ‘NO’
end

from sysobjects a, syscolumns b, systypes c
where a.xtype=’U’ and a.id=b.id and b.xtype=c.xtype
order by a.name

 

 

select name from master..syscolumns where id=object_id(‘master..sysprocesses’)

a 資料庫裡面的 b資料表所有欄位名稱
select name from a..syscolumns where id=object_id(‘a..b’)

a 資料庫裡面的所有資料表名稱
select * from a..sysobjects where xtype=’U’ and name<>’dtproperties’

Get random rows

select top 10 from Member order by NEWID()

Prohibit special characters

Code in SP

只允許存入
1. 半形數字 asc(48~57)
2. 半形英文大寫 asc(65~90)
3. 半形英文小寫 asc(97~122)
4. 中文 (附註)

附註:
用sql的ascii function可以取得中文字第一個byte的ascii碼
在big5中, 中文字分部在A440 ~ F9DC
也就是第一個byte的ascii碼介於164~249
但是249區段中有以下的特殊字元, 所以必需再過濾一下
╔ ╦ ╗╠ ╬ ╣ ╚ ╩ ╝ ╒ ╤ ╕ ╞ ╪ ╡ ╘ ╧ ╛ ╓╥ ╖ ╟ ╫ ╢ ╙ ╨ ╜ ║ ═ ╭ ╮ ╰ ╯ ▓

Declare @ASC int
Declare @CheckCount tinyint
Declare @IllegalCharacters nvarchar(250)
set @IllegalCharacters=’╔ ╦ ╗╠ ╬ ╣ ╚ ╩ ╝ ╒ ╤ ╕ ╞ ╪ ╡ ╘ ╧ ╛ ╓╥ ╖ ╟ ╫ ╢ ╙ ╨ ╜ ║ ═ ╭ ╮ ╰ ╯ ▓’
set @CheckCount=1
while (@CheckCount<=len(@CharacterName))
Begin
set @ASC=ascii(substring(@CharacterName,@CheckCount,1))
if (@ASC<48 or (@ASC>57 and @ASC<65) or (@ASC>90 and @ASC<97) or (@ASC>122 and @ASC<164) or @ASC>249)
return -3
else
Begin
if charindex(substring(@CharacterName,@CheckCount,1),@IllegalCharacters)!=0
return -3
End
set @CheckCount=@CheckCount+1
End

再附註:
以上的寫法, 包含全型的大小寫英文, 數字, 注音文, 都會被過濾出來

分頁技巧

分頁的技巧有兩種，一種是直接透過T-SQL，另一種是透過Store Procedure，在這Post出來跟大家分享一下：
T-SQL：
假設Northwind有一個Customer的Table，你需要取回41~50筆的記錄，T-SQL語法該如何作呢？

Select Top 10 CustomerID,CompanyName,ContactName,Country from Customers where CustomerID Not in
(Select top 40 CustomerID from Customers order by Country,CustomerID)
Order by Country,CustomerID

Store Procedure：
出自MSDN Magazine，是別人的智慧
CREATE PROCEDURE northwind_OrdersPaged
(
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int

– First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

– Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

– Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
OrderID int
)

– Insert into the temp table
INSERT INTO #PageIndex (OrderID)
SELECT
OrderID
FROM
Orders
ORDER BY
OrderID DESC

– Return total count
SELECT COUNT(OrderID) FROM Orders

– Return paged results
SELECT
O.*
FROM
Orders O,
#PageIndex PageIndex
WHERE
O.OrderID = PageIndex.OrderID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID

END

進位相關函數

round(數值,x) ->四捨五入至小數點x位

ceiling(數值) -> 無條件進位

floor(數值) -> 無條件捨去