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)asBeginDeclare @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 @sqlQueryExec (@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";//workcmd.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");}}