ADO Compile Error

[tschilbach]

I am trying to populate my listbox from an access database and I seem to get this compile error whenever I go to preview it:

Compile error:

user-defined type not defined

Here is an example of the code I am using:


'Connecting to an Access Database using ADO
option Explicit
Dim cnn as ADODB.Connection
Dim rs as ADODB.Recordset

private Sub Form_Load()
set cnn = new ADODB.Connection
set rs = new ADODB.Recordset

' Open the database connection and recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;" & _
"Persist Security Info=false"
rs.Open "Select * from Customers", cnn, adOpenStatic, adLockOptimistic

' Place values in the comboBox control
Do While rs.EOF = false
Combo1.AddItem rs!CompanyName
rs.MoveNext
Loop
End Sub




Any ideas as to why I may be getting this error?


Timothy H. Schilbach
Alpha Omega Design Inc.
tschilbach@aodinc.com

[Cakkie]

Check your refferences to see if there's a refference to ADO. The error is typically for missing refferences, and since you are only refferencing ADO, I don't think we need to look any further.
Go to the project>refferences menu, and see if 'Microsoft ActiveX Data Objects 2.X' is selected (X is the version, currently, 2.7 is the latest)

Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Cimperiali]

may be it is the same, but I remember I read something about performances with different sintax when reading data. It may be better:

Combo1.AddItem rs("CompanyName")

instead of

Combo1.AddItem rs!CompanyName

Cesare


Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

[tschilbach]

I found the problem to be that I did not have teh ADO lib refrenced. When I did refrence the library, I got a new message:

Compile Error:

Wrong number of arguments or invalid poperty assignment

I useed both of these methods and this is where VB says the problem is:


' I am populating the combo box until the end of the database
Do While rs.EOF = false
Combo1.AddItem rs("CompanyName")
rs.MoveNext
Loop




Any ideas?


Timothy H. Schilbach
Alpha Omega Design Inc.
tschilbach@aodinc.com

[Iouri]

Tom,
Is ADO2.7 is the latest version?
I thought that ADO2.6 SP1 is the latest. I just want to confirm if I missed the latest version.

Iouri Boutchkine
iouri@hotsheet.com

[sotoasty]

You might try the full reference to the field object like...


rs.fields("CompanyName").value

[tschilbach]

I only have the 2.6 version and I have SP5 loaded for Visual Studio. Its supposed to have the latest ADO in it.


Timothy H. Schilbach
Alpha Omega Design Inc.
tschilbach@aodinc.com

[tschilbach]

No good so far, now I get a 3001 Runtime error that states the object is a wrongt type of in conflict of another object. This object seems to be the one its talking about:


rs.Open "select * from customers", con, adOpenStatic, adLockOptimistic




Here is my whole source code:

option Explicit
Dim con as ADODB.Connection
Dim rs as ADODB.Connection

private Sub Form_Load()

set con = new ADODB.Connection
set rs = new ADODB.Connection

' I am establishing a database connection
con.Open "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;" & _
"Persist Security Info=false"

' I am setting my querry
rs.Open "select * from customers", con, adOpenStatic, adLockOptimistic

' I am populating the combo box until the end of the database
Do While rs.EOF = false
Combo1.AddItem rs.Fields("CompanyName").Value
rs.MoveNext
Loop

End Sub



Any suggestions as to what may be causing this?


Timothy H. Schilbach
Alpha Omega Design Inc.
tschilbach@aodinc.com

[Cakkie]

Well, ADO 2.7 comes with .Net Beta 2 or was it Whistler, so it might not be available yet for the public. I haven't tested with it yet.

Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Cimperiali]

Solution is:
Combo1.AddItem rs.Fields("Company Name") 'a space between Comany and Name: you have to match exact name of fields in DB!

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, Bruno Paris and all the other wonderful people who made and make Codeguru a great place. Come back soon, you Gurus.

[tschilbach]

I Tried separating the name like you said:

("Company Name") instead of ("CompanyName")

I am still getting the same errors. Have you tried executing the code I had cut and paste earlier? I am racking my brain as to what is happening. Its almost as if it doesnt recognize the methods for Combo1.Additem.

Any other ideas would be awesome.


Timothy H. Schilbach
Alpha Omega Design Inc.
tschilbach@aodinc.com

[d.paulson]

If Company Name actually does have a space in it, you must enclose it within the square brackets.

Combo1.AddItem rs![Company Name]

David Paulson

[tschilbach]

Ok, looks like we are getting somewhere. I added the following code and I now get:

Compile Error:

Wrong number of arguments or invalid property assignment.


' I am populating the combo box until the end of the database
Do While rs.EOF = false
Combo1.AddItem rs!["CompanyName"]
rs.MoveNext
Loop



it seems to do this at the RS! part. Ok so lets try this another way:


' I am populating the combo box until the end of the database
Do While rs.EOF = false
Combo1.AddItem rs.Fields["CompanyName"].Value
rs.MoveNext
Loop




Now it gives me this error:

Compile error:
Expected end of statement

I get the same error with this code too:


' I am populating the combo box until the end of the database
Do While rs.EOF = false
Combo1.AddItem rs["CompanyName"]
rs.MoveNext
Loop




Any last suggestions?




Timothy H. Schilbach
Alpha Omega Design Inc.
tschilbach@aodinc.com

[tschilbach]

Oh yeah, I meant to type Company Name with the spaces in them in the post I just put up. Same result.


Timothy H. Schilbach
Alpha Omega Design Inc.
tschilbach@aodinc.com

[d.paulson]

When you use the bang operator '!' , you do not use the quotation marks.

Combo1.AddItem rs![Company Name]

David Paulson

http://www.lanu.london.on.ca/iamcdn.gif

[muthuraj]

There is no error in your code. Just you have not added the Microsoft Activex Data Object 2.x Library to your project. Just add this reference it will work fine.

[tschilbach]

Actually, Yes I have. I have added the refrence for the 2.6 lib and it still does not work. This is driving me nuts.


Timothy H. Schilbach
Alpha Omega Design Inc.
tschilbach@aodinc.com

[tschilbach]

ok I changed it as per your suggestion and I still get:

Compile error:
wrong number of arguments or invalid property assignment.

I am using the ADO 2.6 Library, is there any known problems with this Lib? Should I use the 2.5 or 2.5 Multi-dimensional?

No matter what, adding my data to the combo box seems to be a failure. Is there a better way to do this maybe? Here is my full code as it stands now:


option Explicit
Dim con as ADODB.Connection
Dim rs as ADODB.Connection

private Sub Form_Load()

set con = new ADODB.Connection
set rs = new ADODB.Connection

' I am establishing the connection
con.Open "Provider = Microsoft.Jet.OLEDB;" & _
"Data Source = C:\Nwind.mdb;" & _
"Persist Security Info = false"

' I am setting my querry
rs.Open "SELECT * FROM Customers", con, adOpenStatic, adLockOptimistic

' I am populating a combo box
Do While rs.EOF = false
Combo1.AddItem = rs![Company Name]
rs.MoveNext
Loop

End Sub




I am using a simple for called form1 and I am using a single combobox called Combo1. What can I do to simplify this process?

My ultamate goal is to load the whole Customers table into a record set and have a series of combo boxes take the traget feild and display them. When I set out to do this I thought this to be a no sweat problem. I can get this to work with a list box, but not with my **** combo box. What else could we possibly to here?

Thank you all in advance for any solutions you could bring to this problem.


Timothy H. Schilbach
Alpha Omega Design Inc.
tschilbach@aodinc.com

[d.paulson]

Found a few mistakes



option Explicit
Dim con as ADODB.Connection
Dim rs as ADODB.Recordset

private Sub Form_Load()

set con = new ADODB.Connection
set rs = new ADODB.Recordset

' I am establishing the connection
con.Open "Provider =Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = E:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;" & _
"Persist Security Info = false"

' I am setting my querry
rs.Open "SELECT * FROM Customers", con, adOpenStatic, adLockOptimistic

' I am populating a combo box
Do While rs.EOF = false
Combo1.AddItem rs!CompanyName
rs.MoveNext
Loop

End Sub





You defined the recordset as a connection
You did not fill in provider properly
You may need to use Jet 3.51 if you do not have Jet4.0 installed
You need to use the full path of the database in you are not in it's directory
This is the location of the db on my machine, change it to suit yours
Companyname has no space in it.
I didn't know you were using nwind otherwise I would have picked up on that.





David Paulson

http://www.lanu.london.on.ca/iamcdn.gif

[tschilbach]

I have checked a few things:

I am using Jet 4.0+ (installed with office XP)
I am using ADO 2.5 Lib (2.6 on my laptop)
c:\Nwind.mdb is where I have my Access file located (This is a test partition on my 2k Box).

Now that you have pointed out that I am using a recordset as a connection instead of a recordset I say "Doh!" I have been so focused on my last part of the sub that I completely forgot the basics.

I use the code you provided and I now get this error:

Runtime Error -2147467259 (80004005)
Could not find installable ISAM

I have looked through my MSDN CD's and could not find anything helpful. What would cause this error? This is one I have never seen.

Well anyways, change is good, no? Thank you very much for you much needed feeback.


Timothy H. Schilbach
Alpha Omega Design Inc.
tschilbach@aodinc.com

[d.paulson]

I just tried it again. A new project with a combo box. made reference to ado 2.5 (That all I have). Pasted the code snippet that I posted. Works as expected.

David Paulson

http://www.lanu.london.on.ca/iamcdn.gif

[d.paulson]

Try putting the ado data control on a form and build the connection with it (just point and click to build it). It will let you test this connection, to see if it will work. You then can copy and paste the connection string to use in your code.

David Paulson

http://www.lanu.london.on.ca/iamcdn.gif

[tschilbach]

It works like a charm after I re-installed SP5 for VS6. Thank you for all your help. Just one final question and I will have everything I need to create a few demo programs using ADO. This has been such a great learning expereince.

I want to add a 2nd combo box and populate another field to it. Lets say the "Contact Name". I want the 1st combo box which will have the "Company Name" be linked to the 2nd combo box. When you select a company from the 1st box I want the 2nd combo box to display only the Contact Names that pertain to the 1st combo box. Since I have loaded all the records into memory space I was thinking that I could use the Filter property. Any ideas how this might work or any resources you could point me too about linking two combo boxes with ADO?

Thank you in advance.


Timothy H. Schilbach
Alpha Omega Design Inc.
tschilbach@aodinc.com

[d.paulson]

Well, since there is only one Contact name for each company name, you do not need a combo box, a text box is sufficient. Since there is only one company name (not several with the same name), you need to 'FIND' the record so, in order to acheive this, you can place the following code in the combo click event. I should mention that some company names include an apostophe. In order to make it work in the Find, you need to double them up. Also, since companyname is a string value, it must be enclosed in apostophes. Copy and paste this code to see how it works. Add a text box (text1).


option Explicit
Dim con as ADODB.Connection
Dim rs as ADODB.Recordset

private Sub Combo1_Click()
rs.MoveFirst
rs.Find "CompanyName = '" & Replace(Combo1.Text, "'", "''") & "'", , adSearchForward, adBookmarkFirst

LoadCustomerData
End Sub

private Sub Form_Load()

set con = new ADODB.Connection
set rs = new ADODB.Recordset

' I am establishing the connection
con.Open "Provider =Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = c:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;" & _
"Persist Security Info = false"

' I am setting my querry
rs.Open "SELECT * FROM Customers ORDER BY CompanyName", con, adOpenStatic, adLockOptimistic

' I am populating a combo box
Do While rs.EOF = false
Combo1.AddItem rs!CompanyName
rs.MoveNext
Loop

End Sub



private Sub LoadCustomerData()
Text1 = rs!ContactName
End Sub





David Paulson

http://www.lanu.london.on.ca/iamcdn.gif

[tschilbach]

Thank you for the excellent code and pointers. But I am also interrested in putting selections from one box and filtering it on the 2nd box so that one effects what the other views.

This is not so much for this database, but for future refrence. I think it would be a great way to narrow down many feilds with many selections that could relate to the previous field.

Thank you for everything.


Timothy H. Schilbach
Alpha Omega Design Inc.
tschilbach@aodinc.com

[d.paulson]

I added a bit more. 2 combo baxes and 2 text boxes.

option Explicit
Dim con as ADODB.Connection
Dim rs as ADODB.Recordset

private Sub Combo1_Click()
rs.MoveFirst
rs.Filter = "CompanyName > """ 'set the recordset back to all records
rs.Find "CompanyName = '" & Replace(Combo1.Text, "'", "''") & "'", , adSearchForward, adBookmarkFirst
If Not rs.EOF then
LoadCustomerData
else
MsgBox "Account not found"
End If
End Sub

private Sub Form_Load()

set con = new ADODB.Connection
set rs = new ADODB.Recordset

' I am establishing the connection
con.Open "Provider =Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = e:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;" & _
"Persist Security Info = false"

' I am setting my querry
rs.Open "SELECT * FROM Customers ORDER BY CompanyName", con, adOpenStatic, adLockOptimistic

' I am populating a combo box
Do While rs.EOF = false
Combo1.AddItem rs!CompanyName
rs.MoveNext
Loop

End Sub



private Sub LoadCustomerData()
Text1 = rs!ContactName
Text2 = rs!ContactTitle
PopulateContactCombo
End Sub




private Sub PopulateContactCombo()
rs.Filter = "ContactTitle = '" & Trim(Text2) & "'" 'Filter the recordset
Do Until rs.EOF
Combo2.AddItem rs!CompanyName 'Load the combo
rs.MoveNext
Loop
End Sub







David Paulson

http://www.lanu.london.on.ca/iamcdn.gif

[tschilbach]

Thankyou, This is exactly what I was looking for.


Timothy H. Schilbach
Alpha Omega Design Inc.
tschilbach@aodinc.com