Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 1873
Home > Forums > C# >

How to make search between two dates accept null not obligatory search proplem

Posted by Ahmedsa under C# on 9/3/2014 | Points: 10 | Views : 1230 | Status : [Member] | Replies : 2
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
See more: C#
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");
}
}



Reply | Reply with Attachment
Alert Moderator 

Responses

Posted by: Naveenhcl on: 9/3/2014 [Member] Starter | Points: 25

Up
0
Down
Hi,

If you want to search the result based on entry then refer below sample. 

create procedure search_result

(

   @from_date datetime,

   @to_date datetime,

   @Emp_Id int,

   @Emp_Name varchar(500)

)

as

begin

    declare @result1 table

    ( col1 datatype, col2 datatype....)

    

    declare @result2 table

    ( col1 datatype, col2 datatype....)



    insert into @result1

    select * from tablename



    if @from_date is not null and @to_date is not null

    begin

       insert into @result2

       select * from @result1

       where datefield between @from_date and @to_date

     

       delete from @result1



       insert into @result1

       select * from @result2



       delete from @result2

    end

    if @Emp_id is not null

    begin

       insert into @result2

       select * from @result1

       where Emp_Id = @Emp_Id

     

       delete from @result1



       insert into @result1

       select * from @result2  



       delete from @result2     

    end

    if @Emp_Name is not null

    begin

       insert into @result2

       select * from @result1

       where Emp_Name like '%'+@Emp_Name+'%'

     

       delete from @result1



       insert into @result1

       select * from @result2



       delete from @result2

    end

    

    select * from @result1

end


Try something like above to achieve your goal...

Ahmedsa, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 9/4/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down 
CREATE proc [dbo].[CollectsearchData] 
@StartDate datetime, 
@EndDate datetime, 
@EmployeeID NVARCHAR(50), 
@EmployeeName nvarchar(50) 
as 
Begin
	SELECT * from ViewEmployeeTest 
	Where (1=1)
	And (joindate >= @StartDate OR NULLIF(@StartDate , '') IS NULL)
	And (joindate <= @EndDate OR NULLIF(@EndDate , '') is NULL) 
	And (EmployeeID = @EmployeeID OR NULLIF(@EmployeeID, '') IS NULL)
	AND (DriverName LIKE '%'+@EmployeeName+'%' OR NULLIF(@EmployeeName, '') Is Null)
END
GO

-Testing Procedure by passing NULL for all params and empty string for any of the parameter
DECLARE @Date datetime = getdate()
EXEC CollectsearchData @Date , NULL, NULL, NULL
GO
DECLARE @Date datetime = getdate()
EXEC CollectsearchData NULL, @Date , NULL, NULL
GO
DECLARE @Date datetime = getdate()
EXEC CollectsearchData @date, @Date , NULL, NULL
GO
DECLARE @Date datetime = getdate()
EXEC CollectsearchData @date, '' , NULL, NULL
GO
DECLARE @Date datetime = getdate()
EXEC CollectsearchData '', @date , NULL, NULL
GO
DECLARE @Date datetime = getdate()
EXEC CollectsearchData '', '' , '', ''


Front-end code should pass empty string if any of the textbox is empty...

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Ahmedsa, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response

Latest Posts