0

I'm working on a C# project where you can make reservations.
Now I have to search on a date between the Start and End date from a Microsoft SQL server database selected with a DateTimePicker.

I did the same with searching on name but for some reason it doesn't work with the date.
This is the code I used:

private void btnZoekDatum_Click(object sender, EventArgs e)
{
    DateTime zoekOpdracht = dtpZoekDatum.Value;

    //MessageBox.Show(zoekOpdracht);

    /*if (zoekOpdracht != "")
    {*/
        con.Open();
        /*try
        {*/
            cmd = "SELECT * FROM reserveringen WHERE Startdatum >= @datum AND Einddatum <= @datum";
            command = new SqlCommand(cmd, con);
            SqlDataAdapter da = new SqlDataAdapter(command);

            command.Parameters.Add(new SqlParameter("@datum", SqlDbType.DateTime) { Value = dtpZoekDatum.Value });
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                dgvReserveringen.DataSource = dt;
                dt.Dispose();
                da.Dispose();
                //con.Close();
            }
            else
            {
                MessageBox.Show("Er is helaas geen reservering gevonden.");
                con.Close();
            }
        /*}
        catch (Exception)
        {
            MessageBox.Show("Er is helaas geen reservering gevonden.");
            //con.Close();
        }*/
    /*}
    else
    {
        MessageBox.Show("Voer a.u.b. een zoekopdracht in.");
    }*/
}

Now if I choose a date and press Search, I get the following error (the error shown by da.Fill(dt);):

System.Data.SqlClient.SqlException: 'The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.'

I already tried some things with converting but then I still got an error. Maybe my SQL query is wrong...

So does someone know what the problem is and how I can fix this?

Thanks in advance!

Cas Heynen
  • 11
  • 4
  • 1
    If you have a date to begin with, why are you converting it to a string and add the parameter as a string? Add it as a date. `new SqlParameter("@datum", SqlDbType.DateTime) { Value = dtpZoekDatum.Value }`. The specific error message is because you are converting the date as mm-dd-yyyy and then the server tries to read it as dd-mm-yyyy, or vice versa. – GSerg May 25 '20 at 07:46
  • @GSerg Thank you for your fast reaction. Indeed, I tried it immediately and the error is gone so thank for that! But now I still get no results but I have some reservations in the database so do you think my query is wrong? – Cas Heynen May 25 '20 at 07:52
  • I saw your edit :-). When I do this it gives me an new error on da.Fill(dt); But actually I don't know what it means: System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@datum".' – Cas Heynen May 25 '20 at 07:54
  • It means you are not passing the parameter properly. Please check that code you are running is the code you have shown. It would also be better if you removed all your manual `Close` calls (which you don't have enough of anyway, you must Close regardless of whether you have results) and replace them with [`using` clauses](https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/using-statement) around `con`, `command` and `da`. – GSerg May 25 '20 at 08:02
  • Thank you! I also see the formats of the date are different in the output and the database. Do you know how I can change the format in the parameter? – Cas Heynen May 25 '20 at 08:06
  • A date does not have a format. If your data grid applies a certain format to database dates, then change that format in the grid. – GSerg May 25 '20 at 08:07
  • Alright, I solved the error now to change the parameter but still got no results so I think there is a problem in the query so he can't compare those dates? – Cas Heynen May 25 '20 at 08:12
  • For some reason I still can't find the problem. I looks like the adding the parameter works fine but I don't get any results. Or is there maybe a easier way to do this? – Cas Heynen May 25 '20 at 08:47
  • The date in the database looks like this: 2020-05-01 17:19:13.000. And the output of the DateTimePicker is like this: 21-5-2020 10:49:19. So I think this is the problem. But does someone know how and where I have to change this so it's equal? – Cas Heynen May 25 '20 at 08:50
  • A date in the database does not have a format, nor does DateTimePicker.Value. The date format you see is the result of applying a format by the tool which you are using to browse the value. If the column in the database is in fact of a datetime type, and you are passing an actual date to the query [like suggested](https://stackoverflow.com/questions/61997858/c-sharp-search-on-date-between-start-and-end-date-from-database?noredirect=1#comment109652437_61997858), then it just works. Please edit your question to show your complete code after all the changes you've made. – GSerg May 25 '20 at 10:36
  • Yeah I shall do, I'm new here :-) – Cas Heynen May 25 '20 at 12:06
  • I changed the code. I put out the if statement and the try catch to try some things. But actually I don't know what you mean. Sorry it sounds weird but I'm not that good in C# :-) Because I don't get any errors now I don't know what the problem could be because I still get no results? – Cas Heynen May 25 '20 at 12:09
  • In case this is still unsolved...Have you tried substituting the actual date value passed with `@datum` in your query and running it directly on the database. See if it yields any results there. – Thimmu Lanka Jun 03 '20 at 04:33

0 Answers0