locked
No mapping exists from DbType System.String[] to a known SqlCeType. RRS feed

  • Question

  • User1519602350 posted

    I tried to add "AND Url LIKE @0" but it throws an error. Please refer the coding below.

        commandText = @"SELECT Pages.PageId, Url, Pages.DateCreated, Title, ContentId, Content, Published FROM Pages INNER JOIN PageContent ON Pages.PageId = PageContent.PageId WHERE Pages.PageId AND Url LIKE @0 IN (SELECT PageId FROM Pages ";
        if(!includeArchived) {
            commandText += "WHERE DateArchived IS NULL ";
        } 
        commandText += "ORDER BY PageId, Url DESC OFFSET @1 ROWS FETCH NEXT @2 ROWS ONLY) ORDER BY Pages.DateCreated DESC";
    

    Saturday, June 29, 2013 3:49 PM

All replies

  • User1713851783 posted

    I'm not sure I have understood your query.

    If you want to filter the record that have the PageIds returned by the query

    SELECT PageId FROM Pages ORDER BY PageId, Url DESC OFFSET @1 ROWS FETCH NEXT @2 ROWS ONLY

    and

    Url LIKE @0

    you could try to modify your code as follows

        commandText = @"SELECT Pages.PageId, Url, Pages.DateCreated, Title, ContentId, Content, Published FROM Pages
                INNER JOIN PageContent ON Pages.PageId = PageContent.PageId WHERE Pages.PageId IN (SELECT
                PageId FROM Pages ";
        if(!includeArchived) {
            commandText += "WHERE DateArchived IS NULL ";
        } 
        commandText += @"ORDER BY PageId, Url DESC OFFSET @1 ROWS FETCH NEXT @2 ROWS ONLY) AND Url LIKE @0 
                ORDER BY Pages.DateCreated DESC";



     

    Saturday, June 29, 2013 6:08 PM
  • User-821857111 posted

    Somewhere, you seem to have passed an array in as a parameter value. That's what is causing the error. 

    Saturday, June 29, 2013 6:38 PM
  • User1519602350 posted

    Now i am getting the below error,

    There was an error parsing the query. [ Token line number = 4,Token line offset = 82,Token in error = @0 ]

    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.SqlServerCe.SqlCeException: There was an error parsing the query. [ Token line number = 4,Token line offset = 82,Token in error = @0 ]

    Source Error:

    Line 33:     commandText += "ORDER BY PageId DESC OFFSET @1 ROWS FETCH NEXT @2 ROWS ONLY ) ORDER BY Pages.DateCreated DESC";
    Line 34: 
    Line 35: var pages = db.Query(commandText, searchWords, offset, pageSize).GroupBy(p => p.PageId); Line 36: 
    Line 37:     var draftActions = new List<string> {"\"Preview\""};

    Sunday, June 30, 2013 1:57 AM