p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Classic ASP Databases (http://p2p.wrox.com/forumdisplay.php?f=62)
-   -   ASP connection to MySQL (http://p2p.wrox.com/showthread.php?t=4180)

ells228 September 23rd, 2003 06:33 AM

ASP connection to MySQL

I have a client that is having slow data connections to an Access MDB, and the ISP has provided a free MySQL db.

1. Is it possible to connect to MySQL using ASP ?
2. How would i do this ?


Chris Ellis
Icon-Business Ltd

jacob September 24th, 2003 05:13 AM

Well, yes it is possible. I have once done it myself. I will get back with an answer later...! I have to find some old project.


jacob September 24th, 2003 05:24 AM

Well, I found it pretty quickly. It is something like this...


Dim SomeDB
Set SomeDB = Server.CreateObject ("ADODB.Connection")
SomeDB.Open ("DRIVER=mysql;SERVER=[servername];UID=[username];PWD=[password];DATABASE=[databasename]")

Dim recTmp
Set recTmp = Server.CreateObject ("ADODB.Recordset")
recTmp.Open "SELECT * FROM testtable", SomeDB

Loop While Not recTmp.EOF

Set recTmp = Nothing

Set SomeDB = Nothing

Be aware that you have to change the red parts in the above connection string with the right value, and naturally use you name for the table and column.

Actually the way I found out about this was in the book 'Professional ASP Data Access' (WROX) which has got a section on MySQL databases, and how to connect to these.

Hope this helps ;)


shahhussain July 28th, 2006 12:23 PM

Hello Mr. Jacob.
I have used the driver you have specified above for MySql Database but when i browse the
page it throws an Error as :
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/sql.asp, line 12

while i am using the MySql Database name = [testing] table name =[test] and server=[localhost]
and the code i have written is as :
Dim SomeDB
Set SomeDB = Server.CreateObject ("ADODB.Connection")
SomeDB.Open ("DRIVER=mysql; SERVER=[localhost]; UID=[shah]; PWD=[thefire]; DATABASE=[testing]")

Dim objRS
Set objRS= Server.CreateObject ("ADODB.Recordset")
objRS.Open "SELECT * FROM test", SomeDB

    response.write("name = " & objRS.fields("name").value & "<br>")
    response.write("Email = " & objRS.fields("email").value)

Loop While Not objRS.EOF

Set objRS= Nothing

Set SomeDB = Nothing

Please hlep me in this thanks.


dparsons July 31st, 2006 09:05 AM

Your connection string should look something like this:

"Driver={MySQL ODBC 3.51 Driver};Server=localhost;"
     "Database=testing;User=shah;Password=thefire;Optio n=4;"

You will need to install the 3.51 ODBC driver on your system for this to work.


"The one language all programmers understand is profanity."

samanthatucker June 21st, 2017 01:38 PM

Please help ORDER BY issue
When I add ORDER BY deals.DEAL_ID before LIMIT the page times out and never loads. It works perfect without it. Please HELP !!!!

Set ors = oConn.Execute("SELECT deals.DEAL_ID, deals.EMP_ID, users.fname AS empfname, users.lname AS emplname, users.usrcomm AS empdesc, deals.CLOSER_ID, userz.fname AS clsrfname, userz.usrcomm AS clsrdesc, userz.lname AS clsrlname, deals.split_comm, deals.EMP_LOC, deals.DEAL_DATE, deals.fname, deals.midname, deals.spfname, deals.splname, deals.lname, deals.address1, deals.address2, deals.city, deals.state, deals.zipcode, deals.phone1, deals.phone2, deals.email_addy, deals.pmnt_type, deals.cc_name, deals.card1, deals.card2, deals.card3, deals.card4, deals.card_exp_mo, deals.card_exp_yr, deals.cid_id, deals.bank_name, deals.check_numb, deals.route_numb, deals.acct_numb, deals.app_code, deals.deal_amt, deals.processthru, deals.resortcomp, deals.pkgname, deals.pri_location, deals.tripdetail, deals.pkgcost, deals.rmtype, deals.gift1, deals.gift2, deals.gift3, deals.gift4, deals.gift5, deals.gift6, deals.deal_status, deals.assigned_to AS prev_status, deals.CONF_PREFIX AS conf_code, deals.verify_date, users_1.UID AS verifid, users_1.fname AS vfname, users_1.lname AS vlname, deals.pmnt_date, deals.shdate, deals.cancel_date, deals.cancel_wk, deals.payroll_wk, deals.chargeback_wk, deals.mgr_cb_wk, deals.clsr_cb_wk, deals.pay_amt, deals.clsr_amt, deals.mgr, userz_1.fname AS mgrfname, userz_1.lname AS mgrlname, deals.go_green, deals.mail_sent, deals.deal_printed, deals.deal_issue, deals.wk_tag, deals.mnth_tag, deals.yr_tag, deals.deal_time, deals.vartime, deals.payshift, deals.dfield1, userz_2.fname AS hbfname, userz_2.lname AS hblname, deals.dfield2, deals.dfield3, deals.dfield4, deals.flagit, deals.source FROM ((((deals LEFT JOIN userz ON deals.CLOSER_ID = userz.UID) LEFT JOIN userz AS userz_1 ON deals.mgr = userz_1.UID) LEFT JOIN userz AS userz_2 ON deals.dfield1 = userz_2.UID) LEFT JOIN users AS users_1 ON deals.varifid = users_1.UID) LEFT JOIN users ON deals.EMP_ID = users.UID WHERE (((deals.deal_status) <> 'Delete')) LIMIT " & Session("pgsz") & "")

All times are GMT -4. The time now is 05:36 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.