Hello,

I am trying to insert value in SQL Compact 3.5 SP1 using following code

HarvestEntity harvest = new HarvestEntity(); harvest.HarvestId = GUIDGenerator.NewGuid(); harvest.HarvestNumber = "23"; harvest.CountyFips = "51001"; harvest.HarvestStatus = "Test Status"; harvest.Save();

However I keep getting following error. I have tried this with another table and I get the same error. I can however update the existing values with issues.

I am using LLBLen Pro 2.6 (demo) and here are the runtime library versions:

SD.LLBLGen.Pro.DQE.SqlServerCE.CF35.dll - 2.6.0.0

and

SD.LLBLGen.Pro.ORMSupportClasses.CF35.dll - 2.6.0.0

Message: "VarChar"

Inner Exception: Could not evaluate expression

" at System.Data.SqlServerCe.SqlCeType.FromSqlDbType(SqlDbType type)\r\n at System.Data.SqlServerCe.SqlCeParameter.set_SqlDbType(SqlDbType value)\r\n at System.Data.SqlServerCe.SqlCeParameter..ctor(String parameterName, SqlDbType dbType, Int32 size, ParameterDirection direction, Boolean isNullable, Byte precision, Byte scale, String sourceColumn, DataRowVersion sourceVersion, Object value)\r\n at System.Data.SqlServerCe.SqlCeParameter..ctor(String parameterName, SqlDbType dbType, Int32 size, Boolean isNullable, Byte precision, Byte scale, String sourceColumn, DataRowVersion sourceVersion, Object value)\r\n at SD.LLBLGen.Pro.DQE.SqlServer.SqlServerCeSpecificCreator.CreateParameter(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, ParameterDirection direction, Object valueToSet)\r\n at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateParameter(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, ParameterDirection direction)\r\n at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSingleTargetInsertDQ(IEntityFieldCore[] fields, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, Dictionary2& fieldToParameter)\r\n at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateInsertDQ(IEntityFieldCore[] fields, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse)\r\n at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateInsertDQ(IEntityFields fields, IDbConnection connectionToUse)\r\n at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.AddNew(IEntityFields fields, ITransaction containingTransaction)\r\n at Timmons.IFRIS.MobileBusinessObjects.EntityClasses.HarvestEntity.InsertEntity()\r\n at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase.CallInsertEntity()\r\n at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PersistQueue(List1 queueToPersist, Boolean insertActions, ITransaction transactionToUse, Int32& totalAmountSaved)\r\n at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase.Save(IPredicate updateRestriction, Boolean recurse)\r\n at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase.Save()\r\n at MobilePrototypeLLBLGen.Form1.mnuInsert_Click(Object sender, EventArgs e)\r\n at System.Windows.Forms.MenuItem.OnClick(EventArgs e)\r\n at System.Windows.Forms.Menu.ProcessMnuProc(Control ctlThis, WM wm, Int32 wParam, Int32 lParam)\r\n at System.Windows.Forms.Form.WnProc(WM wm, Int32 wParam, Int32 lParam)\r\n at System.Windows.Forms.Control._InternalWnProc(WM wm, Int32 wParam, Int32 lParam)\r\n at Microsoft.AGL.Forms.EVL.EnterMainLoop(IntPtr hwnMain)\r\n at System.Windows.Forms.Application.Run(Form fm)\r\n at MobilePrototypeLLBLGen.Program.Main()\r\n"

Hi

Please could you post the table definition ? Is it possible that the table definition has changed so you have a mis-match between the table data type and the code ?

Thanks

Matt

Here is my table definition:

CREATE TABLE [gisadmin].[tbl_harvest]( [harvest_id] [uniqueidentifier] NOT NULL, [harvest_driving_directions] varchar NULL, [harvest_number] varchar NOT NULL, [county_fips] char NOT NULL, [harvest_start_date] [datetime] NOT NULL, [harvest_status] varchar NOT NULL, [harvest_method] varchar NULL, [harvest_salvage] [bit] NULL, [harvest_seed_tree_law] [bit] NULL, [harvest_comments] varchar NULL, [harvest_nonsilv_sale_acres] [float] NULL, [harvest_silv_sale_acres] [float] NULL, [harvest_percent_softwood] [int] NULL, [harvest_recommended_by] varchar NULL, [harvest_written_plan] [bit] NULL, [harvest_prof_advice_used] [bit] NULL, [logger_id] [uniqueidentifier] NULL, [harvest_created_emp_id] [int] NULL, [harvest_created_date] [datetime] NULL, [harvest_edited_emp_id] [int] NULL, [harvest_edited_date] [datetime] NULL, [harvest_update_originator_id] [int] NULL DEFAULT ((0)), [harvest_update_timestamp] [timestamp] NOT NULL, [harvest_create_timestamp] [bigint] NULL DEFAULT (@@dbts+(1)),

I have commented out last three fields in the persistenceInfo class since I do not want them on my SDF.

Here is my persistenceInfo class:

   base.AddElementMapping("HarvestEntity", ".//Temp//MobileIFRIS.sdf", @"gisadmin", "gisadmin.tbl_harvest", 24);
        base.AddElementFieldMapping( "HarvestEntity", "HarvestId", "harvest_id", false, (int)SqlDbType.UniqueIdentifier, 0, 0, 0, false, "", null, typeof(System.Guid), 0 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestDrivingDirections", "harvest_driving_directions", true, (int)SqlDbType.VarChar, 250, 0, 0, false, "", null, typeof(System.String), 1 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestNumber", "harvest_number", false, (int)SqlDbType.VarChar, 10, 0, 0, false, "", null, typeof(System.String), 2 );
        base.AddElementFieldMapping( "HarvestEntity", "CountyFips", "county_fips", false, (int)SqlDbType.Char, 5, 0, 0, false, "", null, typeof(System.String), 3 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestStartDate", "harvest_start_date", false, (int)SqlDbType.DateTime, 0, 0, 0, false, "", null, typeof(System.DateTime), 4 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestStatus", "harvest_status", false, (int)SqlDbType.VarChar, 30, 0, 0, false, "", null, typeof(System.String), 5 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestMethod", "harvest_method", true, (int)SqlDbType.VarChar, 30, 0, 0, false, "", null, typeof(System.String), 6 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestSalvage", "harvest_salvage", true, (int)SqlDbType.Bit, 0, 0, 0, false, "", null, typeof(System.Boolean), 7 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestSeedTreeLaw", "harvest_seed_tree_law", true, (int)SqlDbType.Bit, 0, 0, 0, false, "", null, typeof(System.Boolean), 8 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestComments", "harvest_comments", true, (int)SqlDbType.VarChar, 500, 0, 0, false, "", null, typeof(System.String), 9 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestNonsilvSaleAcres", "harvest_nonsilv_sale_acres", true, (int)SqlDbType.Float, 0, 0, 38, false, "", null, typeof(System.Double), 10 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestSilvSaleAcres", "harvest_silv_sale_acres", true, (int)SqlDbType.Float, 0, 0, 38, false, "", null, typeof(System.Double), 11 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestPercentSoftwood", "harvest_percent_softwood", true, (int)SqlDbType.Int, 0, 0, 10, false, "", null, typeof(System.Int32), 12 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestRecommendedBy", "harvest_recommended_by", true, (int)SqlDbType.VarChar, 30, 0, 0, false, "", null, typeof(System.String), 13 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestWrittenPlan", "harvest_written_plan", true, (int)SqlDbType.Bit, 0, 0, 0, false, "", null, typeof(System.Boolean), 14 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestProfAdviceUsed", "harvest_prof_advice_used", true, (int)SqlDbType.Bit, 0, 0, 0, false, "", null, typeof(System.Boolean), 15 );
        base.AddElementFieldMapping( "HarvestEntity", "LoggerId", "logger_id", true, (int)SqlDbType.UniqueIdentifier, 0, 0, 0, false, "", null, typeof(System.Guid), 16 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestCreatedEmpId", "harvest_created_emp_id", true, (int)SqlDbType.Int, 0, 0, 10, false, "", null, typeof(System.Int32), 17 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestCreatedDate", "harvest_created_date", true, (int)SqlDbType.DateTime, 0, 0, 0, false, "", null, typeof(System.DateTime), 18 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestEditedEmpId", "harvest_edited_emp_id", true, (int)SqlDbType.Int, 0, 0, 10, false, "", null, typeof(System.Int32), 19 );
        base.AddElementFieldMapping( "HarvestEntity", "HarvestEditedDate", "harvest_edited_date", true, (int)SqlDbType.DateTime, 0, 0, 0, false, "", null, typeof(System.DateTime), 20 );
        //base.AddElementFieldMapping( "HarvestEntity", "HarvestUpdateOriginatorId", "harvest_update_originator_id", true, (int)SqlDbType.Int, 0, 0, 10, false, "", null, typeof(System.Int32), 21 );
        //base.AddElementFieldMapping( "HarvestEntity", "HarvestUpdateTimestamp", "harvest_update_timestamp", false, (int)SqlDbType.Timestamp, 0, 0, 0, false, "", null, typeof(System.Byte[]), 22 );
        //base.AddElementFieldMapping( "HarvestEntity", "HarvestCreateTimestamp", "harvest_create_timestamp", true, (int)SqlDbType.BigInt, 0, 0, 19, false, "", null, typeof(System.Int64), 23 );

Appreciate your help

Is there a particular reason

[county_fips] char NOT NULL,

is a char rather than a varchar like all the other fields ?

What happens if you make this a varchar as well ?

Matt

I changed the type in SDF and PersistenceInfo class but got the same error.

can it be due to guids?

CountyFips and HarvestStatus are foreign keys but I am just setting the properties and not doing any thing special with those

Maybe the persistenceInfo has been modified wrongly.

Just for testing, would you please re-genearte the code again without altering the persistenceInfo class and re-test the issue.

I created a new project and did not alter PersistenceInfo class at all but I am still getting the same error

Also please note that I am using SQL CE 3.5 SP1. Does that make a difference?

Also I can insert the values fine if I use inline SQL.

Thanks

[harvest_number] varchar NOT NULL,

varchar is an unsupported type on CE, you can only use nvarchar, ntext etc., not varchar, text. Though it should be converted by the DQE.

How did you create the CE desktop database? This is CE Desktop or CE compact for CF.NET?

The way to do this is: - create a sqlserver 2005/8 database schema - use that schema to retrieve the meta data from - generate code with that project - create an SDF file from that sqlserver schema, but be aware that you convert the types to nvarchar etc. - use the sqlserver compatibility setting in llblgen pro (in your config file) to make sure the query engine (DQE) will produce the right SQL.

You shouldn't modify generated code by hand, that's unnecessary. Your persistence info suggests you obtained the meta-data from the sdf file.

This is the CE compact for CF.NET.

I have created the SDF using Microsoft Synchronization Services for ADO.NET.

However I could not specify the sql server compatibility in my DbUtils class since I could not find the property there. I have hard coded the SQL connection string in the DbUtils class and so have not been using the App.config class.

Where should I specify the sql server compatibility in the code? Is it SQl server 2000 by default?

Thanks

Apurv

The adapter model is giving me the same error.

Also Microsoft Sync Services automatically convert the varchar datatype to nvarchar datatype in SDF.

yay so I could make it work!! I had to go into the persistenceInfo class and change the datatype there from varchar to nvarchar to make it work!

Thanks for your pointer!!

AGoyal wrote:

yay so I could make it work!! I had to go into the persistenceInfo class and change the datatype there from varchar to nvarchar to make it work!

Thanks for your pointer!!

That shouldn't be necessary. You should change the db type in the schema you use to create the project: there you should change varchar to nvarchar. The problem is that if you change it in the generated code, next time you re-generate it's overwritten.

Still strange you had to do it that way btw, the query engine changes varchar to nvarchar automatically...

I see that there's a bug in the SqlServer CE DQE: it only converts varchar to ntext if the length is > 8000 but it doesn't convert to nvarchar. Will fix that (so you don't have to alter the code manually).

Please use the attached DQE dlls in your application (I've attached CF.NET 2.0 and CF.NET 3.5 versions) so 'varchar' parameters in queries are automatically converted to 'nvarchar' parameters, so you don't have to convert generated code nor your schema.

Thank you!

However I cannot change the data type in schema because on the SQL server side, I am using varchar data type and am using this schema for llblgen.

I then use Microsoft sync services for ADO.Net to generate SDF and all the data types are automatically changed to nvarchar by the sync services. Since I could not point llblgen to SDF to generate projects, I will always have to change the generated code to change data types. Am I correct?

AGoyal wrote:

Thank you!

However I cannot change the data type in schema because on the SQL server side, I am using varchar data type and am using this schema for llblgen.

I then use Microsoft sync services for ADO.Net to generate SDF and all the data types are automatically changed to nvarchar by the sync services. Since I could not point llblgen to SDF to generate projects, I will always have to change the generated code to change data types. Am I correct?

No, as you can use the fixed DQE I attached to my previous post, which does the conversion from varchar typed parameters to nvarchar typed parameters for you so you don't have to change anything.

Does the fixed DQE work for you?

Awesome!! Testing it now..!!

Should I replace the DQE in program files with the new one?

It worked !!

AGoyal wrote:

It worked !!

pfew

Thank you-Appreciate your help very much!!!

LLBLGen really provides us the ONLY option which can be used on compact framework!!