How do i load mysql data into a dataset then into a datagrid?

[PHPRO]

Hello,

I have been trying for days to get data from a mysql table into a dataset and then fill a datagrid. How do i do this?


MySqlConnection myConnection = new MySqlConnection();
myConnection.ConnectionString = "**********";
myConnection.Open();
string mySelectQuery = "SELECT * FROM table";
MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);

data = new dataset();
DataSet1.fill(data);

[MadHatter]

MySqlDataAdapter adapter = new MySqlDataAdapter(myCommand);
adapter.Fill(data);

[PHPRO]

Hello,

I am getting an error -

Heres the code -


DataSet mydataset = new DataSet();
MySqlConnection myConnection = new MySqlConnection();
myConnection.ConnectionString = "************";
myConnection.Open();
string mySelectQuery = "SELECT * FROM table";
MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
MySqlDataAdapter adapter = new MySqlDataAdapter(myCommand);
adapter.Fill(mydataset, "table");
dataGridView1.SetDataBinding(mydataset, "table");
myConnection.Close();


Heres the error -

'System.Windows.Forms.DataGridView' does not contain a definition for 'System' and no extension method 'System' accepting a first argument of type 'System.Windows.Forms.DataGridView' could be found (are you missing a using directive or an assembly reference?)

Im not sure what the problem is,

[MadHatter]

wherever the error is, its not in that bit of code you posted. look for something named System in your code. (system is the root namespace of .NET apps, so I'd stay away from naming any classes System).

[PHPRO]

Sorry, its actually the SetDataBinding.

'System.Windows.Forms.DataGridView' does not contain a definition for 'SetDataBinding' and no extension method 'SetDataBinding' accepting a first argument of type 'System.Windows.Forms.DataGridView' could be found (are you missing a using directive or an assembly reference?)

[mariocatch]

You need to include the namespace that holds the DataGridView class. Look up MSDN for information on DataGridView class.

[PHPRO]

Yes, but MSDN says the DataGridView is held in the system.windows.forms assembly. Which is already included in my program.

http://msdn2.microsoft.com/en-us/library/system.windows.forms.datagridview.aspx

[Shuja Ali]

Sorry, its actually the SetDataBinding.

'System.Windows.Forms.DataGridView' does not contain a definition for 'SetDataBinding' and no extension method 'SetDataBinding' accepting a first argument of type 'System.Windows.Forms.DataGridView' could be found (are you missing a using directive or an assembly reference?)The message clearly says that SetDataBinding is not a member of DataGridView class. Have you written any extension method called SetDataBinding? If not then this is not the correct way of binding a Dataset to a datagridview. You should go through the documentation shown on MSDN.
You need to include the namespace that holds the DataGridView class. Look up MSDN for information on DataGridView class.SetDataBinding is not a member of datagridview class. There is no namespace missing in that snippet of the code.

[nelo]

Try it this way...

DataSet mydataset = new DataSet();
MySqlConnection myConnection = new MySqlConnection();
myConnection.ConnectionString = "************";
myConnection.Open();
string mySelectQuery = "SELECT * FROM table";
MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
MySqlDataAdapter adapter = new MySqlDataAdapter(myCommand);
adapter.Fill(mydataset, "table");
dataGridView1.DataSource = mydataset;
dataGridView1.DataMember = "table";
myConnection.Close();

[PHPRO]

Yes, thats right. I got mixed up between DataGrid and DataGridView.

Here is the code that runs without errors.


DataSet mydataset = new DataSet();
DataGrid dataGrid1 = new DataGrid();
MySqlConnection myConnection = new MySqlConnection();
myConnection.ConnectionString = "************";
myConnection.Open();
string mySelectQuery = "SELECT * FROM table";
MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
MySqlDataAdapter adapter = new MySqlDataAdapter(myCommand);
adapter.Fill(mydataset, "table");
dataGrid1.SetDataBinding(mydataset, "table");
myConnection.Close();


However, there is nothing on the windows form when i click debug. How do i produce an output?


Also, nelo , your code produces the same result.
Thanks,

[nelo]

However, there is nothing on the windows form when i click debug. How do i produce an output?

What do you mean? What output are you expecting?

[PHPRO]

A grid/table of results.

[nelo]

Have you checked that you actually have the data in the dataset? I haven't used MySQL but I assume that you're able to connect to the database and that your query is correct. So you are currently using the a DataGrid. Have you tried with a DataGridView (which actually is a replacement for the DataGrid)?

[PHPRO]

Im not sure how to check that the data is in the dataset, but, the connection is correct and the query is correct.

I tried the code you posted for the dataGridView, the result is the same as i am getting for the DataGrid.

[Shuja Ali]

Im not sure how to check that the data is in the dataset, but, the connection is correct and the query is correct.

I tried the code you posted for the dataGridView, the result is the same as i am getting for the DataGrid.
Have you placed the datagrid/datagridview on your form? debug through the code and see if the dataset is being properly filled.

[nelo]

Im not sure how to check that the data is in the dataset, but, the connection is correct and the query is correct.

Are you using Vistual Studio? If so it has some sophisticated debugging environment. You can add your dataset to the watch window and do something like

mydataset["table"].Rows.Count that will tell if there are rows retrieved in the table.

You can also serialize the dataset to a file if you haven't got visual studio development environment to see the contents of the dataset.

You can also produce debugging output using the Debug and Trace classes from System.Diagnostics namespace.

[PHPRO]

Hello,

I will tell you what i have done -

In the "Design" view on visual studio i added a DataGridView - VS starts asking for information about the database. MySQL is not supported for the GUI way of adding information to the DataGridView, therefore , the DataGridView must be filled using code - so i ignore the "Add Coloumn" options etc..

I then switch to the code view and add the code nelo posted to the form_load function of my form.


DataSet mydataset = new DataSet();
MySqlConnection myConnection = new MySqlConnection();
myConnection.ConnectionString = "************";
myConnection.Open();
string mySelectQuery = "SELECT * FROM table";
MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
MySqlDataAdapter adapter = new MySqlDataAdapter(myCommand);
adapter.Fill(mydataset, "table");
dataGridView1.DataSource = mydataset;
dataGridView1.DataMember = "table";
myConnection.Close();


I then click debug, open the form and the DataGridView is just grey with no information. Could the problem be that "mydatasource" was not created in the design view of VS and therefore, does not show in the design view?

The code and connection are 100% correct as i im using the same code in another formI dont understand what you mean nelo,

Thanks,

[MadHatter]

set a breakpoint on

dataGridView1.DataSource = mydataset;

(just after the fill method) and look at mydataset (you can hover your mouse over the variable, or right click it and choose quick view. if the dataset it empty, then nothing will be displayed on your grid. try running the query in phpmyadmin and see what results you get.

you can also try using a binding source for the data source (no idea what difference it would make):
DataTable table = new DataTable();
MySqlConnection myConnection = new MySqlConnection();
myConnection.ConnectionString = "************";
myConnection.Open();
string mySelectQuery = "SELECT * FROM table";
MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
MySqlDataAdapter adapter = new MySqlDataAdapter(myCommand);
adapter.Fill(table, "table");
BindingSource bs = new BindingSource();
bs.DataSource = table;
dataGridView1.DataSource = bs;
myConnection.Close();

[PHPRO]

set a breakpoint on

dataGridView1.DataSource = mydataset;

(just after the fill method) and look at mydataset (you can hover your mouse over the variable, or right click it and choose quick view. if the dataset it empty, then nothing will be displayed on your grid. try running the query in phpmyadmin and see what results you get.


I did this and when i hovered over the code it was just explaining the code and telling me what it does not if the dataset contains anything.

I ran the query on phpmyadmin and it worked.


you can also try using a binding source for the data source (no idea what difference it would make):
DataTable table = new DataTable();
MySqlConnection myConnection = new MySqlConnection();
myConnection.ConnectionString = "************";
myConnection.Open();
string mySelectQuery = "SELECT * FROM table";
MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
MySqlDataAdapter adapter = new MySqlDataAdapter(myCommand);
adapter.Fill(table, "table");
BindingSource bs = new BindingSource();
bs.DataSource = table;
dataGridView1.DataSource = bs;
myConnection.Close();

This code did not compile, the error is on adapter.Fill(table, "table");

The best overloaded method match for 'System.Data.Common.DataAdapter.Fill(System.Data.DataTable, System.Data.IDataReader)' has some invalid arguments Argument '2': cannot convert from 'string'

Thanks for your continued help,

[MadHatter]

ah, guess it should be:

adapter.Fill(table);