DateTime SQL Problem

Question

I am trying to get my code to check to see if a number has already been entered for todays date. The reason for this is that the docketnumbers duplicate throughout the book, a bit like raffle tickets. I don't want the same number to be entered into the database more than once on the same day.

and now my code

string thisday = DateTime.Now.ToString("MM/dd/yyyy");

then the check

 public void button11_Click(object sender, EventArgs e)
    {
        string thisday = DateTime.Now.ToString("MM/dd/yyyy");
        errorProvider1.Clear();
        Data.docnum = txtDisplay.Text;
        if (txtDisplay.Text == "")
        {
            errorProvider1.SetError(txtDisplay, "Enter Docket Number");
            return;
        }
        using (var db = new DocketsDataContext())
        {
             var docketCheck = from q in db.Dockets where q.Status== "Ca"  select q;   
             string message = "";
             foreach (var d in docketCheck)
             {
                 message += String.Format(" DocketNum {0} - Status {1} - TimeRaised {2} - Thisday {3}\r\n", d.DocketNum, d.Status, d.TimeRaised, thisday);
             }
             MessageBox.Show(message);


            var complete = from q in db.Dockets
                           where q.DocketNum == txtDisplay.Text && q.Status.Equals("CL") //&& q.TimeRaised.Contains(thisday)
                           select q;

            var cancelcheck = from q in db.Dockets
                              where q.DocketNum == txtDisplay.Text && q.Status.Equals("Ca") && q.TimeRaised.Equals(thisday)
                              select q;

            var docketcheck = from q in db.Dockets
                              where q.DocketNum == txtDisplay.Text && q.Status.Equals("O") //&& q.TimeRaised.Contains(thisday)
                              select q;

            var statuscheck = from q in db.Dockets
                              where q.DocketNum == txtDisplay.Text &&  q.Status.Equals("Ea") //&& q.TimeRaised.Contains(thisday)
                              select q;


            if (cancelcheck.Count() >= 1)
            {
                MessageBox.Show(@"Match Found");
                txtDisplay.Clear();
                txtDisplay.ReadOnly = false;
                var cancel = new Cancel();
                cancel.ShowDialog(this);
            }

            else if (complete.Count() >= 1)
            {
                txtDisplay.Clear();
                txtDisplay.ReadOnly = false;
                var cat = new Docerror();
                cat.ShowDialog(this);
            }

            else if (statuscheck.Count() >= 1)
            {
                txtDisplay.Clear();
                txtDisplay.ReadOnly = false;
                var cat = new Category();
                cat.ShowDialog(this);
            }

            else if (docketcheck.Count() >= 1)
            {
                txtDisplay.Clear();
                txtDisplay.ReadOnly = false;
                var engs = new EngStart();
                engs.ShowDialog(this);
            }

            else
            {

                var sub = new machinesel();
                txtDisplay.Clear();
                sub.ShowDialog(this);
            }
        }

    }

when thisday matches today's date it should trigger the cancel feature but it is not; any ideas?

Jay

Answer 1

You are compering a String with a DateTime, that will never return true. Try using the following:

var cancelCheck = 
  (from q in db.Dockets
   where 
    q.DocketNum == txtDisplay.Text && 
    q.Status.Equals("Ca") &&
    q.TimeRaised.Date == DateTime.Now.Date
  select q).Any();

Also, use Any() rather than Count() > 0

Answer 2

In your query, can you do something like this:

from q in db.Dockets
where q.DocketNum == txtDisplay.Text  
&& q.Status.Equals("CL")  
&& (q.TimeRaised - DateTime.Today).Hours >= 0  
&& !(q.TimeRaised > DateTime.Today.AddDays(1))
select q;

Not sure if it's possible to add records with a future date, so I added the "DateTime.Today.AddDays(1)" The logic is based on the TimeSpan class returned from comparing two dates:

  TimeSpan ts = DateTime.Now - DateTime.Now.AddYears(-1);
  Console.WriteLine(ts.Days);

Which would give you the 365 days difference.