Saving DataGidView data to Excel

[brjames32]

I have a project where a Teacher can choose their class from a ComboBox and this is turn selects all the relevant student from an Access Database and displays this along with some extra columns in a DataGridView. The Teacher can then add 'Penalty Points' and 'Demerits' to the student before saving them to a spreadsheet (reason being the info needs a lot of formulas to get it to work out several thing the Heads of Year want to see!). I can Insert into Excel using code such as:

Imports System.Data
Public Class Form1
Private Sub cmdSave_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdSave.Click
Try
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim myCommand As New System.Data.OleDb.OleDbCommand
Dim sql As String

        MyConnection = New System.Data.OleDb.OleDbConnection \_
        ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + \_
        "'S:\\Penalty Points\\07-08\\Test.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=0""")

        MyConnection.Open()
        myCommand.Connection = MyConnection
        sql = "Insert into \[Sheet1$\] (StudentId,Name) values('5','e')"
        myCommand.CommandText = sql
        myCommand.ExecuteNonQuery()
        MyConnection.Close()
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
    MsgBox("Row Added Successfully")
End Sub

End Class

But I need to loop through the DatagridView, Inserting all rows that have been ammended. Can anyone help me out please? Thanks in Advance ;)

[brjames32]

Here's my latest code:

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click

    Try
        Dim MyExcelConnection As System.Data.OleDb.OleDbConnection
        Dim myCommand As New System.Data.OleDb.OleDbCommand
        Dim daExcel As New System.Data.OleDb.OleDbDataAdapter

        MyExcelConnection = New System.Data.OleDb.OleDbConnection \_
        ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\\SanctionsDb\\2007-08\\points.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=0""")

        For Each row As DataGridViewRow In DataGridView1.Rows
            With myCommand
                .CommandText = "Insert into \[Sheet1$\] (StudentId,StuName,ClassGrp,TheDate,Merits,Demerits,Lesson," + \_
                    "TeachingGrp,Description,Action,MeritType) values(?,?,?,?,?,?,?,?,?,?,?)"
                .CommandType = CommandType.Text
                .Connection = MyConnection
                ' Configure the insert parameters
                .Parameters.Add(New OleDb.OleDbParameter("StudId", OleDb.OleDbType.Double, 0, "StudId"))
                .Parameters.Add(New OleDb.OleDbParameter("Name", OleDb.OleDbType.Double, 0, "Name"))
                .Parameters.Add(New OleDb.OleDbParameter("Class Grp", OleDb.OleDbType.Double, 0, "ClassGrp"))
                .Parameters.Add(New OleDb.OleDbParameter("Date", OleDb.OleDbType.Double, 0, "Date"))
                .Parameters.Add(New OleDb.OleDbParameter("Merits to Add", OleDb.OleDbType.Double, 0, "Merits"))
                .Parameters.Add(New OleDb.OleDbParameter("Demerits to Add", OleDb.OleDbType.Double, 0, "Demerits"))
                .Parameters.Add(New OleDb.OleDbParameter("Lesson", OleDb.OleDbType.Double, 0, "Lesson"))
                .Parameters.Add(New OleDb.OleDbParameter("TeachingGrp", OleDb.OleDbType.Double, 0, "TeachingGrp"))
                .Parameters.Add(New OleDb.OleDbParameter("Description", OleDb.OleDbType.Double, 0, "Description"))
                .Parameters.Add(New OleDb.OleDbParameter("Action", OleDb.OleDbType.Double, 0, "Action"))
                .Parameters.Add(New OleDb.OleDbParameter("Type", OleDb.OleDbType.Double, 0, "Type"))
            End With
        Next
        daExcel.InsertCommand = myCommand

        MyExcelConnection.Open()
        daExcel.Update(ds, "Groups")
        ' Cleanup
        MyExcelConnection.Close()
        daExcel.Dispo