locked
Write sql query on the basis of arithmetic operators. RRS feed

  • Question

  • User-1506965535 posted

    I have a date column in my database table. What I want is that i want to write query based on arithmetic operations,

    On that basis it should filter the result.

    Here is my HTML

    <span class="xy8">Current Exp date :</span>
                         <input id="txtExpCal" runat="server" maxlength="20" readonly="readonly" style="width: 25%;
                                background-repeat: no-repeat; background- right; border: solid 1px #ACACAC"
                                type="text" />
                            <cc1:Calendar ID="CalEntryDt1" runat="server" DatePickerMode="true" TextBoxId="txtExpCal"
                                DatePickerImagePath="../Images/icon2.gif" CultureName="en-GB">
                            </cc1:Calendar>
                        </td>
    
                        <td>
                         <asp:DropDownList ID="ddlAssignvalue" runat="server" Style="width: 20%; background-repeat: no-repeat;
                                background- right; border: solid 1px #ACACAC; font-family: Tahoma,Arial,Helvetica,Geneva,sans-serif">
                                <asp:ListItem Value="Equal to">Equal to</asp:ListItem>
                                <asp:ListItem Value="Greater than">Greater than</asp:ListItem>
                                <asp:ListItem Value="Less than">Less than</asp:ListItem>
                                </asp:DropDownList>
                        </td>

    I want to write query in cs

    if (ddlBin.SelectedValue == "0" && ddlItem.SelectedValue == "0")
            {
                BindGrid();
            }
            else
            {
                string query = "select * from WMS_BIN_STATUS_TRACK where "+
                               "location_name='" + ddlBin.SelectedValue + "' or Current_Item_code='" + ddlItem.SelectedValue +
                               "' or Current_Item_Batch='" + txtBatch.Text + "' or Current_Item_Exp_Dt ='" + here i want that query + "'";
    
                Response.Write(query);
                SqlDataAdapter da = new SqlDataAdapter(query, strConnString);
                DataTable dt = new DataTable();
                da.Fill(dt);
                GrdBinStockTracker.DataSource = dt;
                GrdBinStockTracker.DataBind();
            }

    Saturday, October 31, 2015 1:25 AM

Answers

  • User-469665101 posted

    Hi nadeem,

    First you change the dropdownlist Item value

     <asp:DropDownList ID="ddlAssignvalue" runat="server" Style="width: 20%; background-repeat: no-repeat; 
    background- right; border: solid 1px #ACACAC; font-family: Tahoma,Arial,Helvetica,Geneva,sans-serif"> <asp:ListItem Value="=">Equal to</asp:ListItem> <asp:ListItem Value=">">Greater than</asp:ListItem> <asp:ListItem Value="<">Less than</asp:ListItem> </asp:DropDownList>

    Then, you write this query like this

    string query = "select * from WMS_BIN_STATUS_TRACK where "+
                   "location_name='" + ddlBin.SelectedValue + "' or Current_Item_code='" + ddlItem.SelectedValue +
                   "' or Current_Item_Batch='" + txtBatch.Text + 
    "' or Current_Item_Exp_Dt '" + ddlAssignvalue.SelectedValue + "''" + txtExpCal.Text +"'";

    Thanks

    I hope this will helpful for you.

    Yaduveer Saini

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 2, 2015 8:02 AM

All replies

  • User-469665101 posted

    Hi nadeem,

    First you change the dropdownlist Item value

     <asp:DropDownList ID="ddlAssignvalue" runat="server" Style="width: 20%; background-repeat: no-repeat; 
    background- right; border: solid 1px #ACACAC; font-family: Tahoma,Arial,Helvetica,Geneva,sans-serif"> <asp:ListItem Value="=">Equal to</asp:ListItem> <asp:ListItem Value=">">Greater than</asp:ListItem> <asp:ListItem Value="<">Less than</asp:ListItem> </asp:DropDownList>

    Then, you write this query like this

    string query = "select * from WMS_BIN_STATUS_TRACK where "+
                   "location_name='" + ddlBin.SelectedValue + "' or Current_Item_code='" + ddlItem.SelectedValue +
                   "' or Current_Item_Batch='" + txtBatch.Text + 
    "' or Current_Item_Exp_Dt '" + ddlAssignvalue.SelectedValue + "''" + txtExpCal.Text +"'";

    Thanks

    I hope this will helpful for you.

    Yaduveer Saini

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 2, 2015 8:02 AM
  • User1644755831 posted

    Hello Nadeem157,

    string query = "select * from WMS_BIN_STATUS_TRACK where "+ "location_name='" + ddlBin.SelectedValue + "' or Current_Item_code='" + ddlItem.SelectedValue + "' or Current_Item_Batch='" + txtBatch.Text + "' or Current_Item_Exp_Dt ='" + here i want that query + "'";

    You can write queries this way is fine but there is possibilities of SQL injection.

    http://www.w3schools.com/sql/sql_injection.asp

    To Protect against it you can use SQL parameters.

    You can put the statement into the store procedure and pass the operator as parameter and use if or case statements inside the SQL Store procedure to build the SQL Statement and execute it.

    With Regards,

    Krunal Parekh

    Tuesday, November 3, 2015 9:15 PM