Batch Insert Problem with SqlBulkCopy.

[EDN Admin]

Hi
i have access db as client db and sql server db as web db. all structures of two database is same except PKs, in sql server PK is uniqueidentifier and in access, PK is Text (with 255 max Length).

in client app i want to send some data from client to web(access to sql) via SqlBulkCopy class, to do this, i use this code to transfer data :

this.personsTableAdapter1.Fill(this.testDataSet1.Persons);

DataTable dtImported = this.testDataSet1.Persons.Clone();

// because PK dataType in sql server is uniqueidentifier, hence i convert PK column to guid
dtImported.Columns["PersonID"].DataType = typeof(Guid);
foreach (TestDataSet.PersonsRow row in this.testDataSet1.Persons.Rows)
{
Guid g = new Guid(row.PersonID);
dtImported.Rows.Add(new object[] { g, row.PersonName }); // Error occur
}

System.Data.SqlClient.SqlBulkCopy bc = new System.Data.SqlClient.SqlBulkCopy(Properties.Settings.Default.TestSqlConnectionString);
bc.DestinationTableName = "Persons";
bc.WriteToServer(dtImported);

but at runTime the following error show me in above line of code:

Cannot set column PersonID. The value violates the MaxLength limit of this column.

where does my problem and how to solve it ?
Thanks
this is my Signature

View the full article