Two methods of ASP connection MySQL database

Usually use MYODBC to connect. First, install Mysql ODBC database driver in the system. If the stable version of installation is 3.51. Yes: http://dev.mysql.com/downloads/connector/odbc/3.51.html.

After the download is installed. If you have MySQL ODBC 3.51 driver on the Drivers page in Control Panel--Administration tools--data source (ODBC), you can start writing programs if the driver is installed successfully.

The following is the program I used in the test, there are instructions to no longer introduce.

Method One:

<% ' Set MySQL connection properties 'Description of each variable:' Myhost:mysql database Address 'MyDB: Using theMysqlDatabase name' myUID: The account used to connect to the MySQL database 'MyPwd: ConnectMysqlPassword for data usage account' Mychareset: The encoding type used by the client. Use according to the actual situation. ‘ In general the use ofgb2312 UTF8 GBKThese three kinds of coding. If these three kinds have been tested, there are still garbled characters.' Please check your settings. ‘Database Setup startsDim myHost,MyDB,myUID,Mypwdmyhost= "LocalHost"MyDB= "Knowldge"myUID= "Root"MyPwd= "Mysqladmin"Mychareset= "GB2312"Strconnection="Driver={mysql ODBC 3.51 driver};server=" &MyHost& ";d atabase=" &MyDB& "; User Name=" &myUID& ";p assword=" & mypwdset conn = server.  CreateObject("adodb.connection") ' Connect database conn.open strconnection ' set client character encoding Conn . Execute("Set names '" & mychareset & "'") ' Do not add this statement kanji Part may appear??? Garbled ' database set end %>              

Or:

Set= server.  CreateObject("adodb.connection")Conn.  Open"Driver={mysql ODBC 3.51 DRIVER}; Server=127.0.0.1;database=shops; User=root; password=xxx; "          

Above is the connection method using ADODB, which is normal on the default port of 3306.

But when the port is changed to 3333, an error occurs.

server=127.0.0.1:3333; This is not right, in PHP this can be used

server=127.0.0.1,3333; MSSQL is so changed port, but in this is wrong.

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; "  

Method Two:

In addition, you can create a new system DSN in the ODBC data source, select MySQL ODBC 3.51 driver as the data source, fill in the relevant user name and password and test it. The relevant ASP connection code is as follows:

strconnection="Dsn=dbdsn;driver={mysql  ODBC  3.51  driver};uid=dbuser;password=dbpwd"' DSN: New DSN name 'UID: User name ' Password: password set con = Server.CreateObject ("adodb.connection") Con.open strconnection     

Issues to be aware of (mysql4.1 and above)

mysql4.1 and above version of the character set is very different from the previous version, in the database query when the character set is not set, once the query field has Chinese, it is likely to appear the following error:

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, we need to make some changes to the query statements of ASP, and increase the setting of the relevant character set:

"Select email from the Members  where  username=_gbk  '"&username&"'  COLLATE  gbk_chinese_ci "   

The code example is interpreted as follows: Use the following code link

Example 1:

Conn.  Open"Driver={mysql ODBC 3.51 DRIVER}; Server= "&&";D atabase= "&&"; User= "&&"; Password= "&&"; option=3; "             

If the MYODBC version is different, please modify the string in the driver yourself

<% Strconnection= sql  query statement set RS =  Conn. (sql)  if not Rs.then%>          

Example 2:

<%' Test reads the contents of the MySQL database strconnection= ' driver={mysql ODBC 3.51 driver};d atabase=weste_net;server=localhost;uid=root; Password= "'No Configuration requiredDsnSetAdodataconn=Server.CreateObject("Adodb.connection")Adodataconn.Open Strconnectionstrquery= "SELECT * FROM News"SetRs=Adodataconn.Execute(Strquery)If NotRs.BoFThen%><table><tr><td<b>Serial number</b></td><td><b>Title</b></td></tr><%Do While NotRs.Eof%><tr><td><%=Rs("NEWS_ID")%></td><td><%=Rs("News_title")%></td></tr><%rs. Movenextloop%></table><%< Span class= "KWD" >elseresponse. ( "no data." end if rs.. Closeset Adodataconn = nothing set Rsemaildata = Nothing%>   

Example 3:

<%Dim Conn,sConnString,Mychareset,Strquerymychareset= "GB2312"sConnString="Driver={mysql ODBC 3.51 driver};d atabase=portaldata;server=localhost;uid=root;password=123456"Set Conn=Server.CreateObject("ADODB. Connection ")Conn.Open Sconnstringconn.Execute("Set Names '" &Mychareset& "‘")' Do not add the above statement kanji Part may appear??? Garbled%><%Strquery= "SELECT * FROM article" SetRs= Server.CreateObject("Adodb.recordset")Rs.Open strquery, Conn, 1, 1' Set rs = Conn.execute (strquery) if not Rs.bof then%><table><% Do While NotRs.EOF%><tr> <td>Number</td> <td>Title</td></tr><tr> <td><%=Rs("AID")%></td> <td><%=Rs("Atitle")%></td></tr><%Rs.MoveNextLoop%></table><% else response.write ( "no data!) ") end if rs.set Rs=nothing< Span class= "Typ" >conn. Closeset conn=nothing%>           

Note: The following error occurs when using the above code

Error type:

The Microsoft OLE DB Provider for ODBC Drivers (0X80040E21) ODBC driver does not support the required properties.

It is possible that the SELECT Top 10 * ... is not supported in MySQL, and the syntax for top 10 is not supported