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:
Code:
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.
The program is trying to also add the blank row on the end - change the outer for loop:
Code:
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:
Code:
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.
Help from me is always guaranteed!*
VB.NET code is made up on the spot with VS2008 Professional with .NET 3.5. Everything else is just made up on the spot.
Please Remember to rate posts, use code tags, send me money and all the other things listed in the "Before you post" posts.
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.
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:
is there a way of modifying the below code to ignore updating the relevant column in the spreadsheet if the datagridview combobox value is null or 0??
Code:
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 there a way of modifying the below code to ignore updating the relevant column in the spreadsheet if the datagridview combobox value is null or 0??
Code:
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
Two things. One, For <something> to <something> step <some number>.
Two, Yes, you can check for null. Thirdly, int32? that's integer.
Code:
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 integer = 1 To Row.Cells.Count Step 1
if row(col).value.toString <> system.dbnull.value.tostring then
.Parameters.Add(New OleDb.OleDbParameter("@" & Col, OleDb.OleDbType.Char)).Value = CStr(Row.Cells(Col - 1).Value)
end if
Next Col
.ExecuteNonQuery()
.Parameters.Clear()
Next
End With
MyExcelConnection.Close()
MyExcelConnection.Dispose()
MyExcelConnection = Nothing
And don't even bother asking me how VB has a string representation for Null, it works, so I ain't asking.
Also, i've never quite had much luck with doing for each row in datagridview.rows. It almost never seems to work. You can do this and accomplish the same thing, i know this works.
Code:
For i As Integer = 0 To DataGridView1.Rows.Count - 1 Step 1
For a As Integer = 0 To DataGridView1.Rows(i).Cells.Count - 1 Step 1
If DataGridView1.Rows(i).Cells(a).Value.ToString <> System.DBNull.Value.ToString Then
Last edited by ccubed; July 30th, 2008 at 08:09 AM.
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
Here I added an If statement making sure that the Penalty column is not empty, if it is empty, add a default value of 0, else, just add the info normally.
The whole segment for Year 10, would look like :
Code:
If ExcelYear = "10" Then
Dim MyExcelConnection As New System.Data.OleDb.OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\08-09\Year 10 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
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
* The Best Reasons to Target Windows 8
Learn some of the best reasons why you should seriously consider bringing your Android mobile development expertise to bear on the Windows 8 platform.