locked
GRIDVIEW - Invalid column name 'APIDataRights' RRS feed

  • Question

  • User-1668014665 posted

    But how...

    (ASP Net 2.0 MSVS 2005, MS SQL server 2008 R2)

    It works in DEV

    Here is the production code

    SQL 

    CREATE PROCEDURE [dbo].[procGetDBRoles]
    AS
    	BEGIN
    		SET NOCOUNT ON
    
    		SELECT A.[RoleName]
    			  ,A.[Description] AS SubsRights
    			  ,A.[AllowExpire]
    			  ,A.[AllowClearAll]
    			  ,A.[AllowGroups]
    			  ,A.[ForumGroupID]
    			  ,ISNULL(B.[Name],'<Missing>') AS ForumGroupName
    			  ,A.[DataAPIRights]
    		 FROM [dbo].[aspnet_Roles] A WITH (NOLOCK)
    		 LEFT JOIN WebWizForum.dbo.Forum1Group B ON A.[ForumGroupID] = B.Group_ID
    
    
    		IF @@ROWCOUNT = 0 
    		BEGIN RETURN -100 END
    		SET NOCOUNT OFF
    	END

    Here is the GRID VIEW

            <asp:UpdatePanel ID="UpdatePanel2" runat="server" UpdateMode="Conditional"  >
            <ContentTemplate>
                <asp:GridView ID="grid1" runat="server" AllowPaging="True" AllowSorting="True"
                    AutoGenerateColumns="False" CellPadding="4" DataSourceID="sql1" Font-Size="Smaller"
                    ForeColor="#333333" GridLines="Vertical" PageSize="50" EnableViewState="False">
                    <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                    <Columns >
                        <asp:BoundField DataField="RoleName"  HeaderText="RoleName"  HeaderStyle-Font-Size="Smaller" SortExpression="RoleName" />
                        <asp:BoundField DataField="SubsRights"  HeaderText="SubsRights" HeaderStyle-Font-Size="Smaller" HtmlEncode="true" SortExpression="SubsRights" ItemStyle-HorizontalAlign="Center"  />
                        <asp:BoundField DataField="AllowExpire"  HeaderText="AllowExpire" HeaderStyle-Font-Size="Smaller" HtmlEncode="true" SortExpression="AllowExpire" />
                        <asp:BoundField DataField="AllowClearAll"  HeaderText="AllowClearAll" HeaderStyle-Font-Size="Smaller" HtmlEncode="true" SortExpression="AllowClearAll" />
                        <asp:BoundField DataField="AllowGroups"  HeaderText="AllowGroups" HeaderStyle-Font-Size="Smaller" HtmlEncode="true" SortExpression="AllowGroups" ItemStyle-HorizontalAlign="Center" />
                        <asp:BoundField DataField="ForumGroupID"  HeaderText="ForumGroupID" HeaderStyle-Font-Size="Smaller" HtmlEncode="true" SortExpression="ForumGroupID" ItemStyle-HorizontalAlign="Center"  />
                        <asp:BoundField DataField="ForumGroupName"  HeaderText="ForumGroupName" HeaderStyle-Font-Size="Smaller" HtmlEncode="true" SortExpression="ForumGroupName" />
                        <asp:BoundField DataField="DataAPIRights"  HeaderText="APIRights" HeaderStyle-Font-Size="Smaller" HtmlEncode="true" SortExpression="DataAPIRights" ItemStyle-HorizontalAlign="Center" />
                    </Columns>
                    <RowStyle BackColor="#E3EAEB"  Font-Size="Smaller" />
                    <EditRowStyle BackColor="#7C6F57" />
                    <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
                    <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
                    <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                    <AlternatingRowStyle BackColor="White" />
                    <EmptyDataRowStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                    <EmptyDataTemplate>
                    No Data.
                    </EmptyDataTemplate>
                </asp:GridView>
                <asp:SqlDataSource ID="sql1" runat="server" 
                   ConnectionString="<%$ ConnectionStrings:RTT_OPS_con %>"
                    ProviderName="System.Data.SqlClient" 
                    SelectCommand="procGetDBRoles"
                    SelectCommandType="StoredProcedure">
                    </asp:SqlDataSource>
                </ContentTemplate>
                </asp:UpdatePanel>

    YET I get this error Invalid column name 'APIDataRights'.

    System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Data.SqlClient.SqlException: Invalid column name 'APIDataRights'.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
       at System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments)
       at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback)
       at System.Web.UI.WebControls.DataBoundControl.PerformSelect()
       at System.Web.UI.WebControls.BaseDataBoundControl.DataBind()
       at System.Web.UI.WebControls.GridView.DataBind()
       at System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound()
       at System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls()
       at System.Web.UI.Control.EnsureChildControls()
       at System.Web.UI.Control.PreRenderRecursiveInternal()
       at System.Web.UI.Control.PreRenderRecursiveInternal()
       at System.Web.UI.Control.PreRenderRecursiveInternal()
       at System.Web.UI.Control.PreRenderRecursiveInternal()
       at System.Web.UI.Control.PreRenderRecursiveInternal()
       at System.Web.UI.Control.PreRenderRecursiveInternal()
       at System.Web.UI.Control.PreRenderRecursiveInternal()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
       --- End of inner exception stack trace ---
       at System.Web.UI.Page.HandleError(Exception e)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
       at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
       at System.Web.UI.Page.ProcessRequest()
       at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
       at System.Web.UI.Page.ProcessRequest(HttpContext context)
       at ASP.admin_tables1_aspx.ProcessRequest(HttpContext context)
       at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
       at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

    I have double checked prod vs Dev and they are the same

    Wednesday, March 27, 2019 6:07 AM

Answers

  • User-1668014665 posted

    SOLVED

    Was in  stored proc un related to the above

    stupid me!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 27, 2019 11:01 PM

All replies

  • User-893317190 posted

    Hi icm63,

    Are you sure you are using this procedure in your  production environment?

    Your procedure even has no column named APIDataRights, it only has a column named DataAPIRights.

    If you have Sql Server Profile, you could  watch sql query through it.

    https://dba.stackexchange.com/questions/40960/logging-queries-and-other-t-sql

    If you are not able to see your stored procedure , please try to  use normal sql instead of stored procedure to see whether it is ok.

    If normal sql is ok , then the problem may be with your stored procedure.

    Best regards,

    Ackerly Xu

    Wednesday, March 27, 2019 9:34 AM
  • User-1668014665 posted
        <asp:BoundField DataField="DataAPIRights"  HeaderText="APIRights" HeaderStyle-Font-Size="Smaller" HtmlEncode="true" SortExpression="DataAPIRights" ItemStyle-HorizontalAlign="Center" />

    HeaderText="APIRights"

    Does not need to be the name of the field from the SQL

    Wednesday, March 27, 2019 5:24 PM
  • User753101303 posted

    Hi,

    As pointed already the problem is that "APIDataRights" doesn't appear in what you shown so far. It seems to be a db side issue. You really checked the text of the stored procedure as well?

    Preferably use :

    https://docs.microsoft.com/en-us/sql/ssdt/how-to-use-schema-compare-to-compare-different-database-definitions?view=sql-server-2017

    to compare your database. Or do a db or VS wide seach for this "APIDataRights" string for example using :

    SELECT * FROM sys.sql_modules WHERE definition LIKE '%APIDataRights%'

    Wednesday, March 27, 2019 6:02 PM
  • User-1668014665 posted

    Profiler Check all good
    SQL script above returned NULL

    This error is very strange : Invalid column name 'APIDataRights'.

    Invalid column name 'APIDataRights'.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
    
    Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'APIDataRights'.
    
    Source Error: 
    
    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
    
    Stack Trace: 
    
    
    [SqlException (0x80131904): Invalid column name 'APIDataRights'.]
       System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1960634
       System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4890859
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
       System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2412
       System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +59
       System.Data.SqlClient.SqlDataReader.get_MetaData() +83
       System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +293
       System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
       System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
       System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
       System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
       System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +130
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +287
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +92
       System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1297
       System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +19
       System.Web.UI.WebControls.DataBoundControl.PerformSelect() +142
       System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +73
       System.Web.UI.WebControls.GridView.DataBind() +4
       System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
       System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +72
       System.Web.UI.Control.EnsureChildControls() +87
       System.Web.UI.Control.PreRenderRecursiveInternal() +44
       System.Web.UI.Control.PreRenderRecursiveInternal() +171
       System.Web.UI.Control.PreRenderRecursiveInternal() +171
       System.Web.UI.Control.PreRenderRecursiveInternal() +171
       System.Web.UI.Control.PreRenderRecursiveInternal() +171
       System.Web.UI.Control.PreRenderRecursiveInternal() +171
       System.Web.UI.Control.PreRenderRecursiveInternal() +171
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +842

    Because my new improved code HAS NO FIELD NAMED  'APIDataRights'.

                <asp:GridView ID="grid1" runat="server" AllowPaging="True" AllowSorting="True"
                    AutoGenerateColumns="False" CellPadding="4" DataSourceID="sql1" Font-Size="Smaller"
                    ForeColor="#333333" GridLines="Vertical" PageSize="50" EnableViewState="False">
                    <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                    <Columns >
                        <asp:BoundField DataField="RoleName"  HeaderText="RoleName"  HeaderStyle-Font-Size="Smaller" SortExpression="RoleName" />
                        <asp:BoundField DataField="SubsRights"  HeaderText="SubsRights" HeaderStyle-Font-Size="Smaller" HtmlEncode="true" SortExpression="SubsRights" ItemStyle-HorizontalAlign="Center"  />
                        <asp:BoundField DataField="AllowExpire"  HeaderText="AllowExpire" HeaderStyle-Font-Size="Smaller" HtmlEncode="true" SortExpression="AllowExpire" />
                        <asp:BoundField DataField="AllowClearAll"  HeaderText="AllowClearAll" HeaderStyle-Font-Size="Smaller" HtmlEncode="true" SortExpression="AllowClearAll" />
                        <asp:BoundField DataField="AllowGroups"  HeaderText="AllowGroups" HeaderStyle-Font-Size="Smaller" HtmlEncode="true" SortExpression="AllowGroups" ItemStyle-HorizontalAlign="Center" />
                        <asp:BoundField DataField="ForumGroupID"  HeaderText="ForumGroupID" HeaderStyle-Font-Size="Smaller" HtmlEncode="true" SortExpression="ForumGroupID" ItemStyle-HorizontalAlign="Center"  />
                        <asp:BoundField DataField="ForumGroupName"  HeaderText="ForumGroupName" HeaderStyle-Font-Size="Smaller" HtmlEncode="true" SortExpression="ForumGroupName" />
                        <asp:BoundField DataField="DataAPIRights"  HeaderText="DataAPIRights" HeaderStyle-Font-Size="Smaller" HtmlEncode="true" SortExpression="DataAPIRights" ItemStyle-HorizontalAlign="Center" />
                    </Columns>
                    <RowStyle BackColor="#E3EAEB"  Font-Size="Smaller" />
                    <EditRowStyle BackColor="#7C6F57" />
                    <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
                    <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
                    <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                    <AlternatingRowStyle BackColor="White" />
                    <EmptyDataRowStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                    <EmptyDataTemplate>
                    No Data.
                    </EmptyDataTemplate>
                </asp:GridView>
                <asp:SqlDataSource ID="sql1" runat="server" 
                   ConnectionString="<%$ ConnectionStrings:RTT_OPS_con %>"
                    ProviderName="System.Data.SqlClient" 
                    SelectCommand="procGetDBRoles"
                    SelectCommandType="StoredProcedure">
                    </asp:SqlDataSource>

    See here

                        <asp:BoundField DataField="DataAPIRights"  HeaderText="DataAPIRights" HeaderStyle-Font-Size="Smaller" HtmlEncode="true" SortExpression="DataAPIRights" ItemStyle-HorizontalAlign="Center" />

    Has the BUILD BROKEN or something ????

    Wednesday, March 27, 2019 10:06 PM
  • User-1668014665 posted

    SOLVED

    Was in  stored proc un related to the above

    stupid me!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 27, 2019 11:01 PM