将datagridview的所有数据插入到数据库vb.net [英] Insert all data of a datagridview to database vb.net

问题描述

  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 
 
对于x As Integer = 0到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（）
下一页
 Connection.Close（）
  

Hallo给我所有的高年级，我不知道为什么它没有任何错误显示，不能保存在Access数据库。 / p>

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

1。我有一个未绑定的datagridview，可以添加数据从几个文本框。
2.）一个叫Check-Out的按钮，这个按钮用于将我的datagridview数据传递给Access数据库.....这是我面对的问题.....有人可以帮我解决它.. ...

Thx很多...

我也提到了这个链接，不太熟悉C＃插入datagridview的所有数据到数据库

解决方案

您需要在for循环之前打开连接，并在Command中删除注释。 ExecuteNonQuery（）

您的代码将如下所示

  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（）
对于x As Integer = 0到SalesDataGridView.Rows.Count  -  1 
 MusicID = SalesDataGridView.Rows（x）.Cells ）.Value 
 SubTotalPrice = SalesDataGridView.Rows（x）.Cells（5）.Value 
 Copies1 = SalesDataGridView.Rows（x）.Cells（3）.Value 
 STR1 =INSERT INTO销售（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（）
下一页
 Connection.Close（）
  

    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.

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.....

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

解决方案

you were required to open connection before the for loop and remove the comment at 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()

这篇关于将datagridview的所有数据插入到数据库vb.net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！