Datagridview, Saving data in many Excel rows

[brjames32]

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:

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

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:

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?

[brjames32]

Anyone? Come on - somebody must have a theory!!

[dglienna]

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

[brjames32]

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

[HanneSThEGreaT]

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

[brjames32]

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

[HanneSThEGreaT]

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

[brjames32]

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

[brjames32]

Anyone???

[dglienna]

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.

BULK INSERT Received
FROM ‘\\Admfs1\users\C0009348.xls’
WITH
(
FIELDTERMINATOR =’|',
ROWTERMINATOR =’ {CR}{LF}\n’
)

[brjames32]

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

[brjames32]

Come on everyone - don't let me down! PLEASE!!

[dglienna]

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.

[brjames32]

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!

[dglienna]

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.