在两个日期之间搜索查询不接受空值到日期时间 [英] Searching between two dates query Not accept null value to date time

问题描述

你好。当我在两个日期之间搜索记录时，它确实成功但你必须输入日期从第一天开始进行搜索



我将从这个例子中显示我需要的东西



我需要动态搜索4个文本框



1-datefrom



2-dateto



3-EmployeeNo



4-EmployeeName



但我需要的搜索必须是动态的意思



如果我输入员工不仅仅给我员工找不到在数据库中



如果我输入员工姓名给我发现使用此名称的员工如



如果我输入所有4个文本框null并输入按钮搜索获取所有数据



但是当我需要通过点击搜索按钮搜索时，我在此查询中有问题



i必须首先写日期和日期然后写员工号或员工姓名如果我需要搜索



这样我需要o员工不单独搜索或员工姓名，而不使用日期和日期



如果我不使用datefrom和dateto搜索它会给我消息错误'string wasnot被认为是有效的日期时间



我的存储过程和代码如下：

  ALTER   proc  [dbo]。[CollectsearchData] 
  @ StartDate   datetime ，
  @ EndDate   datetime ，
  @ EmployeeID   NVARCHAR （ 50 ），
  @ EmployeeName   nvarchar （ 50 ）
  as  
 开始 
 声明  @ SQLQuery   as   nvarchar （ 2000 ）
  SET   @ SQLQuery  = '  SELECT * from ViewEmployeeTest Where（1 = 1）' 
 
 如果（ @ StartDate  是 不  NULL ）
 设置  @ SQLQuery  =  @ SQLQuery  + '  和（joindate> =''' + Cast（ @ StartDate   as   varchar （ 100 ））+ ' '''' 
 如果（ @ EndDate    不  NULL ）
 设置  @ SQLQuery  =  @ SQLQuery  + ' 和（joindate< =''' + Cast（ @ EndDate   as   varchar （ 100 ））+ ' < span class =code-string>'''' 
 如果  @ EmployeeID <> ' ' 
 设置  @ SQLQuery  =  @ SQLQuery  + ' 和（EmployeeID =' + @ EmployeeID + ' ）' 
 < span class =code-keyword>如果  @ EmployeeName    不  Null  
 设置  @ SQLQuery  =  @ SQLQuery  + '  AND（DriverName LIKE''％' + @ EmployeeName + ' ％'''''/ span> 
 打印  @sqlQuery  
  Exec （ @ SQLQuery ）
  End  

使用

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

界面按钮搜索

 尝试 
 {
 
 CultureInfo ukCulture =  new  CultureInfo（  en-GB）; 
 FleetManagment.Fleet fleet =  new  FleetManagment.Fleet（）; 
 DataTable Table = fleet.SearchDataA（  Data Source = + value1 +  ;初始目录= + value2 +  < span class =code-string>;用户ID = + value3 +  ;密码=  + value4 +  ，textBox3.Text，textBox4.Text，DateTime.Parse（ textBox1.Text，ukCulture.DateTimeFormat），Convert.ToDateTime（textBox2.Text，ukCulture.DateTimeFormat））; 
 dataGridView1.DataSource = Table; 
 dataGridView1.Refresh（）; 
} 
  catch （例外情况）
 {
 MessageBox.Show（ex +   error）; 
} 

解决方案

如果其中一个日期为空，您需要做的就是选择一个值。例如，你可以做

  COALESCE （ @ EndDate ，GetDate（））

如果@EndDate为空，将使用今天的日期。

将你的存储过程改为这样的东西，它可能有效

  ALTER   proc  [dbo]。[CollectsearchData] 
  @ StartDate   datetime ，
  @ EndDate   datetime ，
  @ EmployeeID   NVARCHAR （ 50 ） ，
  @ EmployeeName   nvarchar （ 50 ）
  as  
 开始 
 声明  @ SQLQuery   as   nvarchar （ 2000 ）
  SET   @ SQLQuery  = '  SELECT * from ViewEmployeeTest Where（1 = 1）' 
  Set   @ SQLQuery  =  @ SQLQuery  + ' 和（' + @ EmployeeID + '  IS NULL OR EmployeeID =' + @ EmployeeID + ' ）' 
 设置  @SQLQuery  =  @ SQLQuery  + ' 和（' + @ EmployeeName + '  IS NULL或DriverName LIKE''％' + @ EmployeeName + ' ％'''' 
 设置  @ SQLQuery  =  @ SQLQuery  + ' 和（' + @ StartDate + '  IS NULL或joindate> =' + @ StartDate + ' ）' 
 设置  @ SQLQuery  =  @ SQLQuery  + ' 和（' + @ EndDate + '  IS NULL或joindate< =' + @ EndDate + ' ）' 
 打印  @sqlQuery  
  Exec （ @ SQLQuery  ）
 结束 

; - ）

当向数据库发送参数时，确保它们是 DBNull.Value 当它们是 NULL 。否则参数不会发送到数据库。

或

将您的Sp parms更改为：

 < span class =code-keyword> ALTER   proc  [dbo]。[CollectsearchData] 
  @StartDate   datetime  =  null ，
  @EndDate   datetime  =  null ，
  @ EmployeeID   NVARCHAR （ 50 ），
  @ EmployeeName   nvarchar （ 50 ）

在这种情况下，您不必将日期参数发送到sp

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

解决方案

All you need to do is choose a value if one of the dates is null. For example you can do

COALESCE(@EndDate, GetDate())

which will use today's date if @EndDate is null.

change ur stored procedure to something like this,it may work

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)' 
Set @SQLQuery = @SQLQuery + 'And ('+ @EmployeeID+'IS NULL OR  EmployeeID = '+ @EmployeeID+') '
Set @SQLQuery = @SQLQuery + 'And ('+ @EmployeeName+'IS NULL OR  DriverName LIKE ''%'+ @EmployeeName+'%'') '
Set @SQLQuery = @SQLQuery + 'And ('+ @StartDate+'IS NULL OR  joindate >= '+ @StartDate+') '
Set @SQLQuery = @SQLQuery + 'And ('+ @EndDate+'IS NULL OR  joindate <= '+ @EndDate+') '
Print @sqlQuery
Exec (@SQLQuery) 
End

;-)

When sending parameters to a database make sure they are DBNull.Value when they are NULL. Otherwise the parameter is not send to the database.
OR
Change your Sp parms to:

ALTER proc [dbo].[CollectsearchData]
@StartDate datetime = null, 
@EndDate datetime = null,
@EmployeeID NVARCHAR(50),
@EmployeeName nvarchar(50)

In that case you do not have to send the date parameters to the sp

这篇关于在两个日期之间搜索查询不接受空值到日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！