CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3

Thread: How to make search between two dates accept null not obligatatory search

  1. #1
    Join Date
    Jan 2014
    Posts
    29

    How to make search between two dates accept null not obligatatory search

    Hi guys when i search record between two dates it works ok success but you must enter date from and dateto first to to make search

    i will show what i need from this example

    I need to search dynamic by 4 textbox

    1-datefrom

    2-dateto

    3-EmployeeNo

    4-EmployeeName

    but search i need must be dynamic meaning

    if i enter employee no only give me employee no found in database

    if i enter employee name give me employees found with this name using like

    if i enter all 4 text box null and enter button search get all data

    but i have proplem in this query when i need to search by click search button

    i must write date from and date to firstly then write employee no or employee name if i need to search

    so that i need to search by employee no alone or employee name alone without using date from and date to

    And if i search without using datefrom and dateto it give me message error 'string wasnot recognized as valid datetime"

    my stored procedure and code as following :
    Collapse | Copy CodeALTER proc [dbo].[CollectsearchData]
    @StartDate datetime,
    @EndDate datetime,
    @EmployeeID NVARCHAR(50),
    @EmployeeName nvarchar(50)
    as
    Begin
    Declare @SQLQuery as nvarchar(2000)
    SET @SQLQuery ='SELECT * from ViewEmployeeTest Where (1=1)'

    If (@StartDate is not NULL)
    Set @SQLQuery = @SQLQuery + ' And (joindate >= '''+ Cast(@StartDate as varchar(100))+''')'
    If (@EndDate is not NULL)
    Set @SQLQuery = @SQLQuery + ' And (joindate <= '''+ Cast(@EndDate as varchar(100))+''')'
    If @EmployeeID <>''
    Set @SQLQuery = @SQLQuery + 'And (EmployeeID = '+ @EmployeeID+') '
    If @EmployeeName Is Not Null
    Set @SQLQuery = @SQLQuery + ' AND (DriverName LIKE ''%'+@EmployeeName+'%'') '
    Print @sqlQuery
    Exec (@SQLQuery)
    End Collapse | Copy CodeFunction using
    public DataTable SearchDataA(string ConnectionString,string EmployeeNo,string EmployeeName, DateTime StartDate, DateTime EndDate)
    {
    SqlConnection con = new SqlConnection(ConnectionString);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "CollectsearchData";//work
    cmd.Parameters.Add("@StartDate", SqlDbType.DateTime);
    cmd.Parameters.Add("@EndDate", SqlDbType.DateTime);
    cmd.Parameters.Add("@EmployeeID", SqlDbType.NVarChar, 50);
    cmd.Parameters.Add("@EmployeeName", SqlDbType.NVarChar, 50);
    cmd.Parameters["@StartDate"].Value = StartDate;
    cmd.Parameters["@EndDate"].Value = EndDate;
    cmd.Parameters["@EmployeeID"].Value = EmployeeNo;
    cmd.Parameters["@EmployeeName"].Value = EmployeeName;
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    DataSet ds = new DataSet();
    da.Fill(ds);
    DataTable dt = ds.Tables[0];
    return dt;
    }
    interface button search
    try
    {
    CultureInfo ukCulture = new CultureInfo("en-GB");
    FleetManagment.Fleet fleet = new FleetManagment.Fleet();
    DataTable Table = fleet.SearchDataA("Data Source=" + value1 + ";Initial Catalog=" + value2 + ";User ID=" + value3 + ";Password=" + value4 + "",textBox3.Text,textBox4.Text, DateTime.Parse(textBox1.Text, ukCulture.DateTimeFormat), Convert.ToDateTime(textBox2.Text, ukCulture.DateTimeFormat));
    dataGridView1.DataSource = Table;
    dataGridView1.Refresh();
    }
    catch (Exception ex)
    {
    MessageBox.Show(ex + "error");
    }
    }

  2. #2
    Join Date
    Aug 2014
    Posts
    6

    Re: How to make search between two dates accept null not obligatatory search

    try that :

    if(!String.IsNullOrEmpty(StartDate))
    {
    cmd.Parameters["@StartDate"].Value = StartDate;
    }

    if(!String.IsNullOrEmpty(EndDate))
    {
    cmd.Parameters["@EndDate"].Value = EndDate;
    }

  3. #3
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,356

    Re: How to make search between two dates accept null not obligatatory search

    Consider using LINQ. With LINQ you can build a base query and then filter it further with additional .Where clauses.

    The following is a query that returns a list of projects that are filtered by user first and last names.

    Code:
    using (var context = new MyEntities())
                {
                    var query = (from upr in context.UserToProjectRole
                        join u in context.users on upr.UserId equals u.UserId
                        select new
                        {
                            ProjectId = upr.ProjectRole.ProjectId,
                            NameFirst = u.FirstName,
                            NameLast = u.LastName,
                        });
    
                    // first name valid
                    if (!String.IsNullOrEmpty(nameFirst) && String.IsNullOrEmpty(nameLast))
                    {
                        query = query.Where( p => p.NameFirst.Contains(nameFirst) );
                    }
    
                    // last name valid
                    if (String.IsNullOrEmpty(nameFirst) && !String.IsNullOrEmpty(nameLast))
                    {
                        query = query.Where( p => p.NameLast.Contains(nameLast));
                    }
    
                    // first and last name valid
                    if (!String.IsNullOrEmpty(nameFirst) && !String.IsNullOrEmpty(nameLast))
                    {
                        query = query.Where(p => p.NameFirst.Contains(nameFirst) && p.NameLast.Contains(nameLast));
                    }
    
                    return query.ToList();
                }

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)