dcsimg
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: Datagridview, Saving data in many Excel rows

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

    Datagridview, Saving data in many Excel rows

    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:

    Code:
    .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):
    Code:
    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:
    Code:
    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?
    Last edited by brjames32; December 4th, 2008 at 02:47 AM.
    Visual Basic 2005 ver. 8.0.50727.867

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

    Re: HanneSThEGreaT or JavaJawa - HELP!!

    Anyone? Come on - somebody must have a theory!!
    Visual Basic 2005 ver. 8.0.50727.867

  3. #3
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Arrow Re: HanneSThEGreaT or JavaJawa - HELP!!

    You did ask them in your title, but I'll toss one in.

    Depending on the version, your spreadsheet may simply have too many rows.

    Add a counter, to check it. Also, what data types? INT is limited as well to 16K
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

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

    Re: HanneSThEGreaT or JavaJawa or ANYONE - HELP!!

    Cheers for the reply dglienna!

    Forgot about the title - now edited

    The choices in ComboBoxColumn (Lesson) are : Am Reg., 1, 2, Break, 3, 4, Lunch, 5, Other

    They are a mixture of Chars and Numbers. The Spreadsheets are Office 2003 and none of them are over 3MB in size so that 16k shouldn't be affecting this. The first 5 Spreadsheets (the ones that it won't save the Lesson column) are around 7000 rows in size.

    Not sure what you mean by add a counter?

    Cheers for the help so far
    Visual Basic 2005 ver. 8.0.50727.867

  5. #5
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,267

    Re: HanneSThEGreaT or JavaJawa or ANYONE - HELP!!

    I get the feeling that the problem lies with a record / records, in the db itself.

    I'm editing this thread's title, because members tend to ignore these types of thread titles
    [SIGPIC][/SIGPIC]
    All my Articles
    Hannes

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

    Re: Datagridview, Saving data in many Excel rows

    Cheers Hannes - I tried to edit it but it didn't save

    Would it help if I uploaded a copy of the one of the Spreadsheets that work and one that doesn't??
    Visual Basic 2005 ver. 8.0.50727.867

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

    Re: Datagridview, Saving data in many Excel rows

    No worries, I've got a few tricks up my sleeve LOL!

    I think if you upload one of the spreadsheets, it would be more helpful - perhaps there's just a silly thing that doesn't belong there
    [SIGPIC][/SIGPIC]
    All my Articles
    Hannes

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

    Re: Datagridview, Saving data in many Excel rows

    I have attached the Spreadsheets. Year 7 is the one that it won't save to and Year 13 it will save to.

    If the Lesson Column only contains a number, it will save to both but if it contains Text, it will only save to the Year 13 Spreadsheet.

    Cheers Hannes and anyone else that has looked
    Attached Files Attached Files
    Visual Basic 2005 ver. 8.0.50727.867

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

    Re: Datagridview, Saving data in many Excel rows

    Anyone???
    Visual Basic 2005 ver. 8.0.50727.867

  10. #10
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Datagridview, Saving data in many Excel rows

    Why in the world are you trying to save Excel spreadsheets of DATABASE info? You're using DB commands, and executing it over and over.

    First, use a database. Save your Excel files as text, and then do BULK IMPORTs in the future.
    Code:
    BULK INSERT Received
    FROM ‘\\Admfs1\users\C0009348.xls’
    WITH
    (
    FIELDTERMINATOR =’|',
    ROWTERMINATOR =’ {CR}{LF}\n’
    )
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

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

    Re: Datagridview, Saving data in many Excel rows

    Hi DGlienna thank for the reply!

    The reason I am saving to a spreadsheet is that if you notice, several of the spreadsheet fields contain complicated formulas used to work out Penalty/Demerits Point etc. As far as I know, you cannot do this with a Database otherwise I would have been using one as they are much easier.

    Can this be done in Access?

    As to why it is executing over and over, when the user enters data, there may be up to 35 rows to be entered into the spreadsheet at a time. That's why is executing over and over until the last row.

    If you want more background info on my project, look at this thread. Infact I think you contributed too!

    Thanks so far . . . . .
    Last edited by brjames32; December 5th, 2008 at 06:35 AM.
    Visual Basic 2005 ver. 8.0.50727.867

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

    Re: Datagridview, Saving data in many Excel rows

    Come on everyone - don't let me down! PLEASE!!
    Visual Basic 2005 ver. 8.0.50727.867

  13. #13
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Datagridview, Saving data in many Excel rows

    Anything that you can calculate in Excel can also be done in VB, so you don't really need excel.

    You could even just store the data, and let the db engine calculate all the variables when you run a query.

    That's what we do. We don't store running totals anymore. It's easier to calculate on the spot.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

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

    Re: Datagridview, Saving data in many Excel rows

    Dglienna

    Thanks again for your suggestions! Although I really don't have much time left to work on this, I will give it a try.

    How would I go about doing the sort of Calculations that I use in my Spreadsheet in VB before saving to DB?? I can do the saving part easily enough. Trouble is there may be 30-35 rows of info needing calculations and then saving at a time!

    Have you any exmple code?

    Thanks in advance!
    Visual Basic 2005 ver. 8.0.50727.867

  15. #15
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Datagridview, Saving data in many Excel rows

    Not really, as most projects I've developed have nothing to do with one another.

    You could set up fields in the db to mirror the spreadsheet. You could even implement rows (although I'd just use each row separate)

    You can name your fields t() and use t(1) for cell A1, then loop thru them.

    You'd have to calculate each row's new values and then update the record.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

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
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)