Click here to Skip to main content
12,074,308 members (65,145 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
Edited 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
Web04 | 2.8.160208.1 | Last Updated 2 Oct 2012
Copyright © CodeProject, 1999-2016
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