Click here to Skip to main content
13,197,548 members (45,039 online)
Rate this:
Please Sign up or sign in to vote.
See more:
I have this stored procedure in sql server

ALTER proc [dbo].[DataDetails3_proc]
@PageNum int OUTPUT
set nocount on
DECLARE @PageSize INT --How many rows to appear per page
--DECLARE @PageNum  INT --What page number to appear
DECLARE @Skip     INT --Working variable for where to start for page
DECLARE @SQL      VARCHAR(8000) --Holds dynamic SQL
--===== Set the local variables for pagesize and page
     -- PageSize and PageNum would be parameters in a stored proc
    SET @PageSize = 50
    --SET @PageNum  = 0
    SET @Skip    = @PageSize*@PageNum
--===== Create the dynamic SQL statement that will produce the page
   FROM dbo.DataDetails c,
         SELECT TOP '+STR(@PageSize)+ ' * 
           FROM dbo.DataDetails WITH (NOLOCK)
          WHERE ID NOT IN (SELECT TOP '+STR(@Skip)+' ID 
                                 FROM dbo.DataDetails
                                ORDER BY ID)
          ORDER BY ID
        ) d
  WHERE c.ID = d.ID
--===== Produce the page
   EXEC (@SQL)

Works fine, when I execute it it asks me to put parameter PageNum and it works, now I am calling this procedure in winform application and it says I have to pass parameter but I dont know what I am doing wrong, obviously I am, here is the code.
SqlDataAdapter daData = new SqlDataAdapter("DataDetails3_proc", cs);
daData.SelectCommand.CommandType = CommandType.StoredProcedure;            
daData.Fill(dsData, "DataDetails");
DataBS.DataSource = dsData.Tables[0];
dg.DataSource = DataBS;
SqlCommand command = new SqlCommand("DataDetails", cs);
SqlParameter param = new SqlParameter("@PageNum", SqlDbType.Int);
param.Direction = ParameterDirection.InputOutput;
param = new SqlParameter("@PageNum", page.Text);

page is textbox in which I am putting that parameter I need
Posted 21-Sep-12 1:15am
Updated 21-Sep-12 1:35am

1 solution

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

Solution 1

Setup your command object first, giving it everything it needs to execute the procedure. Then, assign the command to the DataAdapter and use it to populate your DataSet .

Untested, but *should* work!

SqlCommand command = new SqlCommand("DataDetails3_proc", cs);
command.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@PageNum", SqlDbType.Int);
param.Direction = ParameterDirection.InputOutput;
param.Value = page.Text;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
DataSet ds = new DataSet();

Have a look here for more info...[^]
shonezi 21-Sep-12 7:33am
THANK YOU VERY MUCH DYLAN, I have been going lef and right , up and down busting my head with this, thank you, thank you!!!!!
_Amy 21-Sep-12 7:35am
My +5! :)

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 |
Web01 | 2.8.171020.1 | Last Updated 21 Sep 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