SQL 将 datagridview 的所有数据插入到数据库 vb.net

Insert all data of a datagridview to database vb.net

提问by user3496755

    Dim Con As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Music_Sales_Database.mdb;")
    Dim Com As OleDbCommand
    Dim SaleCode As Integer
    Dim MusicID As String
    Dim SubTotalPrice As Decimal
    Dim Copies1 As Integer
    Dim STR1 As String

    SaleCode = 1

    Com = New OleDbCommand
    Com.Connection = Con

    For x As Integer = 0 To SalesDataGridView.Rows.Count - 1
        MusicID = SalesDataGridView.Rows(x).Cells(0).Value
        SubTotalPrice = SalesDataGridView.Rows(x).Cells(5).Value
        Copies1 = SalesDataGridView.Rows(x).Cells(3).Value
        STR1 = "INSERT INTO Sales(Sales_ID, Sales_Date, Copies, Music_ID, Staff_ID, Total_Price) VALUES (@Sales_ID, @Sales_Date, @Copies, @Music_ID, @Staff_ID, @Total_Price)"
        Dim Comm As New OleDbCommand(STR1, Con)
        Comm.Parameters.AddWithValue("@Sales_ID", SaleCode)
        Comm.Parameters.AddWithValue("@Sales_Date", txtDateAndTime)
        Comm.Parameters.AddWithValue("@Copies", Copies1)
        Comm.Parameters.AddWithValue("@Music_ID", MusicID)
        Comm.Parameters.AddWithValue("@Staff_ID", txtStaff_ID)
        Comm.Parameters.AddWithValue("@Total_Price", SubTotalPrice)
        'Command.ExecuteNonQuery()
        Comm.Dispose()
    Next
    Connection.Close()

Hallo to all my senior, I don't know why it is no any error showing and can't save it in Access Database.

大家好，我不知道为什么没有显示任何错误并且无法将其保存在 Access 数据库中。

The whole code is in the button, I explain how I want my the program works:

整个代码在按钮中，我解释了我希望我的程序如何工作：

1.) I have a unbound datagridview that can add data from few textbox. 2.) A button called Check - Out, this button is for passing my datagridview data to Access Database.....this is the problem I face.....Can somebody help me to solve it.....

1.) 我有一个未绑定的 datagridview，可以从几个文本框中添加数据。2.) 一个名为Check - Out 的按钮，这个按钮用于将我的datagridview 数据传递到Access 数据库.....这是我面临的问题.....谁能帮我解决它.....

Thx a lot...

多谢...

I also referred to the this link, but I'm not too familiar with C# Insert all data of a datagridview to database at once

我也提到了这个链接，但我对 C# 不太熟悉将 datagridview 的所有数据一次性插入数据库

回答by jmcilhinney

You're making things more complex than they need to be. Just create a DataTableand bind it to the grid. When it comes time to save the data, it takes one call to the Updatemethod of a data adapter to save the lot. You use the same data adapter to generate the schema in the DataTableby calling FillSchemaand then use a command builder to generate the INSERTcommand or you can build the schema and the INSERTcommand manually. Here are some examples:

你让事情变得比他们需要的更复杂。只需创建一个DataTable并将其绑定到网格。当需要保存数据时，只需调用Update一次数据适配器的方法即可保存大量数据。您可以使用相同的数据适配器DataTable通过调用生成架构FillSchema，然后使用命令生成器生成INSERT命令，或者您可以INSERT手动生成架构和命令。这里有些例子：

http://www.vbforums.com/showthread.php?469872-Retrieving-and-Saving-Data-in-Databases&highlight=

http://www.vbforums.com/showthread.php?469872-Retrieving-and-Saving-Data-in-Databases&highlight=

回答by jmcilhinney

you were required to open connection before the for loop and remove the comment at Command.ExecuteNonQuery()

您需要在 for 循环之前打开连接并删除 Command.ExecuteNonQuery() 处的注释

your code will be as shown below

您的代码将如下所示

Dim Con As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Music_Sales_Database.mdb;")
Dim Com As OleDbCommand
Dim SaleCode As Integer
Dim MusicID As String
Dim SubTotalPrice As Decimal
Dim Copies1 As Integer
Dim STR1 As String

SaleCode = 1

Com = New OleDbCommand
Com.Connection = Con
Connection.open()
For x As Integer = 0 To SalesDataGridView.Rows.Count - 1
    MusicID = SalesDataGridView.Rows(x).Cells(0).Value
    SubTotalPrice = SalesDataGridView.Rows(x).Cells(5).Value
    Copies1 = SalesDataGridView.Rows(x).Cells(3).Value
    STR1 = "INSERT INTO Sales(Sales_ID, Sales_Date, Copies, Music_ID, Staff_ID, Total_Price) VALUES (@Sales_ID, @Sales_Date, @Copies, @Music_ID, @Staff_ID, @Total_Price)"
    Dim Comm As New OleDbCommand(STR1, Con)
    Comm.Parameters.AddWithValue("@Sales_ID", SaleCode)
    Comm.Parameters.AddWithValue("@Sales_Date", txtDateAndTime)
    Comm.Parameters.AddWithValue("@Copies", Copies1)
    Comm.Parameters.AddWithValue("@Music_ID", MusicID)
    Comm.Parameters.AddWithValue("@Staff_ID", txtStaff_ID)
    Comm.Parameters.AddWithValue("@Total_Price", SubTotalPrice)
    Command.ExecuteNonQuery()
    Comm.Dispose()
Next
Connection.Close()