There is a Windows Forms project in C # using the Access database. Various sorting works standard inline queries and output

this.hotelTableAdapter.FillByLonger(this.hoteldbDataSet.hotel);

in this format.

It remains to do the last, this is sorting by date. To do this, transfer the selected date to the DataTimePicker in the SQL query.

C #

 private void dateTimePicker1_ValueChanged(object sender, EventArgs e) { DateTime result = dateTimePicker1.Value; this.Text = result.ToString(); }

SQL

 SELECT id, [ФИО проживающего], [Дата заселения], [Дата выселения], [Гостиничный номер] FROM hotel WHERE ([Дата выселения] LIKE '" + dateTimePicker1.Text"')

Question. How to sort by date? Is the parameter passed correctly? Swears on To_DATE () in request. How to do it, has not yet decided. I found out only that the format for the request should get something like this # 13/11/2013 #

  • Maybe not sorting, and filter by date? - Donil
  • Yes, more like that. - alexdeia

2 answers 2

Try using System.Data.SqlClient.SqlParameter . Example:

 var ColumnsNames = new List<string>(){"id","Name","DateIn","DateOut","HotelNo"}; List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>(); string query = @"SELECT id, Name, DateIn, DateOut, HotelNo FROM hotel WHERE DateOut = @DateOut"; DateTime _date = DateTime.Now;//как пример значения времени using (SqlCommand Query = myconnect.CreateCommand()) { Query.CommandText = query; SqlParameter _par = new SqlParameter(); _par.ParameterName = "@DateOut"; _par.DbType = DbType.DateTime; _par.Value = _date; Query.Parameters.Add(_par); using (SqlDataReader reader = Query.ExecuteReader()) { while (reader.Read()) { Dictionary<string, object> row = new Dictionary<string, object>(); for (int n = 0; n < ColumnsNames.Length; n++) { row.Add(ColumnsNames[n], reader.GetValue(n)); } rows.Add(row); } reader.Close(); } }

    LIKE for working with dates is not particularly suitable. It is better to use special functions .

    Suppose we want to select all entries created today. To do this, we use the DateDiff function, and pass the date value as an @Date parameter:

      command.CommandText = "SELECT * FROM SomeTable WHERE DateDiff(\"d\", DateCreate, @Date);"; command.Parameters.AddWithValue("Date", DateTime.Today); using (OleDbDataReader reader = command.ExecuteReader()) { while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { Console.WriteLine(string.Format("{0}: {1}", reader.GetName(i), reader.GetValue(i))); } Console.WriteLine("----------------------------"); } }

    As you noticed as the first parameter, we passed the interval of interest - day (d). You can transfer there year (yyyy), month (m), etc.

    And one more subtlety: as they say on SO OleDb does not like milliseconds, so if instead of DateTime.Today to pass DateTime.Now , the request will generate an error (at least I had it)