Link to home
Start Free TrialLog in
Avatar of marypebbels
marypebbels

asked on

How to load a combo box with database info in VB 6.0

Hi all,

I have been reading through solutions to similar questions and trying to follow the solutions, but to no avail. I am not very good at using VB 6.0, but i had recently done a project in VB.Net and had no problems with this.

So i have created a access database called ApplicationsDB and the table in this databse is called Applications. What i want to do in VB is this: i want to load all of the information from the column called Application Name. At the moment i have it so that it is actually bound to the DB and in my combo box, Combo1, it is putting in one item from Application Name but no matter what i try i can't make it so that it fills it with all the information.

Any help would be greatly appreciated asap as i have just started a new job and this is one of my tasks. Thanks in advance.

Regards,
Mary    :)
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

using ADO (ActiveX Data Objects).

1) Add a reference to the ActiveX Data Objects Library (Project/References)

2) in you code:

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<path to your database>;"
cn.Open
strSQL = "Select ApplicationID, ApplicationName from Applications"
Set rs = cn.Execute(strSQL)
With Combo1
    Do While Not rs.EOF
    .AddItem rs.Fields(1)
    .ItemData(.NewIndex) = rs.Fields(0)
    rs.MoveNext
    Loop
End With


AW
Avatar of marypebbels
marypebbels

ASKER

hi arthur,

i forgot to mention that i think it is an adodc connection that i have to the db-its called datPrimaryRS, but i suppose this doesn't matter seeing as its a new connection. I did the ADO thing that you mentioned but was not sure where to place the code in your step2 but i put it into Private Sub Combo1_Click() and it ran but there was still only one item in the combo box.
thanks
place a breakpoint on the line:

Set rs = cn.Execute(strSQL)


and then use F8 to single step throuhg the code.  How many times does the AddItem method get called.

Also, id the ComboBox BOUND to the dataControl?  

AW

arthur,

I placed the breakpoint, and went through the code and the AddItem method never gets called, so the thing is that i obviously have placed the code in the wrong place?? As i said i put it into Private Sub Combo1_Click()

I'm not exactly sure about the ComboBox- the thing is that i had set up an adodc connection to my database and this is called datPrimaryRS. So in the properties of ComboBox i have dataSource set to datPrimaryRs and dataField set to Application Name-i really am so confused!!

Thanks Arthur
No, not in the Combo1_Click event handler.

Try placing the code in the Form_Load() event  so that the combo box is loaded when the form is loaded into memory.

AW
Arthur,

Thanks a million for your help on this.

I put the could into the load-form() and i now have a runtime error '-2147217904' and it says that "No value given for one or more required parameters" and the area of the code that its pointing at is "Set rs = cn.Execute(strSQL)".

Thanks Arthur
Sorry arthur, the runtime error that I am actually getting is:"Syntax error(missing operator) in query expression Application Name".

The sql statement I have is this: strSQL = "Select Application Name from Applications"
If the field in your table is in fact "Application Name" (with the embedded blank), then change the SQL like this:

strSQL = "Select ApplicationID, [Application Name] from Applications"


Access (and in fact almost EVERY database) does NOT like to have embedded Blanks in the names of objects (such as Tables or fields).  I strongly adise you to change the blanks to _:

Application Name ===>  Application_Name for example.  But if you MUST keep the embedded blanks, then surround the object name with [...]  as in [Application Name].

Access sees the embedded blank as an invalid character, and the [...] tells Access to accept that invalid character, anyways.

Same applies to the use of RESERVED words, such as DATE or MONTH or YEAR (all of which are the names of built-in Access functions) which novice users seems to LOVE to use as field names in tables.  These will also lead to syntax error in SQL, so you enclose the RESERVED word(s) in [...] to tell Access that you understand that this was a RESERVED word, but to use it as a Field name anyways.  This is a poor solution, but Access allows it all the same.

My advise here is to avoid using RESERVED words, for fields names, or variable names.

AW
Hi Arthur,

I have done everything now that you asked but to no avail. I started up a new form there now just in case all the stuff that I had be doing to it had messed it up.

So I have created a form with the Combo1 and I Added a reference to the Microsoft ActiveX Data Objects 2.0 Library and i have the following code:

Private Sub Form_Load()
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\Dept\User Support\Database Reports\Application\ApplicationDB.mdb;"
cn.Open
strSQL = "Select Application_Name from Applications"
Set rs = cn.Execute(strSQL)
With Combo1
    Do While Not rs.EOF
    .AddItem rs.Fields(1)
    .ItemData(.NewIndex) = rs.Fields(0)
    rs.MoveNext
    Loop
End With
End Sub


So that's pretty much it, any idea what the problem is?
The runtime error that it is now giving is 3265: "Item cannot be found in the collection corresponding to the requested name or ordinal"

Thanks arthur
ASKER CERTIFIED SOLUTION
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Arthur,

Its perfect-I cannot thank you enough for your help. Cheers   :)