Textbox Value does not fall within the expected range

Question

I'm trying to do a search page with multiple parameters using web forms and oracle db with asp.net.

DisplayRecords.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DisplayRecords-absns.aspx.cs" Inherits="WebApplication1.DisplayRecords_absns" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>

                <h1>Rechercher de la base de donnée</h1>
                <h2></h2>
                Demande du : <asp:TextBox ID="Txtddate" runat="server" required="required" placeholder="Date/Month/Year"></asp:TextBox><asp:Calendar ID="Calendar1" runat="server"></asp:Calendar>
                au :<asp:TextBox ID="Txtfdate" runat="server" required="required" placeholder="Date/Month/Year"></asp:TextBox><asp:Calendar ID="Calendar2" runat="server"></asp:Calendar>
                Matricule :<asp:TextBox ID="Txtmatri" runat="server"></asp:TextBox>
                Nom et prenom :<asp:DropDownList ID="DDnompre" runat="server" ></asp:DropDownList>
                Motif :<asp:DropDownList ID="DDmotif" runat="server"></asp:DropDownList>
                <asp:Button ID="ButSearch" runat="server" Text="Rechercher" OnClick="ButSearch_Click" />
                <hr />
                <asp:GridView ID="GV_AbsenceSaisie" runat="server" ShowHeaderWhenEmpty="True" EmptyDataText="Aucun enregistrement!"></asp:GridView>

        </div>
    </form>
</body>
</html>

DisplayRecords.cs: (Btn)

protected void ButSearch_Click(object sender, EventArgs e)
        {
            OracleConnection conn = new OracleConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["Stelia_Context"].ConnectionString;
            //OracleCommand cursCmd = new OracleCommand("CURSPKG.MV_ABS_SAISIE_LAST", conn);
            String var = "SELECT to_char(HOPHJOUP.DAT,'ww') AS SEM,HOPHJOUP.DAT as dat,to_char(HOPHJOUP.DAT,'DAY') as jou,HOPEMPL.HORSECT as serv,HOPHJOUP.MATRI as matri,HOPEMPL.NOMPRE as nompre,HOPEMPL.SEIDEP as DEP,HOPEMPL.SEILIGNE as LIGNE,HOPHABS.MOTIF AS MOTIF FROM HOPHABS, HOPHJOUP, HOPEMPL WHERE HOPHJOUP.DAT between :DDATE and :FDATE AND HOPHABS.MOTIF like :p_motif AND HOPEMPL.matri like :p_matri and HOPEMPL.MATRI = HOPHJOUP.MATRI and HOPHABS.MATRI = HOPEMPL.MATRI and HOPHJOUP.DAT = HOPHABS.DAT ORDER BY 2; ";
            OracleCommand cursCmd = new OracleCommand(var, conn);


            if (Txtddate.Text.Trim() != "")
            {
                cursCmd.Parameters.Add("DDATE",Txtddate);
            }

            if (Txtfdate.Text.Trim() != "")
            {
                cursCmd.Parameters.Add("FDATE", Txtfdate);
            }

            if (Txtmatri.Text.Trim() != "")
            {
                cursCmd.Parameters.Add("p_matri", Txtmatri);
            }

            /*if (DDnompre.SelectedValue.Trim() != "")
            {
                cursCmd.Parameters.Add("DDATE", DDnompre);
            }*/

            if (DDmotif.SelectedValue.Trim() != "")
            {
                cursCmd.Parameters.Add("p_motif", DDmotif.SelectedValue);
            }

            conn.Open();
            OracleDataReader reader = cursCmd.ExecuteReader();

            GV_AbsenceSaisie.DataSource = reader;
            GV_AbsenceSaisie.DataBind();
        }

Error: 'Txtddate Value does not fall within the expected range'

In my database DDATE is a DATE format, i dont know if i have to change something specific, could you help please ?

SELoug SELoug · Accepted Answer · 2019-09-10T19:35:34

I solved the problem: i needed to change my date parameters in my sql query to:

WHERE HOPHJOUP.DAT between TO_DATE (:DDATE, 'dd/mm/yyyy') AND TO_DATE(:FDATE, 'dd/mm/yyyy')

and add .text for each textbox:

cursCmd.Parameters.Add("DDATE", Txtddate.Text);

Textbox Value does not fall within the expected range

1 Answers