[RESOLVED] DataCombo/MySQL

[BrailleSchool]

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

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:

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)

[dglienna]

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

unless you already did

[BrailleSchool]

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.

[dglienna]

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

SQL = "SELECT * FROM blog WHERE Username = '" & txtUpdateEntryUsername & "'"

[dglienna]

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

[BrailleSchool]

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

SQL = "SELECT * FROM blog WHERE Username = '" & txtUpdateEntryUsername & "'"

blog is the table

[BrailleSchool]

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

Is this correct?

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:

DateUpdate.AddItem rs(0)

Compile error:
Method or data member not found

Highlighted: .AddItem

[dglienna]

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

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

[dglienna]

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.

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

[BrailleSchool]

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

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

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

[dglienna]

Formatted:

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

Unformatted

additem rs.fields(0)

[BrailleSchool]

Formatted:

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

Unformatted

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

[dglienna]

I use this with no problem:

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.

[BrailleSchool]

I use this with no problem:

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.

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

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$$

[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:

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

[BrailleSchool]

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

[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"

[dglienna]

Try this:

[BrailleSchool]

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.

[BrailleSchool]

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