Solved

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

Posted on 2004-04-18
11
977 Views
Last Modified: 2013-12-25
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    :)
0
Comment
Question by:marypebbels
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10855338
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
0
 

Author Comment

by:marypebbels
ID: 10858385
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
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10858461
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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:marypebbels
ID: 10858878
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
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10860102
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
0
 

Author Comment

by:marypebbels
ID: 10860739
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
0
 

Author Comment

by:marypebbels
ID: 10860799
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"
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10861495
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
0
 

Author Comment

by:marypebbels
ID: 10868120
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
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 50 total points
ID: 10870808
I assumed that you were also getting the ApplicationID, so change the code like this:

strSQL = "Select Application_Name from Applications"
Set rs = cn.Execute(strSQL)
With Combo1
    Do While Not rs.EOF
    .AddItem rs.Fields(0)
    rs.MoveNext
    Loop
End With


AW
0
 

Author Comment

by:marypebbels
ID: 10876962
Arthur,

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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

697 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