how do I display data in a datagridview?

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
jbrahy
Posts: 9
Joined: Tue 24 Jan 2006 23:50

how do I display data in a datagridview?

Post by jbrahy » Wed 25 Jan 2006 16:49

I am having a problem getting my data to show up. There aren't any samples of how to get data from a mysql database to a datagridview.

Could someone post a sample?

Thanks,

John

jbrahy
Posts: 9
Joined: Tue 24 Jan 2006 23:50

Post by jbrahy » Wed 25 Jan 2006 17:29

This is what I'm trying... Am I leaving something out?


public partial class DataForm : Form
{
public DataForm()
{
InitializeComponent();
OpenConnection();
Load_Data();

}
protected void OpenConnection()
{
mySqlConnection.Host = "localhost";
mySqlConnection.UserId = "username";
mySqlConnection.Password = "password";
mySqlConnection.Port = 3306;
mySqlConnection.Database = "dbname";
mySqlConnection.Open();
}

protected void Load_Data()
{
try
{
mySqlCommand.CommandType = CommandType.Text;
mySqlCommand.Connection = mySqlConnection;

mySqlDataAdapter.SelectCommand = mySqlCommand;
mySqlDataAdapter.Fill(dataSet);

dataGridView.DataSource = dataSet;
}
catch (Exception ex)
{
MessageBox.Show(ex.Source + ": " + ex.Message);
}
}
}

RobD
Posts: 25
Joined: Mon 08 Nov 2004 03:41
Location: Sunshine Coast, B.C., Canada

Post by RobD » Wed 25 Jan 2006 20:14

This works for me, give it a try. First you have to add:






to your web.config then you can use the code below

Good Luck,
Rob

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

protected MySqlConnection connection;
protected MySqlDataAdapter adapter;
String myConnString = System.Configuration.ConfigurationManager.AppSettings.Get("connectionString");

void Page_Load(Object sender, EventArgs e)
{
if (!IsPostBack)
{
loadtable();
}
}



protected void loadtable()
{

if (!IsPostBack)
{

// Declare the query string.
String queryString =
"Select * From yourDBtableName";

// Run the query and bind the resulting DataSet
// to the GridView control.
DataSet ds = GetData(queryString);
if (ds.Tables.Count > 0)
{
GridView1.DataSource = ds;
GridView1.DataBind();
}
else
{
lblError.Text = "Unable to connect to the database.";
}

}
}

DataSet GetData(String queryString)
{

String connectionString = System.Configuration.ConfigurationManager.AppSettings.Get("connectionString");

DataSet ds = new DataSet();

try
{
// Connect to the database and run the query.
MySqlConnection connection = new MySqlConnection(connectionString);
MySqlDataAdapter adapter = new MySqlDataAdapter(queryString, connection);

// Fill the DataSet.
adapter.Fill(ds);

}
catch (Exception ex)
{

// The connection failed. Display an error message.
lblError.Text = "Unable to connect to the database." + ex.ToString();

}
finally
{
if (null != this.connection && System.Data.ConnectionState.Closed != this.connection.State)
{
connection.Close();
adapter.Dispose();
}

}

return ds;

}

RobD
Posts: 25
Joined: Mon 08 Nov 2004 03:41
Location: Sunshine Coast, B.C., Canada

Post by RobD » Wed 25 Jan 2006 20:17

just an add on,
the tag actually goes under the tag in the web.config file.

jbrahy
Posts: 9
Joined: Tue 24 Jan 2006 23:50

Post by jbrahy » Wed 25 Jan 2006 20:47

Thanks Rob, I'm working in a windows form application so it's not 100% translatable but I do appreciate the help. For the forum's sake I was having problems attaching a dataset to a datagridview but I found that if I create one via c# it's easy to add it to the current Form. I also found out that the dataset can contain more than just a table, so I had to reference the table directly to pass only the data I wanted.


protected void Load_Data()
{
try
{
mySqlCommand.CommandText = "SELECT EmployeeID,FirstName,LastName FROM Employees";
mySqlCommand.CommandType = CommandType.Text;
mySqlCommand.Connection = mySqlConnection;

mySqlDataAdapter.SelectCommand = mySqlCommand;
mySqlDataAdapter.Fill(dataSet,"testing");

DataGridView dataGrid = new DataGridView();
dataGrid.DataSource = dataSet.Tables["testing"];
dataGrid.Dock = DockStyle.Fill;
dataGrid.CellClick += DataGrid_DoubleClick;

this.Controls.Add(dataGrid);

}
catch (Exception ex)
{
MessageBox.Show(ex.Source + ": " + ex.Message);
}

Serious

Post by Serious » Thu 26 Jan 2006 09:25

Visual Studio 2005 has good WinForms designer. What prevents you from using its capabilities?
MySqlDataAdapter's design-time features are tightly integrated with this designer. You can create DataSet and fill it with data, then select corresponding table using DataGridView designer.

To assign DataTable as data source use following syntax

Code: Select all

dataGrid.DataSource = dataSet;
dataGrid.DataMember = "testing"

Post Reply