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
how do I display data in a datagridview?
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);
}
}
}
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);
}
}
}
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;
}
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;
}
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);
}
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);
}
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
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"