CODEWITHSUNDEEP
servicestackormlite-servicestack
jbrabant
jbrabant

Reputation: 345

Misleading SQL Exception Text cannot be compared

I get that exception when OrmLite make the following call :

return db.Select<T>(x => x.Name == name && x.PuId == puId).FirstOrDefault();

Exception :"System.Data.SqlClient.SqlException (0x80131904): The text, ntext, and image data types cannot be compared or sorted, except when usingIS NULL or LIKE operator.

The name is a String and puid is an Int. The type is mapped to a SQL Table which has no columns of type Text, NText or image at all.

When I look at the LastSQLStatement and executes it from SQL Server, it works. When I replace the call with the following, it works fine too

return db.SqlList<T>("SELECT Event_Id, Event_Num, Entry_On, Timestamp, Applied_Product, Source_Event, Event_Status, Confirmed, User_Id, Extended_Info, Comment_Id, PU_Id FROM Events WHERE ((Event_Num = @Event_Num) AND (PU_Id = @PU_Id))",new {Event_Num= "16J2730", PU_Id=91}).FirstOrDefault();

An old version of my service works fine with the same code. Using the latest version of servicestack and ormlite, I am now getting that weird issue...

Is the latest version of OrmLite has issues with old version of SQL Server? We are still on a 2000 version. I used both SQLServer Dialect without luck.

Anyone have an idea?

Here is what Mythz requested

        public ProficyEvent TestGetByName(string name, int puId, bool withDetails = false)
    {
        using (IDbConnection db = OpenDBConnection())
        {
            try
            {                   
                return db.Select<ProficyEvent>(x => x.Name == name && x.PuId == puId).FirstOrDefault();             
            }
            catch (Exception ex)
            {
                log.ErrorFormat("Error querying database: {0}", ex.ToString());
                throw;
            }
        }           
    }

[Alias("Events")]
public class ProficyEvent:IProficyPuEntity
{       
    [AutoIncrement]
    [Alias("Event_Id")]
    public int Id { get; set; }     
    [Ignore]
    public string Code { get; set; }
    [Ignore]
    public string Desc { get; set; }
    [Alias("Event_Num")]
    public string Name { get; set; }
    [Alias("Entry_On")]
    public DateTime? LastModified { get; set; }
    [Ignore]
    public string LastModifiedBy { get; set; }      
    public DateTime? Timestamp { get; set; }
    [Alias("Applied_Product")]
    public int? AppliedProductId { get; set; }
    [Ignore]
    public string AppliedProductName { get; set; }
    [Ignore]
    public int OriginalProductId { get; set; }
    [Ignore]
    public string OriginalProductName { get; set; }
    [Alias("Source_Event")]
    public int? SourceEvent { get; set; }
    [Alias("Event_Status")]
    public int? EventStatus { get; set; }
    [Ignore]
    public string EventStatusName { get; set; }
    public int Confirmed { get; set; }
    [Alias("User_Id")]
    public int UserId { get; set; }
    [Alias("Extended_Info")]
    public string ExtendedInfo { get; set; }
    [Ignore]
    public string Comment { get; set; }
    [Alias("Comment_Id")]
    public int? CommentId { get; set; }     
    [Ignore]
    public IEnumerable<ProficyTest> TestResults { get; set; }
    [Alias("PU_Id")]
    public int PuId { get; set; }
    [Ignore]
    public string UnitName { get; set; }
    [Ignore]
    public string LineName { get; set; }
}

CREATE TABLE [dbo].[Events](
[Event_Id] [int] IDENTITY(1,1) NOT NULL,
[Event_Num] [Varchar_Event_Number] NOT NULL,
[PU_Id] [int] NOT NULL,
[TimeStamp] [datetime] NOT NULL,
[Applied_Product] [int] NULL,
[Source_Event] [int] NULL,
[Event_Status] [tinyint] NULL,
[Confirmed] [bit] NOT NULL DEFAULT (0),
[User_Id] [int] NULL,
[Comment_Id] [int] NULL,
[Entry_On] [datetime] NULL,
[Testing_Status] [int] NULL DEFAULT (1),
[Event_Subtype_Id] [int] NULL,
[Start_Time] [Datetime_ComX] NULL,
[Extended_Info] [varchar](255) NULL,
[Converted_Timestamp] [datetime] NULL,
[Orientation_X] [float] NULL,
[Orientation_Y] [float] NULL,
[Orientation_Z] [float] NULL,
[Final_Dimension_Z] [real] NULL,
[Final_Dimension_A] [real] NULL,
[Initial_Dimension_A] [real] NULL,
[Final_Dimension_X] [real] NULL,
[Final_Dimension_Y] [real] NULL,
[Initial_Dimension_Y] [real] NULL,
[Initial_Dimension_Z] [real] NULL,
[Initial_Dimension_X] [real] NULL,
[Conformance] [tinyint] NULL,
[Testing_Prct_Complete] [tinyint] NULL)

CREATE TYPE [dbo].[Varchar_Event_Number] FROM [varchar](25) NOT NULL
CREATE TYPE [dbo].[Datetime_ComX] FROM [datetime] NOT NULL

Upvotes: 2

Views: 81

Answers (1)

mythz
mythz

Reputation: 143284

To answer this question, this runs without issue on a recent version of SQL Server.

The major change to OrmLite that would have likely affected this behavior was the change to use Parameterized SQL Expressions from in-line SQL params.

You can use in-line SQL Params using OrmLite's legacy APIs or by dropping down to use Custom SQL APIs.

Upvotes: 1

Related Questions