asked on

Creating a List Box using mysql table

Hi,

I have been trying to get a list box to work that shows a list of peoples names from my mysql table.

I have the dblist control on my form however can't get the information to display. This is rather urgent if someone can help me as i have been going round in cirlces for a while now!!!

Further to this, i have 4 text boxes and 1 check box on the same form that as the user clicks on the persons name in the list, it displays contact information for them.

I hope someone can be of assistance.

Thanks,

Andrew.

JesterToo

If you will post your code someone may be able to help answer this question faster... we need to see what you're doing in order to assist you.

Regards,
Lynn

andrewl77

ASKER

This is what i have scrounged together.

Private Sub listall_Click()
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "DSN=umas;database=umas;uid=umas;pwd=umas"
conn.Open

Set rs.ActiveConnection = conn

rs.Open "Select * from umpire", conn, adOpenStatic, adLockOptimistic

Set Me.listall.DataSource = rs

Me.listall.BoundColumn = 1
Me.listall.DataField = rs.umpireid

conn.Close
Set conn = Nothing

End Sub

However i am not sure how much is right or wrong.

pradapkumar

the code snipped you posted here only updates the dblistcontrol. So, every time you clicked on the dblist control that it self again updates the name of the umpires rather than filling of other details.
to overcome this propblem you has to do as following.

1. Fill the dblist control using recordset setup in form_load or form_activate events. so, simply transfer the code segment to one of that event. And do remember to declare the recordset object & connection object as form level objects then only the code works fine.

2. On click_event of dblist control simply use the umpireid(i.t. assigned to controls datafield) and create new query to fetch the details of umpire from database and assign appropriate values to other controls in that form.

I hope this will help you well.

Pradap Kumar

andrewl77

ASKER

Pradap,

I have moved the code to the Form_Load, i am getting a compile error: method or data not found.

Any ideas on this?

This is my first full blown application in VB6 so i am struggling with some of the code.

Andrew.

pradapkumar

have you declared the record set object and connection as formlevel objects?
If you did correctly just move the code segment to Form_Activate Event. or post your complete code and i send you the completed code 4 u.

andrewl77

ASKER

Here is the code i have.

Private Sub Form_Load()
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "DSN=umas;database=umas;uid=umas;pwd=umas"
conn.Open

Set rs.ActiveConnection = conn

rs.Open "Select * from umpire", conn, adOpenStatic, adLockOptimistic

Set Me.listall.DataSource = rs

Me.listall.BoundColumn = 1
Me.listall.DataField = rs.umpireid
Set conn = Nothing

End Sub

Also in the properties section, when i click on the dblist i have the following:

Databindings - Nothing/blank
Datafield - nothing/blank
Dataformat - nothing/blank
Datamember - nothing/blank
Datasource - nothing/blank
BoundColumn - nothing/blank
listfield - nothing/blank
Rowsource - nothing/blank

Sachin Parab

remove Set conn = Nothing

you need to toggle following
lisfield as fieldname
rowsource from where you want to provide data
DataField where you want ot sstore data
BoundColumn field you want to map with

ASKER CERTIFIED SOLUTION

pradapkumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

andrewl77

ASKER

Pradap,

I have pasted the code as you entered it. However now i have got a compile error at the point of
Me.listall.DataField = rs.umpireid - My understanding is that "listall" is the name of the dblist control on the form and umpireid is the name of the field in the table that is used as the umpire reference correct?

Thanks,

Andrew

Option Explicit
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub Form_Load()

conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "DSN=umas;database=umas;uid=umas;pwd=umas"
conn.Open

Set rs.ActiveConnection = conn

rs.Open "Select * from umpire", conn, adOpenStatic, adLockOptimistic

Set Me.listall.DataSource = rs

Me.listall.BoundColumn = 1
Me.listall.DataField = rs.umpireid
End Sub
Private Sub Form_Unload(Cancel As Integer)
rs.Close
conn.Close
Set conn = Nothing
End Sub

pradapkumar

>>>Me.listall.DataField = rs.umpireid

this should look like this

Me.listall.DataField = rs.fields("umpireid")

andrewl77

ASKER

I have now been able to get data to display in the list box, however i now need to setup the ability to have 3 fields on the 1 line. This is the code that i have got to work. However this displays the information on 3 seperate lines. Any ideas?

conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "DSN=umas;database=umas;uid=umas;pwd=umas"
conn.Open

Set rs.ActiveConnection = conn

rs.Open "Select * from umpire", conn, adOpenStatic, adLockOptimistic

Set Me.listall.DataSource = rs
Me.listall.AddItem rs!umpireno
Me.listall.AddItem rs!firstname
Me.listall.AddItem rs!surname

andrewl77

ASKER

conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "DSN=umas;database=umas;uid=umas;pwd=umas"
conn.Open

Set rs.ActiveConnection = conn

rs.Open "Select * from umpire", conn, adOpenStatic, adLockOptimistic

Set Me.listall.DataSource = rs

Do Until rs.EOF
Me.listall.AddItem rs!firstname & " " & rs!surname
rs.MoveNext
Loop

This was the final code that actually work!

Sachin Parab

Good finally you got the bingo