Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to make search between two dates accept null not obligatory search proplem Expand / Collapse
Author
Message
Posted Tuesday, September 2, 2014 8:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:51 AM
Points: 21, Visits: 53
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");
}
}
Notes : this post contain to c# code
but i post it because may be any one can modify stored procedure found in sql server 2005 or c# if possible that
Post #1609633
Posted Wednesday, September 3, 2014 3:05 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:10 AM
Points: 18, Visits: 162
2 Suggestion:
1. Change Datetime parameter to string(anyway you are doing so while making dynamic select query), and pass the string formatted parameter from code side.
2. Make sure your variables StartDate,EndDate,EmployeeNo,EmployeeName always get default value or while checking for null check for empty string as well.
Post #1609914
Posted Wednesday, September 3, 2014 11:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 10,215, Visits: 13,163
Please be aware that the code you have provided is open to a SQL Injection attack. Whenever building dynamic SQL you either need to sanitize the input (very hard), or use sp_executesql to use parameters. Your stored procedure should be written more like this:

ALTER PROC [dbo].[CollectsearchData]
@StartDate DATETIME,
@EndDate DATETIME,
@EmployeeID NVARCHAR(50),
@EmployeeName NVARCHAR(50)
AS
BEGIN
DECLARE
@SQLQuery AS NVARCHAR(2000),
@parameters NVARCHAR(2000) = '@StartDate DATETIME, @EndDate DATETIME, @EmployeeID NVARCHAR(50), @EmployeeName NVARCHAR(50)';


SET @SQLQuery = 'SELECT * from ViewEmployeeTest Where (1=1)'

IF (@StartDate IS NOT NULL)
SET @SQLQuery = @SQLQuery + ' And (joindate >= @StartDate)'
IF (@EndDate IS NOT NULL)
SET @SQLQuery = @SQLQuery + ' And (joindate <= @EndDate)'
IF @EmployeeID <> ''
SET @SQLQuery = @SQLQuery + 'And (EmployeeID = @EmployeeID)'
IF @EmployeeName IS NOT NULL
SET @SQLQuery = @SQLQuery +
' AND (DriverName LIKE ''% + '' + @EmployeeName + ''%'') '
PRINT @SQLQuery

EXEC sys.sp_executesql @SQLQuery, @parameters, @StartDate = @StartDate,
@EndDate = @EndDate, @EmployeeID = @EmployeeID,
@EmployeeName = @EmployeeName;
END

It sounds like the reason you aren't getting data when a date is not passed in is because you aren't getting a NULL passed into the procedure for the date parameters. Evaluate what your application is passing for the parameters and then either convert that value to a NULL in the procedure or check for that value in the procedure. Maybe something like this:

IF (NULLIF(@StartDate, '1900-01-01') IS NOT NULL)





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1610193
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse