Solved

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

Posted on 2004-04-18
11
970 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
  • 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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

762 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

18 Experts available now in Live!

Get 1:1 Help Now