sql statement (correction)

[jed]

string selectstr = "SELECT SUM(hrs) AS total_hours,SUM(mins)AS
total_mins FROM Timerecords WHERE (workdate >='" +
Convert.ToDateTime(dateTimePickerfirst.Text) + "')AND (workdate<='" +
Convert.ToDateTime (dateTimePickersecond.Text) + "')";

I have two datepickerboxs that i want to select dates in.I want the
selectstr string to recieve the values of the the calculated hours and
the calculated minutes but it must only sum up the values between the
two dates that i select in the datepickerboxs.

I dont receive the correct values from total_hours and total_mins.It
doesnt add the the values between the the two dates.I cant work out
where the error is.What is the best way to insert datetime into an SQL
statement.

 

[Tom Spink]

string selectstr = "SELECT SUM(hrs) AS total_hours,SUM(mins)AS
total_mins FROM Timerecords WHERE (workdate >='" +
Convert.ToDateTime(dateTimePickerfirst.Text) + "')AND (workdate<='" +
Convert.ToDateTime (dateTimePickersecond.Text) + "')";

I have two datepickerboxs that i want to select dates in.I want the
selectstr string to recieve the values of the the calculated hours and
the calculated minutes but it must only sum up the values between the
two dates that i select in the datepickerboxs.

I dont receive the correct values from total_hours and total_mins.It
doesnt add the the values between the the two dates.I cant work out
where the error is.What is the best way to insert datetime into an SQL
statement.

Hi,

What do you mean the correct values? Can you post an example of the query
in action, giving some sample tuples, and stating what the output you
expect is, and what the output you're getting is?

Thanks!

 

[Guest]

Howdy,

Use parameters instead of passing date as string. In addition, you could use
Profiler to see what's beeing run on the sql server side.

string selectstr =
"SELECT SUM(hrs) AS total_hours,SUM(mins) AS total_mins " +
"FROM Timerecords WHERE (workdate between @startDate and @endDate)";

SqlConnection connection = new SqlConnection(ConnectionSstring));
SqlCommand command = new SqlCommand(selectstr, connection);
SqlDataReader reader = null;

command.Parameters.Add("@startDate", SqlDbType.DateTime).Value =
DateTime.Parse(dateTimePickerfirst.Text);
command.Parameters.Add("@endDate", SqlDbType.DateTime).Value =
DateTime.Parse(dateTimePickersecond.Text);

try
{
connection.Open();

reader = command.ExecuteReader();

while (reader.Read())
{
// whateever
}
}
catch
{
throw;
}
finally
{
if (reader != null)
reader.Close();
connection.Dispose();
}

Hope thus helps

 

[Jon Skeet [C# MVP]]

string selectstr = "SELECT SUM(hrs) AS total_hours,SUM(mins)AS
total_mins FROM Timerecords WHERE (workdate >='" +
Convert.ToDateTime(dateTimePickerfirst.Text) + "')AND (workdate<='" +
Convert.ToDateTime (dateTimePickersecond.Text) + "')";

I have two datepickerboxs that i want to select dates in.I want the
selectstr string to recieve the values of the the calculated hours and
the calculated minutes but it must only sum up the values between the
two dates that i select in the datepickerboxs.

I dont receive the correct values from total_hours and total_mins.It
doesnt add the the values between the the two dates.I cant work out
where the error is.What is the best way to insert datetime into an SQL
statement.

The same answer as to yesterday's question: use a parameterised query.

See http://pobox.com/~skeet/csharp/faq/#db.parameters

Jon