0

I am trying to use a like parameter to get the year within a date field in my database using a control parameter. I need this to work on the aspx not the aspx.cs page. Below is my code: 

<asp:SqlDataSource ID="getStudentResultsYearOne" runat="server"
    ConnectionString="<%$ ConnectionStrings:UniString %>" 
    SelectCommand="SELECT DISTINCT modules.module_name, student_module_grade.grade, module_desc.cat_points,student_module_grade.result_code, student_module_grade.module_grade_id FROM modules INNER JOIN module_on_pathway ON modules.id = module_on_pathway.module_id INNER JOIN module_desc ON modules.id = module_desc.module_id INNER JOIN year_in_pathway ON module_on_pathway.year_in_pathway_id = year_in_pathway.id INNER JOIN pathway_years ON year_in_pathway.pathway_year_id = pathway_years.id INNER JOIN class ON pathway_years.id = class.pathway_year_id INNER JOIN classlist ON class.class_id = classlist.class_id INNER JOIN student_module_grade ON module_on_pathway.id = student_module_grade.module_on_pathway_id INNER JOIN users ON classlist.user_id = users.id INNER JOIN chosen_modules_list ON classlist.classlist_id = chosen_modules_list.classlist_id WHERE (chosen_modules_list.module_on_pathway_id = module_on_pathway.id OR module_on_pathway.mandatory_module = 1) AND module_on_pathway.pathway_year_id = @pathway_years_id AND (year_in_pathway.year_end LIKE '%'+ @maxYear +'%')">


    <SelectParameters>
        <asp:SessionParameter Name="student_id" SessionField="UserLoggedOn" Type="Int32" />
        <asp:ControlParameter Name="pathway_years_id" ControlID="pathwayYearsId" PropertyName="Text" Type="Int32" />
        <asp:ControlParameter Name="maxYear" ControlID="lastYear" PropertyName="Text" Type="String" />
    </SelectParameters>

(This is the label used to get current year to compare it with my dates in my database)

<asp:Label ID="lastYear" runat="server" Visible="false"></asp:Label>

(This is in the .cs file to get the current year and populate the label mentioned above)

protected void Page_Load(object sender, EventArgs e)
{
    DateTime Today = DateTime.Today;
    Int32 currentYear = Today.Year;

    lastYear.Text = Convert.ToString(currentYear);
}

Thanks for any help you can provide. :)

Ninita
  • 1,209
  • 2
  • 19
  • 45
Danielle McKay
  • 23
  • 1
  • 7
  • Your DB will have a specific function to return an integer year from a date type field, thats likely what you should use in your WHERE clause rather than LIKE. What database are you using and what search criteria do you want to apply to the year? – Alex K. Apr 12 '16 at 15:44
  • I am using SQL Server 2012 in Visual Studio 2015, and I am searching for dates in the current year, so that the results shown are only from the current year. – Danielle McKay Apr 12 '16 at 15:46
  • Then you can: `... WHERE YEAR(your_date_field) = 2016` – Alex K. Apr 12 '16 at 15:47
  • The `LIKE` operator only works with text fields, so you'll have to use something like `year_in_pathway.year_end LIKE CAST(DATEPART(YEAR, @maxyear) AS VARCHAR(4))`. You might also have to cast `year_in_pathway.year_end` to `VARCHAR(4)`, depending on if its a text field. – Icemanind Apr 12 '16 at 15:48
  • LIKE would work with a DATETIME, you just would not do it. – Alex K. Apr 12 '16 at 15:52
  • @AlexK. That worked perfectly, thank you so much for your help!:) – Danielle McKay Apr 12 '16 at 15:52

0 Answers0