MySQL & ASP tutorial [Just added: dsn-less - active server pages, ASP, vbscript

ASP,Learn,Install,MySQL,import,Access,databas

ASP/ VbScript Stats

Code: 174,253. lines
Jobs: 56. postings

How to support the site

Sponsored by:

You are in:

Latest Code Ticker for ASP/ VbScript.

Loop Through Array using Lowest Boundary (LBOUND) and Upper Boundary (UBOUND)
By Jason Tepoorten on 4/29

ASP special date displays
By Brian Battles WS1O on 4/28

(Screen Shot)

kGuestBook
By kibble on 4/28

Export Recordset to Excel
By Mark Buckley on 4/27

(Screen Shot)

fsStats Version 0.4.0
By Andrew Pilgrim on 4/27

(Screen Shot)

Character Map Application
By Gilbert Escaro on 4/26

(Screen Shot)

GetGroupInfo.vb s
By Ralph E. Montgomery on 4/25

NTuser.vbs
By Ralph E. Montgomery on 4/25

file upload.
By milad ghafooreyan on 4/25

Click here to put this ticker on your site!

Add this ticker to your desktop!

Daily Code Email

To join the 'Code of the Day' Mailing List click here!

Affiliate Sites

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

	Print
	Email

Submitted on: 8/6/2002 7:11:28 AM
By: snowboardr
Level: Intermediate
User Rating:

By 21 Users
Compatibility:ASP (Active Server Pages)

Users have accessed this article 14782 times.

(About the author)

Learn how to Install MySQL, import an Access database into MySQL database, and display data on a ASP page. Step by step tutorial with screenshots! Please leave questions or comments.. Updates: I have included a dsn-less connection!

Terms of Agreement:
By using this article, you agree to the following terms...
1) You may use this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.

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.

Other 16 submission(s) by this author

Report Bad Submission

Your Vote!

Other User Comments

8/6/2002 1:49:08 PM:Ivar Kristiansen
Importent information and know how, 5 stars.

8/6/2002 2:50:21 PM:snowboardr
Thanks for the comment, glad you liked it. - Jason

8/7/2002 10:18:41 PM:Kenny
The tutorial was great but I'm confused about one thing. In step 13 you say to create a DSN-less connection then you go on to create a DSN and use it in the asp script. Did I miss something or is that a typo? BTW.. I am currently using a mysql database on a web server without a dsn in case you need information on doing that.

8/8/2002 4:19:14 AM:snowboardr
You are correct, that is a typo thanks! And yes I would like to know how to connect without a dsn, was trying to figure that out.. - thanks and thanks for the globes everyone.

8/8/2002 9:03:37 AM:Ed Ruth
Great article...and please post the DSN-less version when you get it done. Thanks. - Ed

8/8/2002 9:07:24 AM:Rasputin
Hey SnowBoardr, This VISUAL approach to tutorials should be the defacto standard - it's nice and clean, and a straightforward way to learn something new. Nice job, and thanks! 5 Big Planets to you! - Ras

8/8/2002 10:20:45 AM:snowboardr
Yes, I like the visual approach myself, I knew there needed to be a "different" way to teach in a tutorial, because all the other tutorials, were not cutting it. ;) Hey Ed, as soon as I get the dsn-less connection working I will post it, but for now its not working for some reason. - Jason Thanks everyone!

8/8/2002 10:46:35 AM:Jose M Serrano
I just want to say one thing EXCELLENT TUTORIAL AND THANKS FOR SHARING IT. Also you have a well done site. Nice Nice Job. Jose "foxsermon" :0)

8/8/2002 10:55:34 AM:Sam Moses
Snwbrdr. in order to get the dsnless code we discussed running, you need to download and install myODBC. Oddly enough, it's easier than it sounds. Cheers :)

8/8/2002 11:08:44 AM:snowboardr
Thanks Jose, Still working on the site, It hasn't even been up a month yet. ;)

8/8/2002 3:02:57 PM:ISeeAll
to do a DSNLess connection to mysql, you would do something like this dim conn set conn=server.createobject("adodb.connecti on") conn.open "driver={mysql};server=yourserver;databa se=yourdatabase;uid=username;pwd=passwor d;" i use this all the time in asp, vb, and vb.net

8/9/2002 2:36:19 PM:Spencer Ruport
Just to be picky because I'm mean. ;) Its good habit to clear the RS object as it is where all the records are actually stored. 5 Globes from me.

8/10/2002 5:14:59 PM:snowboardr
The dsn-less connection is posted.

8/10/2002 6:27:08 PM:maniac
what versions are u using? my screens look completely diff - plus it doesn't work with either DSN or no DSN

8/11/2002 6:11:40 AM:snowboardr
3.23.51-max-debug

8/11/2002 8:38:12 AM:I var Kristiansen
Yeh, very smart designed turtorials on your homepage. Also asp-dll tutor an more there is importent for many. Thank you very match.

8/11/2002 11:37:49 PM:snowboardr
Thanks again, keep up the comments and those votes! Thanks, Jason / vzio.com

8/22/2002 8:13:23 PM:Marcus
Excellent tutorial! Just what my new project was needing. You said the DSN-less version was available . . . is that on PSC or your site?

8/25/2002 3:44:16 AM:Igor Krupitsky
Can you FULL OUTER JOIN in MySql?

8/26/2002 12:12:20 AM:snowboardr
Join Syntax: http://www.mysql.com/doc/en/J OIN.html

8/26/2002 4:03:02 PM:snowboardr
no space on "JOIN"

9/7/2002 2:38:28 PM:Igor Krupitsky
Thanks, but it says that full outer join is not supported yet: http://www.mysql.com/doc/en/TODO_sometim e.html

9/11/2002 11:22:57 AM:Igor Krupitsky
MySQL-Front project has been discontinued.

1/2/2003 7:51:34 AM:FirebirdDE
Wow. The best MySql Tutorial I ever read. 5 Globel !!

2/19/2003 2:43:58 PM:
VERY VERY VERY VERY HELPFUL

2/24/2003 11:54:11 PM:nfs
Yeah very helpfull. Cool work. Do you want to make money with your programming skills ? Software Objects provide following services : 1)Sell your software. 2)Post a software to be done. 3)Bid on the software projects. 4)Buy software Thanks and have a nice day. Software Objects http://www.thesoftwareobjects.com

3/4/2003 9:54:40 PM:
Hi.. i know i'm being silly but i'm totally clueless about mysql, etc and i'm currently doing a project on it! here's my silly question, where do i type the asp codes in? i've got all my tables running and my only problem is, i dont know how to link mysql to my website! anyway, where do i insert the asp codes? thanks! Great Job btw!

3/5/2003 7:26:58 PM:
i posted the previous comment.... don't worry bout it! i figured it out already! :) Have a great day!

Add Your Feedback!

Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.

NOTICE: The author of this article has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.

For feedback not related to this particular article, please click here.