How to make search between two dates accept null not obligat

[ahmedsa]

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 :

ALTER 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

Function 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");
}
}

[ahmedsa]

Can any one give me answer to my question if possible

[Fenrir()]

So you're not using DateTimePickers for the dates? the problem is that when a textbox has no value the value is actually a blank string " " and not Null. So you need to do a check. IF (string.IsNullOrEmpty(StartDate)){} and then assign it a default value like 1900-01-01 00:00:00.000 and then do the same for end date you can you DateTime.Now for that

[ChrisHunter]

In you stored procedure, define the datetime variables to default to null values like this:

@StartDate datetime = null,
@EndDate datetime = null

This will let you pass null values into your stored proc.

At the moment you convert your datetime variables to a varchar which means they're no longer a datetime. It might not be the best way to do it but try converting the joindate to a varchar too.

This way they are both the same datatype.