Re: nvarchar parameter not truncated based on size of field

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

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
    ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... .NETCF 1.x - Truncates parameter automatically ... "William Vaughn" wrote: ... check the size of each string before assigning? ...
    (microsoft.public.sqlserver.ce)
  • Re: In-Progress Save
    ... "Graham Mayor" wrote: ... > Dim strFileA As String ... > Bill wrote: ...
    (microsoft.public.word.docmanagement)
  • Re: JobID, a pointer or just worthless?
    ... Bill thanks for sharing the code. ... >> ConnectedSubmit in fact returns the IDs in same format as the event ... >>> If the ConnectedSubmit returned a bool result then of course the only ... >>>>> Unique ID in this context is a string. ...
    (microsoft.public.win2000.fax)
  • Re: Fundamental Multi-table DataSet population
    ... William (Bill) Vaughn ... > to populating tables with related rows from a database. ... >> TSQL workaround, that would be helpful. ... >>> It's TSQL that balks at permitting you to pass a string into the IN ...
    (microsoft.public.dotnet.framework.adonet)
  • 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)