Solved

: ODBC connection to MySQL from standard ASP code

Posted on 2011-02-25
5
891 Views
Last Modified: 2012-05-11
I am very familiar with using DSN connections in my standard ASP codes getting connected to MS Access.

I am using traditional ASP and want to get connected to MySQL database on my local server.  Local computer is windows 7 professional (32 bit). I downloaded the relevant MySQL ODBC 5.1 driver from
www.mysql.com.
I am testing the ASP code in my local host. It is giving ODBC connection error message.
Initially, I tried creating a system DSN.  DSN was created successfully.  It gave a message “connection successful”  when I pressed the button “test”. When I tried to open the DSN in administrative tools to look for the configuration again, it is giving the message “Invalid attribute string”.
I have searched web for the solution to the problem on
http://bugs.mysql.com/bug.php?id=56233
I have gone through the discussions.
I am not able to find the solution

Instead of DSN-based connection, I tried to use the DSN-less connection in ASP code which is as follows:
==========================================================
<.html>
…..
<table></tr>


<%
Set objdbconmysqldemostr=Server.CreateObject("ADODB.connection")

set rsusers=server.createobject ("adodb.recordset")



objdbconmysqldemostr.Open ("DRIVER={MySQL ODBC 3.51 Driver};" & _
                 "Server=localhost;" & _
                 "Port=3306;" & _
                 "Option=16384;" & _
                 "Stmt=;" & _
                 "Database=abcxyz;" & _
                 "User=root;" & _
                 "Password=thefire")




rsusers.open "select * from userlogin order by ename",objdbconmysqldemostr,1,1
dim rn,mr
rn=0
while not rsusers.eof

rn=rn+1


response.write   "<td>" & rn & "</td>"
response.write   "<td>" & rsusers("username") & "</td>"
response.write   "<td>" & rsusers("password") & "</td>"
response.write   "<td>" & rsusers("ename") & "</td></tr>"

rsusers.movenext

wend

rsusers.close
set rsusers=nothing
objdbconmysqldemostr.close
set objdbconmysqldemostr=nothing

%>
 
</table>

……………
</html>

Again the same problem with database connection
Any thoughts for solving the problem
0
Comment
Question by:smanthanims
  • 3
  • 2
5 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
This is the code that I use to connect to MySQL both on Windows XP with IIS5.1 and on Godaddy with IIS7.
connectstr = "Driver={MySQL ODBC 3.51 Driver};SERVER=yourserver;DATABASE=yourdb;UID=youruser;PWD=yourpwd"

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectstr

Set idresult = Server.CreateObject("ADODB.Recordset")
idresult.Open "SELECT * FROM yourtable", oConn

Open in new window

0
 

Author Comment

by:smanthanims
Comment Utility
Again the same problem.  The log file on IIS 7.5 shows the following message:

2011-02-26 01:39:17 ::1 GET /mydomain/mysqldemo/mysqldemouserlist1.asp |39|80004005|[Microsoft][ODBC_Driver_Manager]_Data_source_name_not_found_and_no_default_driver_specified 80 - ::1 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+6.1;+Trident/4.0;+GTB6.6;+SLCC2;+.NET+CLR+2.0.50727;+.NET+CLR+3.5.30729;+.NET+CLR+3.0.30729;+Media+Center+PC+6.0;+.NET4.0C;+AskTB5.5) 500 0 0 0

I think problem is with ODBC driver for mysql
0
 
LVL 82

Accepted Solution

by:
Dave Baldwin earned 500 total points
Comment Utility
I believe you're right.  You can try changing it to {MySQL ODBC 5.16 Driver} or whichever version you downloaded or you can download and install the 3.51 version.
0
 

Author Closing Comment

by:smanthanims
Comment Utility
I downloaded the following driver from www.mysql.com
Windows (x86, 32-bit), MSI Installer
Connector-ODBC  5.1.8

Among mirror sites I have chosen, Hoobly Classifieds / Chicago, IL, United States   HTTP  

I reinstalled the driver and restarted the system. I created a new DSN for mysql database.  It was configured correctly without errors.  I used the DSN connection in my ASP code.
I worked perfectly
thanks for the prompt support
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
You're welcome, thanks for the points.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now