Problems connection to MySql database (remote)

[jamesjohnson88]

I am currently writing some software and I need to be able to connect to a MySql database on a remote server, i've spent hours trying different thing but I just can't seem to crack it.

Here is the code that i'm using to -

#region Building the connection string

string Server = "http://student.dc.lincoln.ac.uk";
string Username = "edited";
string Password = "edited";
string Database = "u08105199";

string ConnectionString = "Data Source=" + Server + ";";
ConnectionString += "User ID=" + Username + ";";
ConnectionString += "Password=" + Password + ";";
ConnectionString += "Initial Catalog=" + Database;

#endregion


#region Try to establish a connection to the database

SqlConnection SQLConnection = new SqlConnection();

try
{
SQLConnection.ConnectionString = ConnectionString;
SQLConnection.Open();
MessageBox.Show("Great Success!");

// You can get the server version
// SQLConnection.ServerVersion
}
catch (Exception Ex)
{
// Try to close the connection
if (SQLConnection != null)
SQLConnection.Dispose();

// Create a (useful) error message
string ErrorMessage = "A error occurred while trying to connect to the server.";
ErrorMessage += Environment.NewLine;
ErrorMessage += Environment.NewLine;
ErrorMessage += Ex.Message;

// Show error message (this = the parent Form object)
MessageBox.Show(this, ErrorMessage, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);

// Stop here
return;
}

#endregion





Any help with this would be greatly appreciated.

- James

[dannystommen]

First of all, you need to download the mySql connector, you can download it here (http://dev.mysql.com/downloads/connector/net/1.0.html)

Ad this dll as reference to your project, and use the MySql.Data.MySqlClientMySqlConnection instead ff SqlConnection class.

Next, try to change the connectstriong to

data source=student.dc.lincoln.ac.uk;database=something;Uid=something;password=something;

Note that datasource is without http://, user id is replaced by uid and initial catalog by database.

Finally, you need to be sure that the mySql server allows remote connections

[jamesjohnson88]

Is there a way to check if the server allows remote connections?

I am able to upload files via FTP to it, does this count?

Reason I ask is that it is the university's server so I can't control/change any of the settings.

[dannystommen]

The only way to find out then is to try what happens when you open the connection.

If the it is not allowed, an appropiate error message is returned (something like 'remote connections for user 'bla' is not allowed').

[jamesjohnson88]

I get this -

'A error occured while trying to connect to the server.

Unable to connect to any of the specified MySql hosts'.

I did all you said, except changing the string. It caused about 11 errors when i replaced the original string, so I just changed the words in the string, it now looks like this.

----------------------------------------------

string ConnectionString = "data source=student.dc.lincoln.ac.uk;database=08105199;Uid=u08105199;password=**********;";


#region Try to establish a connection to the database

MySqlConnection SQLConnection = new MySqlConnection();

try
{
SQLConnection.ConnectionString = ConnectionString;
SQLConnection.Open();
MessageBox.Show("Connection LeagueSQL database has been established.");

// You can get the server version
// SQLConnection.ServerVersion
}
catch (Exception Ex)
{
// Try to close the connection
if (SQLConnection != null)
SQLConnection.Dispose();

// Create a (useful) error message
string ErrorMessage = "A error occurred while trying to connect to the server.";
ErrorMessage += Environment.NewLine;
ErrorMessage += Environment.NewLine;
ErrorMessage += Ex.Message;

// Show error message (this = the parent Form object)
MessageBox.Show(this, ErrorMessage, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);

// Stop here
return;
}

#endregion




The references in use are these -

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using MySql.Data;
using MySql.Data.Types;
using MySql.Data.MySqlClient;

[dannystommen]

Is it possible to give some more information about the error. Is there any InnerException? Why are you unable to connect?

[jamesjohnson88]

Not that I can see. I could upload the source code for you if that would help?

The code is nested inside a button, could this be causing the problem?

[jamesjohnson88]

Ok, made a console application to try and get to the bottom, i'm getting this -

[img=http://img136.imageshack.us/img136/4296/capturesqm.th.png] (http://img136.imageshack.us/i/capturesqm.png/)

[dannystommen]

Never seen that error before.

Anyway, with that connectstring I connect to the MySQL DB in .Net and my collegue in PHP. So the connectionstring can't be the problem.

My guess is that the problem is database settings.

What you can do is installing de MySQl administatror (download (http://dev.mysql.com/downloads/gui-tools/5.0.html)). Try to connect to the database from here. If this doesn't work, it definitely a database setting.

Some other info, you don't close the connection. In the finaly block add a line 'con.Close();'. I also notice that you have a line Console.WriteLine('Connection failed'); in your finally block.

This block is always executed and the information 'failed' would be incorrect at this point, to correct it move that line to the catch block

[jamesjohnson88]

Yeah, can't seem to connect, the connection fails, then I try pinging it and the request just keeps timing out.

Looks like this app is going to have to be a php/xml job then. Shame as I was looking forward to using MySql with C sharp.

[jamesjohnson88]

Yeah, just found out from a fellow student that the uni's server is set to block I/O on port 3306 on for anything other than 'localhost'.