Displaying MYSQL data on form - please help!

   I am developing an application using Visual Basic 5, I have a login form which hooks up to a MySQL database and a login form which checks the username and password, this works fine!  What I next need to do is when the main form of the program loads, I need to pull back ALL records from a different table in the database in a sort of data set - the default ADO data set with VB5 does not have MySQL listed....

Does anyone have any ideas?

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

KarenConnect With a Mentor Analyst programmerCommented:
Sorry for the delay in getting back to you.

Well....actually an ADO control is *not* the "best" option, it is the "simplest" option. It is much more flexible to do it all in code.

Okay, so you already code which opens a connection, something like:

Dim myConnection As New ADODB.Connection
myConnection.Connection.Open "My connection string"

Now you need to create a recordset object, which is a set of records from your database. If you want to get all of the records from the database in order to show them all on the screen you would need to do something like:

Dim myData As New ADODB.Recordset
myData.CursorLocation = adUseClient
myData.Open "SELECT * FROM TheNameOfYourTable", myConnection, adOpenStatic, adLockReadOnly
Do While myData.EOF = False
   ' Do stuff with each record. e.g. if you have a combobox called Combo1
    Combo1.AddItem myData!NameOfOneOfYourFields
KarenAnalyst programmerCommented:
You will need to use a connection string, something like:

Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DRIVER={MySQL ODBC 3.51 Driver};SERVER=xxx;PORT=3306;DATABASE=xxx;OPTION=19035;"

Replace with your server and database.
damianb123Author Commented:
Thanks snowberry,
   I already have a connection string, it's just more a case of how I display the rows as seperate rows within the form?

If I do something like:

text1.text = !first_name

It displays the first name correctly, but only for the LAST record in the table.....

Can you help?
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

KarenAnalyst programmerCommented:
I'm not sure what you are asking. Do you want to use an ADO control or do all the connecting in the code? Do you want to move through records with a next/previous button, or have all records displayed on the one form?
damianb123Author Commented:
All of the above :-)

I would like to be able to go previous and next with the various text boxes laid out, and also have the option of displaying all on the form.....  I know an ADO control is the best option, but I couldn't estab;ish how to use the control.

I already have a connection string, which works very well.....
KarenAnalyst programmerCommented:
If you need more help with
(a) specifying which data is loaded, e.g. "I only want records where a persons name starts with the letter A"; or
(b) how to actually show it, e.g. put each bit of data onto the form so the user can see it,
then just ask. I would need to know more about what data you are showing and how you want to show it. Also info about the tables in the database that you want to read, e.g. their names and the field names that you want to load.
damianb123Author Commented:
Perfect, tweaked a little and got what I wanted.  Thx
All Courses

From novice to tech pro — start learning today.