[求助]数据库修改失败！ - VB.NET论坛

问题点数：0 回复次数：2

[求助]数据库修改失败！

小弟刚学不久，做功课时遇见问题，特来请教大家，为什么修改时，只能修改第一排的"公司名"？其他的都失败啊？
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
With da1
.SelectCommand = New SqlCommand
With .SelectCommand
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "select 供货商ID,公司名,联系人,联系人职务,地址,城市,区域,国家,邮编,电话,传真,网址 from dbo.Suppliers"
End With
.InsertCommand = New SqlCommand
With .InsertCommand
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "insert into dbo.Suppliers(供货商ID,公司名,联系人,联系人职务,地址,城市,区域,国家,邮编,电话,传真,网址)"
.CommandText += " values(@a,@b,@c,@d,@e,@f,@g,@h,@i,@j,@k,@l)"
With .Parameters
.Add("@a", SqlDbType.Int, 4, "供货商ID")
.Add("@b", SqlDbType.NVarChar, 40, "公司名")
.Add("@c", SqlDbType.NVarChar, 30, "联系人")
.Add("@d", SqlDbType.NVarChar, 30, "联系人职务")
.Add("@e", SqlDbType.NVarChar, 60, "地址")
.Add("@f", SqlDbType.NVarChar, 15, "城市")
.Add("@g", SqlDbType.NVarChar, 15, "区域")
.Add("@h", SqlDbType.NVarChar, 15, "国家")
.Add("@i", SqlDbType.NVarChar, 10, "邮编")
.Add("@j", SqlDbType.NVarChar, 24, "电话")
.Add("@k", SqlDbType.NVarChar, 24, "传真")
.Add("@l", SqlDbType.NText, 16, "网址")
End With
.AcceptChangesDuringFill = True
.TableMappings.Add("Table", "b1")
.MissingSchemaAction = MissingSchemaAction.AddWithKey
End With

da1.Fill(ds1)

With Me.ComboBox1
.DataSource = ds1.Tables("b1")
.ValueMember = "供货商ID"
.DisplayMember = "供货商ID"
End With
End Sub

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
With dv1
.Table = ds1.Tables("b1")
.RowFilter = "供货商ID=" & Me.ComboBox1.Text
End With
With dv1(0)
Me.TextBox1.Text = .Item("公司名")
Me.TextBox2.Text = IIf(.Item("联系人").ToString <> "", .Item("联系人"), "")
Me.TextBox3.Text = IIf(.Item("联系人职务").ToString <> "", .Item("联系人职务"), "")
Me.TextBox4.Text = IIf(.Item("地址").ToString <> "", .Item("地址"), "")
Me.TextBox5.Text = IIf(.Item("城市").ToString <> "", .Item("城市"), "")
Me.TextBox6.Text = IIf(.Item("区域").ToString <> "", .Item("区域"), "")
Me.TextBox7.Text = IIf(.Item("国家").ToString <> "", .Item("国家"), "")
Me.TextBox8.Text = IIf(.Item("邮编").ToString <> "", .Item("邮编"), "")
Me.TextBox9.Text = IIf(.Item("电话").ToString <> "", .Item("电话"), "")
Me.TextBox10.Text = IIf(.Item("传真").ToString <> "", .Item("传真"), "")
Me.TextBox11.Text = IIf(.Item("网址").ToString <> "", .Item("网址"), "")
End With
End Sub

Private Sub btn2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn2.Click
'修改按钮
With dv1
.RowFilter = "供货商ID=" & Me.ComboBox1.SelectedValue
.Table.Columns.Item("供货商ID").DefaultValue = Me.ComboBox1.SelectedValue
End With
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
With dv1(0)
.Item("公司名") = Me.TextBox1.Text
.Item("联系人") = Me.TextBox2.Text
.Item("联系人职务") = Me.TextBox3.Text
.Item("地址") = Me.TextBox4.Text
.Item("城市") = Me.TextBox5.Text
.Item("区域") = Me.TextBox6.Text
.Item("国家") = Me.TextBox7.Text
.Item("邮编") = Me.TextBox8.Text
.Item("电话") = Me.TextBox9.Text
.Item("传真") = Me.TextBox10.Text
.Item("网址") = Me.TextBox11.Text
End With
da1.Update(ds1)
End Sub

附上程序，希望高手指点，谢谢！

Pgz3mINO.rar (487.52 KB) [求助]数据库修改失败！

搜索更多相关主题的帖子: 数据库　失败　

第 2 楼

得分:0

谢谢版主，前面只是把修改部分放出来，现在放完整的，实在不明白，可以下载上面的附件。

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
With da1
.SelectCommand = New SqlCommand
With .SelectCommand
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "select 供货商ID,公司名,联系人,联系人职务,地址,城市,区域,国家,邮编,电话,传真,网址 from dbo.Suppliers"
End With

.InsertCommand = New SqlCommand
With .InsertCommand
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "insert into dbo.Suppliers(供货商ID,公司名,联系人,联系人职务,地址,城市,区域,国家,邮编,电话,传真,网址)"
.CommandText += " values(@a,@b,@c,@d,@e,@f,@g,@h,@i,@j,@k,@l)"
With .Parameters
.Add("@a", SqlDbType.Int, 4, "供货商ID")
.Add("@b", SqlDbType.NVarChar, 40, "公司名")
.Add("@c", SqlDbType.NVarChar, 30, "联系人")
.Add("@d", SqlDbType.NVarChar, 30, "联系人职务")
.Add("@e", SqlDbType.NVarChar, 60, "地址")
.Add("@f", SqlDbType.NVarChar, 15, "城市")
.Add("@g", SqlDbType.NVarChar, 15, "区域")
.Add("@h", SqlDbType.NVarChar, 15, "国家")
.Add("@i", SqlDbType.NVarChar, 10, "邮编")
.Add("@j", SqlDbType.NVarChar, 24, "电话")
.Add("@k", SqlDbType.NVarChar, 24, "传真")
.Add("@l", SqlDbType.NText, 16, "网址")
End With
End With

.UpdateCommand = New SqlCommand
With .UpdateCommand
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "update dbo.Suppliers set 公司名=@b,联系人=@c,联系人职务=@d,地址=@e,城市=@f,"
.CommandText += "区域=@g,国家=@h,邮编=@i,电话=@j,传真=@k,网址=@l where 供货商ID=@a"
With .Parameters
.Add("@a", SqlDbType.Int, 4, "供货商ID")
.Add("@b", SqlDbType.NVarChar, 40, "公司名")
.Add("@c", SqlDbType.NVarChar, 30, "联系人")
.Add("@d", SqlDbType.NVarChar, 30, "联系人职务")
.Add("@e", SqlDbType.NVarChar, 60, "地址")
.Add("@f", SqlDbType.NVarChar, 15, "城市")
.Add("@g", SqlDbType.NVarChar, 15, "区域")
.Add("@h", SqlDbType.NVarChar, 15, "国家")
.Add("@i", SqlDbType.NVarChar, 10, "邮编")
.Add("@j", SqlDbType.NVarChar, 24, "电话")
.Add("@k", SqlDbType.NVarChar, 24, "传真")
.Add("@l", SqlDbType.NText, 16, "网址")
End With
End With

.DeleteCommand = New SqlCommand
With .DeleteCommand
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "delete dbo.Suppliers where 供货商ID=@a"
.Parameters.Add("@a", SqlDbType.Int, 4, "供货商ID")
End With
.AcceptChangesDuringFill = True
.TableMappings.Add("Table", "b1")
.MissingSchemaAction = MissingSchemaAction.AddWithKey
End With

da1.Fill(ds1)

With Me.ComboBox1
.DataSource = ds1.Tables("b1")
.ValueMember = "供货商ID"
.DisplayMember = "供货商ID"
End With
End Sub

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
With dv1
.Table = ds1.Tables("b1")
.RowFilter = "供货商ID=" & Me.ComboBox1.Text
End With
With dv1(0)
Me.TextBox1.Text = .Item("公司名")
Me.TextBox2.Text = IIf(.Item("联系人").ToString <> "", .Item("联系人"), "")
Me.TextBox3.Text = IIf(.Item("联系人职务").ToString <> "", .Item("联系人职务"), "")
Me.TextBox4.Text = IIf(.Item("地址").ToString <> "", .Item("地址"), "")
Me.TextBox5.Text = IIf(.Item("城市").ToString <> "", .Item("城市"), "")
Me.TextBox6.Text = IIf(.Item("区域").ToString <> "", .Item("区域"), "")
Me.TextBox7.Text = IIf(.Item("国家").ToString <> "", .Item("国家"), "")
Me.TextBox8.Text = IIf(.Item("邮编").ToString <> "", .Item("邮编"), "")
Me.TextBox9.Text = IIf(.Item("电话").ToString <> "", .Item("电话"), "")
Me.TextBox10.Text = IIf(.Item("传真").ToString <> "", .Item("传真"), "")
Me.TextBox11.Text = IIf(.Item("网址").ToString <> "", .Item("网址"), "")
End With
End Sub

Private Sub btn2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn2.Click
'删除按钮
With dv1
.RowFilter = "供货商ID=" & Me.ComboBox1.SelectedValue
.Table.Columns.Item("供货商ID").DefaultValue = Me.ComboBox1.SelectedValue
End With
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'确定按钮
With dv1(0)
.Item("公司名") = Me.TextBox1.Text
.Item("联系人") = Me.TextBox2.Text
.Item("联系人职务") = Me.TextBox3.Text
.Item("地址") = Me.TextBox4.Text
.Item("城市") = Me.TextBox5.Text
.Item("区域") = Me.TextBox6.Text
.Item("国家") = Me.TextBox7.Text
.Item("邮编") = Me.TextBox8.Text
.Item("电话") = Me.TextBox9.Text
.Item("传真") = Me.TextBox10.Text
.Item("网址") = Me.TextBox11.Text
End With
da1.Update(ds1)
End Sub

第 3 楼

得分:0

谢谢版主，换了种写法，问题解决了。
在添加按钮（btn1）里加个 Button2.TabStop = 1
在修改按钮（btn2）里加个 Button2.TabStop = 2
在确定按钮（Button2）里写：
Try

Me.BindingContext(dv1).EndCurrentEdit()
If Button2.TabStop = 1 Then
Me.BindingContext(dv1).AddNew()
ds1.HasChanges(DataRowState.Added)
Else
If Button2.TabStop = 2 Then
ds1.HasChanges(DataRowState.Added)
End If
End If
da1.Update(ds1)
Catch ex As Exception
Finally
End Try
问题就解决了。