SQL Server Express 2005 and Date field

[Eager_Beever]

I am using SQL Server Express 2005 as my Database and programming in C# 2005. In one of my Table "Results" I have a field named "ResultDate" of DateTime data type. In my form I have a DateTimePicker. I want to save only one record for a particular date, hence while saving the record I am checking whether a record for that particular date is available.

My Query string is "Select * from Results where ResultDate = " + dtpresultDate.value;

But it is not working. SQL stores both Date and Time in a DateTime field, so it is saving 12:00:00 in addition to the given date while saving the record. DateTimePicker is including the current time to the value. I tried to use "dtpResultDate.Value.ToShortDateString", but it is giving me an error about data type mismatch. I also tried using convert, but the problem was in India we generally use "dd/mm/yy" as short date format, whereas the closest match is "dd/mm/yyyy". Do I have to use "dd/mm/yyyy" in my DateTimePicker also?

How should I modify the Query string so that a match is done only using the Date parts (ignoring the Times)?

Also can I use Count to check whether a record is available?

Thank you.

Lalit Kumar Barik
India

[sknake]

First off it is not recommended that you use a string when you build SQL Queries dynamically, you should use variables. To get only the date portion you use dateTimePicker1.Value.Date. By using an SQL Variable the date format does not matter, and will work with different locale (region) settings. If you use the string equivelant you will find the queries will break in different countries. Here is an example of parameters and the date portion:

private void simpleButton1_Click(object sender, EventArgs e)
    {
      const string ConnString = @"Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      DataTable result = new DataTable();
      using (SqlConnection conn = new SqlConnection(ConnString))
      {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand())
        {
          cmd.Connection = conn;
          cmd.CommandText = @"Select * From Invoice Where OrderDate = @OrderDate";
          cmd.Parameters.Add(new SqlParameter("@OrderDate", dateTimePicker1.Value.Date));
          SqlDataReader dr = cmd.ExecuteReader();
          result.Load(dr);
        }
        conn.Close();
      }
      //work with your datatable here
      MessageBox.Show(result.Rows.Count.ToString());
    }

[Eager_Beever]

Thank you "SKnake" for guiding me in the proper direction. I was able to learn a lot from your reply and I hope to improve upon my programming style.
Using Parameters is very efficient even though one has to code longer code.

Just one more doubt. I am using a DataReader.HasRows just to prevent duplicate records for the same date. Can I use ExecuteScalar? Which is more efficient?

Lalit Kumar Barik
India

[sknake]

Please mark the thread as solved since your original question was answered.

To answer your next question it depends what you are trying to accomplish. ExecuteScalar() returns the first row, first column. The method I posted returns a DataTable which can contain many columns and many rows. If you are looking for a single value then ExecuteScalar() is the way to go, if not then you might consider the method i posted.

As for parameters the benefits outweigh the risk, and are worth your time. If you spend less time now, you'll spend more time later debugging it:

1) The query will work regardless of Date/Time or currency formatting.
2) You are stopping SQL Injection from occuring. If you get in the habit of building queries from user input directly, its only a matter of time before you code in an injection site.
3) You are helping preserve performance on the SQL Server. When you execute a query the SQL Server evaluates the queries and compiles an execution plan, unless it recognizes it just ran the query and already has a cached execute plan.

"Select * From Results Where aDate = 1/1/2009"
"Select * From Results Where aDate = 1/2/2009"

In this case the two queries are different and it has to compile a new execution plan for something it has already done. If a parameter was used instead of the date then it could rerun the previous execution plan.