Insert all data of a datagridview to database vb.net

Question

    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

why is Command.ExecuteNonQuery() commented out? that makes it do work. Also, Comm.Dispose() should be outside the Next block to insert all rows. Otherwise an explanation of how it fails, or any errors would be helpful. — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented May 21, 2014 at 1:43
@Plutonix it show "Object reference not set to an instance of an object." when nvr comment out the Command.ExecuteQuery() — user3496755
– user3496755, Commented May 21, 2014 at 2:12
your OLEDBCommand object is created as Com; but you also create Comm then use Command. you only need one OleDBCommand object, pick one. Here is a sample of how to use USING blocks - stackoverflow.com/questions/23377408/… — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented May 21, 2014 at 2:45

jmcilhinney · Accepted Answer · 2014-05-21 03:06:15Z

1

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

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

answered May 21, 2014 at 3:06

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user4704036 · Accepted Answer · 2015-03-23 16:28:39Z

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()

Collectives™ on Stack Overflow

Insert all data of a datagridview to database vb.net

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related