VB.Net窗体应用程序数据库表更新

Question

我正在尝试制作一个小型应用程序，允许用户读取描述仓库库存的表的内容，根据指示物品所在仓库的2行和指定的条形码进行搜索，我已经通过使用绑定源和数据网格视图成功地使用了条形码，通过将条形码和位置作为来自两个框的字符串的查询来更新视图。

为了满足我的基本目标，这个应用程序需要的第二部分是有一种方法来添加新行并将其存储到数据库的原始表中，这样用户就可以直接独立于仓库添加新项目。

到目前为止，我遇到了两个问题：我需要一个表示顺序ID的主键，但我不知道如何生成顺序递增的ID，我设法通过使用top 1 order by desc查询组合获得了第一个添加ID，但添加新行后数据没有得到更新，这会产生错误，因为它试图添加另一行具有相同主键值的行。我遇到的第二个问题是：网格视图会根据我在文本框中输入的数据进行相应的更改，我设置文本框是为了收集表的各种值，但数据库上的表本身没有显示任何更改，只保留我在创建时输入的测试数据。

Public Class AddItems
Private Sub AddItems_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'MagazzinoDataSet.LastUsedID' table. You can move, or remove it, as needed.
Me.LastUsedIDTableAdapter.LastUsedID(Me.MagazzinoDataSet.LastUsedID)
'TODO: This line of code loads data into the 'MagazzinoDataSet.Stock' table. You can move, or remove it, as needed.
Me.StockTableAdapter.Fill(Me.MagazzinoDataSet.Stock)
'TODO: This line of code loads data into the 'MagazzinoDataSet.AddWarehouseList' table. You can move, or remove it, as needed.
Me.AddWarehouseListTableAdapter.AddWarehouseList(Me.MagazzinoDataSet.AddWarehouseList)
'TODO: This line of code loads data into the 'MagazzinoDataSet.WarehouseList' table. You can move, or remove it, as needed.
Me.WarehouseListTableAdapter.Fill(Me.MagazzinoDataSet.WarehouseList)
'TODO: This line of code loads data into the 'MagazzinoDataSet.Stock' table. You can move, or remove it, as needed.
Me.StockTableAdapter.Fill(Me.MagazzinoDataSet.Stock)
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim R As DataRow = MagazzinoDataSet.Tables("Stock").NewRow()
R("Supplier") = Supplier.Text
R("Producer_code") = ProducerCode.Text
R("Barcode") = Barcode.Text
R("Comp_name") = ComponentName.Text
R("Warehouse") = Warehouse.Text
R("Internal_Code") = InternalCode.Text
R("Description_IT") = ITDescr.Text
R("Description_EN") = ENDescr.Text
'R("ID") = NextID.SelectedValue <- this would be an hidden uneditable multibox containing the product of the query finding the next value to be inserted in the table (basically last ID + 1, nothing fancy)"ID" would be the primary key of this table
R("Quantity") = "0"
MagazzinoDataSet.Tables("Stock").Rows.Add(R)
DataGridView1.DataSource = MagazzinoDataSet.Stock
End Sub
End Class

综上所述：

• 如何更新数据库表以包含新行
• 有没有一种聪明的方法可以找到最后一个值，将其增加1以获得下一个值并在插入新行时更新它，这样就不会出现两行主键值相同的行，从而产生错误

Answer 1

要在Db中设置增量ID，假设您有权访问SQL Server Management Studio，在表的设计中，对于ID列，在列属性中，向下滚动到"标识规范"，并将(is Identity(设置为"是"。

要添加新行，我使用以下代码：

Using NotesDS As New DataSet
Using NotesDA As New SqlDataAdapter With {.SelectCommand = New SqlCommand With {.Connection = SQLDBConnection, .CommandText = "SELECT * FROM Notes WHERE ID = " & ID}}
NotesDA.Fill(NotesDS, "Notes")
Using NotesDV As New DataView(NotesDS.Tables("Notes"))
Using NoteBuilder As New SqlCommandBuilder(NotesDA) With {.QuotePrefix = "[", .QuoteSuffix = "]"}                        
If NotesDV.Count = 0 Then                             
Dim NoteDRV As DataRowView = NotesDV.AddNew                       
NoteDRV.Item("UserName") = UserName
NoteDRV.Item("Note") = Note
NoteDRV.Item("NoteDate") = NoteDate
NoteDRV.Item("CompanyCode") = CompanyCode
NoteDRV.EndEdit()
NotesDA.UpdateCommand = NoteBuilder.GetUpdateCommand
NotesDA.Update(NotesDS, "Notes")
End If
End Using
End Using
End Using
End Using

显然，修改以适合您的表和列名。

如果您需要检索要显示的ID，您可以在更新中添加一个处理程序，如：

Public Sub GenericOnRowUpdated(sender As Object, e As System.Data.SqlClient.SqlRowUpdatedEventArgs)        
Dim newID As Integer = 0
Dim idCMD As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT @@IDENTITY", SQLDBConnection)
If e.StatementType = StatementType.Insert Then            
newID = CInt(idCMD.ExecuteScalar())
e.Row("ID") = newID
End If
End Sub

和使用类似：

AddHandler NotesDA.RowUpdated, New SqlRowUpdatedEventHandler(AddressOf GenericOnRowUpdated)
NotesDA.Update(NotesDS, "Notes")
NewID = NoteDRV.Item("ID")

编辑

修改并解释如下的第一个示例：

'Declare you connection to the SQL dB. Connection String looks like "Data Source=192.168.71.10dBName; Initial Catalog=dBName; User ID=USER; Password='PASSWORD!';MultipleActiveResultSets=true"  -  You may well already have an open connection, and can use that instead. Not sure what your 
StockBindingSource is...
Dim oConn As New SqlConnection("CONNECTION STRING")
'Open the connection
oConn.Open()
'Declare Your DataAdapter and initialise using your connection
Dim DA As New SqlDataAdapter With {.SelectCommand = New SqlCommand With {.Connection = oConn, .CommandText = "SELECT * FROM Stock WHERE ID=0"}}
'Declare you DataSet
Dim DS As New DataSet
'Fill Your DataSet with the Stock table from your DataAdapter
DA.Fill(DS, "Stock")
'Declare a DataView for easy use (really the same as using DS.Tables("Stock").DefaultView)
Dim DV As New DataView(DS.Tables("Stock"))
'Declare a CommandBuilder and initialise with your DataAdapter. This will now watch for changes made to your data and build the appropriate SQL UPDATE/INSERT/DELETE command. the "[" and "]" are in case any column names use reserved words
Dim Builder As New SqlCommandBuilder(DA) With {.QuotePrefix = "[", .QuoteSuffix = "]"}
'Decalre a DataRowView for data population, based on your DataView table structure
Dim R As DataRowView = DV.AddNew()
'Populate the fileds with your Form data
R("Supplier") = Supplier.Text
R("Producer_code") = ProducerCode.Text
R("Barcode") = Barcode.Text
R("Comp_name") = ComponentName.Text
R("Warehouse") = Warehouse.Text
R("Internal_Code") = InternalCode.Text
R("Description_IT") = ITDescr.Text
R("Description_EN") = ENDescr.Text
R("Quantity") = "0"
'Notify that the edit has finished
R.EndEdit()
'Get the SQL command from the CommandBuilder 
DA.UpdateCommand = Builder.GetUpdateCommand()
'Execute the update (in this case it will be an INSERT) 
DA.Update(DS, "Stock")