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

[John Cazaly]

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) EnableSortingAn dPagingCallback s="true" (and tried false)
but this didn't help.

    <asp:GridView ID="dgA" runat="server" AutoGenerateColumns="False" 
        AllowPaging="True" BackColor="White" BorderColor="#CC9966" BorderStyle="None" 
        BorderWidth="1px" CellPadding="4" 
         PageSize="10" PagerSettings-Mode="NextPreviousFirstLast">
        <Columns>
            <asp:CommandField HeaderText="Select" ShowSelectButton="True">
            </asp:CommandField>
            <asp:BoundField DataField="SuppName" HeaderText="SuppName"></asp:BoundField>
            <asp:BoundField DataField="OrdID" HeaderText="OrdID"></asp:BoundField>
            <asp:BoundField DataField="OrderDate" DataFormatString="{0:d}" 
                HeaderText="OrderDate"></asp:BoundField>
            <asp:BoundField DataField="CP" DataFormatString="{0:c}" HeaderText="CP">
            </asp:BoundField>
            <asp:BoundField DataField="VAT" DataFormatString="{0:c}" HeaderText="VAT">
            </asp:BoundField>
            <asp:BoundField DataField="Invoice" DataFormatString="{0:c}" 
                HeaderText="Invoice"></asp:BoundField>
        </Columns>
        <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
        <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
        <RowStyle BackColor="White" ForeColor="#330099" />
        <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
        <sortedascendingcellstyle backcolor="#FEFCEB" />
        <sortedascendingheaderstyle backcolor="#AF0101" />
        <sorteddescendingcellstyle backcolor="#F6F0C0" />
        <sorteddescendingheaderstyle backcolor="#7E0000" />
    </asp:GridView>

The VB code I am using is (dbconn is my connection string

 Private Sub BindGrid()

        Dim objCmd As New SqlCommand("Ordersisorders6", dbconn)
        objCmd.CommandType = CommandType.StoredProcedure

        Dim pFromDate As SqlParameter = New SqlParameter("@FromDate", SqlDbType.Date, 10)
        pFromDate.Value = tbDateFrom.Text 'Request("tbFromDate")CalFrom.SelectedDate
        objCmd.Parameters.Add(pFromDate)

        Dim pToDate As SqlParameter = New SqlParameter("@ToDate", SqlDbType.Date, 10)
        pToDate.Value = tbDateTo.Text
        objCmd.Parameters.Add(pToDate)

        dbconn.Open()
        Dim da As New SqlDataAdapter(objCmd)
        Dim ds As New DataSet()
        da.Fill(ds)

        ' Dim DR As SqlDataReader = objCmd.ExecuteReader()
        dgA.DataSource = ds
        dgA.DataBind()
        'dr.Close()
        ' DR = Nothing
        objCmd.Dispose()
        objCmd = Nothing
        dbconn.Close()
        dbconn = Nothing

    End Sub
   Private Sub dgA_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs) Handles dgA.PageIndexChanging

        dgA.PageIndex = e.NewPageIndex
        dgA.EditIndex = -1
        dgA.DataBind()

    End Sub

With the enablesortingan dpagingcallback 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

ALTER PROCEDURE [dbo].[Ordersisorders6] 
	-- Add the parameters for the stored procedure here
	@FromDate date,
	@ToDate date
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT 
	SuppName,
	Ordid, 
	OrderDate, 
	CP,
	VAT,
	Invoice

	from Orders
	where OrderDate >= @FromDate
	and OrderDate <= @ToDate
	order by OrderDate
END

[Ewan]

i get the same error when
EnableSortingAn dPagingCallback s="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.