Re: nvarchar parameter not truncated based on size of field

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

BHOM but I'll ask the team. Perhaps someone in India will know.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"rh" <rh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E4558BDA-9C61-43E4-B07F-645E09D8B627@xxxxxxxxxxxxxxxx
Bill, Thanks for the info. I feel like a celebrity just answered my
question!

It's still kind of bothering me that this functionality has changed since
.NetCF 1.0 (caught me off guard and almost went into production because I
had
no reason to believe the SqlCeParameter object would work differently than
before), so I did some testing on other versions of the framework and this
is
what I found:

.NETCF 1.x - Truncates parameter automatically
.NETCF 2.0 - Raises error from engine
.NET 1.1 (full framework) - Truncates parameter automatically
.NET 2.0 (full framework) - Truncates parameter automatically
.NET 2.0 (full framework - SqlCe3.1 client library) - Raises error from
engine

So this behavior seems to only be in the SqlCe3.x library - would there be
any explanation for why this library behaves differently?


"William (Bill) Vaughn" wrote:

While I know Ginny is right, I find it tiresome as well. Ah, yes. I
recommend that all values passed as parameters be pre-validated. This
means
more than matching the datatype. It means range-checking, validation
against
business rules and verifying the length of strings. It's a lot easier to
perform these functions before the engine chokes on the parameter or
value
passed.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"rh" <rh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2C899377-4CF6-47F6-B1FB-25B2B8AB3E35@xxxxxxxxxxxxxxxx
I guess I've gotten used to the old (lazy) way and prefer that. Anyway,
so
what's the best practice when assigning strings to parameters? Does
everyone
check the size of each string before assigning? I've never seen an
example
done that way. Or do people just let the error happen and report back
to
the
user? Or something else better?

Thanks for the response Ginny.


"Ginny Caughey [MVP]" wrote:
My opinion is that the current behavior is correct.

FWIW,

--
Ginny


"rh" <rh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7973F761-2780-41FF-B761-CF2640FF0AFB@xxxxxxxxxxxxxxxx
We just migrated an app from .NETCF 1.0 to .NETCF 2.0 and SQL Server
2005
Compact Edition (3.1?) and I'm running into a problem where the
nvarchar
parameters aren't truncated based on the size of the field. For
example:

Dim cmd As SqlCeCommand = New SqlCeCommand(sSql)
cmd.Parameters.Add("@p1", SqlDbType.NVarChar, 25, "Note").Value =
sVal

If the length of sVal is 26, I get an error like:

String truncation: max=25, len=26, value=whatever the value was...

In previous versions of .NETCF/SqlCe, this didn't happen. The string
would
just automatically get truncated and the truncated string would
successfully
be inserted into the database.

I guess I can see arguments both ways for this but I'm trying to
find
out
what the expected bahavior is here so I can properly assign the
parameters
in
my queries without throwing exceptions or find out if there is some
new
property on the command or parameter objects to automatically
truncated
values when they are too large.

Thanks.






.

Relevant Pages

  • Re: nvarchar parameter not truncated based on size of field
    ... Bill, Thanks for the info. ... It's still kind of bothering me that this functionality has changed since ... ..NETCF 1.x - Truncates parameter automatically ... check the size of each string before assigning? ...
    (microsoft.public.sqlserver.ce)
  • Re: sed issue
    ... the log file, it truncates the file after the first line, and I have been ... unable to get it to read past the last character of the first line. ... 0D, or to be more exact, the string with which sed seems to be having ... To get rid of control chars leaving only tabs and newlines, ...
    (comp.unix.shell)
  • Re: sed issue
    ... the log file, it truncates the file after the first line, and I have been ... This seems to be the only instance of the problematic string, but it is in every one of the expect log output files. ... How can I remove the offending characters so I can have sed go about the business of parsing the rest of the file for the strings in which I am interested? ... To get rid of control chars leaving only tabs and newlines, ...
    (comp.unix.shell)
  • sed issue
    ... the log file, it truncates the file after the first line, and I have been ... unable to get it to read past the last character of the first line. ... 0D, or to be more exact, the string with which sed seems to be having the ... one of the expect log output files. ...
    (comp.unix.shell)
  • Re: SqlParameter.Size - When to use?
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... Is there an easy way to have the framework determine what the size of the parameter actually is for non-fixed length items (String, text, etc)? ...
    (microsoft.public.dotnet.framework.adonet)