By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,714 Members | 1,363 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,714 IT Pros & Developers. It's quick & easy.

DataView: Date Where: Stored Procedures: Select Column and Paging

P: n/a
I have what wouold at first seem a simple request: to create a datagrid / View or similar for an Orders table with OrdID and OrderDate fields (plus some others). I want to select OrderDaate >= From @OrderDate and Orders <= @OrderDate. I need to page these orders when showing in the dataview. I also need to select them so that I can delve deeper and look at the Invoice images etc.
My understanding: In order to select by dates I need to run a stored procedure - fine, created and working and the where clause does its job.
However I can't have the select column and the paging facility at the same time - or so it would seem. I am using VS2010 VB and SQL 2008. On a test page I have created some simplified code. Can someone look at see where I am going wrong please?
I have removed (as suggested) EnableSortingAndPagingCallbacks="true" (and tried false)
but this didn't help.
Expand|Select|Wrap|Line Numbers
  1.     <asp:GridView ID="dgA" runat="server" AutoGenerateColumns="False" 
  2.         AllowPaging="True" BackColor="White" BorderColor="#CC9966" BorderStyle="None" 
  3.         BorderWidth="1px" CellPadding="4" 
  4.          PageSize="10" PagerSettings-Mode="NextPreviousFirstLast">
  5.         <Columns>
  6.             <asp:CommandField HeaderText="Select" ShowSelectButton="True">
  7.             </asp:CommandField>
  8.             <asp:BoundField DataField="SuppName" HeaderText="SuppName"></asp:BoundField>
  9.             <asp:BoundField DataField="OrdID" HeaderText="OrdID"></asp:BoundField>
  10.             <asp:BoundField DataField="OrderDate" DataFormatString="{0:d}" 
  11.                 HeaderText="OrderDate"></asp:BoundField>
  12.             <asp:BoundField DataField="CP" DataFormatString="{0:c}" HeaderText="CP">
  13.             </asp:BoundField>
  14.             <asp:BoundField DataField="VAT" DataFormatString="{0:c}" HeaderText="VAT">
  15.             </asp:BoundField>
  16.             <asp:BoundField DataField="Invoice" DataFormatString="{0:c}" 
  17.                 HeaderText="Invoice"></asp:BoundField>
  18.         </Columns>
  19.         <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
  20.         <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
  21.         <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
  22.         <RowStyle BackColor="White" ForeColor="#330099" />
  23.         <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
  24.         <sortedascendingcellstyle backcolor="#FEFCEB" />
  25.         <sortedascendingheaderstyle backcolor="#AF0101" />
  26.         <sorteddescendingcellstyle backcolor="#F6F0C0" />
  27.         <sorteddescendingheaderstyle backcolor="#7E0000" />
  28.     </asp:GridView>
  29.  
The VB code I am using is (dbconn is my connection string
Expand|Select|Wrap|Line Numbers
  1.  Private Sub BindGrid()
  2.  
  3.         Dim objCmd As New SqlCommand("Ordersisorders6", dbconn)
  4.         objCmd.CommandType = CommandType.StoredProcedure
  5.  
  6.         Dim pFromDate As SqlParameter = New SqlParameter("@FromDate", SqlDbType.Date, 10)
  7.         pFromDate.Value = tbDateFrom.Text 'Request("tbFromDate")CalFrom.SelectedDate
  8.         objCmd.Parameters.Add(pFromDate)
  9.  
  10.         Dim pToDate As SqlParameter = New SqlParameter("@ToDate", SqlDbType.Date, 10)
  11.         pToDate.Value = tbDateTo.Text
  12.         objCmd.Parameters.Add(pToDate)
  13.  
  14.         dbconn.Open()
  15.         Dim da As New SqlDataAdapter(objCmd)
  16.         Dim ds As New DataSet()
  17.         da.Fill(ds)
  18.  
  19.         ' Dim DR As SqlDataReader = objCmd.ExecuteReader()
  20.         dgA.DataSource = ds
  21.         dgA.DataBind()
  22.         'dr.Close()
  23.         ' DR = Nothing
  24.         objCmd.Dispose()
  25.         objCmd = Nothing
  26.         dbconn.Close()
  27.         dbconn = Nothing
  28.  
  29.     End Sub
  30.    Private Sub dgA_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs) Handles dgA.PageIndexChanging
  31.  
  32.         dgA.PageIndex = e.NewPageIndex
  33.         dgA.EditIndex = -1
  34.         dgA.DataBind()
  35.  
  36.     End Sub
  37.  
  38.  
With the enablesortingandpagingcallback removed I get the data but no paging. With the enable... as true I get the message "Callbacks are not supported on CommandField when the select button is enabled because other controls on your page that are dependent on the selected value of 'dgA' for their rendering will not update in a callback. Turn callbacks off on 'dgA'."

Any suggestions please?

The stored Proc is
Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE [dbo].[Ordersisorders6] 
  2.     -- Add the parameters for the stored procedure here
  3.     @FromDate date,
  4.     @ToDate date
  5.  
  6. AS
  7. BEGIN
  8.     -- SET NOCOUNT ON added to prevent extra result sets from
  9.     -- interfering with SELECT statements.
  10.     SET NOCOUNT ON;
  11.  
  12.     SELECT 
  13.     SuppName,
  14.     Ordid, 
  15.     OrderDate, 
  16.     CP,
  17.     VAT,
  18.     Invoice
  19.  
  20.     from Orders
  21.     where OrderDate >= @FromDate
  22.     and OrderDate <= @ToDate
  23.     order by OrderDate
  24. END
  25.  
Sep 29 '10 #1
Share this Question
Share on Google+
1 Reply


Ewan
P: 18
i get the same error when
EnableSortingAndPagingCallbacks="true"

However i want to have sorting/paging enabled and to use the Select option at the same time....
is there any one who could provide a fix for this.. or maybe a work around.
Feb 28 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.