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





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)
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) 

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();
DataTable dt = ds.Tables[0];
return dt;

interface button search


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;
catch (Exception ex)
MessageBox.Show(ex + "error");
2 Years
Discussion Span
Last Post by ChrisHunter

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

Edited by Fenrir()


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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.