Insert all data of a datagridview to database vb.net
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=
user3496755
Updated on November 26, 2022Comments
-
user3496755 over 1 year
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
-
Ňɏssa Pøngjǣrdenlarp almost 10 yearswhy 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. -
user3496755 almost 10 years@Plutonix it show "Object reference not set to an instance of an object." when nvr comment out the Command.ExecuteQuery()
-
Ňɏssa Pøngjǣrdenlarp almost 10 yearsyour OLEDBCommand object is created as
Com
; but you also createComm
then useCommand
. you only need one OleDBCommand object, pick one. Here is a sample of how to use USING blocks - stackoverflow.com/questions/23377408/…
-