Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#4.0
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 
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???
Posted 2-Oct-12 7:24am
Edited 2-Oct-12 7:29am
JF201553.1K
v2

1 solution

Rate this: bad
good
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?
  Permalink  
Comments
shonezi at 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
0 Nirav Prabtani 339
1 OriginalGriff 309
2 _Amy 285
3 Sergey Alexandrovich Kryukov 200
4 Kumarbs 144
0 OriginalGriff 7,889
1 Sergey Alexandrovich Kryukov 7,006
2 Maciej Los 4,014
3 Peter Leow 3,708
4 CHill60 2,762


Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 2 Oct 2012
Copyright © CodeProject, 1999-2014
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