为日期参数传递空值? [英] pass null value for date parameter?

问题描述

你好

从sql服务器中的存储过程中检索数据到win窗体应用程序datagridview中，我几乎没有问题.

这是过程的代码，在sql server中可以正常工作

hello

I have little problem with retrieving data from stored procedure in sql server to my win form application datagridview

this is the code for procedure and in sql server it works

USE [db]
GO
create proc test_proc

@PageSize int output, 
@PageNum int output,
@column20 varchar(MAX) output,
@datefrom date = null output ,
@dateto date = null output


as
set nocount on

Declare @RowStart int 
Declare @RowEnd int 

if @PageNum > 0 
Begin 

SET @PageNum = @PageNum -1 

SET @RowStart = @PageSize * @PageNum + 1; 
SET @RowEnd = @RowStart + @PageSize - 1 ; 

With Cust AS 

(SELECT *, 
       ROW_NUMBER() OVER (order by ID) as RowNumber 
       FROM database
WHERE ((Column20 LIKE +@column20+ '%') OR (@column20 is null))
AND ((Date >= @datefrom) OR (@datefrom is null))
AND ((Date <= @dateto) OR (@dateto is null))

)

select * 
from Cust 
Where RowNumber >= @RowStart and RowNumber <= @RowEnd end 

在winform应用程序中，我有3个复选框，分别称为c1，c2，c3和1个textbox(column20)，还有两个简短的datetimepickers(date1和date2)，只是日期.如果选中每个复选框，则应使用相应的文本框或日期时间选择器进行过滤，如在过程中所见.

这是用于将数据检索到datagridview的代码

and in winform application I have 3 checkoboxes called c1, c2, c3, and 1 textbox(column20) and two datetimepickers (date1 and date2) in short format, just dates. each checkbox if checked then that corresponding textbox or datetime picker should be used for filtering as you can see in procedure.

this is the code for retrieving data to datagridview

dsData.Clear();

            SqlCommand com = new SqlCommand("test_proc", cs);
            com.CommandType = CommandType.StoredProcedure;

            SqlParameter par1 = new SqlParameter("@column20", SqlDbType.VarChar, -1);
            SqlParameter par2 = new SqlParameter("@datefrom", SqlDbType.Date);
            SqlParameter par3 = new SqlParameter("@dateto", SqlDbType.Date);

            SqlParameter par4= new SqlParameter("@PageSize", SqlDbType.Int);
            SqlParameter par5 = new SqlParameter("@PageNum", SqlDbType.Int);

            
            par1.Direction = ParameterDirection.InputOutput;
            par2.Direction = ParameterDirection.InputOutput;
            par3.Direction = ParameterDirection.InputOutput;
            par4.Direction = ParameterDirection.InputOutput;
            par5.Direction = ParameterDirection.InputOutput;           

           
            par4.Value = "50";
            par5.Value = "1";             
            
            
            if (c1.Checked = false)
            {
                par3.Value = DBNull.Value; 
            }
            else
            {
                par3.Value = date1.Value;
            }
            if (c2.Checked = false)
            {
                par4.Value = DBNull.Value; 
            }
            else
            {
                par4.Value = date2.Value;
            }
            if (c3.Checked = false)
            {
                par1.Value = null;
            }
            else
            {
                par1.Value = column20.Text;

            }

            com.Parameters.Add(par1);
            com.Parameters.Add(par2);
            com.Parameters.Add(par3);
            com.Parameters.Add(par4);
            com.Parameters.Add(par5);           


            daData.SelectCommand = com;

            daData.Fill(dsData, "database");

            DataBS.DataSource = dsData.Tables["database"];

            datagridview.DataSource = DataBS;

但它不起作用，它只是不忽略日期值，不将其作为空值传递，我在做什么错????

but it doesnt work, it just doesnt ignore date values, doesnt pass them as null values, what am I doing wrong???

推荐答案

您正在传递date1.Value和date2.Value作为par3和par4.他们不应该是par2和par3吗?

You''re passing the date1.Value and date2.Value as par3 and par4. Shouldn''t they be par2 and par3?

这篇关于为日期参数传递空值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！