GRIDVIEW - Invalid column name 'APIDataRights'

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

Answer 1

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

Answer 2

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

Answer 3

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%'

Answer 4

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 ????

Answer 5

User-1668014665 posted

SOLVED

Was in  stored proc un related to the above

stupid me!