How to save data from datatable to database using vb.net

[Eunice VB junior]

Hi Everyone, I'm working on window application. I'm adding a set of new rows to datagrid using a datatable. Once completed, i want to save all the added rows from datagrid to database. How should i save it into database?? Can anyone advice me? Thanks in advanced. Here is the codes:- public Sub DataGrid() myTable = New DataTable("Exam") Dim Employee As DataColumn = New DataColumn("Employee") Employee.DataType = System.Type.GetType("System.String") myTable.Columns.Add(Employee) Dim Score As DataColumn = New DataColumn("Score") Score.DataType = System.Type.GetType("System.String") myTable.Columns.Add(Score) Dim ds As New DataSet ds = New DataSet ds.Tables.Add(myTable) datagrid1.SetDataBinding(ds, "Exam") end sub Private Sub btnComplete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnComplete.Click Dim strQuery1 As String strQuery1 = "Insert into MMExam (Employee,Score) values('" & DataTable.Rows(Employee) & "','" & DataTable.Rows(Score )& "')" myConnection.Open() myCommand = New SqlCommand(strQuery1, myConnection) Try ra = myCommand.ExecuteNonQuery() myConnection.Close() Me.Close() Catch ex As Exception myConnection.Close() MessageBox.Show(ex.Message) End Try End Sub thanks, eunice

[Dave Kreskowiak]

A DataGrid is used to show and edit the data in a DataSource, such as a DataTable or DataSet. You don't go from DataTable to DataGrid to database. You go from DataGrid - which shows and edits a DataTable/DataSet - which synchronizes changes with a Database. From what you've posted, your code is pretty much spaghetti. If you're adding records to an existing database, I'd start by creating a DataSet and DataAdapter with the proper SQL statement to retrieve the fields from the table that you're going to manipulate. Use it's FillSchema method to create a new DataTable object in your DataSet object. This will automatically setup your DataTable for your, mapping the columns in the database to the columns in the DataTable using the proper data types. Then, you use a CommandBuilder (SqlCommandBuilder, OleDbCommandBuilder, ...) on the DataAdapter to create the SQL UPDATE, INSERT, and DELETE commands for the DataAdatper. Then bind the DataGrid to that DataTable, make your changes, then use the DataAdapter's Update method to write the changes back to the database.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007