DataAdapter.acceptchangesduringupdate问题
如果将dataadapter.acceptchangesduringupdate设为false,则插入一行至datatable后,再执行dataAdapter.update(datatable)后,IDE不会执行datarow.acceptchange(改插入行的rowstate也不会由added变为unchanged),这些是MSDN上的理论,但实际操作时,发现根本不是这样,想弄明白到底何处出错。代码,截图,数据库在跟帖中会贴出来 --------------------编程问答--------------------Imports System.Data.SqlClient--------------------编程问答-------------------- 窗口控件:
Public Class Form1
Public DS As New DataSet
Public DV As DataView
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
DisableControl()
btnInsert.Text = "插入"
Using Cn As New SqlConnection
Dim Cm As New SqlCommand
Dim i As Object
Dim Id As Integer
Cn.ConnectionString = GetC()
Cm.CommandText = "select max(id) from person"
Cm.CommandType = CommandType.Text
Cm.Connection = Cn
Cn.Open()
i = Cm.ExecuteScalar
If i Is DBNull.Value Then
Id = 1
Else
Id = CType(i, Integer) + 1
End If
Dim Da As SqlDataAdapter = GetDataAdapter(Cn)
Da.Fill(DS)
End Using
dgv.AutoGenerateColumns = False
dgv.Columns.Add(New DataGridViewTextBoxColumn)
dgv.Columns.Add(New DataGridViewTextBoxColumn)
dgv.Columns(0).HeaderText = "姓"
dgv.Columns(0).DataPropertyName = "secondname"
dgv.Columns(0).Width = dgv.Width / 2
dgv.Columns(1).HeaderText = "名"
dgv.Columns(1).DataPropertyName = "firstname"
dgv.Columns(1).Width = dgv.Width - dgv.Width / 2
dgv.AllowUserToAddRows = False
dgv.SelectionMode = DataGridViewSelectionMode.FullRowSelect
dgv.RowHeadersVisible = False
DV = DS.Tables("person").DefaultView
dgv.DataSource = DV
End Sub
Private Function GetC() As String
Dim SB As New SqlConnectionStringBuilder
SB.DataSource = "ruben" '测试代码时,这里要改成你自己电脑的名字
SB.InitialCatalog = "payables"
SB.IntegratedSecurity = True
Return SB.ConnectionString
End Function
Private Sub btnInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnInsert.Click
If btnInsert.Text = "插入" Then
btnInsert.Text = "保存"
EnableControl()
Else
If String.IsNullOrEmpty(txtFirstName.Text.Trim) And String.IsNullOrEmpty(txtSecondName.Text.Trim) Then
btnInsert.Text = "插入"
DisableControl()
Exit Sub
End If
Using Cn As New SqlConnection
Dim i As Object
Dim Id As Integer
Dim Da As SqlDataAdapter
Cn.ConnectionString = GetC()
Dim Cm As SqlCommand = New SqlCommand("select max(id) from person", Cn)
Cm.CommandType = CommandType.Text
Cm.Connection = Cn
Cn.Open()
i = Cm.ExecuteScalar
If i Is DBNull.Value Then
Id = 1
Else
Id = CType(i, Integer) + 1
End If
Da = GetDataAdapter(Cn)
Da.AcceptChangesDuringUpdate = False '此处,我已经设定了sqldataadapter用update更新Dataset后,不调用
Dim Dr As DataRow
Dr = DS.Tables(0).NewRow
Dr("ID") = Id ' 数据库结构 id int not null,(注意不是auto increment)
Dr("firstname") = txtFirstName.Text.Trim ' firstname nvarchar(50),
Dr("secondname") = txtSecondName.Text.Trim ' secondname nvarchar(50)
DS.Tables(0).Rows.Add(Dr)
Try
Da.Update(DS)
If Dr.RowState = DataRowState.Added Then
MsgBox("dr的状态为added")
End If
DS.Tables(0).AcceptChanges()
Catch ex As Exception
DS.Tables(0).RejectChanges()
MsgBox("wrong")
End Try
btnInsert.Text = "插入"
txtFirstName.Clear()
txtSecondName.Clear()
DisableControl()
End Using
End If
End Sub
Private Sub DisableControl()
txtFirstName.ReadOnly = True
txtFirstName.BackColor = Color.White
txtSecondName.ReadOnly = True
txtSecondName.BackColor = Color.White
End Sub
Private Sub EnableControl()
txtFirstName.ReadOnly = False
txtFirstName.BackColor = Color.Khaki
txtSecondName.ReadOnly = False
txtSecondName.BackColor = Color.Khaki
End Sub
Private Function GetDataAdapter(ByRef Cn As SqlConnection) As SqlDataAdapter
Dim Da As New SqlDataAdapter
Da.SelectCommand = New SqlCommand("select * from person", Cn)
Da.InsertCommand = New SqlCommand("insert into person values(@id,@firstname,@secondname)", Cn)
Da.InsertCommand.Parameters.Clear()
Da.InsertCommand.Parameters.Add("@id", SqlDbType.Int, 7, "id")
Da.InsertCommand.Parameters.Add("@firstname", SqlDbType.NVarChar, 50, "firstname")
Da.InsertCommand.Parameters.Add("@secondname", SqlDbType.NVarChar, 50, "secondname")
Da.UpdateCommand = New SqlCommand("update person set firstname=@firstname,secondname=@secondname where id=@id", Cn)
Da.UpdateCommand.Parameters.Clear()
Da.UpdateCommand.Parameters.Add("@firstname", SqlDbType.NVarChar, 50, "firstname")
Da.UpdateCommand.Parameters.Add("@secondname", SqlDbType.NVarChar, 50, "secondname")
Da.UpdateCommand.Parameters.Add("@id", SqlDbType.Int, 7, "id")
Da.DeleteCommand = New SqlCommand("delete from person where id=@id", Cn)
Da.DeleteCommand.Parameters.Clear()
Da.DeleteCommand.Parameters.Add("@id", SqlDbType.Int, 7, "id")
Da.TableMappings.Add("Table", "person")
Return Da
End Function
End Class
控件名字自上至下依次为(不包含label):
dgv
btnInsert
txtSecondname
txtFirstName
数据库结构(我的是sql server 2008 R2),如果你的也是这个版本,请直接附加我的mdf文件即可
请注意,ID不是auto increment
--------------------编程问答-------------------- MDF文件不能上传,我就不附上了。 如果此代码在你的机器上能够执行 MsgBox("dr的状态为added"),那么说明你的机器上不存在我说的问题,如果你修改了相关代码,使得这句能够执行,请告诉我修改部分的代码
补充:.NET技术 , VB.NET