DataGirdViewComboBox saving problem
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: DataGirdViewComboBox saving problem

  1. #1
    Join Date
    Dec 2002
    Location
    Tenby, Wales
    Posts
    277

    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:
    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
    Visual Basic 2005 ver. 8.0.50727.867

  2. #2
    Join Date
    Aug 2005
    Location
    Imperial College London, England
    Posts
    490

    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.
    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.

    *Guarantee may not be honoured.

  3. #3
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,074

    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.

  4. #4
    Join Date
    Dec 2002
    Location
    Tenby, Wales
    Posts
    277

    Re: DataGirdViewComboBox saving problem

    Cheers mate

    Will give it a go now if I can figure out where to put it
    Visual Basic 2005 ver. 8.0.50727.867

  5. #5
    Join Date
    Dec 2002
    Location
    Tenby, Wales
    Posts
    277

    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
    Visual Basic 2005 ver. 8.0.50727.867

  6. #6
    Join Date
    Dec 2002
    Location
    Tenby, Wales
    Posts
    277

    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
    Visual Basic 2005 ver. 8.0.50727.867

  7. #7
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,074

    Re: DataGirdViewComboBox saving problem

    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.
    Attached Images Attached Images  
    Last edited by HanneSThEGreaT; July 30th, 2008 at 06:26 AM.

  8. #8
    Join Date
    Dec 2002
    Location
    Tenby, Wales
    Posts
    277

    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.
    Visual Basic 2005 ver. 8.0.50727.867

  9. #9
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,074

    Re: DataGirdViewComboBox saving problem

    Ah, OK I see.
    Sorry about that - just trying to help

  10. #10
    Join Date
    Dec 2002
    Location
    Tenby, Wales
    Posts
    277

    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?
    Visual Basic 2005 ver. 8.0.50727.867

  11. #11
    Join Date
    Dec 2002
    Location
    Tenby, Wales
    Posts
    277

    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
    Visual Basic 2005 ver. 8.0.50727.867

  12. #12
    Join Date
    Jun 2007
    Location
    .NET 3.5 Beta SP1, Visual Basic 2008 Express
    Posts
    225

    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
    Last edited by ccubed; July 30th, 2008 at 08:09 AM.
    Microsoft Visual Basic 2008 Express Edition
    .NET Framwork 3.5 Beta SP1

  13. #13
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,074

    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

  14. #14
    Join Date
    Dec 2002
    Location
    Tenby, Wales
    Posts
    277

    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!

    Thanks a lot!
    Visual Basic 2005 ver. 8.0.50727.867

  15. #15
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,074

    Re: DataGirdViewComboBox saving problem

    No problem, glad you got it solved!

    Your app is quite cool, I like the menu effort you have there ( Yep, I finally figured out where is what. )

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center