CODEWITHSUNDEEP
c#sqlasp.netsql-servervisual-studio
MHD BKT
MHD BKT

Reputation: 19

asp.net chart read from aspx.cs file with custom query from sql server

I have a chart in asp.net web form page. i want to fill it with a custom query created by joining two variables from the same sql table.

Here is the code (query):

String year2 = Convert.ToString(dropDownStartYear.Text);
String month2 = Convert.ToString(dropDownMonth2.Text);
String day2 = Convert.ToString(dropDownDay2.Text);
String hour2 = Convert.ToString(dropDownHour2.Text);
String minute2 = Convert.ToString(dropDownMinute2.Text);

String year3 = Convert.ToString(dropdownYear3.Text);
String month3 = Convert.ToString(dropDownMonth3.Text);
String day3 = Convert.ToString(dropDownDay3.Text);
String hour3 = Convert.ToString(dropDownHour3.Text);
String minute3 = Convert.ToString(dropDownMinute3.Text);

SqlConnection connection = ConnectionManager.getConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandText = "SELECT id, temperature, year, month, day , hour, minu from datab where year = @year and month=@month and day=@day and hour=@hour and minu=@minute ; ";
cmd.Parameters.AddWithValue("@year", year2);
cmd.Parameters.AddWithValue("@month", month2);
cmd.Parameters.AddWithValue("@day", day2);
cmd.Parameters.AddWithValue("@hour", hour2);
cmd.Parameters.AddWithValue("@minute", minute2);

SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{

}

where i want to select a start date and time (year, month, day, hour, minute) from a table in sql server and also select an end date with same variables. then i want to draw the "temperature" that varies from the start time to the end time.

any help there?

Upvotes: 1

Views: 194

Answers (2)

Faisal.lh
Faisal.lh

Reputation: 29

Try this query...

    cmd.CommandText = "SELECT id, temperature, year, month, day , hour, minu from datab 
where CONVERT ( datetime , cast(year as varchar)+'-'+cast(month as varchar)+'-'+cast(day as varchar)+' '+cast(hour as varchar)+':'+cast(minute as varchar)+':'+'00' , 20) >= CONVERT ( datetime , @yearfrom+'-'+@monthFrom+'-'+@dayFrom+' '+@hourFrom+':'+@minuteFrom+':'+'00' , 20)
and CONVERT ( datetime , cast(year as varchar)+'-'+cast(month as varchar)+'-'+cast(day as varchar)+' '+cast(hour as varchar)+':'+cast(minute as varchar)+':'+'00' , 20) <= CONVERT ( datetime , @yearTo+'-'+@monthTo+'-'+@dayTo+' '+@hourTo+':'+@minuteTo+':'+'00' , 20);";

Upvotes: 0

Poosh
Poosh

Reputation: 542

Try this query, building DateTime objects from the date parts in the input and stored in your table. That should make it easier to compare and restrict your return values by the selected input values:

cmd.CommandText = @"select [id], [temperature], [year], [month], [day], [hour], [minu] 
from datab
where DATETIMEFROMPARTS([year], [month], [day], [hour], [minu], 0, 0) 
between DATETIMEFROMPARTS(@year2, @month2, @day2, @hour2, @minute2, 0, 0) 
and DATETIMEFROMPARTS(@year3, @month3, @day3, @hour3, @minute3, 0, 0)";

// beginning of date range
cmd.Parameters.AddWithValue("@year2", year2);
cmd.Parameters.AddWithValue("@month2", month2);
cmd.Parameters.AddWithValue("@day2", day2);
cmd.Parameters.AddWithValue("@hour2", hour2);
cmd.Parameters.AddWithValue("@minute2", minute2);

// end of date range
cmd.Parameters.AddWithValue("@year3", year3);
cmd.Parameters.AddWithValue("@month3", month3);
cmd.Parameters.AddWithValue("@day3", day3);
cmd.Parameters.AddWithValue("@hour3", hour3);
cmd.Parameters.AddWithValue("@minute3", minute3);

Upvotes: 1

Related Questions