Character limits on text fields

Welcome Guest! To enable all features please Login or Register.

Ian Lucas
Member Topic Starter

2014-03-03T00:09:01Z

Hi,

Can you help with this?

1. I have an IGrid table linked to an Excel 2010 spreadsheet via ADO.

2. In one of the fields I need to be able hold significant numbers of characters per cell, but way less than Excel 2010's limit (32,767 characters).

3. When submitting data to Excel via IGrid, I have occasionally been getting an error message saying that the amount of data I am trying to paste is too large. When this happens, I can submit around 200 characters to the spreadsheet, but no more.

4. I can submit larger numbers of characters directly into the Excel sheet.

5. After I submit some data directly into an Excel cell on the linked spreadsheet, the problem disappears! When I go back to iGrid, I can now submit large numbers of characters to the spreadsheet without getting the error message.

I appreciate that this may have more to do with ADO and/or Excel than with iGrid, but you may have come across it before.

Do you know what's going on?

Or, more importantly, whether I can do anything to stop it happening in the future, so that people who are even less savvy than me don't get stumped?

Best wishes,

Ian

Igor/10Tec
Administration

2014-03-03T08:10:11Z

Originally Posted by: Ian Lucas

3. When submitting data to Excel via IGrid...

iGrid has no any built-in features to export its cells to Excel, so the problem is definitely in the code that does this work.

In any case, we have never heard about such a problem. An you also need to provide a sample one can use to reproduce the problem - it's impossible to say what is wrong without it.

Ian Lucas
Member Topic Starter

2014-03-03T10:02:31Z

Thanks Igor,

The code I am using is below. It works most of the time.

Just now I got a "too much text to paste" error when using the code to try to submit 2000 characters to a cell. This was immediately after turning on my laptop.

Then I pasted the 2000 characters directly into Excel. No problems.

Then I came back to iGrid. I submitted the same 2000 characters to Excel successfully via the code to the next 5 cells in the column.

Looking at the code now, it is basically rewriting the DB every time it is run which isn't super smart. But I don't think this is the problem, because I'm working on a small test sheet with only 6 rows and 18 columns.

Word has closed on me a couple of times when I have tried to submit data. Maybe it's choking? iGrid takes maybe 5-8 seconds to load when it doesn't have much text on board and a bit longer when it has more text to load. My system is 2Ghz 8GbRAM 64 bit Win8.1.

Best wishes,

Ian


'*******Submission of edits to the spreadsheet**********
Private Sub cmdSubmitEdits_Click()
i = 1
j = 1
OpenConnectionAndRecordset "DocumentData", "*", rsDocumentData
rsDocumentData.MoveFirst
For i = 1 To iGridDocumentCodes.Rowcount
    With iGridDocumentCodes
        If .RowVisible(i) = True Then
            For j = 2 To .Colcount
               If .ColVisible(j) = True Then 
                    🅱rsDocumentData.Fields(j - 1).Value = .CellValue(i, j) [i]'WHEN ROUTINE CHOKES, IT CHOKES HERE[/i]
                    rsDocumentData.Update[/b]
               End If
            Next j
        End If
    End With
    
rsDocumentData.MoveNext
Next i

exitsub:
Call MsgBox("Any edits to the document codes have been submitted.", vbInformation, "Edits have been submitted.")

rsDocumentData.Close
Set rsDocumentData = Nothing
oConn.Close
Set oConn = Nothing

End Sub


'---------------------------------------------------------------------------------------
' Procedure : OpenConnectionAndRecordset
' Author    : Ian
' Date      : 23/02/2014
' Purpose   : Code to open an ADO connection and recordset (takes parameters).
'---------------------------------------------------------------------------------------
'
Sub OpenConnectionAndRecordset(ConnectToWhat As String, Fields As String, RecordSetName As ADODB.Recordset)

Set oConn = New ADODB.Connection

Set RecordSetName = New ADODB.Recordset

Dim SQL As String

 SQL = "SELECT " & Fields & "FROM [" & ConnectToWhat & "];"

 With oConn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & SettingsFile & ";" & "Extended Properties=Excel 12.0;"
    .Open
 End With
     
 With RecordSetName
    .Source = SQL
    .ActiveConnection = oConn
    .CursorLocation = adUseServer
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
 End With

RecordSetName.Open

End Sub

Edited by moderator 2014-03-03T13:19:54Z | Reason: Igor/10Tec added code formatting

Igor/10Tec
Administration

2014-03-03T13:25:06Z

No ideas at the moment, but maybe, the community has some?

I would also suggest to exclude iGrid from your routine to see whether the behavior changes. Try to populate rsDocumentData with simple values (say string literals) written directly in the code - like this:

rsDocumentData.Fields(j - 1).Value = "abcdef"

Ian Lucas
Member Topic Starter

2014-03-03T21:59:46Z

Thanks Igor,

I tried taking iGrid out of the equation as you suggested.

"abcde" worked fine. It very quickly rewrote my recordset (6 records x 14 fields, not 18 fields as I said before).

The I tried longer string literals. I got up to about 300 characters without a problem. But with strings longer than that I got the "too much text to paste" message again.

So there might be a limit to the total number of characters that can be submitted to Excel in a single ADO recordset update. About 300 characters x 14 fields x 6 records = About 25200 characters in total.

For my next try, I'm going to look at my submit routine and try to keep down the number of characters submitted per update.

Could I use the AfterCommitEdit event or another event to send changes through to the recordset one by one as they happen?

Ian

Ian Lucas
Member Topic Starter

2014-03-04T02:16:03Z

A further update:

I have fixed my recordset updating routine so that it only updates the cell that has been edited, not the whole recordset, using the AfterCommitEdit event.

Then I tried again. Same problem. I got the "too much text to paste" error even with a shortish paragraph.

So I went into Excel and pasted 9000 characters directly into a cell. Then I went back to iGrid. I could now paste 9000 characters into 5 different cells with no problems.

Oliver
Advanced Member

2014-03-06T17:57:04Z

Originally Posted by: Ian Lucas

A further update:

I have fixed my recordset updating routine so that it only updates the cell that has been edited, not the whole recordset, using the AfterCommitEdit event.

Then I tried again. Same problem. I got the "too much text to paste" error even with a shortish paragraph.

So I went into Excel and pasted 9000 characters directly into a cell. Then I went back to iGrid. I could now paste 9000 characters into 5 different cells with no problems.

Hello Ian

As I did a few years of working with reading and writing Excel-files from VBA, I think I can help.

Fist of all, stop using ADO to do this! It causes an infinite number of problems and you are seeing the first ones right now. Your actual problem is likely that the Recordset you get from your OpenConnectionAndRecordset function has fields with a max width that gets set by the ADO-driver over which you have little to no control. It does this by guessing (!!!) the needed size of the column. Other problems will only make it worse later. (e.g. a field with the value 50% in Excel sometimes gets reported as "50%" (String) or 0.5 (float), depending on the mood of ADO)

The right way to do it is to use the Excel-API directly. Just use an Excel-Object to write to the cells directly and it will work much better. Perfomance-wise this should be no problem for a few hundred cells. If your iGrid is already in that same workbook it will be very easy. otherwise you will have to open it. Something like this will do it:

Set xlApp = New Excel.Application
xlApp.DisplayAlerts = False
xlApp.ScreenUpdating = False
xlApp.EnableEvents = False

Set wb = xlApp.Workbooks.Add(sFilename)

xlApp.Calculation = xlCalculationManual

Set ws = wb.Worksheets(ConnectToWhat)

 For i = 1 To iGridDocumentCodes.Rowcount
    With iGridDocumentCodes
        If .RowVisible(i) = True Then
            For j = 2 To .Colcount
               If .ColVisible(j) = True Then 
                  ws.Cells(i, j).Value = objGrid.CellValue(i, j)
               End If
            Next j
        End If
    End With
 Next i

xlApp.Calculation = xlCalculationAutomatic
xlApp.DisplayAlerts = True
xlApp.ScreenUpdating = True
xlApp.EnableEvents = True

wb.Close SaveChanges:=True ' clean up workbook

' clean up properly! Otherwise some dead Excel-processes will be left in memory.
xlApp.Quit
Set xlApp = Nothing

This is essentially the loop from your code only that the writing is done directly into the worksheet. The flags that get set at the beginning stop Excel from doing things on its own while you operate on it with the code. That will speed things up a lot.

There are some additional ways to read and write recordsets via Excel-API if you need to write massive amounts of data, but these are more complicated.

Hope this helps :)

Best wishes,
Oliver

Edited by user 2014-03-06T18:01:01Z | Reason: Not specified

Ian Lucas
Member Topic Starter

2014-03-06T20:37:43Z

Oliver, that is fantastic! Thank you very much.

Best wishes,

Ian

Important Information:

The 10Tec Forum uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close