Click here to Skip to main content
12,693,389 members (29,040 online)
Rate this:
Please Sign up or sign in to vote.
See more: C# C#4.0

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]
create proc test_proc

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

set nocount on

Declare @RowStart int 
Declare @RowEnd int 

if @PageNum > 0 

SET @PageNum = @PageNum -1 

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

With Cust AS 

       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 

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

            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; 
                par3.Value = date1.Value;
            if (c2.Checked = false)
                par4.Value = DBNull.Value; 
                par4.Value = date2.Value;
            if (c3.Checked = false)
                par1.Value = null;
                par1.Value = column20.Text;



            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???
Posted 2-Oct-12 8:24am
Updated 2-Oct-12 8:29am

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 2

You're passing the date1.Value and date2.Value as par3 and par4. Shouldn't they be par2 and par3?
shonezi 3-Oct-12 2:14am
I misstyped, and you are write, thank you

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web01 | 2.8.170118.1 | Last Updated 2 Oct 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100