Monthly Sales using two datetimepickers

Question

I have two datetimepicker and one button I want sales of monthly or wahtever date selected in two datetimepickers

Below is my query:

ad2 = New SqlDataAdapter(
        "select sum(amt) from TailorLedger where date between'" &
        CDate(DateTimePicker1.Value.ToShortDateString) &
        "' AND '" &
        CDate(DateTimePicker2.Value.ToShortDateString) &
        "'", con)

But on button press it shows following error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Can anyone suggest the correct query?

score 0 · Answer 1 · edited May 23 '17 at 11:57

You should always parameterize your SQL queries to avoid SQL Injection.

Once you have used strong typing on the SQL parameters you should be able to solve the problem you are seeing. To start you off, here's a snippet (not compiled or tested):

 SqlDataAdapter myCommand = new SqlDataAdapter("select sum(amt) from TailorLedger where date between @par1 and @par2", connection);
  myCommand.SelectCommand.Parameters.Add("@par1", SqlDbType.DateTime);
  myCommand.SelectCommand.Parameters["@par1"].Value = yourFirstDate;
  myCommand.SelectCommand.Parameters.Add("@par2", SqlDbType.DateTime);
  myCommand.SelectCommand.Parameters["@par2"].Value = yourSecondDate;

If this is still not working, you should investigate the culture being used, see this post for more details.

Monthly Sales using two datetimepickers

1 Answers1