Knowledgebase » Knowledgebase » Database » MySQL

How do I connect MySQL DB with MySQL .NET connector

Mark — Wed, 24 Jun 2009 08:57:35 GMT

There is no installation required on server, you only need to upload the Mysql.Data.dll to the site "bin" folder.
You can download the MySQL .NET connector in 'http://dev.mysql.com/downloads/connector/net/6.0.html'.
The MySQL.Data.dll locates on the "Installation path\Assemblies". Default path is "C:\Program Files\MySQL\MySQL Connector Net 6.0.3\Assemblies".

********************************************************************
[Sample .aspx]
<%@ Page Language="VB" debug="true" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>
<script language="VB" runat="server">

Sub Page_Load(sender As Object, e As EventArgs)

    Dim myConnection As MySqlConnection
    Dim myDataAdapter As MySqlDataAdapter
    Dim myDataSet     As DataSet

    Dim strSQL        As String
    Dim iRecordCount As Integer

        myConnection = New MySqlConnection("Server=mysqlxxx.mysite4now.com;Database=DBname;Uid=DB_ID;Pwd=DB_pw")

        strSQL = "SELECT * From [table name];"

    myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
    myDataSet = New Dataset()
        myDataAdapter.Fill(myDataSet, "[table name]")

    MySQLDataGrid.DataSource = myDataSet
    MySQLDataGrid.DataBind()

End Sub

</script>

<html>
<head>
<title>MySQL test</title>
</head>
<body>

<form id="Form1" runat="server">

<asp:DataGrid id="MySQLDataGrid" runat="server" />

</form>

</body>
</html>
********************************************************************

In some application you require to add the following lines in web.config.
[web.config]

<system.web>
<compilation debug="true" urlLinePragmas="true">
<assemblies>
<add assembly="MySql.Data,Version=6.0.3.0, Culture=neutral,PublicKeyToken=C5687FC88969C44D"/></assemblies></compilation>
<authentication mode="Windows"/>
<customErrors mode="Off"/>
</system.web>
<system.data>
<DbProviderFactories>
   <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL"type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data,Version=6.0.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
</DbProviderFactories>
</system.data>

How to use phpMyAdmin to manage the mySQL database?

Mark — Tue, 19 May 2009 18:50:21 GMT

You can read this article if you have difficulties in inputting the database information in the phpMyAdmin.

To open the phpMyAdmin, you can browse the link, http://phpmyadmin.mysite4now.com/, with your web browser and you will seethe following page.
( you can also open it with Hosting Control Panel -> Database -> MySql manager )

To create connection to your database, please type in the database information and click on the Go button.

After you login with your database user, the main page of phpMyAdmin will show up and there are many function for you to choose. The following picture will show the simple description of links/icons.

To manage your database, please click on the link with your database name. You will see the main page of your database.

You can create table with the bottom function by typing the new table info and clicking on "Go".

You also see some icon . They represent different function to manage your table.
Here is the list of the function.

- to browse your data
- to manage your table structure
- to search your data in the table
- to insert new data to your table
- to empty your table
- to drop the table from the database

To run your query,
1) You can open the query window from the main page of phpMyAdmin,
2) or you can click on the Query Tab in the database page.

Website using MySQL return "Too many connections" error

Mark — Thu, 07 May 2009 13:42:48 GMT

Your website may have established too many connections to MySQL database. When the number of open connection reach the limit, "Too many connections" error will return.

To fix it temporary, you can login your Hosting Control Panel -> Database -> MySQL Admin, click the "Flush Host" button beside your database.

* IMPORTANT: You need to check your script whether all opened connections are closed. Otherwise the same problem will occur again and again when your site being visited

SELECT command denied to user 'user@'xxx.xxx.xxx.xxx' 'for table 'proc'

Mark — Sun, 12 Apr 2009 16:58:12 GMT

Below solution is applied for MySQL Connector/ODBC 5.1.

When users connect their Mysql database with Mysql .Net connector and try to create stored procedure, Mysql will return error :

SELECT command denied to user 'user@'xxx.xxx.xxx.xxx' 'for table 'proc'

It is a bug in Mysql (http://bugs.mysql.com/bug.php?id=33455)

To fix it, please add "Use Procedure Bodies=false;" to the connection string:

"server=mysqlxxx.mysite4now.com;user id=dbuser; password=dbpassword; database=dbname; Use Procedure Bodies=false;"

ref: http://bugs.mysql.com/bug.php?id=35938

How do I backup my database?

Mark — Mon, 30 Mar 2009 10:11:55 GMT

MSSQL:

1) Login to your hosting control panel. Click on the menu "Database". On the left menu, click on MSSQL.

2) You'll now see a list of your mssql db. Click on the "Backup" link next to the database that you want to backup.

3) Follow the backup steps to setup FTP Account and to Start the backup of your db.

4) FTP to the account that you just setup for your database backup and retrieve the backup.

Please note that we do backup your db on a daily basis as well.

MySQL:

1) Login to your hosting control panel. Click on the menu "Database". On the left menu, click on MySQL.

2) You\'ll now see a list of your mysql db. Click on the "Backup" link next to the database that you want to backup.

3) Follow the backup steps to setup FTP Account and to Start the backup of your db.

4) FTP to the account that you just setup for your database backup and retrieve the backup.

Please note that we do backup your db on a daily basis as well.

To backup your files, please refer to this article.

What do I use to connect to MySQL?

Mark — Wed, 24 Dec 2008 11:36:24 GMT

You can connect your mysql database through control panel >> database >> MySQL Manager.

Or you can download some GUI tools from "http://dev.mysql.com/downloads/gui-tools/5.0.html"
L Manager

I can't login FTP to retrieve my db backup file.

Mark — Wed, 24 Dec 2008 11:31:28 GMT

Please make sure you have setup your FTPlogin first.

To do so, login Hosting Control Panel -> Database -> MSSQL admin or MySQL admin -> Backup DB

You'll see that option at your DB backup/restore section (Step 1) of yourcontrol panel.

How do I use MySQL with ASP?

Mark — Wed, 24 Dec 2008 11:02:31 GMT

For a complete tutorial please refer to this site: http://www.mazsoft.com/mysql.asp