最佳解答者
具有函數給值的欄項的新增問題

問題
-
大大MVP您好
這問題不好描述,大意上是,我設計的Table有十個欄位,其中有一個index,Primary Key,自動給號,還有其中一個,CreatedDate,採用函數getdate()給值。
程式端,我用TableAdapter設計,製作相關的dataset.xsd,執行期間我安排了一段程式碼,經過一段邏輯運算後,要連續新增數筆資料,我先定義了一個該TableRow的區域變數,用TableAdapter去NewRow(),然後在此變數下的欄位屬性設定所要的值,所有資料列處理完後,最後,就是要異動到資料庫,我執行了Update(_myRow)的批次更新方法。
問題來了,index欄位的值,我要怎麼給?CreatedDate的值要怎麼給?因為,index欄位的值,我怎麼給都會發生Run-Time Error,因為該值是在資料庫階段才會指派。
麻煩囉
解答
-
Dear Marson,
根據您的問題,我用 VS 2005 寫了一段 Code 測試 (但不確定與您的程式碼相同),是可以正常作業的。
1. Table Schema
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ADO_ISSUE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ADO_ISSUE]
GOCREATE TABLE [dbo].[ADO_ISSUE] (
[Identies] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [char] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[EMP_NAME] [char] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[CREATE_DATE] [datetime] Default getdate() Not NULL
) ON [PRIMARY]
GO2. Code:
Private Sub Add_DataRow()
Dim cn As New SqlClient.SqlConnection
Dim da As SqlClient.SqlDataAdapter
Dim dt As New Data.DataTable
Dim dtRow As Data.DataRowcn.ConnectionString = "Server=rayyenxpp;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=Test"
cn.Open()da = New SqlClient.SqlDataAdapter("Select EMP_ID,EMP_NAME,CREATE_DATE from ADO_ISSUE", cn)
da.Fill(dt)
dtRow = dt.NewRow()
dtRow("EMP_ID") = "000001"
dtRow("EMP_NAME") = "Bill Gates"
dt.Rows.Add(dtRow)Dim builder As SqlCommandBuilder = New SqlCommandBuilder(da)
da.Update(dt)'Me.DataGridView1.DataSource = dt
'Me.DataBind()cn.Close()
cn = Nothing
End Sub3. 結果:
Identies,EMP_ID,EMP_NAME,CREATE_DATE
1,000001,Bill Gates ,2007-03-09 19:02:48.850
2,000001,Bill Gates ,2007-03-09 19:02:49.880
3,000001,Bill Gates ,2007-03-09 19:02:50.630
4,000001,Bill Gates ,2007-03-09 19:02:51.147
5,000001,Bill Gates ,2007-03-09 19:02:51.600
6,000001,Bill Gates ,2007-03-09 19:02:52.567
7,000001,Bill Gates ,2007-03-09 19:02:53.770Thanks! Have a nice weekend!
Ray Yen
微軟技術支援處
所有回覆
-
Dear Marson,
根據您的問題,我用 VS 2005 寫了一段 Code 測試 (但不確定與您的程式碼相同),是可以正常作業的。
1. Table Schema
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ADO_ISSUE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ADO_ISSUE]
GOCREATE TABLE [dbo].[ADO_ISSUE] (
[Identies] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [char] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[EMP_NAME] [char] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[CREATE_DATE] [datetime] Default getdate() Not NULL
) ON [PRIMARY]
GO2. Code:
Private Sub Add_DataRow()
Dim cn As New SqlClient.SqlConnection
Dim da As SqlClient.SqlDataAdapter
Dim dt As New Data.DataTable
Dim dtRow As Data.DataRowcn.ConnectionString = "Server=rayyenxpp;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=Test"
cn.Open()da = New SqlClient.SqlDataAdapter("Select EMP_ID,EMP_NAME,CREATE_DATE from ADO_ISSUE", cn)
da.Fill(dt)
dtRow = dt.NewRow()
dtRow("EMP_ID") = "000001"
dtRow("EMP_NAME") = "Bill Gates"
dt.Rows.Add(dtRow)Dim builder As SqlCommandBuilder = New SqlCommandBuilder(da)
da.Update(dt)'Me.DataGridView1.DataSource = dt
'Me.DataBind()cn.Close()
cn = Nothing
End Sub3. 結果:
Identies,EMP_ID,EMP_NAME,CREATE_DATE
1,000001,Bill Gates ,2007-03-09 19:02:48.850
2,000001,Bill Gates ,2007-03-09 19:02:49.880
3,000001,Bill Gates ,2007-03-09 19:02:50.630
4,000001,Bill Gates ,2007-03-09 19:02:51.147
5,000001,Bill Gates ,2007-03-09 19:02:51.600
6,000001,Bill Gates ,2007-03-09 19:02:52.567
7,000001,Bill Gates ,2007-03-09 19:02:53.770Thanks! Have a nice weekend!
Ray Yen
微軟技術支援處
-
Dear Marson,
根據您的問題,我用 VS 2005 寫了一段 Code 測試 (但不確定與您的程式碼相同),是可以正常作業的。
1. Table Schema
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ADO_ISSUE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ADO_ISSUE]
GOCREATE TABLE [dbo].[ADO_ISSUE] (
[Identies] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [char] (6) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[EMP_NAME] [char] (20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[CREATE_DATE] [datetime] Default getdate() Not NULL
) ON [PRIMARY]
GO2. Code:
Private Sub Add_DataRow()
Dim cn As New SqlClient.SqlConnection
Dim da As SqlClient.SqlDataAdapter
Dim dt As New Data.DataTable
Dim dtRow As Data.DataRowcn.ConnectionString = "Server=rayyenxpp;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=Test"
cn.Open()da = New SqlClient.SqlDataAdapter("Select EMP_ID,EMP_NAME,CREATE_DATE from ADO_ISSUE", cn)
da.Fill(dt)
dtRow = dt.NewRow()
dtRow("EMP_ID") = "000001"
dtRow("EMP_NAME") = "Bill Gates"
dt.Rows.Add(dtRow)Dim builder As SqlCommandBuilder = New SqlCommandBuilder(da)
da.Update(dt)'Me.DataGridView1.DataSource = dt
'Me.DataBind()cn.Close()
cn = Nothing
End Sub3. 結果:
Identies,EMP_ID,EMP_NAME,CREATE_DATE
1,000001,Bill Gates ,2007-03-09 19:02:48.850
2,000001,Bill Gates ,2007-03-09 19:02:49.880
3,000001,Bill Gates ,2007-03-09 19:02:50.630
4,000001,Bill Gates ,2007-03-09 19:02:51.147
5,000001,Bill Gates ,2007-03-09 19:02:51.600
6,000001,Bill Gates ,2007-03-09 19:02:52.567
7,000001,Bill Gates ,2007-03-09 19:02:53.770Thanks! Have a nice weekend!
Ray Yen
微軟技術支援處