ASP connection to MySQL

Source: Internet
Author: User
Tags dsn

First, install the ODBC database driver of MySQL in the system. The current stable version is 3.51. Yes: http://dev.mysql.com/downloads/connector/odbc/3.51.html. Download and install it. On the driver page in Control Panel> Administrative Tools> data source (ODBC), if MySQL ODBC 3.51 driver exists, it indicates that the driver has been installed successfully and you can start to write the program.

The following is the program I used during the test. I will not introduce it any more.
<%
'Set MySQL Connection Properties
'Variable description:
'Myhost: MySQL database address
'Mydb: MySQL database name used
'Myuid: The account used to connect to the MySQL database
'Mypwd: password of the account used to connect to MySQL DATA
'Mychareset: Specifies the encoding type used by the client. Use it based on actual conditions.
'Generally, gb2312 utf8 GBK encoding is used. If there are still garbled characters in all three tests.
'Check your settings.
'Database settings start
Dim myhost, mydb, myuid, mypwd
Myhost = "localhost"
Mydb = "knowldge"
Myuid = "root"
Mypwd = "mysqladmin"
Mychareset = "gb2312"
Strconnection = "driver = {MySQL ODBC 3.51 driver}; server =" & myhost & "; database =" & mydb & "; user name =" & myuid &"; password = "& mypwd
Set conn = server. Createobject ("ADODB. Connection ")
'Connect to the database
Conn. Open strconnection
'Set the client character encoding
Conn.exe cute ("set names '" & mychareset &"'")
'Database settings are complete
%>
Or:
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open "driver = {MySQL ODBC 3.51 driver}; server = 127.0.0.1; database = shops; user = root; Password = xxx ;"

The above is the connection method using ADODB, and the default port 3306 is normal.
But an error occurs when the port is changed to 3333.
Server = 127.0.0.1: 3333; this is not the case. It can be used in PHP.
Server = 127.0.0.1, 3333; MSSQL changes the port in this way, but this is incorrect.
This may be possible:
Conn. Open "driver = {MySQL ODBC 3.51 driver}; server = 127.0.0.1; Port = 3333; database = shops; user = root; Password = xxx ;"
Certificate -------------------------------------------------------------------------------------------------------------------------------------------------

In general, MySQL databases are rarely used when ASP is used, but sometimes it is necessary to connect to MySQL. For example, kaoyan.com is used by the Forum for MySQL databases, other channels are created based on ASP. Therefore, ASP is often used to connect to MySQL when user authentication is involved.
1. Basic ASP connection to MySQL
Generally, myodbc is used for connection. Install myodbc at http://www.mysql.com.
After myodbc is installed, you can directly connect to the MySQL database using the following statement in the ASP code:

Strconnection = "driver = {MySQL ODBC 3.51 driver };
Database = dbname; server = localhost; uid = dbuser; Password = dbpwd"
'Database: Database Name
'Server: server name/IP Address
'Uid: User Name
'Password: Password
Set con = server. Createobject ("ADODB. Connection ")
Con. Open strconnection
In addition, you can create a system DSN In the ODBC data source, select MySql ODBC 3.51 driver as the data source, enter the relevant user name and password, and test it. The related ASP connection code is as follows:

Strconnection = "DSN = dbdsn; driver = {MySQL ODBC 3.51 driver}; uid = dbuser; Password = dbpwd"
'Dsn: the name of the new DSN.
'Uid: User Name
'Password: Password
Set con = server. Createobject ("ADODB. Connection ")
Con. Open strconnection
2. Notes (MySQL and later)

The character set restrictions of MySQL and later versions are very different from those of earlier versions. If you do not set the character set during database query, once the queried fields contain Chinese characters, the following error may occur:

Microsoft ole db provider for ODBC drivers error '80040e31 ′

[MySQL] [ODBC 3.51 driver] [mysqld-4.1.18] Illegal mix of collations (gbk_chinese_ci, implicit) and (latin1_swedish_ci, coercible) for Operation '='

At this time, you need to modify the ASP query statement to add the relevant Character Set settings:

"Select email from members where username = _ GBK '" & username & "'collate gbk_chinese_ci"

Certificate ---------------------------------------------------------------------------------------------------------------------------------------------
Go to the MySQL Website: http://dev.mysql.com/downloads/download MySQL ODBC 3.51 driver for Installation
2. Use the following code link:
Conn. Open "driver = {MySQL ODBC 3.51 driver}; server =" & Strip & "; database =" & strdbname & "; user = "&

Strusername & "; Password =" & strpass & "; option = 3 ;"
If the myodbc version is different, modify the string in the driver.

<%
Strconnection = "DSN = mysqltest;
Driver = {MySQL ODBC 3.51 driver };
Server = fill in the server address; uid = user name; Pwd = password; database = mm"
'Connection string. DSN is the data source identifier we set.
Note: we mentioned the driver when setting the system DSN.
 
Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open strconnection
SQL = "select * from my" 'SQL query statement
Set rs = conn.exe cute (SQL)
If not Rs. bof then

%>

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.