[RESOLVED] DataCombo/MySQL

Printable View

Sep 30th, 2005, 10:16 PM
BrailleSchool

[RESOLVED] DataCombo/MySQL
Hi Guys. I have a form that can successfully access a MySQL database. I have two DataCombo's on this form. They are both set up in the same way but to access a different column in the database.

The first DataCombo has a name called DateUpdate. The datasource has a value of Adodc1 (also has the control or what ever you all it on the form). The DataField is set to the column I want. The DataFormat is set to the format I want. However, when I run the form, ONLY the first entry in the database is placed into the DataCombo, nothing after that. The test database has two entries for testing. The second entry does not go in.

This is the same for the other DataCombo on the form. Of course, each DataCombo has a different name. And is set in the same way but to access a different column.

But when I added the following, the form doesn't come up, just shows the error message below and then the app closes
VB Code:
```
Do While Not rs.EOF
DateUpdate.AddItem rs(0)
rs.MoveNext
Loop
 
Do While Not rs.EOF
TimeUpdate.AddItem rs(0)
rs.MoveNext
Loop
```
Which I found on a thread in here a moment ago and was given the following error message:

Compile error:
Method or data memeber not found.

.AddItem for the first Do While was highlighted.

The complete sub is:
VB Code:
```
Private Sub Form_Load()
Dim SQL As String
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
 
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
 
conn.CursorLocation = adUseClient
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
  & "SERVER=00.00.000.000;" _
  & "DATABASE=paddy_mbrblog;" _
  & "UID=paddy_mbrblog;" _
  & "PWD=mbrblog;" _
  & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
 
conn.Open conn.ConnectionString
SQL = "SELECT * FROM blog WHERE Username = '" & txtUpdateEntryUsername & "'"
 
Do While Not rs.EOF
DateUpdate.AddItem rs(0)
rs.MoveNext
Loop
 
Do While Not rs.EOF
TimeUpdate.AddItem rs(0)
rs.MoveNext
Loop
 
rs.Open SQL, conn, adOpenStatic, adLockReadOnly
 
rs.Close
    Set rs = Nothing
 
conn.Close
    Set conn = Nothing
End Sub
```
Please advise if you can. (DataMember property does not have anything in it and doesnt allow me to add anything either)
Sep 30th, 2005, 10:46 PM
dglienna

Re: DataCombo/MySQL

Edit out your ip address, user name, and PASSWORD. And, you might want to change it also.

unless you already did :)
Sep 30th, 2005, 10:48 PM
BrailleSchool

Re: DataCombo/MySQL

Quote:

Originally Posted by dglienna

Edit out your ip address, user name, and PASSWORD. And, you might want to change it also.

unless you already did :)

Ops, forgot about that. It's ok though, the real database is going to have different settings. This is only a test one. Has nothing important in it.

Still having a hard time with the population.
Sep 30th, 2005, 10:51 PM
dglienna

Re: DataCombo/MySQL
You have to use the same name as the table in your SQL
VB Code:
```
SQL = "SELECT * FROM blog WHERE Username = '" & txtUpdateEntryUsername & "'"
```
Sep 30th, 2005, 10:52 PM
dglienna

Re: DataCombo/MySQL

Also, you need a .movefirst before you can go thru it a second time.
Sep 30th, 2005, 10:55 PM
BrailleSchool

Re: DataCombo/MySQL
Quote:
Originally Posted by dglienna

You have to use the same name as the table in your SQL

VB Code:

SQL = "SELECT * FROM blog WHERE Username = '" & txtUpdateEntryUsername & "'"
blog is the table
Sep 30th, 2005, 10:57 PM
BrailleSchool

Re: DataCombo/MySQL
Quote:

Originally Posted by dglienna

Also, you need a .movefirst before you can go thru it a second time.

Is this correct?
VB Code:
```
Do While Not rs.EOF
rs.MoveFirst
DateUpdate.AddItem rs(0)
rs.MoveNext
Loop
 
Do While Not rs.EOF
rs.MoveFirst
TimeUpdate.AddItem rs(0)
rs.MoveNext
Loop
```
Update:
VB Code:
```
DateUpdate.AddItem rs(0)
```
Compile error:
Method or data member not found

Highlighted: .AddItem

Re: DataCombo/MySQL

Nope, you can't keep moving to the first, or you'll be in a loop

VB Code:

Do While Not rs.EOF
rs.MoveFirst
DateUpdate.AddItem rs(0)
rs.MoveNext
Loop
 
[COLOR=Red]rs.MoveFirst
[/COLOR]
Do While Not rs.EOF
TimeUpdate.AddItem rs(0)
rs.MoveNext
Loop

Sep 30th, 2005, 11:01 PM
dglienna

Re: DataCombo/MySQL
And, if you want to use the same field for both combo boxes, then you might want to apply some formatting as you add them.
VB Code:
```
additem format(rs.fields(0),"HH:mm")
```

Re: DataCombo/MySQL

Quote:
Originally Posted by dglienna
Nope, you can't keep moving to the first, or you'll be in a loop
VB Code:
Do While Not rs.EOF
rs.MoveFirst
DateUpdate.AddItem rs(0)
rs.MoveNext
Loop
 
[COLOR=Red]rs.MoveFirst
[/COLOR]
Do While Not rs.EOF
TimeUpdate.AddItem rs(0)
rs.MoveNext
Loop
VB Code:
Do While Not rs.EOF
rs.MoveFirst
DateDelete.AddItem rs(0) <-----error here for .AddItem
rs.MoveNext
Loop
 
rs.MoveFirst
Do While Not rs.EOF
TimeDelete.AddItem rs(0)
rs.MoveNext
Loop
Compile error:
Method or data member not found

Highlighted: .AddItem

Sep 30th, 2005, 11:07 PM
dglienna

Re: DataCombo/MySQL
Formatted:
VB Code:
```
additem format(rs.fields(0),"HH:mm")
```
Unformatted
VB Code:
```
additem rs.fields(0)
```
Sep 30th, 2005, 11:10 PM
BrailleSchool

Re: DataCombo/MySQL
Quote:
Originally Posted by dglienna

Formatted:

VB Code:

additem format(rs.fields(0),"HH:mm")

Unformatted

VB Code:

additem rs.fields(0)
The formatting is done through the properties window using DataFormat property.

Still getting the same error problem with .AddItem even when I change it to rs.Fields(0)

Even if I do:
rs.Fields("Column_Name") I still get the same error
Sep 30th, 2005, 11:13 PM
dglienna

Re: DataCombo/MySQL
I use this with no problem:
VB Code:
```
Do While rec.EOF = False
    RecID = rec.Fields(0)
    rec.MoveNext
  Loop
```
I am only returning one value, so that is kind of unnecessary. EOF is true after the first value.

Re: DataCombo/MySQL

Quote:
Originally Posted by dglienna
I use this with no problem:
VB Code:
Do While rec.EOF = False
    RecID = rec.Fields(0)
    rec.MoveNext
  Loop
I am only returning one value, so that is kind of unnecessary. EOF is true after the first value.
There is always going to be more than one value here so EOF is going to be needed I am sure.
VB Code:
Do While Not rs.EOF = False
rs.MoveFirst
DateDelete.AddItem rs.Fields(0)
rs.MoveNext
Loop
 
rs.MoveFirst
Do While Not rs.EOF = False
TimeDelete.AddItem rs.Fields(0)
rs.MoveNext
Loop
or
VB Code:
Do While rs.EOF = False
rs.MoveFirst
DateDelete.AddItem rs.Fields(0)
rs.MoveNext
Loop
 
rs.MoveFirst
Do While rs.EOF = False
TimeDelete.AddItem rs.Fields(0)
rs.MoveNext
Loop
STILL produces same error. DataCombo is a pain in my A$$

Sep 30th, 2005, 11:29 PM
dglienna

Re: DataCombo/MySQL

Oh, thought you were using a ComboBox, which has the additem property. I see that a datacombo is a bound (evil) control

If you insist, though, here is an excerpt:

Quote:

In the Properties window, set the properties of the DataCombo control as shown in the table below.Property Setting
Name dcbSuppliers
DataSource adoDataSource
DataField SupplierID
RowSource adoRowSource
ListField CompanyName
BoundColumn SupplierID
Sep 30th, 2005, 11:31 PM
BrailleSchool

Re: DataCombo/MySQL

Quote:

Originally Posted by dglienna

Oh, thought you were using a ComboBox, which has the additem property. I see that a datacombo is a bound (evil) control

If you insist, though, here is an excerpt:

I would use the ComboBox but cant find it hehe
Sep 30th, 2005, 11:38 PM
dglienna

Re: DataCombo/MySQL

Fifth from the top, on the left, next to the listbox. That will simplify things greatly! Set it to drop-down, and set the text to a message like "Choose Time"
Sep 30th, 2005, 11:40 PM
dglienna

1 Attachment(s)

Re: DataCombo/MySQL

Try this:
Sep 30th, 2005, 11:43 PM
BrailleSchool

Re: DataCombo/MySQL

Quote:

Originally Posted by dglienna

Fifth from the top, on the left, next to the listbox. That will simplify things greatly! Set it to drop-down, and set the text to a message like "Choose Time"

Replaced DataCombo with ComboBox now.

Both are set to 0 - Dropdown Combo

The one called DateDelete has a message of Choose Date
The one called TimeDelete has a message of Choose Time

DateDelete is going to house the dates
TimeDelete is going to house the times

The code you enclosed also doesnt work with mysql by what i can tell but when i did it, it added 0-100 in both of them. just have to modify to work with records.
Oct 1st, 2005, 02:11 PM
BrailleSchool

Re: DataCombo/MySQL

Bound controls may be evil but right now, VB6 is evil.