SqlCeCommand Parameters Bug?

[jonfroehlich]

The following SqlCeCommand throws an exception. Is this a bug or am I
doing something wrong?

SqlCeCommand sqlCmd = sqlConn.CreateCommand();
sqlCmd.CommandText = String.Format("INSERT INTO {0} (UserId, VersionId,
{1}, PropertyName, PropertyValue, PropertyValueType) VALUES (@userid,
@versionid, @headercolumnval, @propertyname, @propertyvalue,
@propertyvaluetype)", strTable, headerName);
String strValue = p.Value.ToString();
sqlCmd.Parameters.Add("@userid", SqlDbType.NVarChar, 255,
"UserId").Value = Globals.UserId;
sqlCmd.Parameters.Add("@versionid", SqlDbType.BigInt, 8,
"VersionId").Value = Globals.VersionId;
sqlCmd.Parameters.Add("@headercolumnval", SqlDbType.NVarChar, 255,
headerName).Value = headerColumnValue;
sqlCmd.Parameters.Add("@propertyname", SqlDbType.NVarChar, 255,
"PropertyName").Value = p.Key;
sqlCmd.Parameters.Add("@propertyvalue", SqlDbType.NVarChar, 4000,
"PropertyValue").Value = strValue;
sqlCmd.Parameters.Add("@propertyvaluetype", SqlDbType.NVarChar, 255,
"PropertyValueType").Value = p.Type.FullName;
sqlCmd.Transaction = sqlTransact;
sqlCmd.Prepare();
sqlCmd.ExecuteNonQuery();
sqlCmd.Dispose();

Throws the following exception at the sqlCmd.ExecuteNonQuery() line:

System.InvalidOperationException was unhandled
Message="@propertyvalue : String truncation: max=255, len=463,
value=<a string of 463 chars>."
StackTrace:
at System.Data.SqlServerCe.SqlCeCommand.FillParameterDataBindings()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
at MyExperience.IO.DatabasePopulator.InsertProperty()
at MyExperience.IO.DatabasePopulator.ParseTriggers()
at MyExperience.IO.DatabasePopulator.Populate()
at MyExperience.MyExperienceFramework.Initialize()
at MyExperience.Application.MainForm.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at OpenNETCF.Windows.Forms.Application2.RunMessageLoop()
at OpenNETCF.Windows.Forms.Application2.Run()
at MyExperience.Application.Program.Main()

However, if I change the line:

sqlCmd.Parameters.Add("@propertyvalue", SqlDbType.NVarChar, 4000,
"PropertyValue").Value = strValue;

to

sqlCmd.Parameters.Add("@propertyvalue", SqlDbType.NVarChar).Value =
strValue;

no exception is thrown and, better yet, the full amount of data
actually makes it into the database. That is, the value is not
truncated to the 255 length that Sql somehow thinks exists as a
constraint.

Note that my schema for this table looks like:
TableName:
TriggerProperties

Columns:
UserId (PK, nvarchar(255), not null)
VersionId (PK, nvarchar(255), not null)
TriggerName (PK, nvarchar(255), not null)
PropertyName (PK, nvarchar(255), not null)
PropertyValue (nvarchar(4000), not null)
PropertyValueType (nvarchar(255), not null)

[Graham McKechnie]

> sqlCmd.Parameters.Add("@propertyvalue", SqlDbType.NVarChar, 4000,

Too big.

"jonfroehlich" <jonfro...@gmail.com> wrote in message
news:1155841823.3...@m79g2000cwm.googlegroups.com...

[jonfroehlich]

Hmm, I don't think that's it. According to the Sql Server 2005 Mobile
Edition Books Online, the nvarchar data type can range in length from 1
to 4000 characters. In addition, note that the actual nvarchar value
that I'm inserting here is far less than that (the exception thrown
says len=463). I will check into it a little more and update the
thread.

[Graham McKechnie]

I just checked the Mobile docs and you are right. It was 255 for Sql Ce 2.0.

Are you sure you are not using Sql Ce 2.0 - the exception certainly seems to
indicate that 255 is the limit.

Sorry just read the last piece of your first post, you must be using Mobile.
Better wait on a MS answer.

Graham

"jonfroehlich" <jonfro...@gmail.com> wrote in message

news:1155922919.5...@m79g2000cwm.googlegroups.com...

[jonfroehlich]

Ah, it looks like SqlCeParameter forces the Size property to 255.
That's strange given the fact that the docs say, as previously
mentioned, that the nvarchar data type can range from 1 to 4000
characters.

However, bear in mind, that by simply avoiding setting the Size
property (either via the constructor or the property itself) allows one
to INSERT nvarchar data much larger than 255. In other words, it looks
like this "bug" is at the parameter level and not database level.

This test code:

for (int i = 0; i <= 500; i+=100){
SqlCeParameter param = new SqlCeParameter("@Test",
SqlDbType.NVarChar, i);
Debug.WriteLine(i + ": " + param.Size);

//Just in case the property acts differently, set it and print again
param.Size = i;
Debug.WriteLine(i + ": " + param.Size);
}

prints:

0: 0
0: 0
100: 100
100: 100
200: 200
200: 200
300: 255
300: 255
400: 255
400: 255
500: 255
500: 255

Any input from MS (or MVPs) on this? I'm far from being a seasoned C#
SQL developer so maybe I'm doing something wrong.

Thanks,

j

----
http://csharponphone.blogspot.com

[Graham McKechnie]

j,

Yes, certainly looks that way.

Good that you have a work around.

Graham

"jonfroehlich" <jonfro...@gmail.com> wrote in message

news:1155951700.0...@m73g2000cwd.googlegroups.com...