listbox error

Hi,

I am using a listbox to get my recordset from Oracle.

Using additem i am pulling data in list box

. List(i, 0)=recordset!column1
. List(i, 1)=recordset!column2

Etc...

I am getting the error
Run time error 380
Could not set the list property. Invalid property value at
. List(i, 10)=recordset!column11

Can you please help me with this?

Thanks
Raj DAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Could it be possible that the data you've pulled from the Oracle database contains Null values?

If it does you could try something like this.
If Not IsNull(recordset!column1) Then
    . List(i, 0)=recordset!column1
End If
If Not IsNull(recordset!column2) Then
    . List(i, 1)=recordset!column2
End If

Open in new window

Note if there nulls it could be possible to eliminate them from the recordset using the query you used on the database.

PS You might be able to use a loop to populate the listbox.
For J = 0 To recordset.Fields.Count-1
    If Not IsNull(recordset.Fields(J) Then
        .List(i, J) = recordset.Fields(J)
    End If
Next J

Open in new window

0
Raj DAuthor Commented:
Thank you Norie, I was having the null issue as well, however that got sorter with the isnull though suggested.

However for some reason I get same error at 10th loop. I tried the for method you gave again same error at


Is there a limit set to display only 1, records?

Thanks
Raj
0
NorieAnalyst Assistant Commented:
Raj

When using AddItem, which is what I assume you are using to add rows to the listbox, there is a limit to the no of columns you can populate and that limit, if I recall correctly, is 10 (might be 9).

The way round this is to use List to populate the listbox and to do that you'll need to populate an array with the data from the recordset.

How are you getting the recordset?

Are you using ADO?
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Raj DAuthor Commented:
Thanks Norie I was under impression my listbox properties are not set right.
. Yes I am using ADO.
0
NorieAnalyst Assistant Commented:
Raj

Where is the listbox located?
0
Raj DAuthor Commented:
In a form. Sorry for incomplete info.

I am Open to replace the list box with something else if it's neatly presentable.

Also reduced my fields to 10 and tried to use the for loop suggestion, but only last field got displayed in form may be I messed up.

Thanks
Raj
0
NorieAnalyst Assistant Commented:
Raj

Is the listbox in Word/Excel? Some other application?

Is it on a userform/document/spreadsheet/something else?
0
Raj DAuthor Commented:
Hi Norie, morning

This is on excel. I am calling a form which has this listbox which is getting data from DB.
0
Raj DAuthor Commented:
Hi Norie, morning

This is on excel. I am calling a form which has this listbox which is getting data from DB.
0
NorieAnalyst Assistant Commented:
That's what I thought but wasn't sure.

Anyway, as I said you need to get the data into an array.

There are 2 ways to do that:

1 Loop through the recordset.

2 Use ADO's GetRows.

For the latter the code might look something like this.
arrData = recordset.GetRows()

ListBox1.List = arrData ' might need to tranpose, if so use Application.Transpose(arrData)

Open in new window


Looping would me a little more involved, so give the above a shot first and if it doesn't work post back.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raj DAuthor Commented:
thanks Norie.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.