Question

I m 存在按日田搜索数据的问题。

我有一个储存的搜寻程序:

ALTER PROCEDURE [dbo].[spSearchTraining]
(
  @CourseName VARCHAR(50)= null,
  @TrainingProvider VARCHAR(50)= null,
  @TrainingCost VARCHAR(50) = null,
  @StartDate Varchar(50) = null,
  @EndDate Varchar(50)= null,
  @RowCount int output

)
AS
 BEGIN

  SELECT *  FROM vwTrainingDetails 
  WHERE ( CourseName Like  %  + @CourseName + %  or @CourseName is null)
  AND ( TrainingProvider Like  %  + @TrainingProvider +  %  or @TrainingProvider is null)
  AND ( Cost Like  %  +  @TrainingCost + %   or @TrainingCost is null)
  AND ([Start Date] >=  @StartDate or @StartDate is null)
  AND ([Start Date] <=  @EndDate  or @EndDate is null)


  select @RowCount=@@ROWCOUNT

END

我的参数如下:

//Create and add a parameter to Parameters collection for the stored procedure.
        MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@CourseName", SqlDbType.VarChar, 40));
        MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@TrainingProvider", SqlDbType.VarChar, 40));
        MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@TrainingCost", SqlDbType.VarChar, 40));
        MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.VarChar, 40));
        MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.VarChar, 40));


        //string mysdate = (StartTextBox.Text).ToString;
        //Assign the search value to the parameter.
        MyDataAdapter.SelectCommand.Parameters["@CourseName"].Value = (CourseTextBox.Text).Trim();
        MyDataAdapter.SelectCommand.Parameters["@TrainingProvider"].Value = (ProviderTextBox.Text).Trim();
        MyDataAdapter.SelectCommand.Parameters["@TrainingCost"].Value = (CostTextBox.Text).Trim();
        MyDataAdapter.SelectCommand.Parameters["@StartDate"].Value = (StartTextBox.Text);
        MyDataAdapter.SelectCommand.Parameters["@EndDate"].Value =(EndTextBox.Text);

Problem I m having is that when I run as is the stored procedure does not return anything. If I comment out :

MyDataAdapter.SelectCommand.Parameters["@StartDate"].Value = (StartTextBox.Text);
MyDataAdapter.SelectCommand.Parameters["@EndDate"].Value =(EndTextBox.Text);

然后它运作,但无法使用日期进行搜索。

当我从服务器上执行所储存的程序并在包括日期在内的参数上通过时,它会进行罚款。

谁能帮助我,看一看我做的错!

成就

Answer 1

如果你使用2008年卡片,你可以利用CAST的功能来转换“StartDate”和“EndDate”的参数,这些参数的类型为:

AND ([Start Date] >=  CAST(@StartDate AS DATE) or @StartDate is null)
AND ([Start Date] <=  CAST(@EndDate  AS DATE) or @EndDate is null)

Answer 2

您的《特殊标准》中的使用情况如下:

SELECT *  
FROM vwTrainingDetails    
WHERE ( CourseName Like  %  + ISNULL(@CourseName,CourseName) + % )
AND ( TrainingProvider Like  %  + ISNULL(@TrainingProvider, TrainingProvider) +  % )
AND ( Cost Like  %  +  ISNULL(@TrainingCost,Cost) + % )
AND ([Start Date] >=  ISNULL( @StartDate, [Start Date]))
AND ([Start Date] <=  ISNULL(@EndDate, [Start Date]))

Answer 3

修改:

@StartDate Varchar(50) = null,
@EndDate Varchar(50)= null,

为此:

@StartDate DATE = null,
@EndDate DATE = null,

然后改变:

MyDataAdapter.SelectCommand.Parameters.Add(
    new SqlParameter("@StartDate", SqlDbType.Date));
MyDataAdapter.SelectCommand.Parameters.Add(
    new SqlParameter("@EndDate", SqlDbType.Date));

为此:

// I don t know the exact format of your date, 
// fill-in this accordingly from your Substring d textboxes:
int startYear = int.Parse(...); 
int startMonth = int.Parse(...); 
int startDay = int.Parse(...); 

int endYear = int.Parse(...); 
int endMonth = int.Parse(...); 
int endDay = int.Parse(...); 



DateTime startDate = new DateTime(startYear,startMonth,startDay);
DateTime endDate = new DateTime(endYear,endMonth,endDay);

MyDataAdapter.SelectCommand.Parameters["@StartDate"].Value = startDate;
MyDataAdapter.SelectCommand.Parameters["@EndDate"].Value = endDate;

You can also shorten your parameters 为此:

MyDataAdapter.SelectCommand.Parameters.AddWithValue("@CourseName",
    CourseTextBox.Text.Trim());
MyDataAdapter.SelectCommand.Parameters.AddWithValue("@TrainingProvider", 
    ProviderTextBox.Text.Trim());        
MyDataAdapter.SelectCommand.Parameters.AddWithValue("@TrainingCost", 
    CostTextBox.Text.Trim());

MyDataAdapter.SelectCommand.Parameters.AddWithValue("@StartDate", startDate);
MyDataAdapter.SelectCommand.Parameters.AddWithValue("@EndDate", endDate);

另有一件事,修改了@TrainingCost VARCHAR(50) = 无,至@TrainingCost NUMERIC(18,6) = 无。



之后,情况发生变化:

MyDataAdapter.SelectCommand.Parameters.Add(
   new SqlParameter("@TrainingCost", SqlDbType.Varchar, 40));


:

MyDataAdapter.SelectCommand.Parameters.Add(
   new SqlParameter("@TrainingCost", SqlDbType.Decimal));


之后,情况也发生变化:

MyDataAdapter.SelectCommand.Parameters["@TrainingCost"].Value = 
     decimal.Parse(CostTextBox.Text.Trim());


或者:

MyDataAdapter.SelectCommand.Parameters.AddWithValue("@TrainingCost", 
    decimal.Parse(CostTextBox.Text.Trim()));


因此,改变你储存的原样,这种视而不见,费用领域是金钱;钱不应作为扼杀手段处理。 为此:

AND ( Cost Like  %  +  @TrainingCost + %   or @TrainingCost is null)


为此:

AND (Cost = @TrainingCost or @TrainingCost is null)


同样,如果你需要取消,则妥善处理:

MyDataAdapter.SelectCommand.Parameters["@TrainingCost"].Value = 
     CostTextBox.Text.Trim().Length == 0 ? 
       (decimal?) null :
       (decimal?) decimal.Parse(CostTextBox.Text.Trim());

友情链接