As somebody posted another issue here

http://llblgen.com/TinyForum/Messages.aspx?ThreadID=12054

I got exactly the same problem with him. When adding a note field in database using LLBLGen, if length of this field reaches 256 characters, an exception will be fired. But when I used a manual way to add the same field (using SqlCeCommand.ExecuteNonQuery() function), it worked!

This is what LLBLGen generated

base.AddElementFieldInfo("UserEntity", "Note", typeof(System.String), false, false, false, true,  (int)UserFieldIndex.Note, 1500, 0, 0);

I did try this

int maxLength = UserEntity.Fields["Note"].MaxLength;

and it said 1500.

I have a friend who has worked with SQL CE 3.1 since Jan 2007 and he told me it's possible to store up to 4000 characters in a nvarchar field.

Have you guys got any thought for this?

Arthur.

Which LLBLGen Pro runtime library version are you using?

I'm using version 2.5 Final.

That's not the runtime library version. Please check the following thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7725

Also 'an exception is fired' isn't helpful. What's the exact exception, and more importantly: what's the stacktrace? Also, please post the code snippet which caused this exception.

If possible, also the table DDL SQL.

You're not explaining if you're using CE on WinCE/pocketpc or on the desktop. This is important.

Oops, sorry! Here is all information you need:

• Runtime libraries:

+ SD.LLBLGen.Pro.DQE.SqlServerCE.NET20.dll version 2.5.0.0
   + SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll version 2.5.7.1129

• I am using CE on a desktop application.
• When the exception fired, received stacktrace is

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of an action query: @Memo : String truncation: max=255, len=1500, value='This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very l'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.InvalidOperationException: @Memo : String truncation: max=255, len=1500, value='This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very long long long line of text. This is a very l'.
   at System.Data.SqlServerCe.SqlCeCommand.FillParameterDataBindings()
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor, Boolean& isBaseTableCursor)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
   at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()

• And the code snippet that caused exception:

UserEntity entUser = new UserEntity();
entUser.Memo = txtMemo.Text;
entUser.Save();

the txtMemo variable is the name of a textbox. I hope these help you to understand the situation more easily.

Another couple of questions

Would you please provide the DDL SQL of the database table involved?

But when I used a manual way to add the same field (using SqlCeCommand.ExecuteNonQuery() function), it worked!

Would you please post that code? (using SqlCeCommand.ExecuteNonQuery())

The DDL SQL code

CREATE TABLE [User] (
    [UserID] INTEGER IDENTITY(1,1) NOT NULL,
    [UserName] NVARCHAR(10) NOT NULL,
    [Password] NVARCHAR(10) NOT NULL,
    [Memo] NVARCHAR(1500),
    CONSTRAINT [TUC_User] UNIQUE ([UserName], [Password])
)
GO

And the code which add data in database using SqlCeCommand:

SqlCeConnection con = new SqlCeConnection(ConnectionString);
string sql = "INSERT INTO User(UserName, Password, Memo) VALUES (' " + txtUserName.Text + "', '" + txtPassword.Text + "', '" + txtMemo.Text + "')";
con.Open();
SqlCeCommand comm = new SqlCeCommand(sql, con);
comm.ExecuteNonQuery();

And the code which add data in database using SqlCeCommand: Code: SqlCeConnection con = new SqlCeConnection(ConnectionString); string sql = "INSERT INTO User(UserName, Password, Memo) VALUES (' " + txtUserName.Text + "', '" + txtPassword.Text + "', '" + txtMemo.Text + "')"; con.Open(); SqlCeCommand comm = new SqlCeCommand(sql, con); comm.ExecuteNonQuery();

LLBLGen Pro uses Command parameters(in this case SqlCeParameter) to avoid sql injection, would you please use SqlCeParameters to pass the values in the above example.

Hi Walaa,

The exception fired when I used SqlCeParameter, here is the code:

SqlCeConnection con = new SqlCeConnection(ConnectionString);
con.Open();
SqlCeParameter paramUser = new SqlCeParameter("@username", SqlDbType.NVarChar, 10, "UserName");
paramUser.Value = txtUserName.Text;
SqlCeParameter paramPass = new SqlCeParameter("@password", SqlDbType.NVarChar, 10, "Password");
paramPass.Value = txtPassword.Text;
SqlCeParameter paramMemo = new SqlCeParameter("@memo", SqlDbType.NVarChar, 1500, "Memo");
paramMemo.Value = txtMemo.Text;
string sql = "INSERT INTO User(UserName, Password, Memo) VALUES (@username, @password, @memo)";
SqlCeCommand comm = new SqlCeCommand(sql, con);
SqlCeParameterCollection parameterCol = comm.Parameters;
parameterCol.Add(paramUser);
parameterCol.Add(paramPass);
parameterCol.Add(paramMemo);
comm.ExecuteNonQuery();

So, the problem is that the SqlCeParameter cannot work with a string longer than 255 character length. Do you have any suggestion for this?

Could you change the field to ntext ?

Yes, sure. But I just want to be sure that we did everything to get it work with nvarchar datatype... Is that the final solution for this?

As per the following link: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1797052&SiteID=17

I'm not sure that nText will solve this issue.

According to that thread, MS had a bug and fixed it in SQLServer CE .NET 3.5...

I then wonder: is it also fixed in .NET 2.0 SP1? Do you have .NET 2.0 SP1 installed? If not, could you check it to see if this fixes things? (that thread Walaa linked to suggests the size is always capped to 255 in parameters... )