DataGirdViewComboBox saving problem
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:
Quote:
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.
Any ideas?
Cheers ;)
Re: DataGirdViewComboBox saving problem
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.
Re: DataGirdViewComboBox saving problem
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.
Re: DataGirdViewComboBox saving problem
Cheers mate
Will give it a go now if I can figure out where to put it ;)
Re: DataGirdViewComboBox saving problem
HanneSThEGreaT
The MeritType column is there but hidden1:
Code:
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
Re: DataGirdViewComboBox saving problem
JavaJawa I am not quite sure how to add your changes to my code! Any chance you can show me??
Thanks a lot
1 Attachment(s)
Re: DataGirdViewComboBox saving problem
OK, sorry about that mate! Didn't see that :)
But, I don't see an Action column :eek:
And, I still get an error about parameter @13.
Strange.
Re: DataGirdViewComboBox saving problem
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:
Code:
DataGridView1.Columns.Add("Action", "Action")
DataGridView1.Columns("Action").Width = 150
and it is in the cmdShow section.
Re: DataGirdViewComboBox saving problem
Ah, OK I see.
Sorry about that - just trying to help
Re: DataGirdViewComboBox saving problem
LOL - I am very glad of the help - thanks so far! :)
Have you any idea what could be stopping the rows in the ComboBox columns from saving - hence giving the parameter @7 has no default value error? :ehh:
Re: DataGirdViewComboBox saving problem
OR
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
Re: DataGirdViewComboBox saving problem
Quote:
Originally Posted by brjames32
OR
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
Re: DataGirdViewComboBox saving problem
You could also try :
Code:
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
Re: DataGirdViewComboBox saving problem
Thanks for both of your suggestions guys! HanneSThEGreaT once again mate you have come up trumps - you code works a treat! :thumb: :)
Thanks a lot!
Re: DataGirdViewComboBox saving problem
No problem, glad you got it solved! :)
Your app is quite cool, I like the menu effort you have there :thumb: ( Yep, I finally figured out where is what. :lol: )