none
SQL Error in C# but not on SQL Server RRS feed

  • Question

  • Okay... I'm completely stumped on this.  I keep getting a Sql Error from my web application.  I test the stored procedure on the server and it works fine.  I run a test query in Visual studio and it works fine.  When I attempt to load the page in a browser, I get the following Error: (Product name excluded, but it was definitely looking at the field for ProductFamily_Name)

    [SqlException (0x80131904): Conversion failed when converting the nvarchar value '[ProductFamily_Name]' to data type int.]
       System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +388
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +588
       System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4171
       System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows) +356
       System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) +499
       System.Data.SqlClient.SqlDataReader.Read() +34
       System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +167
       System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +292
       System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +529
       System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +422
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +420
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +280
       System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +3529043
       System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +28
       System.Web.UI.WebControls.DataBoundControl.PerformSelect() +313
       System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +146
       System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +192
       System.Web.UI.Control.EnsureChildControls() +182
       System.Web.UI.Control.PreRenderRecursiveInternal() +60
       System.Web.UI.Control.PreRenderRecursiveInternal() +222
       System.Web.UI.Control.PreRenderRecursiveInternal() +222
       System.Web.UI.Control.PreRenderRecursiveInternal() +222
       System.Web.UI.Control.PreRenderRecursiveInternal() +222
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4297
    

    Here is a copy of the Stored Procedure:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[_Products_Under_Category]
    	-- Add the parameters for the stored procedure here
    	  @Category as nvarchar(100)
    	, @Sort as nvarchar(50)
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    
    
    SELECT
    		ProductFamily.ProductFamily_ID
    	  , [ProductFamily_Name]
          , [ProductFamily_Image]
          , [ProductFamily_Thumbnail]
          , ProductFamily.[DisplayURL]
    	  , Min(Product.Product_ListPrice) as ProductFamily_MinPrice
    	  , Max(Product.Product_ListPrice) as ProductFamily_MaxPrice
    into #TempProductStats
    From ProductFamily
    Left Outer Join Category_ProductFamily 
    On Category_ProductFamily.ProductFamily_ID = ProductFamily.ProductFamily_ID
    Left Outer Join Category
    On Category_ProductFamily.Category_ID = Category.Category_ID
    Left Outer Join Category as ParentCategory
    On ParentCategory.Category_ID = Category.Category_ParentID
    Left Outer Join Category as GrandParentCategory
    On GrandParentCategory.Category_ID = ParentCategory.Category_ParentID
    Left Outer Join Category as GreatGrandParentCategory
    On GreatGrandParentCategory.Category_ID = GrandParentCategory.Category_ParentID
    Left Outer Join Product 
    on Product.ProductFamily_ID = ProductFamily.ProductFamily_ID
    Left Outer Join AttributeValue as Att1
    On Product.Product_Attribute1 = Att1.AttributeValue_ID
    Left Outer Join AttributeValue as Att2
    On Product.Product_Attribute2 = Att2.AttributeValue_ID
    Left Outer Join AttributeValue as Att3
    On Product.Product_Attribute3 = Att3.AttributeValue_ID
    Left Outer Join AttributeValue as Att4
    On Product.Product_Attribute4 = Att4.AttributeValue_ID
    Left Outer Join AttributeValue as Att5
    On Product.Product_Attribute5 = Att5.AttributeValue_ID
    Where (Category.DisplayURL = @Category
    OR ParentCategory.DisplayURL = @Category
    OR GrandParentCategory.DisplayURL = @Category
    Or GreatGrandParentCategory.DisplayURL = @Category
    )
    And ProductFamily_Display = 1
    AND (Product_Attribute1 is NULL OR Att1.AttributeValue_Display = 1)
    AND (Product_Attribute2 is NULL OR Att2.AttributeValue_Display = 1)
    AND (Product_Attribute3 is NULL OR Att3.AttributeValue_Display = 1)
    AND (Product_Attribute4 is NULL OR Att4.AttributeValue_Display = 1)
    AND (Product_Attribute5 is NULL OR Att5.AttributeValue_Display = 1)
    And Product.Product_Enabled = 1
    Group By ProductFamily.[ProductFamily_ID]
          , [ProductFamily_Name]
          , [ProductFamily_Image]
          , [ProductFamily_Thumbnail]
          , ProductFamily.[DisplayURL]
    
    
    -- Update with Sales Stats
    Select
    		Product.ProductFamily_ID
    	  , SUM(CASE WHEN Item_Status = 'Sold' OR Item_Status = 'Oversold' THEN 1 Else 0 END) as TotalSold
    	  , MAX(Item_SoldDate) as LastSoldDate
    	  , MAX(ProductFamily.ProductFamily_TimeCreated) as CreatedDate
    into #TempSalesStats
    from #TempProductStats
    Left Outer Join ProductFamily
    on ProductFamily.ProductFamily_ID = #TempProductStats.ProductFamily_ID
    Left Outer Join Product
    on Product.ProductFamily_ID = #TempProductStats.ProductFamily_ID
    Left Outer Join Item
    on Item.Product_ID = Product.Product_ID
    Group By Product.ProductFamily_ID
    
    -- Update with Reviews Stats
    
    
    Select
    		Review.ProductFamily_ID
    	  , Cast(AVG(Review_Rating *10) as float) /10 As AvgRating
    	  , MAX(Review_CreatedDate) as LastReviewDate
    	  , Count(Review_ID) as TimesReviewed
    into #TempReviewStats
    from #TempProductStats
    Left Outer Join Review
    on Review.ProductFamily_ID = #TempProductStats.ProductFamily_ID
    Group By Review.ProductFamily_ID
    
    Select 	#TempProductStats.[ProductFamily_ID] 
          , [ProductFamily_Name]  
          , [ProductFamily_Image] 
          , [ProductFamily_Thumbnail] 
    	  , ProductFamily_MinPrice  
    	  , ProductFamily_MaxPrice 
          , [DisplayURL] 
    	  , AvgRating  
          , LastReviewDate  
    	  , TimesReviewed 
    	  , TotalSold  
    	  , LastSoldDate 
    	  , CreatedDate 
    from #TempProductStats
    Left Outer Join #TempSalesStats
    On #TempSalesStats.ProductFamily_ID = #TempProductStats.ProductFamily_ID
    Left Outer Join #TempReviewStats
    On #TempReviewStats.ProductFamily_ID = #TempProductStats.ProductFamily_ID
    Order By 
    	CASE WHEN @Sort = 'Top Rated' THEN
    		AvgRating
    	ELSE
    		CASE WHEN @Sort = 'Top Selling' THEN
    			TotalSold
    		ELSE 
    			CASE WHEN @Sort = 'Most Reviews' THEN
    				TimesReviewed
    			ELSE
    				CASE WHEN @Sort = 'Newest' THEN
    					CreatedDate
    				ELSE
    					CASE WHEN @Sort = 'A to Z' THEN
    						ProductFamily_Name
    					ELSE
    						CASE WHEN @Sort = 'Highest Price' THEN
    							ProductFamily_MaxPrice
    						END
    					END
    				END
    			END
    		END
    	END DESC
    	, CASE WHEN @Sort = 'Oldest' THEN
    		CreatedDate
      	  ELSE
    		CASE WHEN @Sort = 'Z to A' THEN
    			ProductFamily_Name
    		ELSE
    			CASE WHEN @Sort = 'Lowest Price' THEN
    				ProductFamily_MinPrice
    			END
    		END
    	  END ASC
    	, ProductFamily_Name
    
    
    
    	Drop Table #TempProductStats
    	Drop Table #TempReviewStats
    	Drop Table #TempSalesStats
    
    END
    Go

    and the Relevant test code from the page:

        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:dotnetConnection %>" SelectCommand="_Products_Under_Category" SelectCommandType="StoredProcedure">
            <SelectParameters>
                <asp:QueryStringParameter Name="Category" QueryStringField="URL" Type="String" />
                <asp:ControlParameter ControlID="DropDownList2" Name="Sort" PropertyName="SelectedValue" Type="String" DefaultValue="A to Z" />
            </SelectParameters>
        </asp:SqlDataSource>
    
    
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource2">
            <Columns>
                <asp:BoundField DataField="ProductFamily_ID" HeaderText="ProductFamily_ID" SortExpression="ProductFamily_ID" />
                <asp:BoundField DataField="ProductFamily_Name" HeaderText="ProductFamily_Name" SortExpression="ProductFamily_Name" />
                <asp:BoundField DataField="ProductFamily_Image" HeaderText="ProductFamily_Image" SortExpression="ProductFamily_Image" />
                <asp:BoundField DataField="ProductFamily_Thumbnail" HeaderText="ProductFamily_Thumbnail" SortExpression="ProductFamily_Thumbnail" />
                <asp:BoundField DataField="ProductFamily_MinPrice" HeaderText="ProductFamily_MinPrice" SortExpression="ProductFamily_MinPrice" />
                <asp:BoundField DataField="ProductFamily_MaxPrice" HeaderText="ProductFamily_MaxPrice" SortExpression="ProductFamily_MaxPrice" />
                <asp:BoundField DataField="DisplayURL" HeaderText="DisplayURL" SortExpression="DisplayURL" />
                <asp:BoundField DataField="AvgRating" HeaderText="AvgRating" SortExpression="AvgRating" />
                <asp:BoundField DataField="LastReviewDate" HeaderText="LastReviewDate" SortExpression="LastReviewDate" />
                <asp:BoundField DataField="TimesReviewed" HeaderText="TimesReviewed" SortExpression="TimesReviewed" />
                <asp:BoundField DataField="TotalSold" HeaderText="TotalSold" SortExpression="TotalSold" />
                <asp:BoundField DataField="LastSoldDate" HeaderText="LastSoldDate" SortExpression="LastSoldDate" />
                <asp:BoundField DataField="CreatedDate" HeaderText="CreatedDate" SortExpression="CreatedDate" />
            </Columns>
        </asp:GridView>

    I'm not doing any conversions through the entire thing, so why am I getting a type conversion Error?

    Wednesday, January 2, 2013 7:48 PM

Answers

  • It took a while but I finally found it.  the error message from C# didn't help, but as I gradually changed the process over to use Views I was able to figure it out as different parts of the code were shown to work until I added in the sort expression.  For some reason ASP.Net does not like having different types in the same sort term even though SQL server was fine with it.  I split the sort expression up by types and it all worked fine.

    • Marked as answer by Alexander Sun Monday, January 7, 2013 9:28 AM
    Thursday, January 3, 2013 1:11 PM

All replies

  • Does the exception occur somewhere in your code or is it just a straight databind to the GridView? The exception is indicating that there is a data type mismatch between the data and the target variable (although I'm not sure what that is).

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, January 2, 2013 10:23 PM
  • It took a while but I finally found it.  the error message from C# didn't help, but as I gradually changed the process over to use Views I was able to figure it out as different parts of the code were shown to work until I added in the sort expression.  For some reason ASP.Net does not like having different types in the same sort term even though SQL server was fine with it.  I split the sort expression up by types and it all worked fine.

    • Marked as answer by Alexander Sun Monday, January 7, 2013 9:28 AM
    Thursday, January 3, 2013 1:11 PM