DataGirdViewComboBox saving problem

[brjames32]

I have a DataGridView with several columns that are all added at runtime. Some of the columns are also DataBound at runtime too. Now this has been working great but since I added a ComboBoxColumn, it won't save!

I get the error:

Parameter @7 has no default value

This is the ComboBoxColumn. The code I am using to save is:

 If ExcelYear = "7" Then
                Dim MyExcelConnection As New System.Data.OleDb.OleDbConnection _
                ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\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$] ([StudentId],[StudentName],[ClassGrp],[TheDate],[Merits],[Demerits],[Penalty],[Lesson]," + _
                                        "[TeachingGrp],[Staff],[Description],[Action],[MeritType]) VALUES(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13)"
                    .CommandType = CommandType.Text
                    .Connection = MyExcelConnection
                    For Each Row As DataGridViewRow In DataGridView1.Rows
                        For Col As Int32 = 1 To Row.Cells.Count
                            .Parameters.Add(New OleDb.OleDbParameter("@" & Col, OleDb.OleDbType.Char)).Value = CStr(Row.Cells(Col - 1).Value)
                        Next Col
                        .ExecuteNonQuery()
                        .Parameters.Clear()
                    Next
                End With
                MyExcelConnection.Close()
                MyExcelConnection.Dispose()
                MyExcelConnection = Nothing

Is it something to do with the code and column type that is stopping it from saving? I have tried both selecting values for each of the rows with the ComboBox and also have set .DefaultCellStyle.NullValue = "0" incase the user doesn't select a value.

Any ideas?

Cheers

[javajawa]

The program is trying to also add the blank row on the end - change the outer for loop:

Dim Row As DataGridViewRow
For R as Int32 = 1 to DataGridView.RowCount - 1
  Row = DataGridView.Rows(R)

You may also want to take the parameter creation into the outer loop, use:

                        For Col As Int32 = 1 To Row.Cells.Count
                            .Parameters.(Col - 1).Value = CStr(Row.Cells(Col - 1).Value)
                        Next Col

As the structure is the same every time.

[HanneSThEGreaT]

Your dataGridView only contains 11 columns, it does not have columns for Action and MeritType! That is why I got an error saying there is no default parameter for column 13.

You may also want to check whether or not values are indeed entered / selected beofore adding the records. I fnothing is selected in your Penalty column, it moans about parameter 7, if there is something enetered, it complains about parameter 13.

[brjames32]

Cheers mate

Will give it a go now if I can figure out where to put it

[brjames32]

HanneSThEGreaT

The MeritType column is there but hidden1:

DataGridView1.Columns.Add("MeritType", "MeritType")
        DataGridView1.Columns("MeritType").Visible = False

Also, the Action column is showing here on my system. Everything was working and saving until I added the ComboBox Column

[brjames32]

JavaJawa I am not quite sure how to add your changes to my code! Any chance you can show me??

Thanks a lot

[HanneSThEGreaT]

OK, sorry about that mate! Didn't see that

But, I don't see an Action column

And, I still get an error about parameter @13.

Strange.

[brjames32]

That's another sort of 'hidden' thing too - you need to click on the bar at the top and Select Class Acts -> Add Class acts before clicking 'Save' otherwise, there is nothing in the MeritType Column (@13) to save

The action column is here for me - it's the last column in the DataGridView! The code for it is:

DataGridView1.Columns.Add("Action", "Action")
        DataGridView1.Columns("Action").Width = 150

and it is in the cmdShow section.