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

Question

0.00/5 (No votes)

See more:

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 :

SQL

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

C#

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

Posted 2-Sep-14 2:53am

ahmed_sa

Updated 2-Sep-14 3:22am

Nelek

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nathan Minier · Answer 1 · 2014-09-02T03:06:00

Your stored procedure is smart enough to tell if the dates (or employee name) are null, but your C# is not.

C#

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 ?? DBNull.Value;
cmd.Parameters["@EndDate"].Value = EndDate ?? DBNull.Value;
cmd.Parameters["@EmployeeID"].Value = EmployeeNo;
cmd.Parameters["@EmployeeName"].Value = EmployeeName ?? DBNull.Value;

or, for brevity:

C#

cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = StartDate ?? DBNull.Value;
cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = EndDate ?? DBNull.Value;
cmd.Parameters.Add("@EmployeeID", SqlDbType.NVarChar, 50).Value = EmployeeNo;
cmd.Parameters.Add("@EmployeeName", SqlDbType.NVarChar, 50).Value = EmployeeName ?? DBNull.Value;

Richard Deeming · Answer 2 · 2014-09-02T04:03:00

A perfect example of why stored procedures don't protect you from SQL Injection[^]!

Use sp_executesql[^] to call your dynamic query; that way, you can pass the parameters properly, without using string concatenation.

SQL

DECLARE @SQLQuery as nvarchar(max);
SET @SQLQuery = N'SELECT * from ViewEmployeeTest Where (1=1)';

If @StartDate Is Not Null
    SET @SQLQuery = @SQLQuery + N' And (joindate >= @StartDate)';

If @EndDate Is Not Null
    SET @SQLQuery = @SQLQuery + N' And (joindate <= @EndDate)';

If @EmployeeID Is Not Null And @EmployeeID != ''
    SET @SQLQuery = @SQLQuery + N' And (EmployeeID = @EmployeeID)';

If @EmployeeName Is Not Null And @EmployeeName != ''
    SET @SQLQuery = @SQLQuery + N' And (DriverName Like ''%'' + @EmployeeName + ''%'')';

Print @SQLQuery;

Exec sp_executesql @SQLQuery,
    N'@StartDate datetime, @EndDate datetime, @EmployeeID nvarchar(50), @EmployeeName nvarchar(50)',
    @StartDate, @EndDate, @EmployeeID, @EmployeeName;

Member 11056766 · Answer 3 · 2014-09-03T08:56:00

Each of the parameters below is checked for NULL and if so returns true, thereby effectively bypassing the filter. If the parameter is NOT NULL, the parameter is used as a filter.

SQL

ALTER proc [dbo].[CollectsearchData]
    @StartDate datetime, 
    @EndDate datetime,
    @EmployeeID  NVARCHAR(50),
    @EmployeeName  nvarchar(50)
AS
BEGIN
    SELECT * from ViewEmployeeTest 
    WHERE
        (@StartDate IS NULL OR joindate >= @StartDate)
        AND
        (@EndDate IS NULL OR joindate <= @EndDate)
        AND
        (@EmployeeID IS NULL OR EmployeeID = @EmployeID)
        AND
        (@EmployeeName IS NULL OR DriverName LIKE '%' + @EmployeeName + '%'
END

The value being passed into your C# function guarantees that you will never have a NULL as it is a DateTime rather than a nullable DateTime (represented by DateTime?). Correction to function definition is below:

C#

public DataTable SearchDataA(string ConnectionString,string EmployeeNo,string EmployeeName, DateTime? StartDate, DateTime? EndDate)

All of the parameters need to be converted to DBNull.Value, if they are C# null.

Solution #1 is correct for the string parameters:

C#

cmd.Parameters.Add("@EmployeeID", SqlDbType.NVarChar, 50).Value = EmployeeNo ?? DBNull.Value;
cmd.Parameters.Add("@EmployeeName", SqlDbType.NVarChar, 50).Value = EmployeeName ?? DBNull.Value;

Unfortunately, DateTime to DBNull.Value conversions are not compatible and must done with an if statement.

C#

cmd.Parameters.Add("@StartDate", SqlDbType.DateTime);
cmd.Parameters.Add("@EndDate", SqlDbType.DateTime);
if(StartDate.HasValue)
{
    cmd.Parameters["@StartDate"].Value = StartDate.Value;
}
else
{
    cmd.Parameters["@StartDate"].Value = DBNull.Value;
}

if(EndDate.HasValue)
{
    cmd.Parameters["@EndDate"].Value = EndDate.Value;
}
else
{
    cmd.Parameters["@EndDate"].Value = DBNull.Value;
}

Hope this helps!

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

3 solutions

Solution 1

Solution 2

Solution 5

Add your solution here

Preview 0