locked
Query even when textboxes are left empty RRS feed

  • Question

  • User-701766627 posted

    I'm giving a user the option to select from a range of fields (check boxes). Once selected, the user enters the value for the selected fields. Then, the code is supposed to retrieve database data only from the fields (columns) selected.

    Here is my Default.cshtml file so you have an idea how it looks:

    <form method="get" autocomplete="off" action="/ExportFilter">                                                                            
    <fieldset>
        <legend>Export Filter</legend>
            <div>
                <table style="padding-left:1%;padding-top:10px;">
                    <tr style="line-height:24px;">
                        <td><label style="padding-right: 57px;">Date From:</label></td>                                     
                        <td><input type="text" name="DateFrom" class="Picker" style="width:136px;margin-left:24px"/></td>
                    </tr>
                    <tr style="line-height:24px;">
                        <td><label>Date To:</label></td>
                        <td><input type="text" name="DateTo" class="Picker" style="width:136px;margin-left:24px"/></td>
                    </tr>
                </table>
                <table style="padding-left:1%;padding-top:17px;"> 
                    <tr style="line-height:24px;">
                        <td><label>Project:</label></td>                                                                                    
                        <td><input type="checkbox" name="ProjectCheckbox" id="ProjectCheckbox" value="1" style="width:35px; vertical-align: middle; ;"/></td>
                        <td><div id="Projectdiv" style="display:none">
                            <input type="text" name="Project2" id="Project2" style="width:136px;"/></div>
                        </td>
                    </tr>
                    <tr style="line-height:24px;">
                        <td><label>Flight Hours:</label></td>
                        <td><input type="checkbox" name="FHCheckbox" id="FHCheckbox" value="1" style="width:35px; vertical-align: middle; ;"/></td>
                        <td><div id="FHdiv" style="display:none">
                            <input type="text" name="FH" id="FH" style="width:136px;"/></div>
                        </td>
                    </tr>
                    <tr style="line-height:24px;">
                        <td><label>System:</label></td>
                        <td><input type="checkbox" name="SystemCheckbox" id="SystemCheckbox" value="hello" style="width:35px; vertical-align: middle; ;"/></td>
                        <td><div id="Systemdiv" style="display:none">
                            <input type="text" name="Sys" id="Sys" style="width:136px;"/></div>
                        </td>
                    </tr>
                    <tr style="line-height:24px;">
                        <td><label>AGL (m):</label></td>
                        <td><input type="checkbox" name="AGLCheckbox" id="AGLCheckbox" value="1" style="width:35px; vertical-align: middle; ;"/></td>
                        <td><div id="AGLdiv" style="display:none">
                            <input type="text" name="AGL" id="AGL" style="width:136px;"/></div>
                        </td>
                    </tr>
                    <tr style="line-height:43px;">
                        <td><input type="Submit" value="Export" id="ExcelMS"/></td>
                    </tr>
                </table>
            </div>
    </fieldset><br/>
    </form>

    And this is the ExportFilter.cshtml file where a query is used together with the values entered by the user (I'm using SQL Server):

    @{
        Layout = null;
        
        @Html.Hidden("DateFrom", Request["DateFrom"])                     
        @Html.Hidden("DateTo", Request["DateTo"])
        @Html.Hidden("Project2", Request["Project2"])
        @Html.Hidden("FH", Request["FH"])
        @Html.Hidden("Sys", Request["Sys"])
        @Html.Hidden("AGL", Request["AGL"])
        
        var cookieValue1 = Request["DateFrom"];
        var cookieValue2 = Request["DateTo"];
        var cookieValue3 = Request["Project2"];
        var cookieValue4 = Request["FH"];
        var cookieValue5 = Request["Sys"];
        var cookieValue6 = Request["AGL"];
    
        var db = Database.Open("MissionSummary");
        IEnumerable<dynamic> data = null;
        var sql ="";
    
        if(!cookieValue1.IsEmpty() && !cookieValue2.IsEmpty() && !cookieValue3.IsEmpty() && !cookieValue4.IsEmpty() && !cookieValue5.IsEmpty() && !cookieValue6.IsEmpty())
        {
            sql =
            @"SELECT    Mission.Project, Mission.FlightDate, Mission.FlightHours, Mission.System, Project.AGL_m
            FROM        Project, Mission, Aircraft_Billing
    	WHERE	    FlightDate BETWEEN @0 AND @1
            AND	    Mission.[Project] = @2
            AND         FlightHours = @3
            AND         System = @4
            AND         AGL_m = @5
    	AND	    Project.[Project] = Mission.[Project]
            AND         Aircraft_Billing.[ID] = Mission.[ID]
            ORDER BY    Mission.[ID]";
            data = db.Query(sql,cookieValue1,cookieValue2,cookieValue3,cookieValue4,cookieValue5,cookieValue6);
        }
        
        var columns = data.First().Columns;
        Response.AddHeader("Content-disposition", "attachment; filename=PO.xls");
        Response.ContentType = "application/octet-stream";
    }
    

    The code as it is works fine. The problem lies inside the IF statement. The code retrieves data correctly when ALL the textboxes aren't empty, as you can tell from the conditions:

    if(!cookieValue1.IsEmpty() && !cookieValue2.IsEmpty() &&.....

    But how about when only one box has values, or two or three? In that case I would have to write an IF statement for every single variation or combination of fields selected. May be the solution lies in a while statement or something alike, I'm not sure.

    Thanks in advance for your time and help.

    Sunday, January 8, 2017 12:39 AM

Answers

  • User2117486576 posted

    You could use OR in the where clause:

     sql =
            @"SELECT    Mission.Project, Mission.FlightDate, Mission.FlightHours, Mission.System, Project.AGL_m
            FROM        Project, Mission, Aircraft_Billing
    	WHERE	    FlightDate BETWEEN @0 AND @1
            AND	    (Mission.[Project] = @2 OR @2 IS NULL) 
            AND         (FlightHours = @3 OR @3 IS NULL)
            AND         (System = @4 OR @4 IS NULL)
            AND         (AGL_m = @5 OR @5 IS NULL)
    	AND	    Project.[Project] = Mission.[Project]
            AND         Aircraft_Billing.[ID] = Mission.[ID]
            ORDER BY    Mission.[ID]";
            data = db.Query(sql,cookieValue1,cookieValue2,cookieValue3,cookieValue4,cookieValue5,cookieValue6);

    If you don't wrap it in the if(..) statement this should return all rows if no values are present.  So you will need to decide if you want to execute the query if no values are selected by the user.  Also, if the values can be empty strings (not-null) you may need to also OR LEN(@2) = 0.

    You did not tell us what database you are using.  This works for Sql Server.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 8, 2017 2:14 PM

All replies

  • User2117486576 posted

    You could use OR in the where clause:

     sql =
            @"SELECT    Mission.Project, Mission.FlightDate, Mission.FlightHours, Mission.System, Project.AGL_m
            FROM        Project, Mission, Aircraft_Billing
    	WHERE	    FlightDate BETWEEN @0 AND @1
            AND	    (Mission.[Project] = @2 OR @2 IS NULL) 
            AND         (FlightHours = @3 OR @3 IS NULL)
            AND         (System = @4 OR @4 IS NULL)
            AND         (AGL_m = @5 OR @5 IS NULL)
    	AND	    Project.[Project] = Mission.[Project]
            AND         Aircraft_Billing.[ID] = Mission.[ID]
            ORDER BY    Mission.[ID]";
            data = db.Query(sql,cookieValue1,cookieValue2,cookieValue3,cookieValue4,cookieValue5,cookieValue6);

    If you don't wrap it in the if(..) statement this should return all rows if no values are present.  So you will need to decide if you want to execute the query if no values are selected by the user.  Also, if the values can be empty strings (not-null) you may need to also OR LEN(@2) = 0.

    You did not tell us what database you are using.  This works for Sql Server.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 8, 2017 2:14 PM
  • User-701766627 posted

    Richardy, this worked out very well (I am using SQL Server):

    sql =
            @"SELECT    Mission.Project, Mission.FlightDate, Mission.FlightHours, Mission.System, Project.AGL_m
            FROM        Project, Mission, Aircraft_Billing
    	WHERE	    FlightDate BETWEEN @0 AND @1
            AND	    (Mission.[Project] = @2 OR LEN(@2) = 0)
            AND         (FlightHours = @3 OR LEN(@3) = 0)
            AND         (System = @4 OR LEN(@4) = 0)
            AND         (AGL_m = @5 OR LEN(@5) = 0)
    	AND	    Project.[Project] = Mission.[Project]
            AND         Aircraft_Billing.[ID] = Mission.[ID]
            ORDER BY    Mission.[ID]";
            data = db.Query(sql,cookieValue1,cookieValue2,cookieValue3,cookieValue4,cookieValue5,cookieValue6);

    You have saved me a lot of coding and headaches.

    Many thanks.

    Sunday, January 8, 2017 9:48 PM
  • User-701766627 posted

    One more thing, what can I do so that the query SELECTs only the columns that the user has entered?

    For instance: If the user entered a value for Mission.[Project] and System, those will be the only two columns selected.

    Sunday, January 8, 2017 10:01 PM