Solved

error type mismatch when loading vb listbox1

Posted on 2014-01-19
19
417 Views
Last Modified: 2014-01-20
Folks

Need assistance, when I load a listbox I receive a error 13 type mismatch, can anyone assist, I guess it should be something to do with what field type I am loading, need help

below is my sample code


  Set oRsOracle = oConOracle.Execute(strSQL)

'Then add that to the list box

'ListBox1.AddItem ListBoxText
 
  Do Until oRsOracle.EOF
    ListBox1.AddItem oRsOracle.Fields(4).Value
    oRsOracle.MoveNext
  Loop
 
  oRsOracle.Close
0
Comment
Question by:rutgermons
  • 8
  • 7
  • 4
19 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39792222
Have you set the RowSource  type of the listbox to value list?
0
 

Author Comment

by:rutgermons
ID: 39792270
hi Martin , nope! the data comes from oracle , I have set the variable strSQL


Dim strSQL As String

strSQL = "SELECT *FROM QUERY WHERE APP ='vehicles'"
   
    MsgBox (strSQL)
   
    Set oRsOracle = oConOracle.Execute(strSQL)

how and where do I set the rowsource type as you suggest?

keen for your guidance
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39792329
It's a property of the Listbox.
0
 

Author Comment

by:rutgermons
ID: 39792468
Can't find it, it doesnt seem(hope i am wrong) an option on listboxes in my excel sheet (excel 2010)
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39792513
Can you attach your workbook or a demo workbook that illustrates the problem?
0
 

Author Comment

by:rutgermons
ID: 39792550
see attached, when I call the button to download the list and populate the listbox I get the problem
SCHEDULER.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39792570
I can't connect to your oracle database so I can only suggest that you try changing this

Dim oConOracle, oRsOracle

to

Dim oConOracle As New ADODB.Connection
Dim oRsOracle As New ADODB.Recordset
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39792853
I believe the listbox is expecting a String data type and whatever is coming back from your database isn't so I suggest converting to a string:

 Do Until oRsOracle.EOF
    ListBox1.AddItem CStr(oRsOracle.Fields(4).Value)
    oRsOracle.MoveNext
  Loop
0
 

Author Comment

by:rutgermons
ID: 39792887
No luck tagit, so the data i have are where clauses used for sql queries

let's say SELECT *FROM QUERY WHERE APP, the query object field I am using is the actual where clause itself

where "field 1 = 'car' and startdate ='2014-01-12'", im wondering if the varying where clause values are causing the problem
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39792901
It may be, that was my first thought as well but just had to rule out the basic type conversion first.


let's say SELECT *FROM QUERY WHERE APP, the query object field I am using is the actual where clause itself

this I don't understand.  Are QUERY and APP variables in Oracle or VBA?

Where do you get where "field 1 = 'car' and startdate ='2014-01-12'" from?
0
 

Author Comment

by:rutgermons
ID: 39792994
Tagit, those are variables in Oracle

but the field that I am displaying is a field/column called "clause" in the query table and this contains the actual where clauses
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39793002
Ok then I would start by debugging what is being returned by the query:

Do Until oRsOracle.EOF
    debug.print oRsOracle.Fields(4).Value
    'ListBox1.AddItem oRsOracle.Fields(4).Value
    oRsOracle.MoveNext
  Loop

Note i've commented out the AddItem line.  Check the "Immediate Window" in the VBA editor for the output
0
 

Author Comment

by:rutgermons
ID: 39793023
ok, so I get to see all the records in the immediate window, not something obvious but is there a imit to the amount or rows possible in a list box or field lenghts?
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39793037
(EDIT) Can you post what was in the debug Window?  Getting an idea of the data may help
0
 
LVL 42

Accepted Solution

by:
Rob Jurd, EE MVE earned 500 total points
ID: 39793188
This test works fine in your workbook.  I've truncated the data, which you might want to try

Sub listTest()
    ListBox1.Clear
    Dim x As Variant
    For Each x In Array("password", "username", "myfields", "myvalues", "january")
        ListBox1.AddItem Left(x, 5)
    Next x
    
End Sub

Open in new window


So I suspect it will be due to some of the data coming in so try this:

Do Until oRsOracle.EOF
    debug.print oRsOracle.Fields(4).Value
    ListBox1.AddItem Left(oRsOracle.Fields(4).Value,10)
    oRsOracle.MoveNext
  Loop

Open in new window

0
 

Author Comment

by:rutgermons
ID: 39793555
wow, that works like a charm!

what exactly does this fix do? is this something to do with dodgy characters between lines (for lack of a better phrase)?

thanks Rob! your efforts are really appreciated
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39793571
no worries.  all i did was trunacate the response.  You drew me to that conclusion and from your comments I suspected that some of the data is quite large coming back.  Each record in a listbox might be limited to 255 characters but thats just a guess :)
0
 

Author Closing Comment

by:rutgermons
ID: 39793577
yep! the where clauses are longwinded and many)  thanks a stack
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39793580
Actually it could be dodgy chars because I only included the first 10 characters of each record.  Going to be really hard to know unless I can see the full content of each record via the immediate window.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now