DESC sql command not supported in datagridview?

[markthien]

Hello, my code is below:

private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'sampleDataSet.contact_Query' table. You can move, or remove it, as needed.
            //this.contact_QueryTableAdapter.Fill(this.sampleDataSet.contact_Query);
            
            OleDbConnection aConnection = new OleDbConnection(dbconnection);
            OleDbCommand aCommand = new OleDbCommand("select id, name, mobile_num, date_created from contact order by id", aConnection);

            try
            {
                aConnection.Open();
                //OleDbDataReader aReader = aCommand.ExecuteReader();
                OleDbDataAdapter oAdapter = new OleDbDataAdapter();
                oAdapter.SelectCommand = aCommand;
                DataSet ds = new DataSet();
                //Get the data for the selected table and populate the Grid
                oAdapter.Fill(ds);

                //Persist the Table Data to enable paging.
                dsTemp = ds;

                //If the dataset contains rows
                if (ds.Tables[0].Rows.Count > 0)
                {
                    //Send to our Fill Grid method
                    fillDataGrid_dtgBrowse(ds);
                    dataGridView1.Columns[0].Visible = false ;
                    dataGridView1.Columns[1].HeaderText = "Name";
                    dataGridView1.Columns[2].HeaderText = "Mobile Number";
                    dataGridView1.Columns[2].Width = 110;
                    dataGridView1.Columns[3].HeaderText = "Date Added";

                    DataGridViewCheckBoxColumn col = new DataGridViewCheckBoxColumn();
                    col.FalseValue = "0";
                    col.TrueValue = "1";
                    col.Width = 30;
                    dataGridView1.Columns.Insert(0, col);

                }
                else
                {
                    
                    bindingSource1.DataSource = null;
                    bindingSource1.Clear();
                    dataGridView1.DataSource = bindingSource1;
                }
                
                aConnection.Close();
            }

            catch (Exception exp)
            {
                MessageBox.Show("Error: {0}", exp.Message);
            }

            finally
            {

            }
        }

and it filled up the datagridview. however, when I change the sql statement to:

OleDbCommand aCommand = new OleDbCommand("select id, name, mobile_num, date_created from contact order by id DESC", aConnection);

with a DESC behind, no record display in the datagridview.

Please kindly advice. Thanks in advance!

Cheers,
Mark

[Lusiphur]

One thing to check (because I don't think that DESC is exclusionary to using DGVs) is that the table values in the database are what you expect them to be. For example, is the result 0 lines populated to the DGV or is it several lines with id's but no other content? If it's the latter you could be dealing with null values in the other columns that hadn't been anticipated by the design of this project.

There's literally no reason why changing the sort order of the select statement should be affecting whether or not data shows within the DGV. Alternately (though not preferred) you can use the following:

dataGridView1.Sort(dataGridView1.Columns["id"].ListSortDirection.Descending)

if you truly can't get the DESC to work in the select statement.

Hope this helps :) Please remember to mark as solved once the issue is resolved.

[nick.crane]

If there is a lot of records then this could be a timing issue.
It is not recommended to do any lengthy process in form_load.
Try moving the code to a seperate method and call it from form_shown instead.

[hirenpatel53]

according to me your query produce desc result but when binding with gride it sort to asc becouse sorting properti of gride must be asc

[arunkumars]

@HirenPatel, there is no problem with sorting property in the grid, what ever your query generates, the same order will be considered by the DGV too...

[markthien]

There are 30 records in the MDB. before adding DESC in the query statement, all the record displayed in the DGV. After adding DESC, no single record displayed. I am sure that I did not touch other code to cause this problem.

[arunkumars]

ok, then try using what Lusipur told...
probably I think, that would give an answer...

[markthien]

but what is really causing the problem? I just added the word DESC and nothing more.

[nick.crane]

If you break after oAdapter.Fill(ds); , does ds have data in it?
Also, what do you do in fillDataGrid_dtgBrowse(ds); ?

[markthien]

Hello,

I just follow code from this page: http://www.codeproject.com/KB/miscctrl/Pagable_DatagridView.aspx
to do paging. But instead I got my data from mdb.

#region fillDataGrid_dtgBrowse
        
        // Fills the grid with the data from the DataSet
        private void fillDataGrid_dtgBrowse(DataSet dtsTableData)
        {
            try
            {
                Cursor = Cursors.WaitCursor;
                
                //Set the BindingSource's DataSource to the DataSet Parameter passed to it.
                bindingSource1.DataSource = dtsTableData.Tables[0];

                //Determine the page boundaries
                DeterminePageBoundaries(dtsTableData);
                //Now that we have the boundaries, filter the bindingsource.
                //This has the following effect as the following Query:
                //select * from [LogTable] where id >= 10 and id <= 12
                bindingSource1.Filter = "RecordID >= " + RecordID1 + " and RecordID <= " + RecordID2;
                //Set the Grid's DataSource to the BindingSource. This populates the grid with data.
                dgNames.DataSource = bindingSource1;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Cursor = Cursors.Default;
            }
        }
        #endregion

        #region DeterminePageBoundaries
        // This method will determine the correct Page Boundaries for the RecordID's to filter by
        private void DeterminePageBoundaries(DataSet dsPages)
        {
            int TotalRowCount = dsPages.Tables[0].Rows.Count;
            //This is the maximum rows to display on a page. So we want paging to be implemented at 100 rows a page
            int pageRows = 100;
            int pages = 0;

            //If the rows per page are less than the total row count do the following:
            if (pageRows < TotalRowCount)
            {
                //If the Modulus returns > 0 then there should be another page.
                if ((TotalRowCount % pageRows) > 0)
                {
                    pages = ((TotalRowCount / pageRows) + 1);
                }
                else
                {
                    //There is nothing left after the Modulus, so the pageRows divide exactly...
                    //...into TotalRowCount leaving no rest, thus no extra page needs to be added.
                    pages = TotalRowCount / pageRows;
                }
            }
            else
            {
                //If the rows per page are more than the total row count, we will obviously only ever have 1 page
                pages = 1;
            }


            //We now need to determine the LowerBoundary and UpperBoundary in order to correctly...
            //...determine the Correct RecordID's to use in the bindingSource1.Filter property.
            int LowerBoundary = 0;
            int UpperBoundary = 0;

            //We now need to know what button was clicked, if any (First, Last, Next, Previous)
            switch (NavClicked)
            {
                case "First":
                    //First clicked, the Current Page will always be 1
                    CurrentPage = 1;
                    //The LowerBoundary will thus be ((50 * 1) - (50 - 1)) = 1
                    LowerBoundary = ((pageRows * CurrentPage) - (pageRows - 1));

                    //If the rows per page are less than the total row count do the following:
                    if (pageRows < TotalRowCount)
                    {
                        UpperBoundary = (pageRows * CurrentPage);
                    }
                    else
                    {
                        //If the rows per page are more than the total row count do the following:
                        //There is only one page, so get the total row count as an UpperBoundary
                        UpperBoundary = TotalRowCount;
                    }

                    //Now using these boundaries, get the appropriate RecordID's (min & max)...
                    //...for this specific page.
                    //Remember, .NET is 0 based, so subtract 1 from both boundaries
                    RecordID1 = dsPages.Tables[0].Rows[LowerBoundary - 1]["id"].ToString();
                    RecordID2 = dsPages.Tables[0].Rows[UpperBoundary - 1]["id"].ToString();

                    break;

                case "Last":
                    //Last clicked, the CurrentPage will always be = to the variable pages
                    CurrentPage = pages;
                    LowerBoundary = ((pageRows * CurrentPage) - (pageRows - 1));
                    //The UpperBoundary will always be the sum total of all the rows
                    UpperBoundary = TotalRowCount;
                    //Now using these boundaries, get the appropriate RecordID's (min & max)...
                    //...for this specific page.
                    //Remember, .NET is 0 based, so subtract 1 from both boundaries
                    RecordID1 = dsPages.Tables[0].Rows[LowerBoundary - 1]["id"].ToString();
                    RecordID2 = dsPages.Tables[0].Rows[UpperBoundary - 1]["id"].ToString();

                    break;

                case "Next":
                    //Next clicked
                    if (CurrentPage != pages)
                    {
                        //If we arent on the last page already, add another page
                        CurrentPage += 1;
                    }

                    LowerBoundary = ((pageRows * CurrentPage) - (pageRows - 1));

                    if (CurrentPage == pages)
                    {
                        //If we are on the last page, the UpperBoundary will always be the sum total of all the rows
                        UpperBoundary = TotalRowCount;
                    }
                    else
                    {
                        //Else if we have a pageRow of 50 and we are on page 3, the UpperBoundary = 150
                        UpperBoundary = (pageRows * CurrentPage);
                    }

                    //Now using these boundaries, get the appropriate RecordID's (min & max)...
                    //...for this specific page.
                    //Remember, .NET is 0 based, so subtract 1 from both boundaries
                    RecordID1 = dsPages.Tables[0].Rows[LowerBoundary - 1]["id"].ToString();
                    RecordID2 = dsPages.Tables[0].Rows[UpperBoundary - 1]["id"].ToString();

                    break;

                case "Previous":
                    //Previous clicked
                    if (CurrentPage != 1)
                    {
                        //If we aren't on the first page already, subtract 1 from the CurrentPage
                        CurrentPage -= 1;
                    }
                    //Get the LowerBoundary
                    LowerBoundary = ((pageRows * CurrentPage) - (pageRows - 1));

                    if (pageRows < TotalRowCount)
                    {
                        UpperBoundary = (pageRows * CurrentPage);
                    }
                    else
                    {
                        UpperBoundary = TotalRowCount;
                    }

                    //Now using these boundaries, get the appropriate RecordID's (min & max)...
                    //...for this specific page.
                    //Remember, .NET is 0 based, so subtract 1 from both boundaries
                    RecordID1 = dsPages.Tables[0].Rows[LowerBoundary - 1]["id"].ToString();
                    RecordID2 = dsPages.Tables[0].Rows[UpperBoundary - 1]["id"].ToString();

                    break;

                default:
                    //No button was clicked.
                    LowerBoundary = ((pageRows * CurrentPage) - (pageRows - 1));

                    //If the rows per page are less than the total row count do the following:
                    if (pageRows < TotalRowCount)
                    {
                        UpperBoundary = (pageRows * CurrentPage);
                    }
                    else
                    {
                        //If the rows per page are more than the total row count do the following:
                        //Therefore there is only one page, so get the total row count as an UpperBoundary
                        UpperBoundary = TotalRowCount;
                    }

                    //Now using these boundaries, get the appropriate RecordID's (min & max)...
                    //...for this specific page.
                    //Remember, .NET is 0 based, so subtract 1 from both boundaries
                    RecordID1 = dsPages.Tables[0].Rows[LowerBoundary - 1]["id"].ToString();
                    RecordID2 = dsPages.Tables[0].Rows[UpperBoundary - 1]["id"].ToString();

                    break;
            }

        }
        #endregion

[nick.crane]

This line is removing all your records. bindingSource1.Filter = "RecordID >= " + RecordID1 + " and RecordID <= " + RecordID2;

[markthien]

sorry actually I have already changed it to:

bindingSource1.Filter = "id >= " + RecordID1 + " and id <= " + RecordID2;

but the problem still there after adding the DESC

[nick.crane]

No, I did not mean that the field name was incorrect.
The values for RecordID1 and RecordID2 are taken from the first and last rows in the data table.
Since you have now inverted your table using DESC RecordID1 > RecordID2 so no rows are displayed.

[nick.crane]

Try this.

if (RecordID1 < RecordID2)
    bindingSource1.Filter = "RecordID >= " + RecordID1 + " and RecordID <= " + RecordID2;
else
    bindingSource1.Filter = "RecordID >= " + RecordID2 + " and RecordID <= " + RecordID1;

[markthien]

Thanks a lot guys. Let me try it in the office as the code r in my office pc .... lol !