The text, ntext, or image data type cannot be selected as DISTINCT.

[MayoorPatel]

Hi there this is the error im getting.
when i try to run my asp.net application.

Stack Trace: 


[SqlException: The text, ntext, or image data type cannot be selected as DISTINCT.]
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742
   System.Data.SqlClient.SqlCommand.ExecuteScalar() +177
   Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteScalar(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) +78
   Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteScalar(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) +88
   DCA.TribunalsService.LandsAdjudicator.Data.DecisionData.SearchPaged(SearchCriteria criteria, Int32 page, Int32 size, Int32& resultsCount) in C:\Documents and Settings\TSDATABASE\My Documents\Visual Studio Projects\Lands\LandsData\DecisionData.vb:221
   DCA.TribunalsService.LandsAdjudicator.Business.Decision.SearchPaged(SearchCriteria criteria, Int32 page, Int32 size, Int32& resultsCount) in C:\Documents and Settings\TSDATABASE\My Documents\Visual Studio Projects\Lands\LandsBusiness\Decision.vb:10
   DCA.TribunalsService.LandsAdjudicator.Web.Search.PopulateGrid() in c:\inetpub\[URL unfurl="true"]wwwroot\LandsAdjudicator\Aspx\Default.aspx.vb:107[/URL]
   DCA.TribunalsService.LandsAdjudicator.Web.Search.Page_PreRender(Object sender, EventArgs e) in c:\inetpub\[URL unfurl="true"]wwwroot\LandsAdjudicator\Aspx\Default.aspx.vb:95[/URL]
   System.Web.UI.Control.OnPreRender(EventArgs e) +67
   System.Web.UI.Control.PreRenderRecursiveInternal() +62
   System.Web.UI.Page.ProcessRequestMain() +1499

line 221 in decisiondata is

    resultsCount = CInt(SqlHelper.ExecuteScalar(Configuration.SqlConnectionString(), CommandType.Text, sqlRowCount, parameters))

and line 10 in decision.vb is

 Return data.SearchPaged(criteria, page, size, resultsCount)

I can post up the whole functions if anyone wants to see them?

Cheers for any help!

Mayoor

 

[ca8msm]

The text, ntext, or image data type cannot be selected as DISTINCT

And are you doing this in your SQL Statement?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[MayoorPatel]

Yes here is the whole searched page function wich shows the distinct setting

Public Function SearchPaged(ByVal criteria As SearchCriteria, ByVal page As Integer, _
                                ByVal size As Integer, ByRef resultsCount As Integer) As DataSet

        Dim parameters() As SqlParameter = {New SqlParameter("@CategoryID", SqlDbType.Int), _
                                            New SqlParameter("@SubCategoryID", SqlDbType.Int), _
                                            New SqlParameter("@DecisionDate", SqlDbType.DateTime), _
                                            New SqlParameter("@FromDate", SqlDbType.DateTime), _
                                            New SqlParameter("@ToDate", SqlDbType.DateTime), _
                                            New SqlParameter("@Appellant", SqlDbType.VarChar, 50), _
                                            New SqlParameter("@Respondent", SqlDbType.VarChar, 50), _
                                            New SqlParameter("@Year", SqlDbType.VarChar, 4), _
                                            New SqlParameter("@CaseNo", SqlDbType.VarChar, 5), _
                                            New SqlParameter("@Prefix", SqlDbType.VarChar, 5), _
                                            New SqlParameter("@CommissionerID", SqlDbType.Int, 5), _
                                            New SqlParameter("@TitleNo", SqlDbType.VarChar, 8) _
                                            }

        'left join commissionerjudgmentmap cjm on j.[id] = cjm.judgment_id
        'left join commissioner cm on cjm.commissioner_id = cm.[id]
        Dim tableJudgment As FromTerm = FromTerm.Table("judgment", "j")
        Dim tableSubCategory As FromTerm = FromTerm.Table("subcategory", "s")
        Dim tableCategory As FromTerm = FromTerm.Table("category", "c")
        Dim tableCategory2 As FromTerm = FromTerm.Table("category", "c2")
        Dim tableSubCategory2 As FromTerm = FromTerm.Table("subcategory", "s2")
        Dim tableCommissionerJudgmentMap As FromTerm = FromTerm.Table("commissionerjudgmentmap", "cjm")
        Dim tableCommissioner As FromTerm = FromTerm.Table("commissioner", "cm")

        Dim query As New SelectQuery
        query.Distinct = True
        query.Columns.Add(New SelectColumn("id", tableJudgment, "judgmentid"))
        query.Columns.Add(New SelectColumn("decision_datetime", tableJudgment))
        query.Columns.Add(New SelectColumn("file_no_1", tableJudgment, "prefix"))
        query.Columns.Add(New SelectColumn("file_no_2", tableJudgment, "casenumber"))
        query.Columns.Add(New SelectColumn("file_no_3", tableJudgment, "year"))
        query.Columns.Add(New SelectColumn("applicant", tableJudgment, "applicant"))
        query.Columns.Add(New SelectColumn("respondent", tableJudgment, "respondent"))
        query.Columns.Add(New SelectColumn("title_no", tableJudgment, "title_no"))
        query.Columns.Add(New SelectColumn("is_published", tableJudgment))
        query.Columns.Add(New SelectColumn("description", tableSubCategory, "subcategory"))
        query.Columns.Add(New SelectColumn("description", tableCategory, "category"))
        query.FromClause.BaseTable = tableJudgment
        query.FromClause.Join(JoinType.Inner, tableJudgment, tableSubCategory, "main_subcategory_id", "id")
        query.FromClause.Join(JoinType.Inner, tableSubCategory, tableCategory, "parent_num", "num")
        query.FromClause.Join(JoinType.Left, tableJudgment, tableSubCategory2, "sec_subcategory_id", "id")
        query.FromClause.Join(JoinType.Left, tableSubCategory2, tableCategory2, "parent_num", "num")
        query.FromClause.Join(JoinType.Left, tableJudgment, tableCommissionerJudgmentMap, "id", "judgment_id")
        query.FromClause.Join(JoinType.Left, tableCommissionerJudgmentMap, tableCommissioner, "commissioner_id", "id")

        ' Category
        If criteria.CategoryID <> -1 Then
            Dim group As New WhereClause(WhereClauseRelationship.Or)
            group.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("num", tableCategory), SqlExpression.Parameter("@CategoryID"), CompareOperator.Equal))
            group.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("num", tableCategory2), SqlExpression.Parameter("@CategoryID"), CompareOperator.Equal))
            query.WherePhrase.SubClauses.Add(group)
            parameters(0).Value = criteria.CategoryID
        End If

        ' Subcategory
        If criteria.SubCategoryID <> -1 Then
            Dim group As New WhereClause(WhereClauseRelationship.Or)
            group.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("id", tableSubCategory), SqlExpression.Parameter("@SubCategoryID"), CompareOperator.Equal))
            group.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("id", tableSubCategory2), SqlExpression.Parameter("@SubCategoryID"), CompareOperator.Equal))
            query.WherePhrase.SubClauses.Add(group)
            parameters(1).Value = criteria.SubCategoryID
        End If

        'Decision Date
        If Not criteria.DecisionDate.Equals(DateTime.MinValue) Then
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("decision_datetime", tableJudgment), SqlExpression.Parameter("@DecisionDate"), CompareOperator.Equal))
            parameters(2).Value = criteria.DecisionDate
        End If

        ' From Date
        If Not criteria.FromDate.Equals(DateTime.MinValue) Then
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("decision_datetime", tableJudgment), SqlExpression.Parameter("@FromDate"), CompareOperator.GreaterOrEqual))
            parameters(3).Value = criteria.FromDate
        End If

        ' To Date
        If Not criteria.ToDate.Equals(DateTime.MinValue) Then
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("decision_datetime", tableJudgment), SqlExpression.Parameter("@ToDate"), CompareOperator.LessOrEqual))
            parameters(4).Value = criteria.ToDate
        End If

        ' Applicant
        If Not criteria.Applicant.Equals(String.Empty) Then
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("applicant", tableJudgment), SqlExpression.Parameter("@Appellant"), CompareOperator.Like))
            parameters(5).Value = String.Concat("%", criteria.Applicant, "%")
        End If

        ' Respondent
        If Not criteria.Respondent.Equals(String.Empty) Then
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("respondent", tableJudgment), SqlExpression.Parameter("@Respondent"), CompareOperator.Like))
            parameters(6).Value = String.Concat("%", criteria.Respondent, "%")
        End If

        ' Year
        If Not criteria.Year.Equals(String.Empty) Then
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("file_no_3", tableJudgment), SqlExpression.Parameter("@Year"), CompareOperator.Equal))
            parameters(7).Value = criteria.Year
        End If

        ' CaseNo
        If Not criteria.CaseNo.Equals(String.Empty) Then
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("file_no_2", tableJudgment), SqlExpression.Parameter("@CaseNo"), CompareOperator.Equal))
            parameters(8).Value = criteria.CaseNo
        End If

        ' Prefix
        If Not criteria.Prefix.Equals(String.Empty) Then
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("file_no_1", tableJudgment), SqlExpression.Parameter("@Prefix"), CompareOperator.Equal))
            parameters(9).Value = criteria.Prefix
        End If

        'Commissioner
        If criteria.CommissionerID <> -1 Then
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("id", tableCommissioner), SqlExpression.Parameter("@CommissionerID"), CompareOperator.Equal))
            parameters(10).Value = criteria.CommissionerID
        End If

        ' Title No
        If Not criteria.TitleNo.Equals(String.Empty) Then
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("title_no", tableJudgment), SqlExpression.Parameter("@TitleNo"), CompareOperator.Like))
            parameters(11).Value = String.Concat("%", criteria.TitleNo, "%")
        End If

        ' IsPublished
        If criteria.IsPublished Then
            query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("is_published", tableJudgment), SqlExpression.Number(1), CompareOperator.Equal))
        End If

        ' order by direction
        Dim direction As OrderByDirection
        If criteria.SortDirection = "ASC" Then
            direction = OrderByDirection.Ascending
        Else
            direction = OrderByDirection.Descending
        End If

        ' order col
        Dim table As FromTerm
        If criteria.SortColumn = "category" Then
            table = tableCategory
            criteria.SortColumn = "description"
        End If
        If criteria.SortColumn = "subcategory" Then
            table = tableSubCategory
            criteria.SortColumn = "description"
        End If

        query.OrderByTerms.Add(New OrderByTerm(criteria.SortColumn, table, direction))

        If criteria.SortColumn = "file_no_1" Then
            query.OrderByTerms.Add(New OrderByTerm("file_no_2", table, direction))
            query.OrderByTerms.Add(New OrderByTerm("file_no_3", table, direction))
        End If

        Dim renderer As New SqlServerRenderer
        Dim sqlRowCount As String = renderer.RenderRowCount(query)

        resultsCount = CInt(SqlHelper.ExecuteScalar(Configuration.SqlConnectionString(), CommandType.Text, sqlRowCount, parameters))
        Dim sql As String = New SqlServerRenderer().RenderPage(page - 1, size, resultsCount, query)
        Dim ds As DataSet = SqlHelper.ExecuteDataset(Configuration.SqlConnectionString(), CommandType.Text, sql, parameters)

        Return ds
    End Function

 

[ca8msm]

So, the database is telling you that you can't have a DISTINCT on a text, ntext, or image data type and you are saying that you do have a DISTINCT on this field. Is that correct?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[MayoorPatel]

Indeed!

 

[iaresean]

I would ask this question in the SQL server forum. It isn't really an ASP.NET issue but rather a SQL SERVER issue. They could probably give you some tips on overcoming this problem.

I wouldn't paste the code in the forum, rather a question of how you could get around the constraint against using distinct against a text-type field.

Regards;

Sean.

 

[ca8msm]

If the database doesn't support it but you are attempting it, then it's bound to error! You'll have to find some other way of getting the results you want.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[chrissie1]

One more reason why people don't like text,ntext or image. And why they say you should better of keeping those kind of fiels in a separate table/object.

I think this problem could be solved in sql-server 2005 but I can't be sure.

And BTW triggers don't like them either.



Christiaan Baes
Belgium

"My new site" - Me

 

[SQLDenis]

>>I think this problem could be solved in sql-server 2005 but I can't be sure.


Yes VARCHAR(MAX) would be a good candidate

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/