?
Solved

c# form - having data sorted by date in descending order

Posted on 2011-10-13
3
Medium Priority
?
335 Views
Last Modified: 2012-05-12
hello this code may not be enough to have you help- but am hoping it is-
i have c# windows form w/microsoft access database as backend-
The sql statement below fiills a grid with data-
I want to have the data sorted by date in descending order.
I had help w/program awhile ago-
data field names EnterDateTime-
If this is not enough to help- let me know
thank you
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace ProjectId_1305852099
{
    public partial class frmView : Form
    {
        
        //OleDbConnection aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\data13.mdb");       
        OleDbConnection aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Program.dbPath + "\\data13.mdb");        

        public String gstrServiceNo = "";

        public frmView()
        {
            InitializeComponent();
        }

        private void frmView_Load(object sender, EventArgs e)
        {
            aConnection.Open();
            dgvData.AutoGenerateColumns = false;
            fillGrid();
        }

        private void fillGrid()
        {
            string sql = "";

            if (gstrServiceNo == "")
            {

                sql = "SELECT tblHeader.Header_Id, tblDetail.Detail_Id, tblHeader.[Service Number] As ServiceNumber, tblUsers.Name, tblParts.Part_Number, tblHeader.EnterDateTime FROM ((tblHeader INNER JOIN tblDetail ON tblHeader.Header_Id = tblDetail.Header_Id) INNER JOIN tblParts ON tblDetail.Part_Id = tblParts.Parts_Id) INNER JOIN tblUsers ON tblHeader.User_Id = tblUsers.User_Id;";
            }
            else
            {
                sql = "SELECT tblHeader.Header_Id, tblDetail.Detail_Id, tblHeader.[Service Number] As ServiceNumber, tblUsers.Name, tblParts.Part_Number, tblHeader.EnterDateTime FROM ((tblHeader INNER JOIN tblDetail ON tblHeader.Header_Id = tblDetail.Header_Id) INNER JOIN tblParts ON tblDetail.Part_Id = tblParts.Parts_Id) INNER JOIN tblUsers ON tblHeader.User_Id = tblUsers.User_Id WHERE tblHeader.[Service Number] = " + gstrServiceNo.Replace("'", "''") + ";";
            }
            
            OleDbDataAdapter da = new OleDbDataAdapter(sql, aConnection);
            DataSet ds = new DataSet();
            da.Fill(ds);
            dgvData.DataSource = ds.Tables[0].DefaultView;

            btnDelete.Enabled = false ;
            if (dgvData.Rows.Count > 0 )
            {
                btnDelete.Enabled = true;
            }

        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (dgvData.SelectedRows.Count > 0)
            {
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = aConnection;
                cmd.CommandText = "Delete From tblDetail Where Detail_Id=" + Convert.ToInt32(dgvData.SelectedRows[0].Cells[1].Value);

                cmd.ExecuteNonQuery();

                fillGrid();
            }
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {

        }
    }
}

Open in new window

0
Comment
Question by:davetough
3 Comments
 
LVL 2

Accepted Solution

by:
vossjck earned 1000 total points
ID: 36964627
add to the end of your sql queries.   where it has SQL =

add "Order by tblHeader.EnterDateTime desc" before the last ;
0
 
LVL 11

Assisted Solution

by:brutaldev
brutaldev earned 1000 total points
ID: 36964910
To elaborate on vossjck's answer with an example:
...
if (gstrServiceNo == "")
{
  // Look at the end for the ORDER BY clause
  sql = "SELECT tblHeader.Header_Id, tblDetail.Detail_Id, tblHeader.[Service Number] As ServiceNumber, tblUsers.Name, tblParts.Part_Number, tblHeader.EnterDateTime FROM ((tblHeader INNER JOIN tblDetail ON tblHeader.Header_Id = tblDetail.Header_Id) INNER JOIN tblParts ON tblDetail.Part_Id = tblParts.Parts_Id) INNER JOIN tblUsers ON tblHeader.User_Id = tblUsers.User_Id ORDER BY tblHeader.EnterDateTime DESC;";
}
else
{
  // Look at the end for the ORDER BY clause
  sql = "SELECT tblHeader.Header_Id, tblDetail.Detail_Id, tblHeader.[Service Number] As ServiceNumber, tblUsers.Name, tblParts.Part_Number, tblHeader.EnterDateTime FROM ((tblHeader INNER JOIN tblDetail ON tblHeader.Header_Id = tblDetail.Header_Id) INNER JOIN tblParts ON tblDetail.Part_Id = tblParts.Parts_Id) INNER JOIN tblUsers ON tblHeader.User_Id = tblUsers.User_Id WHERE tblHeader.[Service Number] = " + gstrServiceNo.Replace("'", "''") + " ORDER BY tblHeader.EnterDateTime DESC;";
}
...

Open in new window

0
 

Author Closing Comment

by:davetough
ID: 36966291
great - thank you
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question