Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

ahm1985

Programmer
Dec 6, 2012
138
EG
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");
}
}

 
Check thread183-1736916

Borislav Borissov
VFP9 SP2, SQL Server
 
You need to change a couple things
1. Your stored procedure should be
SQL:
ALTER proc [dbo].[CollectsearchData]
@StartDate datetime = '1/1/1900',
@EndDate datetime = 12/31/9999',
@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
2. In code behind

Code:
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);
if (StartDate !="")
{
cmd.Parameters["@StartDate"].Value = StartDate;
}
if(EndDate != "")
{
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");
}
}
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top