asp.net and mysql

**spangeman** · 10-26-2009 01:25 PM

Hi All

Could you tell me if it is possible to get aspx pages talking to mysql on x10 hosting?

I have looked through the forums and someone seems to be suggesting adding MySql.Data.dll to a directory called bin and adding the reference to the web.config file. I have tried this and I get the following error.
System.Exception: Exception of type 'System.Exception' was thrown.
at MySql.Data.MySqlClient.NativeDriver.Open () [0x00000]

Here is my aspx code

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="MySql.Data.MySqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>CD cat</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<script runat="server">
private void Page_Load(Object sender, EventArgs e)
{
string connectionString = "Server=localhost;Database=my_database;User ID=mysuerid;Password=mypassword;Pooling=false;";
MySqlConnection dbcon = new MySqlConnection(connectionString);
dbcon.Open();

MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM artists", dbcon);
DataSet ds = new DataSet();
adapter.Fill(ds, "result");

dbcon.Close();
dbcon = null;

ArtistsControl.DataSource = ds.Tables["result"];
ArtistsControl.DataBind();
}
</script>

</head>

<body>
<h1>Artists</h1>
<asp:DataGrid runat="server" id="ArtistsControl" />
</body>

</html>

Thanks
SPangeman

**thegriff** · 10-27-2009 04:23 AM

Yes it is - but I had loads of problems myself!
The solution:

Copy both mysql.Data.dll and mysql.web.dll to the bin directory, i didn't have to add any references, it just worked.

Code:

using MySql.Data.MySqlClient;
...
strConnect = ConfigurationManager.ConnectionStrings["LoginDatabase"].ConnectionString;
sqlCmd.Connection = newMySqlConnection(strConnect);
sqlCmd.Connection.Open();
reader = sqlCmd.ExecuteReader();
reader.Read();
...use reader["fieldname"]
sqlCmd.Connection.Close();

In your ONLINE web.config:

Code:

<?xml version="1.0" ?>
<configuration>
  <appSettings />
  <connectionStrings>
    <add name="LoginDatabase"
      connectionString="server=mysql-chopin.x10hosting.com;database=XXX_YYY;user id=ZZZ;password=???" />
    <remove name="LocalMySqlServer"/>
    <add name="LocalMySqlServer" 
  </connectionStrings>
 
...anything else you have

where XXX is your login ID, YYY is the name of your database: so if you run phpMyAdmin it will show a database as XXX_YYY
ZZZ is the userid as set via phpMYAdmin,
??? is the user password fro ZZZ

Doing it this way makes it easier to debug locally as your online web.config can contain different values from your local system - my local system for example uses "localhost".
The really important bit is "mysql-chopin.x10hosting.com" rather than "localhost".

Haven't tried the dataadapter binding, but if the above works it should all be obvious.

**spangeman** · 10-28-2009 03:07 AM

Thanks for this post, it helped a lot.

Written a tutorial for anyone else that gets stuck with setting up MySql with asp.net

I coded mine slightly differently so I have posted both examples in the tutorial.

How to get ASP.Net to work with MySql at x10Hosting

Can we get it made sticky?

Thanks again.
Spangeman

**pioneer_fawad** · 10-28-2009 03:51 AM

Thanx

Thread: asp.net and mysql

LinkBack

Thread Tools

Display

asp.net and mysql

Re: asp.net and mysql

Re: asp.net and mysql

Re: asp.net and mysql

Similar Threads

[PHP] MySQL and PHP

MySql + ASP.net Connections problem..

mySQL and ASP.NET

Im Getting A Mysql Error And I Challenge Anyone To Help Me Fix It

New Site-Suggestions?

Posting Permissions