程式設計與資料庫

What can I do to resolve a “Row not found or changed” Exception in LINQ to SQL on a SQL Server?

2012-07-25T16:07:00.000+08:00

The query generates the following SQL: UPDATE [Sessions] SET [Is_Active] = @p0, [Disconnected] = @p1 WHERE 0 = 1 -- @p0: Input Boolean (Size = 0; Prec = 0; Scale = 0) [False] -- @p1: Input DateTime (Size = 0; Prec = 0; Scale = 0) [9/4/2008 5:12:02 PM] -- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8 The obvious problem is the WHERE 0=1, After the record was loaded, I've confirmed that all the properties in the "deviceSessionRecord" are correct to include the primary key. Also when catching the "ChangeConflictException" there is no additional information about why this failed. I've also confirmed that this exception get's thrown with exactly one record in the database (the record I'm attempting to update) I solved this error by redragging over a table from the server explorer to the designer and re-building. or change nullable value.

SQL資料庫用Windows認證出現:Cannot Generate SSPI Context Error

2012-07-05T10:17:00.001+08:00

Cannot Generate SSPI Context Error @ SQL 遇到一個SQL問題。經驗中，這鐵定是使用NT整合式認證連線SQL資料庫失敗所致。只要調整時間將Client & Db一致即可. 或者用setspn的方式也可

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

2012-01-13T17:24:00.004+08:00

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) 範例

2011-12-20T11:42:00.001+08:00

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

2011-12-16T16:20:00.001+08:00

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

2009-09-04T15:12:00.002+08:00

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

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

判斷IP是哪個國家的位址

2009-07-24T11:44:00.002+08:00

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

2009-05-12T09:29:00.001+08:00

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的設定方式

2009-01-13T16:44:00.001+08:00

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無法更新查詢

2007-01-04T09:28:00.000+08:00

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

2006-12-28T15:14:00.000+08:00

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

VS2003 VS2005無法啟動偵錯

2006-10-23T08:56:00.000+08:00

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)

2006-10-18T19:14:00.000+08:00

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出現記憶體毀損的解決方法

2006-10-18T19:13:00.000+08:00

部分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)