IUSR
asked on
sqlbulkcopy error
Hi,
would you please help me out with this problem, i already stuck for couple days, any help will be greatly appreciate...
i'm working on a webpage with a temp datatable (one row and 30 columns), i try use sqlbulkcopy to insert to my sql table but with the following error:
System.InvalidOperationExc eption: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. ---> System.InvalidOperationExc eption: String or binary data would be truncated. at System.Data.SqlClient.SqlB ulkCopy.Co nvertValue (Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlB ulkCopy.Co nvertValue (Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed) at System.Data.SqlClient.SqlB ulkCopy.Re adWriteCol umnValueAs ync(Int32 col) at System.Data.SqlClient.SqlB ulkCopy.Co pyColumnsA sync(Int32 col, TaskCompletionSource`1 source) at System.Data.SqlClient.SqlB ulkCopy.Co pyRowsAsyn c(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource`1 source) at System.Data.SqlClient.SqlB ulkCopy.Co pyBatchesA syncContin ued(BulkCo pySimpleRe sultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source) at System.Data.SqlClient.SqlB ulkCopy.Co pyBatchesA sync(BulkC opySimpleR esultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source) at System.Data.SqlClient.SqlB ulkCopy.Wr iteToServe rInternalR estContinu edAsync(Bu lkCopySimp leResultSe t internalResults, CancellationToken cts, TaskCompletionSource`1 source) at System.Data.SqlClient.SqlB ulkCopy.Wr iteToServe rInternalR estAsync(C ancellatio nToken cts, TaskCompletionSource`1 source) at System.Data.SqlClient.SqlB ulkCopy.Wr iteToServe rInternalA sync(Cance llationTok en ctoken) at System.Data.SqlClient.SqlB ulkCopy.Wr iteRowSour ceToServer Async(Int3 2 columnCount, CancellationToken ctoken) at System.Data.SqlClient.SqlB ulkCopy.Wr iteToServe r(DataTabl e table, DataRowState rowState) at System.Data.SqlClient.SqlB ulkCopy.Wr iteToServe r(DataTabl e table) at Embroidery_embroideryOrder .setTrailN um(DataTab le dt) in C:\temp\embroideryOrder.as px.vb:line 1438
here's my code:
'create datatable
Dim dt As DataTable = New DataTable
dt.Columns.Add(New DataColumn("c1", GetType(String)))
dt.Columns.Add(New DataColumn("c2", GetType(String)))
dt.Columns.Add(New DataColumn("c3", GetType(String)))
dt.Columns.Add(New DataColumn("c4", GetType(Integer)))
dt.Columns.Add(New DataColumn("c5", GetType(Integer)))
...
dt.Columns.Add(New DataColumn("c28", GetType(String)))
dt.Columns.Add(New DataColumn("c30", GetType(Decimal)))
'assign values
dim row = dt.NewRow()
row("c1") = "simon"
row("c2") = "crew"
row("c3") = "1234"
row("c4") = 2
row("c5") = 5
...
row("28") = "VC"
row("c30") = 3.40
dt.Rows.Add(row)
'display without issue
GridView2.DataSource = dt
GridView2.DataBind()
GridView2.Visible = True
'insert to sql
Dim con2 As String = ConfigurationManager.Conne ctionStrin gs("TestCo n").Connec tionString
Dim copy As SqlBulkCopy
copy = New SqlBulkCopy(con2, SqlBulkCopyOptions.KeepIde ntity)
Using copy
copy.BatchSize = 25
copy.DestinationTableName = "dbo.embTest"
copy.ColumnMappings.Add("c 1", "c1")
copy.ColumnMappings.Add("c 2", "c2")
copy.ColumnMappings.Add("c 3", "c3")
copy.ColumnMappings.Add("c 4", "c4")
copy.ColumnMappings.Add("c 5", "c5")
...
copy.ColumnMappings.Add("c 28", "c28")
copy.ColumnMappings.Add("c 30", "c30")
Try
copy.WriteToServer(dt)
Catch ex As Exception
Label6.Text = ex.ToString
Label6.Visible = True
Finally
copy.Close()
End Try
End Using
here's my sql table definition:
CREATE TABLE [dbo].[embTest](
[id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[c1] [nvarchar](17) NULL,
[c2] [nvarchar](20) NULL,
[c3] [nvarchar](100) NULL,
[c4] [int] NULL,
[c5] [int] NULL,
...
[c29] [smalldatetime] NULL,
[c30] [decimal] (10,4) NULL
CONSTRAINT [PK_embTest] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
thank you.
would you please help me out with this problem, i already stuck for couple days, any help will be greatly appreciate...
i'm working on a webpage with a temp datatable (one row and 30 columns), i try use sqlbulkcopy to insert to my sql table but with the following error:
System.InvalidOperationExc
here's my code:
'create datatable
Dim dt As DataTable = New DataTable
dt.Columns.Add(New DataColumn("c1", GetType(String)))
dt.Columns.Add(New DataColumn("c2", GetType(String)))
dt.Columns.Add(New DataColumn("c3", GetType(String)))
dt.Columns.Add(New DataColumn("c4", GetType(Integer)))
dt.Columns.Add(New DataColumn("c5", GetType(Integer)))
...
dt.Columns.Add(New DataColumn("c28", GetType(String)))
dt.Columns.Add(New DataColumn("c30", GetType(Decimal)))
'assign values
dim row = dt.NewRow()
row("c1") = "simon"
row("c2") = "crew"
row("c3") = "1234"
row("c4") = 2
row("c5") = 5
...
row("28") = "VC"
row("c30") = 3.40
dt.Rows.Add(row)
'display without issue
GridView2.DataSource = dt
GridView2.DataBind()
GridView2.Visible = True
'insert to sql
Dim con2 As String = ConfigurationManager.Conne
Dim copy As SqlBulkCopy
copy = New SqlBulkCopy(con2, SqlBulkCopyOptions.KeepIde
Using copy
copy.BatchSize = 25
copy.DestinationTableName = "dbo.embTest"
copy.ColumnMappings.Add("c
copy.ColumnMappings.Add("c
copy.ColumnMappings.Add("c
copy.ColumnMappings.Add("c
copy.ColumnMappings.Add("c
...
copy.ColumnMappings.Add("c
copy.ColumnMappings.Add("c
Try
copy.WriteToServer(dt)
Catch ex As Exception
Label6.Text = ex.ToString
Label6.Visible = True
Finally
copy.Close()
End Try
End Using
here's my sql table definition:
CREATE TABLE [dbo].[embTest](
[id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[c1] [nvarchar](17) NULL,
[c2] [nvarchar](20) NULL,
[c3] [nvarchar](100) NULL,
[c4] [int] NULL,
[c5] [int] NULL,
...
[c29] [smalldatetime] NULL,
[c30] [decimal] (10,4) NULL
CONSTRAINT [PK_embTest] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sorry, when I go through the original data in my datatable, c2 has length 60 but when I create my new datatable, I set it to 20...
now everything is working. thank you.
now everything is working. thank you.
Why is this line does not contain "c"?
row("28") = "VC"
Shouldn't it be: row("c28") = "VC"
ASKER
i triple checked the string and column length in my table: the string is 1000 and sql table datatype is nvarchar(100)...
i have no clue what went wrong... =(