Datagridview, Saving data in many Excel rows

[brjames32]

This question is about a project HannesTheGreat and JavaJawa have helped me with but anyone feel free to help!

The problem is that I am saving from a DataGridView to a Spreadsheet (which works great!!). I have changed one of the columns so that it is a DataGridViewComboboxColumn so that user can select from a number of values - both numerical and Text. The Column is called Lesson and basically holds details about the Lesson that an incident with a student happened. Here's the weird part - It seems that if the Spreadsheet has a lot of rows, the program returns a Data Type Mismatch in Criteria Expression on that column but if the Spreadsheet only has say 150 rows, it saves! The Data Type Mismatch stops on this line:

.ExecuteNonQuery()

All 7 spreadsheets (1 for each school Year from 7 - 13) that the data is saved to are identical, they have same columns and are all named identically. Infact they were all created by copying and pasting the original.

Code I am using to save is - this is just for 1 of the yeargroups (7):

If ExcelYear = "7" Then
                        Dim MyExcelConnection As New System.Data.OleDb.OleDbConnection _
                                    ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\logon\shared\Penalty Points\08-09\Year 7 2008-09.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=0""")
                        MyExcelConnection.Open()
                        With myInsertCommand
                            .CommandText = "Insert INTO [Sheet1$] ([ID],[Name],[Form],[Date],[Merits],[Demerits],[Lesson]," + _
                                    "[Group],[Staff],[Description],[Action],[MeritType]) VALUES(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12)"
                            .CommandType = CommandType.Text
                            .Connection = MyExcelConnection
                            For Each Row As DataGridViewRow In DataGridView1.Rows
                                For Col As Int32 = 1 To Row.Cells.Count
                                    If Not CStr(Row.Cells(Col - 1).Value) = String.Empty Then
                                        .Parameters.Add(New OleDb.OleDbParameter("@" & Col, OleDb.OleDbType.Char)).Value = CStr(Row.Cells(Col - 1).Value)
                                    Else
                                        .Parameters.Add(New OleDb.OleDbParameter("@" & Col, OleDb.OleDbType.Char)).Value = CStr(Row.Cells(Col - 1).Value)
                                        '.Parameters.Item("@7").Value = "0"
                                    End If
                                Next Col
                                .ExecuteNonQuery()
                                .Parameters.Clear()
                            Next
                        End With
                        MyExcelConnection.Close()
                        MyExcelConnection.Dispose()
                        MyExcelConnection = Nothing

and the code for the DataGridViewComboBoxColumn is:

Dim Lesson As New DataGridViewComboBoxColumn 
DataGridView1.Columns.Add("Lesson", "Lesson")         
With Lesson
            .Name = "Lesson"
            .HeaderText = "Lesson"
            .Width = 275
            .Visible = True
            .ReadOnly = False
            '.AutoComplete = True
            .Items.Add("AM Reg.")
            .Items.Add("1")
            .Items.Add("2")
            .Items.Add("Break")
            .Items.Add("3")
            .Items.Add("4")
            .Items.Add("Lunch")
            .Items.Add("5")
            .Items.Add("Other")
            .Items.Add("!")
        End With
        DataGridView1.Columns.Add(Lesson)

Is can't be the code as it works for Years 12 & 13 - they only have around 160 rows each. Years 7 - 11 have between 5000 - 6000 rows and it won't save to them.

Any ideas please?