MySQL & ASP tutorial [Just added: dsn-less]

				



 







 
 
 
UPDATE 
- DSN-LESS connection click here.
 
 
 
 
 

 
 
 
 
 
MySQL 
& ASP
 
 
 
 
 
MySql Database
 
 
 
 

 
 
 
 
Using 
a MySql database in ASP is pretty simple, but it's hard to come accross 
a straight forward tutorial on ASP & MySQL. So here it is, a mysql tutorial 
for you.. Enjoy!
 
 
 
 

 
 
 
 
1. 
Start by downloading the latest version of MySQL at http://www.mysql.com.
 
 
 
 
 

The latest 
 versions are listed to the right on the front page.
On the 
 next page you will find different OS's, download the windows version.
Then find 
 the mirror closes to you and start the download!

 
 
 
 
 
2. 
Extract and run the setup.exe file
 
 
 
 
 

Install 
 to c:\mysql


 
 
 
 
3. 
Open Notpad and enter the following information:
 
 
 
 
 

Save it 
 to Windows Root Directory (C:/WINDOWS)
Save it 
 as my.ini

 
 
 
 
 

 
 
 
 
 
 
[mysqld]



basedir=c:/mysql

datadir=c:/mysql/data
 
 
 
 
 

 
 
 
 
4. 
Open Windows command prompt.
 
 
 
 

 
 
 
 
 

Start 
 > Programs > Accessories > Comand Prompt
Type the 
 following:

 
 
 
 
 

 
 
 
 
 
C:\>cd 
C:/mysql/bin
 
 
 
 
 
 
C:\mysql/bin>mysqld-nt --install
 
 
 
 

 
 
 
 
5.	
The service is now installed. It can be started and stopped with the Windows 
Service manager, or the NET START/STOP commands.
 
 
 
 

 
 
 
 

 
 
 
 
6. 
Now download MyODBC which is the driver to connect to mysql from within 
ASP.
 
 
 
 

 
 
 
 
 

 
 
http://www.mysql.com

On the 
 right side click MyODBC_VERSION#
Download 
 the exe and install

 
 
 
 
 

 
 
 
 
7. 
A nice program to have when using MySQL is MySQL-Front which is a pretty 
easy to understand program that access your mysql database(s).
 
 
 
 

 
 
 
 
 

http://www.anse.de/mysqlfront/

 
 
 
 
 

 
 
 
 
8. 
Start up MySQL front.
 
 
 
 

 
 
 
 
 

Click 
 New
Name the 
 connect whatever you like..
Host / 
 IP: Localhost or Your IP
Password 
 can be left as root for now..
Click 
 connect.
Right 
 click root@localhost and click create new database.
Name the 
 new database tutorialdb


 
 
 
 
 
 

 
 


 
 
 
 
 
 
 
 
Page 
2 - MySQL Database
 
 
 
 

 
 
 
 
9. 
Entering data into the database can be done several ways, manualy in MySQL-Front 
or by Importing from another database. Here we are going to import this 
database: Download 
database
 
 
 
 

 
 
 
 

 
 
 
 
10. 
Im-/Export > ODBC Import
 
 
 
 

 
 
 
 
 

 
 
 
 
 

 
 
 
 
11. 
Check the MS Access File radio button then tables will load in the table 
list box below, select the tables you want to important and then click import.
 
 
 
 

 
 
 
 
 

 
 
 
 
 

 
 
 
 
12. 
Now you can view the table and its data.
 
 
 
 

 
 
 
 
 

 
 


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Page 
3 - MySQL Database
 
 
 
 

 
 
 
 
13. 
Now we need to create a dsn connection this really easy and takes about 
3 minutes at most.
 
 
 
 

 
 
 
 
 
 
 
 
 
NEW! 
> DSN-LESS connnection:
 
 
 
 
Ok 
here it is, people have been asking for how to connect to MySQL without 
a dsn.
 
 
 
 
 
 
 
 
 
<%

set conn = createobject("ADODB.Connection")

conn.open = "DRIVER={MySQL ODBC 3.51 Driver};"_

& "SERVER=localhost;"_ 

& "DATABASE=useremails;"_

& "UID=root;PWD=; OPTION=35;"  
set rs = 
conn.Execute("SELECT * FROM UserEmails")

%> 
 
 
 
 
 
 
 
 
 
 

 
 
 
 
14. 
Start > Administrator Tools > Data Sources (ODBC)
 
 
 
 

 
 
 
 
 

 
 
Click 
 system dns tab

Click 
 the "Add..." button.
Scroll 
 to the bottom and select MySQL x.xx Driver

 
 
 
 
 

 
 
 
 
15. 
Fill in the data like it is shown below in the screenshot.
 
 
 
 

 
 
 
 
 

When finished 
 filling it in, click "Test Data Source" to see if it works..
Click 
 OK

 
 
 
 
 
 

 
 
 
 
 

 
 
 
 
16. 
Now for the ASP... (If the ASP doesn't show up, please visit page 
3 below...)
 
 
 
 

 
 
 
 
 
 
 
 <% 
 


 Set my_conn = Server.CreateObject("ADODB.Connection") 
 

 Set rs = Server.CreateObject("ADODB.Recordset") 
 

my_conn.Open 
"DSN=mysql_dsn" ' Data source name


strSQL 
= "SELECT * FROM UserEmails" 



' Execute SQL statement 

Set rs = my_conn.Execute(strSQL) 



Do while not rs.eof ' Do until it reaches end of db 





Response.Write "User ID# " & rs("userID") & "

" 

Response.Write "Name: " & rs("name") & "

" 

Response.Write "Email: " & rs("email") & "

" 




rs.MoveNext ' Next record 

loop 



my_conn.close ' Close database connection 

set my_conn = nothing 'obj 
variable released 



%> 


  
 
 
 
 
 
 
Page 
1 - Page 
2 - Page 
3
 
 
 
 
 

 
 
 
 
 
 
<% 
 Response.End %>

 
 
 
 
 
 
(The 
End) 
 
 
 
 
 

 
 
 
 
 
View 
other tutorials by me at my ASP web development Web site.
 
 
 
 
 
 
 
 
 
 
 
Leave a comment! And vote if you liked my tutorial.