listBox item string limitation

[alfi]

I created a recordSet that contains three columns of data, one of which the string value length exceeds 255 characters, When binding the recordSet with a listBox control the value was truncated to 255. I checked the record source to make sure the problem is not in the data coming from the database, and that the record contains the full string. Checking on the web I found some article explaining list box control limitation of 1 k of data on each item, hence 1K = 1024 bytes and 1 character = 1 byte the limit should be 1024 character for each item. I made another test and manually add a list item to the list box control with the use of the AddItem method making sure the item content length exceeds 255. When extracting the data with the use of listBox.Column(x,y) method the length of the string was truncated to 255. Any idea about this limitation and how to overcome this problem?

 

[dkinley]

Unless the rest of the memory is used for overhead of the list box.

First, I would pose a question that if you had 3 columns, and if any one of them exceeded a 15 character limit, then it would no longer qualify as a list but would sound more like a narrative. At that point your basically presenting the user a datasheet view of a recordset crammed into a list box.

A work-around would be to have the list box contain the record ID (hidden from the user, etc) and present the user with a summarized version. Once selected, you could then populate controls on the form see the full text.

-dK

 

[alfi]

Hi dK,

thanks for the feedback, i am presenting the user with the content of one column and the others are hidden and used as keys, the problem is that one hidden column contain a sql statement that will be executed according to the user selection , but the sql statement is truncated and therefore the SQL is not being executed , I think of using a dictionary to hold the key and sql statement , but I was just curious if someone else knew about this issue and come up with a different solution, I couldn’t find on the web any information on this limitation and since I am not exceeding 1024 I though I am ok, any ideas ??

 

[dkinley]

Ah, unsure how to proceed without perhaps setting the form control to invisible and stashing the data in there on the after update event of the listbox. I saw something on the net about using a call-back procedure that might have some possibilities but for your purposes, your request has surpassed my experience in handling something of this nature.

Apologies,
-dK

 

[gemma-the-husky]

store a numeric ref, to a sql string in a general sqlstrings table

that might make your sql more re-usable anyway

--------
note generally that running sqlstrings is less efficient than runnnig stored queries, as access creates a query plan for a stored query
so it might be better to use named stored queries