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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
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?
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.