dataTimePicker as a search item

Question

I have a search application, which needs to retrive data from my mssql database.

I put a dataTimePicker and two (2) radioButtons which are meant for searching before or after selected date.

How can I do this simple?

Answer 1

I know this is hard.

Maybe I wasn`t so clear in what I would like to do.

My application stores files into databse. When the file is saved (inserted) into databse, I have a coulmn "StoredDate", which apparently stores the date when the file is inserted into database.

Now, I am doing somekind a search form in this application, where user can look for files, he wants to get. And one of the options is that the user chooses the date and selects a radionButton, and with this combination he tells to the program that he wants to search from  that selected date to now, or back of that selected date.

Example: if he chooses 10.6.2009 and a radioButton Back, that means that the program has to look for files which are older then 10.6.2009.

I would know how to do for 10.6.2009 and younger (from selected date from dataTimePicter to now). But I am not sure how to do it for the past (I can set one date in the past (lets say 10 years ago), but is there any option to tell just "older" then this selected date?

Answer 2

You can have 2 Radio buttons for Next and Previous and another for DateTimePicker.

The code can go this way to search for records before and after:

SqlCommand command = new SqlCommand();
if (radioButtonNext.Checked)
{
	command.CommandText = "SELECT * FROM Customers WHERE StoredDate >= @mydate";
}
else
{
	command.CommandText = "SELECT * FROM Customers WHERE StoredDate <= @mydate";
}
command.Parameters.Add(new SqlParameter("@mydate", SqlDbType.DateTime, 5)).Value = dateTimePicker1.Value;

 

---

Balaji Baskar [Please mark the post as answer if it answers your question]

Answer 3

I did:

                if (dateTimePicker1.Checked == true)
                {
                    DateTime izbranČas;
                    izbranČas = dateTimePicker1.Value;
                    cmd.Parameters.Add(new SqlParameter("@DatumVnosa", System.Data.SqlDbType.DateTime, 8, "DatumVnosaU"));
                    cmd.Parameters["@DatumVnosa"].Value = izbranČas.ToString("dd.MM.yyyy");
                }

I will use your code with StoredDate >= @mydate"; and StoredDate <= @mydate";

Btw, what means in your case: SqlDbType.DateTime, 5 - I mean 5?

In my case is 8, I just put a number, otherwise it was an error. What is representing?

Answer 4

Btw, what means in your case: SqlDbType.DateTime, 5 - I mean 5?

In my case is 8, I just put a number, otherwise it was an error. What is representing?

I am sorry it was a typo error, it should have been "8" instead of "5"; "8" simply means the size of the column!

You might have noticed that some other overloads of the add method are also available.  You can add a parameter by just setting its name and data type.  The command object takes care of its size automatically.  The data types for which size matter a lot ( char, varchar etc.) you should pass the size, but for parameters like date time you can ignore the size.  So, your modified statement(s) would be:

command.Parameters.Add(new SqlParameter("@DatumVnosa", SqlDbType.DateTime));
command.Parameters["@DatumVnosa"].Value = izbranČas.ToString("dd.MM.yyyy");;

---

Balaji Baskar [Please mark the post as answer if it answers your question]

Answer 5

I have a problem you know, I have a damn long query, take a look (sorry, cause its in my language) - if you dont get it, let me know, I`ll translate it (but you will see the point anyway)

            string Iskanje = "SELECT Številka, Ime, Tip, Pomembnost, Področje, DatumVnosa FROM UradniList WHERE " +
                                 "((@Številka IS NULL) OR (Številka = @Številka)) AND " +
                                 "((@Ime IS NULL) OR (Ime = @Ime)) AND " +
                                 "((@Tip IS NULL) OR (Tip = @Tip)) AND " +
                                 "((@Pomembnost IS NULL) OR (Pomembnost = @Pomembnost)) AND " +
                                 "((@Področje IS NULL) OR (Področje = @Področje)) AND " +
                                 "((@DatumVnosa IS NULL) OR (DatumVnosa = @DatumVnosa)) AND " +
                                 "DatumVnosa >= @DatumVnosa AS Novejši AND DatumVnosa < @DatumVnosa AS Starejši";
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = Iskanje;
                cmd.Connection = povezava;
                cmd.CommandType = CommandType.Text;

Don`t mind about Števila, Ime, Tip, Pomembnost, Področje (in eng: Numbers, Names, Type, Importance, Area) - All these works well!

"DatumVnosa" is the one that I have problems with (eng: DateTime Entry)

Are the last 2 lines in the query ok? I doubt. I got an error: "Must declare the scalar variable "@DatumVnosa".

But I don`t know how to seperate two radioButtons. I did that in the query, and I dont think its good.

And then I have:

                if (dateTimePicker1.Checked == true)
                {
                    if (radioButtonNovejši.Checked)
                    {
                        cmd.Parameters.Add(new SqlParameter("@Novejši", System.Data.SqlDbType.DateTime, 5, "DatumVnosa"));
                        cmd.Parameters["@Novejši"].Value = dateTimePicker1.Value;
                    }
                    else if (radioButtonStarejši.Checked)
                    {
                        cmd.Parameters.Add(new SqlParameter("@Starejši", System.Data.SqlDbType.DateTime, 5, "DatumVnosa"));
                        cmd.Parameters["@Starejši"].Value = dateTimePicker1.Value;
                    }
                }

What do you think? 

Answer 6

i see that you are using "5" still in your code. Use "8" instead. Meanwhile i am looking at the query.

                if (dateTimePicker1.Checked == true)
                {
                    if (radioButtonNovejši.Checked)
                    {
                        cmd.Parameters.Add(new SqlParameter("@Novejši", System.Data.SqlDbType.DateTime, 8, "DatumVnosaU"));
                        cmd.Parameters["@Novejši"].Value = dateTimePicker1.Value;
                    }
                    else if (radioButtonStarejši.Checked)
                    {                        
                        cmd.Parameters.Add(new SqlParameter("@Starejši", System.Data.SqlDbType.DateTime, 8, "DatumVnosaU"));
                        cmd.Parameters["@Starejši"].Value = dateTimePicker1.Value;
                    }

---

Balaji Baskar [Please mark the post as answer if it answers your question]

Answer 7

The upper code it doesnt work, but this bellow does (partly :) ), I just dont know how to declare that DBNull of DateTime. Because always is one radioButoon UNCKECKED!

string Iskanje = "SELECT ŠtevilkaU, ImeU, TipU, PomembnostU, PodročjeU, DatumVnosaU FROM UradniList WHERE " +
                 "((@Številka IS NULL) OR (ŠtevilkaU = @Številka)) AND " +
                 "((@Ime IS NULL) OR (ImeU = @Ime)) AND " +
                 "((@Tip IS NULL) OR (TipU = @Tip)) AND " +
                 "((@Pomembnost IS NULL) OR (PomembnostU = @Pomembnost)) AND " +
                 "((@Področje IS NULL) OR (PodročjeU = @Področje)) AND " +
                 "((@Novejši IS NULL) OR (DatumVnosaU >= @Novejši))";// AND " +
                 //"((@Starejši IS NULL) OR (DatumVnosaU < @Starejši))";  

If I have like this and radioButtonNext.Checked (From selected date and newer) it all works. I can not use both in the query, becuase SqlReader throws an error, that "Must declare the scalar variable "@Starejši".

bellow I have :

                if (dateTimePicker1.Checked == true)
                {
                    if (radioButtonNovejši.Checked)
                    {
                        cmd.Parameters.Add(new SqlParameter("@Novejši", SqlDbType.DateTime));
                        cmd.Parameters["@Novejši"].Value = dateTimePicker1.Value;
                    }
                    
                    
                    else if (radioButtonStarejši.Checked)
                    {
                        cmd.Parameters.Add(new SqlParameter("@Starejši", SqlDbType.DateTime));
                        cmd.Parameters["@Starejši"].Value = dateTimePicker1.Value;
                    }

is it possible to do sometihng like for a comboBox if there is nothing selected:

cmd.Parameters["@Področje"].Value = comboBox1.SelectedItem ?? DBNull.Value;

Answer 8

here is the complete code for the listing; please do let me know how it goes!

SqlCommand cmd = new SqlCommand();

string Iskanje = "SELECT Številka, Ime, Tip, Pomembnost, Podrocje, DatumVnosa FROM UradniList WHERE " +
		 "((@Številka IS NULL) OR (Številka = @Številka)) AND " +
		 "((@Ime IS NULL) OR (Ime = @Ime)) AND " +
		 "((@Tip IS NULL) OR (Tip = @Tip)) AND " +
		 "((@Pomembnost IS NULL) OR (Pomembnost = @Pomembnost)) AND " +
		 "((@Podrocje IS NULL) OR (Podrocje = @Podrocje))";

if (dateTimePicker1.Checked == true)
{
	if (radioButtonNovejši.Checked)
	{
	    Iskanje = Iskanje + " AND DatumVnosa >= @DatumVnosa AS Novejši";
	    cmd.Parameters.Add(new SqlParameter("@Novejši", System.Data.SqlDbType.DateTime));
	    cmd.Parameters["@Novejši"].Value = dateTimePicker1.Value;
	}
	else if (radioButtonStarejši.Checked)
	{
	    Iskanje = Iskanje + " AND DatumVnosa < @DatumVnosa AS Starejši";
	    cmd.Parameters.Add(new SqlParameter("@Starejši", System.Data.SqlDbType.DateTime));
	    cmd.Parameters["@Starejši"].Value = dateTimePicker1.Value;
	}
}
else
{
	Iskanje = Iskanje + " AND ((@DatumVnosa IS NULL) OR (DatumVnosa = @DatumVnosa))";
	cmd.Parameters.Add(new SqlParameter("@DatumVnosa", System.Data.SqlDbType.DateTime));
	cmd.Parameters["@DatumVnosa"].Value = dateTimePicker1.Value;
}

cmd.CommandText = Iskanje;
cmd.Connection = povezava;
cmd.CommandType = CommandType.Text;

---

Balaji Baskar [Please mark the post as answer if it answers your question]

Answer 9

This is cool! I have never seen this, that you can continue with thr query. Nice - I will remember that, its a very useful thing.

And btw, about your else statement. if the code will go through the it, it will find all the values in database. I would like none. But I would say it is impossible to set DateTime to DBNull.Value, right?

Answer 10

In C# datetime variable cannot be set to null but you can do your validation for DateTime.MinValue

But you can set a null to your DateTime SQLParameter, see this example below:

if(someDate == DateTime.MinValue) { // or "== null" for Nullable<DateTime> 
    dateParam.Value = DBNull.Value; 
} else { 
    dateParm.Value = someDate; 
} 

---

Balaji Baskar [Please mark the post as answer if it answers your question]

Answer 11

Works now, thx :)