Displaying details from a remote MySQL database

NUI Galway Home
Information Technology ››
- Information
- News
- Timetables
- Prospective Students
- Past Projects
- Programmes ››
  - Undergraduate
  - Postgraduate
- Research
- People
- Seminars
- Publications
- Intranet
- Contact Us
- Fun Links
- Links

Search Web Site [?]WHAT IS THIS?
Searches this and other areas of the Web site as you type. Results appear on this page.
Also searches the entire Web site (with results on a new page) if you press enter or click on 'Go'.

lugh2.it.nuigalway.ie

Home / Samples.
If you require further technical support then Email : Support@it.nuigalway.ie.

DSN-Less Connection to a remote MySQL Server Database

This page is a simple example of how to connect to a MySQL database running on a remote machine. View the source by logging in with your ftp client and viewing the files in the "samples" subdirectory.You can also scroll to end of this file for the code
The connection is made using DSN-Less connections. This example connects to a remote machine (danu.it.nuigalway.ie) which is running MySQL. It connects to a MySQL sample database, NorthWind, and retrieves a record set of the data in the "Customers" table. The User is "browser" and password is "browser".
For a DSN-Less connection you need to specify the driver to be used for the database.
The only difference from this page and one using Access is in the connection string :
adoCon.Open "DRIVER={DATABASE DRIVER};Server=DATABASE server;database=database name;uid=username;pwd=password"
The actual values used in this example Is at the End of the page. The connection String used:
adoCon.Open "DRIVER={MySQL ODBC 3.51 Driver};Server=danu2.it.nuigalway.ie;database=****;uid=*****;pwd=****;"

ALFKI	Alfreds Futterkiste	Maria Anders	Sales Representative	Obere Str. 57	Berlin
ANATR	Ana Trujillo Emparedados y helados	Ana Trujillo	Owner	Avda. de la Constitución 2222	México D.F.
ANTON	Antonio Moreno Taquería	Antonio Moreno	Owner	Mataderos 2312	México D.F.
AROUT	Around the Horn	Thomas Hardy	Sales Representative	120 Hanover Sq.	London
BERGS	Berglunds snabbköp	Christina Berglund	Order Administrator	Berguvsvägen 8	Luleå
BLAUS	Blauer See Delikatessen	Hanna Moos	Sales Representative	Forsterstr. 57	Mannheim
BLONP	Blondesddsl père et fils	Frédérique Citeaux	Marketing Manager	24, place Kléber	Strasbourg
BOLID	Bólido Comidas preparadas	Martín Sommer	Owner	C/ Araquil, 67	Madrid

-----------------------CODE BELOW---------------------
Dim adoCon 'Holds the Database Connection Object
Dim rsTest 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query to query the database

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={MySQL ODBC 3.51 Driver};Server=danu2.it.nuigalway.ie;database=****;uid=*****;pwd=****;"

'Create an ADO recordset object
Set rsTest = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database with
strSQL = "SELECT tblDetails.ID, tblDetails.Name, tblDetails.Address, tblDetails.City, tblDetails.Phone FROM tblDetails;"

'Open the recordset with the SQL query
rsTest.Open strSQL, adoCon

Response.Write ""
Response.Write ""

'Loop through the recordset
Do While not rsTest.EOF

'Write the HTML to display the contents of the recordset
Response.Write ""

'Move to the next record in the recordset
rsTest.MoveNext

Loop
Response.Write "

Name	Phone	Room	Location
" & rsTest("Name") & "	" Response.Write rsTest("Phone") & "	" & rsTest("Address") & "	" Response.Write rsTest("City") & "

"

'Reset server variables
rsTest.Close
Set rsTest = Nothing
Set adoCon = Nothing

Department of Information Technology
NUI Galway

This page was last updated November 25th, 2010.