MySQL Connect String for ASP

**holivar** · 28th June 2005, 03:11 PM

Does anyone know how to connect to a MySQL database in ASP? I'm a php programmer but have to use asp for just one page ... how hard can it be?

I've got this so far:

' open mysql connection
connect_string = "Driver={Mysql}; Server=82.138.243.148; Database=***; UID=***; PWD=***"
set dbConn = server.createObject("ADODB.connection")
dbConn.open connect_string

' close connection
dbConn.close
set dbConn=nothing

and get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/holivar2006-org/register/conn.asp, line 29

Can someone help an ASP newbie how to do this?
Mike

**Staff: Warren Ashcroft** · 28th June 2005, 03:23 PM

Hello There,
Firstly, never use the IP Address in local code - this will cause the connection to go via the public network, slow the connection and will possibly count against your bandwidth. Always use the server name, as shown in Helm - in your case HYDROGEN.

Secondly, you connection string should be:
"DRIVER={MySQL ODBC 3.51 Driver};SERVER=HYDROGEN;DATABASE=myDatabase;USER=m yUsername;PASSWORD=myPassword;OPTION=3;"

Warm Regards,
Warren Ashcroft

**holivar** · 28th June 2005, 03:26 PM

great, that works ... thanks for rapid reply Warren! I noticed my site was down earlier for about 10 minutes ... hope it's nothing serious.

Cheers
Mike

**Staff: Warren Ashcroft** · 28th June 2005, 03:30 PM

Originally Posted by holivar

great, that works ... thanks for rapid reply Warren! I noticed my site was down earlier for about 10 minutes ... hope it's nothing serious.

Cheers
Mike

No problem

A problem with power: http://status.redfoxhosting.com
Still monitoring the situation...

**Little My** · 2nd March 2006, 10:47 AM

Hi,

I am using asp with mySQL 5.0.18 from a Windows 2003 server.

I have been using some .asp scripts with ms access but am now looking to use mySQL. I partially used 'extractsql' to glean the 'data' exchange & I changed the connection string in the scripts and have managed to extract data from the new database via web pages. The problem now occuring is an error in mySQL statement when attempting to use the 'INSERT INTO nameofDB VALUES (...... etc), that previously worked with the MS Access database - I am getting an error:

"Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[MySQL][ODBC 3.51 Driver][mysqld-5.0.18-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''proj_detail' VALUES (user_username, proj_name, no_of_pics, pic1_url, pic2_url, ' at line 1"

this is the offending sql..
SQL="INSERT INTO 'proj_detail' VALUES (user_username, proj_name, no_of_pics, pic1_url, pic2_url, pic3_url, pic4_url, pic5_url, pic6_url, pic7_url, pic8_url, pic9_url, pic10_url,) VALUES ('" & strLogin & "','" & strProjName & "','" & NoOfPics & "','" & strPicUrl1 & "','" & strPicUrl2 & "','" & strPicUrl3 & "','" & strPicUrl4 & "','" & strPicUrl5 & "','" & strPicUrl6 & "','" & strPicUrl7 & "','" & strPicUrl8 & "', '" & strPicUrl9 & "','" & strPicUrl10 & "," & "')"
set conn = server.createobject("ADODB.Connection")
conn.CursorLocation = 3
conn.Open("Driver={MySQL ODBC 3.51 Driver}; Server=servername; Database=images; UID=*****; PWD=*******; Option=3")

Does anyone know off hand what the difference is in the syntax - I have checked the mySQL INSERT INTO statements and it loks the same, any ideas.

regards

Maxine
p.s. - I will be moving to PHP in the next phase

but for my clients I just need it to work for the time being.

**RFH Reseller: nick** · 2nd March 2006, 12:18 PM

You have an additional comma after 'pic10_url'

Code:

[...]pic9_url, pic10_url,)

try

Code:

INSERT 
    INTO 
        proj_detail 
      ( user_username, proj_name, no_of_pics, 
        pic1_url, pic2_url, pic3_url, pic4_url,
        pic5_url, pic6_url, pic7_url, pic8_url,
        pic9_url, pic10_url)
    VALUES
      ( '" & strLogin & "','" & strProjName & "',
        '" & NoOfPics & "','" & strPicUrl1 & "',
        '" & strPicUrl2 & "','" & strPicUrl3 & "',
        '" & strPicUrl4 & "','" & strPicUrl5 & "',
        '" & strPicUrl6 & "','" & strPicUrl7 & "',
        '" & strPicUrl8 & "', '" & strPicUrl9 & "',
        '" & strPicUrl10 & "," & "');

EDIT
____
And you had an extra 'VALUES' after the table name. I've updated my post

**RFH Customer: Reactivate** · 2nd March 2006, 01:45 PM

Originally Posted by Warren Ashcroft

Firstly, never use the IP Address in local code - this will cause the connection to go via the public network, slow the connection and will possibly count against your bandwidth. Always use the server name, as shown in Helm - in your case HYDROGEN.

I never knew that!

I've quickly changed all references to the IP on my site to the server name, cheers for the advice.

**Little My** · 8th March 2006, 09:24 AM

Originally Posted by nick

You have an additional comma after 'pic10_url'

Code:

[...]pic9_url, pic10_url,)

... i actually posted the wrong code to the forum (I had been playing around with possibilies and posted that. The code that works one the server connected to MS ACESS is

SQL="INSERT INTO proj_detail (user_username, proj_name, no_of_pics, pic1_url, pic2_url, pic3_url, pic4_url, pic5_url, pic6_url, pic7_url, pic8_url, pic9_url, pic10_url) VALUES ('" & strLogin & "','" & strProjName & "','" & NoOfPics & "','" & strPicUrl1 & "','" & strPicUrl2 & "','" & strPicUrl3 & "','" & strPicUrl4 & "','" & strPicUrl5 & "','" & strPicUrl6 & "','" & strPicUrl7 & "','" & strPicUrl8 & "', '" & strPicUrl9 & "','" & strPicUrl10 & "')"
set conn = server.createobject("ADODB.Connection")
conn.CursorLocation = 3
conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Inetpub\wwwroot\xxxxxxxx\xxxxxxxx\images.mdb;Je t OLEDB:glistDSN;")
set rs=conn.execute(SQL)
conn.Close
Set rs=Nothing

EDIT
____
And you had an extra 'VALUES' after the table name. I've updated my post

That too was part of the 'wrong code. can you look at the new post herein - the above is taken directly from the server that works If I simply copy the whole code to the new server running mySQL it doesn't like it.

**RFH Reseller: Mark Voss** · 8th March 2006, 10:14 AM

It won't because you're trying to use an Access connection:

Code:

conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Inetpub\wwwroot\xxxxxxxx\xxxxxxxx\images.mdb;Je t OLEDB:glistDSN;")

Try using the mySQL connection you posted earlier instead:

Code:

conn.Open("Driver={MySQL ODBC 3.51 Driver}; Server=servername; Database=images; UID=*****; PWD=*******; Option=3")

**RFH Customer: iomff** · 24th April 2006, 02:39 PM

Okay ... I can get a connection up and running in VB, no problem, using the connection string.

The problem is I have a local webserver running which I use for testing with ASP code but I can't get a connection from any page to my database. I used the "SERVER=82.138.243.148" so it would go out and get it but still says I can't connect. I even gave it the port number.

The full string (with names and passwords changed :p ) is :

"DRIVER={MySQL ODBC 3.51 Driver};SERVER=82.138.243.148;PORT=3306;DATABASE=m yDatabase;USER=myUser;PASSWORD=myPass;OPTION=3;"

Can you help me out? Is it possible to access my database from another website?

....or am I missing something obvious?:crying:

Thanks

Daniel.