In this tutorial we will show you how to connect to MySQL database. Everything is commented so you won't have trouble.
Connect with a DSN
DSN stands for 'Data Source Name'. It is an easy way to assign useful and easily rememberable names to data sources which may not be limited to databases alone. If you do not know how to set up a system DSN read our tutorial How to set up a system DSN.
<% 'declare the variables Dim Connection Dim Recordset Dim SQL
'declare the SQL statement that will query the database SQL = "SELECT * FROM TABLE_NAME" 'create an instance of the ADO connection and recordset objects Set Connection = Server.CreateObject("ADODB.Connection") Set Recordset = Server.CreateObject("ADODB.Recordset") 'open the connection to the database Connection.Open "DSN=dsn_name"
'Open the recordset object executing the SQL statement and return records Recordset.Open SQL,Connection
'first of all determine whether there are any records If Recordset.EOF Then Response.Write("No records returned.") Else 'if there are records then loop through the fields Do While NOT Recordset.Eof Response.write Recordset("FIRST_FIELD_NAME") Response.write Recordset("SECOND_FIELD_NAME") Response.write Recordset("THIRD_FIELD_NAME") Response.write "<br>" Recordset.MoveNext Loop End If
'close the connection and recordset objects freeing up resources Recordset.Close Set Recordset=nothing Connection.Close Set Connection=nothing %>
Connect without a DSN (using a connection string)
Let’s look at a sample script to get an idea how to connect to MySQL Server database without DSN:
<% 'declare the variables Dim Connection Dim ConnectionString Dim Recordset Dim SQL
'declare the SQL statement that will query the database SQL = "SELECT * FROM TABLE_NAME"
'define the connection string, specify database driver ConnString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=Your_Mysql_DB; " &_ "UID=mysql_username;PASSWORD=mysql_password; OPTION=3" 'create an instance of the ADO connection and recordset objects Set Connection = Server.CreateObject("ADODB.Connection") Set Recordset = Server.CreateObject("ADODB.Recordset") 'Open the connection to the database Connection.Open ConnString
'Open the recordset object executing the SQL statement and return records Recordset.Open SQL,Connection
'first of all determine whether there are any records If Recordset.EOF Then Response.Write("No records returned.") Else 'if there are records then loop through the fields Do While NOT Recordset.Eof Response.write Recordset("FIRST_FIELD_NAME") Response.write Recordset("SECOND_FIELD_NAME") Response.write Recordset("THIRD_FIELD_NAME") Response.write "<br>" Recordset.MoveNext Loop End If
'close the connection and recordset objects freeing up resources Recordset.Close Set Recordset=nothing Connection.Close Set Connection=nothing %>
Note: Don't forget to replace the constants (TABLE_NAME, FIELD_NAME) with real names of tables and fields in your database.