Hi,

How to establish MySQL DB connection from ASP Script ? Anyone Please provide the neccessory information in detail !

Hi priyan,
Using MyODBC To Access Your MySQL Database Via ASP.

Steps to follow
1. Installing MyODBC

I'm assuming that you either already have MySQL installed either on your PC, or have the login details of a remote PC where MySQL is installed. Either way, make sure you have access to a MySQL server before continuing. If you’ve never used MySQL, checkout Mitchell's article entitled "MySQL: Open-Source Power" here [End Note]

1. Start by firing up your web browser and heading over to MySQL AB :: The world's most popular open source database. Take a look under the "Versions" tab on the right hand side of the page and click on MyODBC. At the time of writing, the current version on MyODBC was 2.50.39.
2. Under the "MyODBC for Windows" section, click on the link that matches your operating system. In this article I'm focusing on Windows NT/2000, so click the NT/2000/XP link. This will take you to the mirrors page. Click on the link for the mirror that's closest to you and save the download to a folder on your hard drive (The download is a 1.45MB .zip file, so visit WinZip.com to download WinZip if you haven’t got it already).
3. Once complete, extract the zip file to a temporary directory and run the included setup.exe file. This will install the MyODBC driver. When the installation is completed, simply click on the close button to bypass the data sources configuration dialog. Click the OK button and you're done!

2. Setting up a system DSN

The first way that we can connect to a MySQL database is via a System Data Source Name (DSN). Using a system DSN to setup a connection details to a MySQL database is easy, and can be done through the "Data Sources (ODBC)" option in the Administrative folder of the control panel. Let's create one now (I will assume that you have MySQL installed on the same machine as your Windows NT/2000 web server).

1. Click on the start menu -> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC) menu option. This will load the ODBC data source administrator tab.
2. Because we want to create a system DSN that is accessible to any user logged into our web server, click on the "System DSN" tab, and then on the "Add..." button.
3. Select the MySQL driver from the bottom of the list and click the finish button.
4. The MySQL driver configuration dialog appears. It contains seven text boxes and a variety of check boxes.

Into the Windows DSN name field, enter "mysql_dsn". This is simply the name that we will use to refer to our DSN from within ASP. Into the MySQL host field, enter either the Net BIOS name or I.P. address of your MySQL server. If you have MySQL installed locally (on the same machine that you are creating the system DSN on), then enter 127.0.0.1.

In the database name field, enter the name of our address book database, "address". This tells MySQL the name of the database we want to issue our SQL queries to when we are connected.

Enter your MySQL username and password details into the user and password fields. If you aren't sure what they are and you're running MySQL on Windows, then run c:\mysql\bin\winmysql.exe and click on the "my.ini Setup" tab. Under the "[WinMySQLAdmin]" section, you'll find your default MySQL username and password. Leave both the port and sql command on connect fields empty.
5. Lastly, make sure that the "Return matching rows" checkbox is ticked. Click on the OK button to create your new MySQL system DSN.

You'll notice the new MySQL DSN is displayed in the system data sources list of the system DSN tab. The details of our new system DSN are stored in the registry under the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\mysql_ds n tab. You can use the edit -> find menu option of regedit (start -> run -> "regedit") to search for "mysql_dsn" to see how its details are stored.

To use a DSN to connect to our MySQL database, we used the following parameter for the open method of our ADO connection object:

"DSN=mysql_dsn"

3. set a connection string

adoConn.Open "Driver={mySQL}; Server=localhost; Port=3306; Option=0; Socket=; Stmt=; Database=address; Uid=admin; Pwd=password;"

i think it will help to connect mysql in ASP

__________________
With,
J. Jeyaseelan
Everything Possible

Thanx Jai,

Good theoretical presentation...

Here some practical representation of ASP codes for connecting MySQL.

<%
Dim sConnection, objConn , objRS

sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=Your_Mysql_DB; UID=mysql_username;PASSWORD=mysql_password; OPTION=3"

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

objConn.Open(sConnection)

Set objRS = objConn.Execute("SELECT FirstName, LastName FROM tblUsers")

While Not objRS.EOF
Response.Write objRS.Fields("LastName") & ", " & objRS.Fields("FirstName") & "<br>"
Response.Write & " "
objRS.MoveNext
Wend

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


- Priyan..