Data grid view not showing data from mysql table

Question

Im trying to get data in data grid view . this code works fine if i use this query :- "Select * from employee.transaction"

But when im trying to put the conditions it does not give any output (just shows a blank table)

My table has month,year column of type Int.Im using mysql server 5.6.25

I cant find the problem with my code.Please help.Thx in advance.

private void load_data_Click(object sender, EventArgs e)
        {


            string constring = "datasource = localhost;port = 3306;username = ****;password = ****";
            MySqlConnection conDataBase = new MySqlConnection(constring);
            var cmdDataBase = conDataBase.CreateCommand();

            cmdDataBase.CommandText = @"select * from employee.transaction where department = @department AND month = @month AND year = @year";
            cmdDataBase.Parameters.AddWithValue("@department", this.department.Text);
            cmdDataBase.Parameters.AddWithValue("@month", this.dateTimePicker1.Value.Month);
            cmdDataBase.Parameters.AddWithValue("@year", this.dateTimePicker1.Value.Year);
         try
            {
         // here im trying to show table in datagrid view
               MySqlDataAdapter sda = new MySqlDataAdapter();
                sda.SelectCommand = cmdDataBase;
                DataTable dbdataset = new DataTable();
                sda.Fill(dbdataset);
                BindingSource bSource = new BindingSource();

                bSource.DataSource = dbdataset;
                dataGridView1.DataSource = bSource;
                sda.Update(dbdataset);

        //here im trying to make a excel file which would contain what is currently being displayed in the datagrid view
                DataSet ds = new DataSet("New_DataSet");
                DataTable dt = new DataTable("New_DataTable");
                dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
                ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
                ds.Tables.Add(dbdataset);
                ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Did you try to step through this code with the debugger? What is the count of rows in the dbdataset table after the call to fill? If it is zero then you have a problem in your query. Try to run it with the same values in the MySql Workbench — Steve, Jul 04 '15 at 14:06
The cause is probably a mix of: 1) `year` and `month` are [reserved words](https://dev.mysql.com/doc/refman/5.5/en/keywords.html). 2) The `Load` event of a winform Form [swallows exceptions](http://stackoverflow.com/questions/4933958/vs2010-does-not-show-unhandled-exception-message-in-a-winforms-application-on-a/4934010#4934010) — Bjørn-Roger Kringsjå, Jul 04 '15 at 14:25

AnkitMittal · Answer 1 · 2015-07-04T16:23:49.167

The problem could be, as mentioned by @Bjorn-Roger in the comments, that both 'month' and 'year' are keywords in SQL.

I would suggest you try :

select * from employee.transaction where department = @department AND [month] = @month AND [year] = @year

P.S: Notice the use of [] with the fields 'month' and 'year'.

Edit 1

Also, you might want to check if the date format of the input fields 'month' and 'year' is same as that of database table fields.

Data grid view not showing data from mysql table

1 Answers1