Click here to Skip to main content
12,505,335 members (65,139 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# C#4.0
I have this stored procedure in sql server

ALTER proc [dbo].[DataDetails3_proc]
@PageNum int OUTPUT
as
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
SET @SQL =
'
 SELECT c.*
   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
  ORDER BY 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;            
dsData.Clear();            
daData.Fill(dsData, "DataDetails");
DataBS.DataSource = dsData.Tables[0];
daData.Dispose();
cs.Close();
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
_Amy56.1K
v2

1 solution

Rate this: bad
 
good
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;
command.Parameters.Add(param);
 
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
 
DataSet ds = new DataSet();
adapter.Fill(ds);

Have a look here for more info...

http://csharp.net-informations.com/dataadapter/selectcommand-sqlserver.htm[^]
  Permalink  
Comments
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 | Mobile
Web01 | 2.8.160927.1 | Last Updated 21 Sep 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