MySQL ASP Connection (Using ODBC)

Hi, Sometimes ago I came around a problem.

The problem was I want to connect to my MySQL Database from ASP ( Active server pages) code. MySQL can be easily connected from PHP (Hypertext Pre Processor). But with ASP it is some thing else.

Then I found a way to solve the problem:

1. If you donot have mysql istalled in your machine then you can istall mysql 5.0 form
http://dev.mysql.com/downloads/mysql/5.0.html and MySQL 4.1. From
http://dev.mysql.com/downloads/mysql/4.1.html

2. Then download the suitable version of mysql-odbc-connector (3.51 or 5.1)
http://dev.mysql.com/downloads/connector/odbc/3.51.html
http://dev.mysql.com/downloads/connector/odbc/5.1.html 

After downloading this connector install it. You can check it is installed successfully or not from START-> COntrol Panel->Administrative Tools ->Data Sources (ODBC).
It will open ODBC Datasource Administrator. Click ADD. You will find MYSQL ODBC 3.51 or 5.1 Driver.
If you find it then it is installed correctly and ready for use.

3. If you istall mysql successfully then craete a table is the database (Use your own database).
 
e.g. CREATE DATABASE my_tst;

then Create a tbale:
CREATE TABLE my_test_table(id INT(3), name VARCHAR(20));

Then Insert some values in the db:
INSERT INTO my_test(id,name) VALUES(1,’udaybhanu’);
INSERT INTO my_test(id,name) VALUES(2,’qwer’);
INSERT INTO my_test(id,name) VALUES(3,’asdf’);
INSERT INTO my_test(id,name) VALUES(4, ‘zxcv’);

4. Then if you have IIS Installed on the server then create a virtual directory to run ASP ( If you have it already then leave this step.)

5. Then create a file named connect-mysql.asp and put the contents written below.

<%
Dim sConnection, objConn , objRS

‘define connection string, specify database driver and location of the database

sConnection = “DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=tst; UID=root;PASSWORD=root123; OPTION=3”
Set objConn = Server.CreateObject(“ADODB.Connection”)
objConn.Open(sConnection)

Set objRS = objConn.Execute(“SELECT id, name FROM tst1”)
While Not objRS.EOF
Response.Write objRS.Fields(“id”) & “, ” & objRS.Fields(“name”) & “<br>”
objRS.MoveNext
Wend

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

Now point your Internet Explorer to the path of the script (e.g. http://localhost/asp/connect-mysql.asp)

Then it will display all the id and names which are on the database.

And the problem is solved 🙂

Hope this blog will help you to solve your problem also.