Member Avatar for Lalithayadav 
CREATE procedure [dbo].[date_sp_new]
(
@fromdate as varchar(50),
@todate as varchar(50)
)
AS
declare @date1 as datetime
declare @date2 as datetime
select @date1 =  convert(varchar(50), @fromdate,120)
select @date2 =   convert(varchar(50), @todate,120)
begin
if (@date1 = '' or @date2 = '')

select * from mkt_contact

else if(@date1 != ''and @date2 = '')

select * from mkt_contact where CONVERT(CHAR(10),contact_addeddate_dt,120) >= @date1

else if(@date1 = '' and  @date2 != '')

select * from mkt_contact where CONVERT(CHAR(10),contact_addeddate_dt,120) <= @date2 

else if(@date1 != ''and @date2 != '')

select * from mkt_contact where CONVERT(CHAR(10),contact_addeddate_dt,120) between @date1 and @date2

End

and
the aspx page code is

//try
    //    {

    //          SqlDataSource sds_confirm_date = new SqlDataSource();
    //           sds_confirm_date.ConnectionString = ConfigurationManager.ConnectionStrings["BTI_NEWConnectionString"].ToString();
    //           sds_confirm_date.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
    //           sds_confirm_date.SelectCommand = "[date_sp_new]";
    //            //sds_confirm_date.SelectParameters.Add(new Parameter("fromdate", System.TypeCode.String, TextBox_addedDate_from.Text));
    //            //sds_confirm_date.SelectParameters.Add(new Parameter("todate", System.TypeCode.String, TextBox_addedDate_to.Text));

    //            //String dtfromdate = null;
    //            //if (TextBox_addedDate_from.Text != string.Empty)
    //            //    dtfromdate = TextBox_addedDate_from.Text;

    //            //String dttodate = null;
    //            //if (TextBox_addedDate_to.Text != string.Empty)
    //            //    dttodate = TextBox_addedDate_to.Text;
    //            //if (TextBox_addedDate_from.Text == "")
    //            //{
    //            //    sds_confirm_date.SelectParameters.Add("fromdate", System.TypeCode.DBNull, null);
    //            //}
    //            //else
    //            //{
    //            //    sds_confirm_date.SelectParameters.Add(new Parameter("fromdate", System.TypeCode.String, TextBox_addedDate_from.Text));
    //            //}
    //            //if (TextBox_addedDate_to.Text == "")
    //            //{
    //            //    sds_confirm_date.SelectParameters.Add("todate", System.TypeCode.DBNull, null);
    //            //}
    //            //else
    //            //{
    //            //    sds_confirm_date.SelectParameters.Add(new Parameter("todate", System.TypeCode.String, TextBox_addedDate_to.Text));
    //            //}
    //            //


    //            sds_confirm_date.SelectParameters.Add(new Parameter("fromdate", System.TypeCode.String, TextBox_addedDate_from.Text));
    //            sds_confirm_date.SelectParameters.Add(new Parameter("todate", System.TypeCode.String, TextBox_addedDate_to.Text));
    //            GridView1.DataSource = sds_confirm_date;
    //            GridView1.DataBind();
    //        }


    //    catch (Exception ex)
    //    {
    //        Label1.Text=ex.Message;
    //
}

but not getting the result for when blank textbox values are pass to store prcedure

Edited by mike_2000_17 because: Fixed formatting
  • 3 Contributors
  • 2 Replies
  • 100 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by ashishkumar008

Dani AI

Generated

Quick diagnosis: the stored procedure should accept proper datetime parameters (or NULL) and the caller should pass DBNull.Value when a textbox is empty. Converting inputs back and forth between varchar and datetime, and comparing a datetime variable to an empty string, breaks the logic. Also avoid applying CONVERT/Cast to the table column in the WHERE clause — that makes the predicate non‑SARGable and can stop index use. As noted, post a minimal, readable repro and remove commented clutter; is right that trimmed examples help responders.

A safer stored-proc pattern is to declare datetime parameters that default to NULL and write the WHERE as optional range checks:

CREATE PROCEDURE dbo.date_sp_new
  @FromDate DATETIME = NULL,
  @ToDate   DATETIME = NULL
AS
BEGIN
  SET NOCOUNT ON;

  SELECT *
  FROM mkt_contact
  WHERE (@FromDate IS NULL OR contact_addeddate_dt >= @FromDate)
    AND (@ToDate   IS NULL OR contact_addeddate_dt <  DATEADD(day, 1, @ToDate));
END;

In the caller, parse the textboxes into nullable DateTime values and pass DBNull.Value when parsing fails:

DateTime? from = null, to = null;
DateTime tmp;

if (DateTime.TryParse(TextBox_addedDate_from.Text, out tmp)) from = tmp;
if (DateTime.TryParse(TextBox_addedDate_to.Text,   out tmp)) to   = tmp;

using (var conn = new SqlConnection(connString))
using (var cmd  = new SqlCommand("dbo.date_sp_new", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = (object)from ?? DBNull.Value;
    cmd.Parameters.Add("@ToDate",   SqlDbType.DateTime).Value = (object)to   ?? DBNull.Value;
    // fill GridView...
}

Troubleshooting tips: test the stored procedure directly in SSMS with NULL and concrete dates; log or inspect the exact parameter values received; prefer passing typed DateTime parameters rather than strings to avoid culture/format issues (if sending strings, use ISO 8601). See the SqlParameter guidance and CAST/CONVERT details for formats and behaviors: SqlParameter (System.Data.SqlClient) and CAST and CONVERT (Transact-SQL).

Recommended Answers

All 2 Replies

Member Avatar for Momerath

Since you didn't use code tags, that's really hard to read and why would you include commented out code?

Member Avatar for ashishkumar008

please don't use unnecessary comments.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.