ASP.net網頁出現URL-encoded form data is not valid的處理方法

HttpException (0x80004005): The URL-encoded form data is not valid 於web.config中加入

<appsettings>
<add key="aspnet:MaxHttpCollectionKeys" value="5000"></add>
</appsettings>

如果還超過則要將該值放大

CDC (Change Data Capture) 範例

EXEC sp_cdc_enable_db
select name, is_cdc_enabled from sys.databases
exec sp_cdc_enable_table 'dbo','tblA',@role_name='sa'
select name, type, type_desc, is_tracked_by_cdc from sys.tables
select * from tbla
insert into tblA values ('6','aga')
select * from cdc.dbo_tblA_CT --__$operation欄位1表示DELETE 2表示INSERT 3表示UPDATE之前 4表示UPDATE之後
Declare @begin_LSN binary(10),@End_LSN binary(10)
select @begin_LSN=sys.fn_cdc_get_min_lsn('dbo_tblA')
select @End_LSN=sys.fn_cdc_get_max_lsn()
select @begin_LSN,@End_LSN
select * from cdc.fn_cdc_get_net_changes_dbo_tblA(@begin_LSN,@End_LSN,'ALL');
select * from cdc.fn_cdc_get_all_changes_dbo_tblA(@begin_LSN,@End_LSN,'ALL');
select * from cdc.lsn_time_mapping --用來查看LSN的時間
exec test.sys.sp_cdc_disable_table @source_schema='dbo',@source_name='tblA',@capture_instance='dbo_tblA'
EXEC sp_cdc_disable_db

四種 EXCEL 讀寫.Net Library without Excel.Application

ExcelPackage http://excelpackage.codeplex.com/
EPPlus http://epplus.codeplex.com/
NPOI http://code.google.com/p/npoi/
ExcelLibrary http://code.google.com/p/excellibrary/

各有優缺點
目前要同時支援xls vs xlsx只有ExcelLibrary
NPOI支援xls
ExcelPackage支援xlsx (office 2007之後)
EPPlus是ExcelPackage改善來的

如何解SQL lock

首先透過 sp_lock 去查詢被 Lock 的資料有哪些,
因為他不會直接列出 Table 名稱, 所以可透過select object_id('TABLE_NAME') 或是 select object_name('OBJECT_ID') 去查出該 Lock 是屬於哪個 Table 的

直接下kill spid...spid為sp_lock查出的第一個欄位
kill xxx

判斷IP是哪個國家的位址

http://www.maxmind.com/app/geoip_country
http://ip-to-country.webhosting.info/downloads/ip-to-country.csv.zip

這兩個網站可以下載到ip對應的國家..
但是
資料要自己從CSV匯入到SQL內

ip="168.95.1.1"
arr=split(ip)
sum = arr(0) * 16777216 + arr(1) * 65536 + arr(2) * 256 + arr(3)
SELECT country, name FROM ip_country WHERE sum BETWEEN begin_num AND end_num

Error: Common Language Runtime...Application has generated an exception that could not be handled

The Microsoft .NET Framework 1.1 Cache must be cleared.

1. Go to the Control Panel.
2. Double-click Administrative Tools.
3. Double-click Microsoft .NET Framework 1.1 Configuration.
4. From the left panel, click Assembly Cache.
5. From the right panel, click View List of Assemblies...
6. CRTL+A 刪除全部
7. 重新執行程式

SqlDependency的設定方式

Private Delegate Sub UICallBack()
Private changeCnt As Integer


Private Sub getdata()
Dim cn As New SqlClient.SqlConnection("Data Source=localhost;Initial Catalog=MIS;Persist Security Info=True;User ID=sa;password=")
cn.Open()
Dim strSQL As String = "select test from dbo.tblTest where test>=2"
Dim da As New SqlClient.SqlDataAdapter(strSQL, cn)
Dim sqld As New SqlClient.SqlDependency(da.SelectCommand)

SqlDependency.Start(cn.ConnectionString)
ds.Clear()
da.Fill(ds, "test")
DataGridView1.DataSource = ds
DataGridView1.DataMember = "test"
DataGridView1.Refresh()
AddHandler sqld.OnChange, AddressOf Me.dataChanged


End Sub

Private Sub rebind()
getdata()
lblmsg.Text = ds.Tables("test").Rows.Count

End Sub

Private Sub dataChanged(ByVal sender As Object, ByVal e As SqlNotificationEventArgs)
changeCnt += 1
Me.Invoke(New UICallBack(AddressOf rebind))

End Sub

主要不能使用*來選取所有欄位...並且一定要有where 篩選條件..
不然dataChanged會形成無窮迴圈

ACCESS無法更新查詢

ACCESS無法更新查詢
1.如果用ASP出現此問題一般為檔案權限的問題
將之檔案唯獨取消即可
2.如果用在進階SQL 語法根據某個Table or View的值更新update某個Table的值
如果是update Table by Table語法對了，則沒有問題

如果是update Table by View則一定會出現ACCESS無法更新查詢..必須改成真實Table才可正常...

ex.
UPDATE autoymd INNER JOIN viwAUTOYMD ON (autoymd.TITLE = viwAUTOYMD.vtype)SET autoymd.LASTNO = CInt(viwAUTOYMD.[no])

必須將view改成真實table才不會出現此錯誤

一些小小經驗

OleDbConnection Good, SQLConnection Bad

vs2003
\WINNT\SYSTEM32\COM\comadmin.dll lost
所以找光碟或找其他台電腦拷貝過來即可正常

VS2003 VS2005無法啟動偵錯

v2.0.50727為.net版本 或是你也可以在command prompt執行下列指令 set path=%path%;%Windir%\Microsoft.NET\Framework\v2.0.50727 aspnet_regiis -s W3SVC/1/ROOT/<你的virtual directory name> 或 VS2005無法啟動偵錯 IP的問題 則於IIS內改成為指定IP即可.....

如何在DATA REPORT抓在按下Print的事件(VB6)

Private Sub DataReport_AsyncProgress(ByVal JobType As MSDataReportLib.AsyncTypeConstants, ByVal Cookie As Long, ByVal PageCompleted As Long, ByVal TotalPages As Long)
If JobType = MSDataReportLib.AsyncTypeConstants.rptAsyncPrint Then
SendMail
End If
End Sub

jobtype有列印、匯出與預覽的事件
如此方便許多..
而如果用Crystal Report要抓到Print的事件
則只能自訂按鈕來取代Crystal Report內建的列印按鈕

部分Com轉.Net出現記憶體毀損的解決方法

部分Com轉.Net出現記憶體毀損的解決方法
改成late binding
ex.將dim a as hctaddr2.addr_Compare2改成以下原始碼
Dim a As Type = Type.GetTypeFromProgID("hctaddr2.addr_Compare2")
Dim hct As Object = Activator.CreateInstance(a)