September 2, 2014 at 8:07 am
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
September 3, 2014 at 3:05 am
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.
September 3, 2014 at 11:34 am
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
' 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
Consultant - Straight Path Solutions
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply