Problem w/bulk updates using DataAdapter & SqlParameterCollection.

[MJB]

I’m doing bulk updates to my database via a dataset by using the
SqlDataAdapter.Update method. The problem is there seems to mysterious
string padding showing up in the database entry. For example here are the
parameters I am using.

adapter.InsertCommand.Parameters.Add("@MachineName", SqlDbType.NVarChar,
256, "MachineName");

adapter.UpdateCommand.Parameters.Add("@MachineName", SqlDbType.NVarChar,
256, "MachineName");

If I create a row in my dataset like so:
MachineName = “MYMACHINEâ€

it shows up in the database as
“MYMACHINE___________________“ (exactly 256 characters in length).

I have checked the incoming data and it does not have this padding. Does
anyone have an explanation on this or a solution?

Thanks in advance,
MJB

 

[Mary Chipman [MSFT]]

Is the database column itself defined as nchar or nvarchar? If the
former, then that would explain the behavior. See
http://msdn.microsoft.com/en-us/library/ms186939.aspx for more
information.

--Mary

 

[MJB]

nchar it was. Wow, major oversight on my part. Thanks to you I only wasted
half a day.....

Thanks a million

 

[William \(Bill\) Vaughn]

Ah, perhaps I can save you some more time. Consider that using the
DataAdapter to bulk load data is like using a pitchfork to load gravel. I
suggest using the SqlBulkCopy class to import your data. Google this class
and look for the articles and references I've made to this over the last
couple of years... It's also in my book.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com/blog/billva
____________________________________________________________________________________________