asp.net and mysql

spangeman

New Member
Messages
6
Reaction score
2
Points
0
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

New Member
Messages
14
Reaction score
0
Points
0
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:
[SIZE=2][COLOR=#2b91af][SIZE=2][COLOR=#2b91af][SIZE=2][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]using[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] MySql.Data.MySqlClient;[/COLOR][/SIZE]
[SIZE=2][COLOR=#000000]...[/COLOR][/SIZE]
[/SIZE][SIZE=2]strConnect = [/SIZE][SIZE=2][COLOR=#2b91af][SIZE=2][COLOR=#2b91af]ConfigurationManager[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2].ConnectionStrings[[/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"LoginDatabase"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]].ConnectionString;[/SIZE]
[SIZE=2]sqlCmd.Connection = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]new[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#2b91af][SIZE=2][COLOR=#2b91af]MySqlConnection[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2](strConnect);[/SIZE]
[SIZE=2]sqlCmd.Connection.Open();[/SIZE]
[SIZE=2]reader = sqlCmd.ExecuteReader();[/SIZE]
[SIZE=2]reader.Read();[/SIZE]
[SIZE=2]...use reader["fieldname"][/SIZE]
[SIZE=2]sqlCmd.Connection.Close();[/SIZE]
[/COLOR][/SIZE][/COLOR][/SIZE]


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

New Member
Messages
6
Reaction score
2
Points
0
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
 
Last edited:
Top