[Footsie]

I'm struggling to get my C# web app to "talk" to MySQL (in Visual Studio).
I set up the MySQL tables and inserted a test row to try and get them synching.

I've been told that it is possible to use OleDb connections to access MySQL.
All I want to do is get a DataGrid to display what is in the Suppliers table.

Here's my Code:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;

public partial class _Default : System.Web.UI.Page 
{
	protected void Page_Load(object sender, EventArgs e)
	{
		string strCon;
		strCon = "Provider=MySQLProv;" +
				 "Data Source=afp;" +
				 "Initial Catalog=Suppliers;";
		
		OleDbConnection conn = new OleDbConnection(strCon);
				
		conn.Open();
		DataGrid1.DataBind();
		conn.Close();
			
	}
}

The error it gives me is that MySQLProv is, "not registered on the local machine."

Any pointers would be really great!
Thanks!

[PsychoCoder]

Well to connection to a mySQL database follow the following procedure:

• First, you need to install the mysql connector/net, it is located here
  
  
• Next add reference to: MySql.Data
  
  
• Next add "using MySql.Data.MySqlClient;"
  
  
• Finally try some code (in your test application we just created) like this:

/// <summary>
	/// Method for connection to a mySQL database. You
	/// need to download the mySQL Connector located at
	/// http://dev.mysql.com/downloads/connector/net/1.0.html]
	/// 
	/// NOTE: I am a big supporter of having the connection
	/// stored in the web.config, not inline like this
	/// </summary>
	/// <param name="server"></param>
	private void connectoToMySql(string server)
	{
		//set your connection string. 
		//NOTE: I am a big supporter of having the connection
		//stored in the web.config, not inline like this
		string connString = "SERVER=" + server + ";" +
			"DATABASE=mydatabase;" +
			"UID=testuser;" +
			"PASSWORD=testpassword;";
		//create your mySQL connection
		MySqlConnection cnMySQL = new MySqlConnection(connString);
		//create your mySql command object
		MySqlCommand cmdMySQL = cnMySQL.CreateCommand();	
		//create your mySQL reeader object
		MySqlDataReader reader;
		//set the command text (query) of the
		//mySQL command object
		cmdMySQL.CommandText = "select * from mycustomers";
		//open the mySQL connection
		cnMySQL.Open();
		//execute the reader, thus retrieving the data
		reader = cmdMySQL.ExecuteReader();
		//while theres data keep reading
		while (reader.Read())
		{
			string thisrow = "";
			for (int i= 0;i<reader.FieldCount;i++)
					thisrow+=reader.GetValue(i).ToString() + ",";
			listBox1.Items.Add(thisrow);
		}
		cnMySQL.Close();
	}

Hope that helps

This post has been edited by PsychoCoder: 20 January 2008 - 10:37 AM

[Footsie]

I downloaded that connector, thanks...but:
How would I know what my SERVER name and UID is? I haven't set any of that up in the DB yet.
All I have is a password that I enter on the console command line once I click the "MySQL command line client" off my start bar.

Thanks, Psycho.

[Footsie]

Never mind guys.

I worked out that my UID was "root" SERVER "localhost" and fortunately I knew my password. (perseverence pays off I suppose...)

All working fine now.

thanks again!

This post has been edited by Footsie: 20 January 2008 - 02:47 PM