Atlas example with GridView Paging, Sorting and inline Update/Delete

Hi Bobby, I also got the same error with latest revision...

Sorry for not updating this example sooner.  There is a new property for the SqlParameter object that adds support for the new parameterized query builder classes.

I have added an upated message to the original example post.  To fix the error, add UseParameterizedFilters="false" to the SqlParameter that generates the WhereClause.

Thanks.

Thanks Bobby! Iit seems to me that more logically set it to true?

The default is "true,"  but since I was using GetPaged as the SelectMethod, I wanted to show that it should be set to "false."  The reason I did not change the SelectMethod, is due to the fact that the new Find methods have not yet been implemented in the ServiceLayer.  If you are not using the ServiceLayer, you can omit the property altogether if you change the SelectMethod to "Find."

Hope that clears things up a bit!

Hi Bobby,

I'm curious if you have had filtering working with integers and not strings, your example shows the 'All' selection for the country list as being an empty string.

I've populated that dropdown list from a strongly typed datasource which works a treat, except that it does not have an 'all' entry. To work around this I added a new ListItem with the text (All) and a value of an empty string on the dropdownlist PreRender event. Again, this works until I try to select the (All) entry because it cannot be converted to a null.

Atlas reports 'Conversion failed when converting the varchar value null to data type int' on the client.

The dropdown list looks like this;

 <option selected="selected" value="1">Digital</option>
 <option value="2">Analogue</option>
 <option value="3">Text</option>
 <option value="4">GPS</option>
 <option value="">(All)</option>

Setting the value to Null/null does not work either. I can see this might be a limitation of the latest Atlas build but wondered if you had seen any way round it?

I'll go dig into Atlas now and see if I can find the client side type converters to see if they handle nulls and how. Will let you know how I get on.

Thanks in advance,

Ryan

Whoops!

It looks like it was caused by caching on the part of Atlas. Atlas was returning the SQL error from GetPaged so I got SQL Profiler out to see what was being passed and by that time my session had expired. Trying again it all worked just as it should.

To save other people embarassment, kill the session and check that Atlas is not caching submit values.

Sorry for bothering you Bobby.

Ryan

 

Hi y'all, i'm looking at maybe using .nettiers, and i'm trying to test out the results of codegeneration on my own database. i'm starting out with a simple gridview with sorting and paging as per the example here and in the documentation.. and i'm getting an error when i try to sort. i'm using a typed datasource's getpaged function eith enablepaging and enablesorting tue, and a gridview assigned to the datasource with allowpaging and allowsorting set to true..

 the gridview loads and displays, but errors on clicking a headerlink to sort. the error i'm getting is:

sqlexception: "invalid column: <sortcolumname>"

my stacktrace is as so:

[SqlException (0x80131904): Invalid column name 'LoanAmount'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244
   System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo) +178
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2531
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +52
   System.Data.SqlClient.SqlDataReader.get_MetaData() +130
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +371
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +45
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +162
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +35
   System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +32
   Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior) +200
   Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command, DbTransaction transaction) +64
   eSAM.Data.Utility.ExecuteReader(TransactionManager transactionManager, DbCommand dbCommand) in C:\NetTiers\eSAM\eSAM.Data\Utility.cs:332
   eSAM.Data.SqlClient.SqlTrkLoanbrightImportedProviderBase.GetPaged(TransactionManager transactionManager, String whereClause, String orderBy, Int32 start, Int32 pageLength, Int32& count) in C:\NetTiers\eSAM\eSAM.Data.SqlClient\SqlTrkLoanbrightImportedProviderBase.generated.cs:627
   eSAM.Web.Data.TrkLoanbrightImportedDataSourceView.GetSelectData(Int32& count) in C:\NetTiers\eSAM\eSAM.Web\DataSourceControls\TrkLoanbrightImportedDataSource.cs:188
   eSAM.Web.Data.BaseDataSourceView`2.GetCachedData(Int32& count) in C:\NetTiers\eSAM\eSAM.Web\Data\BaseDataSource.cs:1345
   eSAM.Web.Data.BaseDataSourceView`2.GetEntityList(DataSourceSelectArguments arguments, IDictionary values) in C:\NetTiers\eSAM\eSAM.Web\Data\BaseDataSource.cs:1303
   eSAM.Web.Data.BaseDataSourceView`2.ExecuteSelectCore(DataSourceSelectArguments arguments) in C:\NetTiers\eSAM\eSAM.Web\Data\BaseDataSource.cs:1251
   eSAM.Web.Data.BaseDataSourceView`2.ExecuteSelect(DataSourceSelectArguments arguments) in C:\NetTiers\eSAM\eSAM.Web\Data\BaseDataSource.cs:1202
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +84
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +154
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +99
   System.Web.UI.WebControls.GridView.DataBind() +24
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +91
   System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e) +33
   System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +75
   System.Web.UI.Control.PreRenderRecursiveInternal() +148
   System.Web.UI.Control.PreRenderRecursiveInternal() +233
   System.Web.UI.Control.PreRenderRecursiveInternal() +233
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4435

what am i doing wrong? i can post code, but it's pretty verbatim to the example beyond using object generated on my own db.

Does your TrkLoanbrightImported table have a column called LoanAmount?

Thanks.
 

yes, and the grid binds, updates, and pages successfully. in fact, everything has been absolutely amazing so far, what with the intellisense selectmethods and all. i sincerely hope that i can make use of this.

i'm using the latest codesmith (demo, not sure if i'll purchase yet) and latest .nettier downloads. i'll think of what else i can tell you.

 

 

Can you post the code you are using so we can see if there is anything else that may be causing the sorting error?

Thanks.
 

Weird. I've tried the same procedure on another table and it worked fine. there's something specific about this table, i guess. I wondered if I accidentally generated the solution on an out of date copy of my database, so I recreated the whole project and tried again. I still get the failure on this table, though another table worked fine..

here's my current source, the gridview uses fewer columns than the entity provides.

<body>
    <form id="form1" runat="server">
    <div>
<data:TrkLoanbrightImportedDataSource ID="TrkLoanbrightImportedDataSource1" runat="server" EnablePaging="true" EnableSorting="true" SelectMethod="getpaged" />
        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
            AutoGenerateColumns="False" DataKeyNames="RateQuoteId" DataSourceID="TrkLoanbrightImportedDataSource1"
            PageSize="3">
            <Columns>
                <asp:BoundField DataField="LoanAmount" HeaderText="LoanAmount" SortExpression="LoanAmount" />
                <asp:BoundField DataField="RateQuoteId" HeaderText="RateQuoteId" ReadOnly="True"
                    SortExpression="RateQuoteId" />
                <asp:BoundField DataField="BrrwrWorkPhone" HeaderText="BrrwrWorkPhone" SortExpression="BrrwrWorkPhone" />
                <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
                <asp:BoundField DataField="BrrwrCcdebt" HeaderText="BrrwrCcdebt" SortExpression="BrrwrCcdebt" />
                <asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address" />
            </Columns>
        </asp:GridView>

    </div>
    </form>
</body>

here's the createscript for the table; it has several fields that key to lookup tables and an odd both-or-either-but-not-neither phonerule.

the table that's got this example working for me is cut and dry with no relationships to other tables.

CREATE TABLE [dbo].[trk_loanbright_imported](
    [rate_quote_id] [int] NOT NULL,
    [prop_region_id] [int] NOT NULL,
    [prop_value] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [loan_purpose] [int] NOT NULL,
    [loan_product] [int] NOT NULL,
    [loan_amount] [int] NOT NULL,
    [loan_close_date] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [brrwr_fname] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [brrwr_lname] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [brrwr_home_phone] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [brrwr_work_phone] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [brrwr_email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [brrwr_credit_rating] [int] NOT NULL,
    [brrwr_bankruptcy] [int] NOT NULL,
    [brrwr_document_income] [int] NOT NULL,
    [comments_questions] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [created] [datetime] NOT NULL,
    [prop_purpose] [int] NULL,
    [prop_type] [int] NULL,
    [address] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [city] [varchar](35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [state] [int] NULL,
    [zip] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [brrwr_income] [int] NULL,
    [brrwr_car_payment] [int] NULL,
    [brrwr_student_loan] [int] NULL,
    [brrwr_ccdebt] [int] NULL,
    [brrwr_cell_phone] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [brrwr_best_time_to_call] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [mtg_current_provider] [int] NULL,
    [mtg_current_type] [int] NULL,
    [mtg_current_rate] [real] NULL,
 CONSTRAINT [PK_trk_loanbright_imported] PRIMARY KEY CLUSTERED
(
    [rate_quote_id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[trk_loanbright_imported]  WITH CHECK ADD  CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_bankruptcy] FOREIGN KEY([brrwr_bankruptcy])
REFERENCES [dbo].[tlu_sbb_bankruptcy] ([sbbCode])
GO
ALTER TABLE [dbo].[trk_loanbright_imported] CHECK CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_bankruptcy]
GO
ALTER TABLE [dbo].[trk_loanbright_imported]  WITH CHECK ADD  CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_creditrating] FOREIGN KEY([brrwr_credit_rating])
REFERENCES [dbo].[tlu_sbb_creditrating] ([sbbCode])
GO
ALTER TABLE [dbo].[trk_loanbright_imported] CHECK CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_creditrating]
GO
ALTER TABLE [dbo].[trk_loanbright_imported]  WITH CHECK ADD  CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_loanproduct] FOREIGN KEY([loan_product])
REFERENCES [dbo].[tlu_sbb_loanproduct] ([sbbCode])
GO
ALTER TABLE [dbo].[trk_loanbright_imported] CHECK CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_loanproduct]
GO
ALTER TABLE [dbo].[trk_loanbright_imported]  WITH CHECK ADD  CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_loanpurpose] FOREIGN KEY([loan_purpose])
REFERENCES [dbo].[tlu_sbb_loanpurpose] ([sbbCode])
GO
ALTER TABLE [dbo].[trk_loanbright_imported] CHECK CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_loanpurpose]
GO
ALTER TABLE [dbo].[trk_loanbright_imported]  WITH CHECK ADD  CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_mortgageprovider] FOREIGN KEY([mtg_current_provider])
REFERENCES [dbo].[tlu_sbb_mortgageprovider] ([sbbCode])
GO
ALTER TABLE [dbo].[trk_loanbright_imported] CHECK CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_mortgageprovider]
GO
ALTER TABLE [dbo].[trk_loanbright_imported]  WITH CHECK ADD  CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_mortgagetype] FOREIGN KEY([mtg_current_type])
REFERENCES [dbo].[tlu_sbb_mortgagetype] ([sbbCode])
GO
ALTER TABLE [dbo].[trk_loanbright_imported] CHECK CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_mortgagetype]
GO
ALTER TABLE [dbo].[trk_loanbright_imported]  WITH CHECK ADD  CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_propertypurpose] FOREIGN KEY([prop_purpose])
REFERENCES [dbo].[tlu_sbb_propertypurpose] ([sbbCode])
GO
ALTER TABLE [dbo].[trk_loanbright_imported] CHECK CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_propertypurpose]
GO
ALTER TABLE [dbo].[trk_loanbright_imported]  WITH CHECK ADD  CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_propertytype] FOREIGN KEY([prop_type])
REFERENCES [dbo].[tlu_sbb_propertytype] ([sbbCode])
GO
ALTER TABLE [dbo].[trk_loanbright_imported] CHECK CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_propertytype]
GO
ALTER TABLE [dbo].[trk_loanbright_imported]  WITH CHECK ADD  CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_region] FOREIGN KEY([prop_region_id])
REFERENCES [dbo].[tlu_sbb_region] ([sbbCode])
GO
ALTER TABLE [dbo].[trk_loanbright_imported] CHECK CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_region]
GO
ALTER TABLE [dbo].[trk_loanbright_imported]  WITH CHECK ADD  CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_region1] FOREIGN KEY([state])
REFERENCES [dbo].[tlu_sbb_region] ([sbbCode])
GO
ALTER TABLE [dbo].[trk_loanbright_imported] CHECK CONSTRAINT [FK_trk_loanbright_imported_tlu_sbb_region1]
GO
ALTER TABLE [dbo].[trk_loanbright_imported]  WITH CHECK ADD  CONSTRAINT [CK_trk_loanbright_imported_eitherOrPhoneNull] CHECK  ((NOT ([brrwr_home_phone] IS NULL AND [brrwr_work_phone] IS NULL)))
GO
ALTER TABLE [dbo].[trk_loanbright_imported] CHECK CONSTRAINT [CK_trk_loanbright_imported_eitherOrPhoneNull]

 

I'm still searching for more useful info to provide.
 

update: the website admin page is functionally sorting for this table. ...i'm looking to see what's different. 

mousse,

 The problem seems is with your sortexpression values, you have to use the actual db column names, rather then property names

        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
            AutoGenerateColumns="False" DataKeyNames="RateQuoteId" DataSourceID="TrkLoanbrightImportedDataSource1"
            PageSize="3">
            <Columns>
                <asp:BoundField DataField="LoanAmount" HeaderText="LoanAmount" SortExpression="Loan_Amount" />
                <asp:BoundField DataField="RateQuoteId" HeaderText="RateQuoteId" ReadOnly="True"
                    SortExpression="Rate_Quote_Id" />
                <asp:BoundField DataField="BrrwrWorkPhone" HeaderText="BrrwrWorkPhone" SortExpression="Brrwr_Work_Phone" />
                <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
                <asp:BoundField DataField="BrrwrCcdebt" HeaderText="BrrwrCcdebt" SortExpression="Brrwr_Cc_debt" />
                <asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address" />
            </Columns>
        </asp:GridView>

ahaaa i figured it was something stupid on my part.

although, there weren't any custom actions done, the sortvalues were autogenerated by the datagrid control when i specified the datasource in design mode, all i did was remove some columns...

still, i'm in awe of all this.

by the way, i'm considering using anthem.net instead of atlas. i'll let you know how that goes if there's any impact specific to this.
 

You're right, there was a bug in the templates, it was fixed a couple of days ago

I get an error trying to use the inline update for a grid.....I've posted it here" http://community.codesmithtools.com/forums/thread/28943.aspx.  I haven't got a response.  Is there still a bug with the gridview inline update?