sqlBulkCopy: The given value of type String from the data source cannot be converted to type int of the specified target column

asp.net datacolumn gridview sqlbulkcopy vb.net

Question

I am trying to store values from grid rows to data column and then using the sqlBulkCopy to insert the data into my database.

My gridview design sets as per following:

    <asp:GridView ID="gvDCR" runat="server" Width="100%" AutoGenerateColumns="false" 
        HeaderStyle-HorizontalAlign="Center" HeaderStyle-VerticalAlign="Middle" OnRowDeleting="OnRowDeleting">
        <Columns>            
            <asp:BoundField DataField="UserID" HeaderText="Name of User" ItemStyle-CssClass="Column_Hide" HeaderStyle-CssClass="Column_Hide"></asp:BoundField>
            <asp:BoundField DataField="Date" HeaderText="Date"></asp:BoundField>
            <asp:BoundField DataField="Cluster" HeaderText="Cluster"></asp:BoundField>
            <asp:BoundField DataField="Drcode" HeaderText="Code" ItemStyle-CssClass="Column_Hide" HeaderStyle-CssClass="Column_Hide"></asp:BoundField>
            <asp:BoundField DataField="Act_name" HeaderText="Customer"></asp:BoundField>
            <asp:BoundField DataField="Ordno" HeaderText="Order No."></asp:BoundField>
            <asp:BoundField DataField="OrdAmt" HeaderText="Order Amount"></asp:BoundField>
            <asp:BoundField DataField="Remarks" HeaderText="Remarks"></asp:BoundField>
            <asp:CommandField ShowDeleteButton="True" ButtonType="Button" ItemStyle-HorizontalAlign="Center" ItemStyle-VerticalAlign="Middle" />
        </Columns>
    </asp:GridView>

My gridview Row stores the data as per following:

Private Sub btnInsertData_Click(sender As Object, e As EventArgs) Handles btnInsertData.Click
    Dim dt As DataTable = DirectCast(ViewState("Customers"), DataTable)
    Dim sOrdAmt As String
    dt.Rows.Add(Trim(txtInf.Text), Trim(txtDt.Text), ddlCluster.SelectedValue, ddlCustomer.SelectedValue, ddlCustomer.SelectedItem, Trim(txtOrdNo.Text), Trim(sOrdAmt), Trim(txtRemarks.Text))
    ViewState("Customers") = dt
    Try
      Me.BindGrid()
      lblMsg.Text = "Data Loaded Successfully"
      lblMsg.ForeColor = System.Drawing.Color.Green
    Catch ex As Exception
      lblMsg.Text = "Exception occured: " & ex.Message
      lblMsg.ForeColor = System.Drawing.Color.Red
    End Try
End Sub

This is how I try to instert data into database from gridview row to data column and data column to database:

Private Sub btnUpload_Click(sender As Object, e As EventArgs) Handles btnUpload.Click
  Dim dtu As New DataTable
  dtu.Columns.AddRange(New DataColumn(7) {New DataColumn("dUserId", GetType(String)), New DataColumn("dDate", GetType(Date)), New DataColumn("dCluster", GetType(String)), New DataColumn("dDRCODE", GetType(String)), New DataColumn("dAct_Name", GetType(String)), New DataColumn("dOrdNo", GetType(String)), New DataColumn("dOrdAmt", GetType(String)), New DataColumn("dRemarks", GetType(String))})
  For Each row As GridViewRow In gvDCR.Rows
     Try
       Dim dUserid As String = row.Cells(0).Text
       Dim dDate As Date = row.Cells(1).Text
       Dim dCluster As String = row.Cells(2).Text
       Dim dDrcode As String = row.Cells(3).Text
       Dim dOrdNo As String = row.Cells(5).Text
       Dim dOrdAmt As Int32 = System.Convert.ToInt32(row.Cells(6).Text)
       Dim dRemarks As String = row.Cells(8).Text

       dtu.Rows.Add(dUserid, dDate, dCluster, dDrcode, dOrdNo, dOrdAmt, dRemarks)
       lblMsg.Text = "Data is ready to upload..!"
       lblMsg.ForeColor = System.Drawing.Color.Green
     Catch ex As Exception
       lblMsg.Text = "Exception occured during checking of data: " & ex.Message
       lblMsg.ForeColor = System.Drawing.Color.Red
     End Try
  Next
     If dtu.Rows.Count > 0 Then
       Using con As New SqlConnection(strConnection)
         Using sqlBulkCopy As New SqlBulkCopy(con)
           sqlBulkCopy.DestinationTableName = "dbo.dwr"
             sqlBulkCopy.ColumnMappings.Add("dUserId", "dwr_for")
             sqlBulkCopy.ColumnMappings.Add("dDate", "dwr_dt")
             sqlBulkCopy.ColumnMappings.Add("dCluster", "block")
             sqlBulkCopy.ColumnMappings.Add("dDrcode", "drcode")
             sqlBulkCopy.ColumnMappings.Add("dOrdNo", "order_no")
             sqlBulkCopy.ColumnMappings.Add("dOrdAmt", "ord_amt")
             sqlBulkCopy.ColumnMappings.Add("dRemarks", "remarks")
           con.Open()
           Try
             sqlBulkCopy.WriteToServer(dtu)
             lblMsg.Text = "Data successfully updated into database."
             lblMsg.ForeColor = System.Drawing.Color.Green
           Catch ex As Exception
             lblMsg.Text = "Exception occured during upload: " & ex.Message
             lblMsg.ForeColor = System.Drawing.Color.Red
           End Try
           con.Close()
         End Using
       End Using
     End If
   End Sub

And the data type of table as per following:

CREATE TABLE [dbo].[dwr](
    [dwr_id] [int] IDENTITY(1,1) NOT NULL,
    [dwr_dt] [date] NULL,
    [dwr_for] [nvarchar](50) NULL,
    [block] [int] NULL,
    [drcode] [nvarchar](6) NULL,
    [visited] [bit] NULL,
    [order_no] [nvarchar](10) NULL,
    [ord_amt] [float] NULL,
    [coll_amt] [float] NULL,
    [remarks] [nvarchar](max) NULL,
    [updated_on] [datetime] NULL,
 CONSTRAINT [PK_dwr] PRIMARY KEY CLUSTERED 
(
    [dwr_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] TEXTIMAGE_ON [PRIMARY]

Now its showing the error as "Exception occured during upload: The given value of type String from the data source cannot be converted to type int of the specified target column"

Any idea about what's causing this error?

Popular Answer

Use below code. May be its helps you. I have changed the datatype of dCluster column string to integer.

Private Sub btnUpload_Click(sender As Object, e As EventArgs) Handles btnUpload.Click
Dim dtu As New DataTable
dtu.Columns.AddRange(New DataColumn(7) {New DataColumn("dUserId", GetType(String)), New DataColumn("dDate", GetType(Date)), New DataColumn("dCluster", GetType(Integer)), New DataColumn("dDRCODE", GetType(String)), New DataColumn("dAct_Name", GetType(String)), New DataColumn("dOrdNo", GetType(String)), New DataColumn("dOrdAmt", GetType(Float)), New DataColumn("dRemarks", GetType(String))})
For Each row As GridViewRow In gvDCR.Rows
 Try
   Dim dUserid As String = row.Cells(0).Text
   Dim dDate As Date = row.Cells(1).Text
   Dim dCluster As Integer = Convert.toInt32(row.Cells(2).Text)
   Dim dDrcode As String = row.Cells(3).Text
   Dim dOrdNo As String = row.Cells(5).Text
   Dim dOrdAmt As Float = System.Convert.ToFloat(row.Cells(6).Text)
   Dim dRemarks As String = row.Cells(8).Text

   dtu.Rows.Add(dUserid, dDate, dCluster, dDrcode, dOrdNo, dOrdAmt, dRemarks)
   lblMsg.Text = "Data is ready to upload..!"
   lblMsg.ForeColor = System.Drawing.Color.Green
 Catch ex As Exception
   lblMsg.Text = "Exception occured during checking of data: " & ex.Message
   lblMsg.ForeColor = System.Drawing.Color.Red
 End Try
Next
 If dtu.Rows.Count > 0 Then
   Using con As New SqlConnection(strConnection)
     Using sqlBulkCopy As New SqlBulkCopy(con)
       sqlBulkCopy.DestinationTableName = "dbo.dwr"
         sqlBulkCopy.ColumnMappings.Add("dUserId", "dwr_for")
         sqlBulkCopy.ColumnMappings.Add("dDate", "dwr_dt")
         sqlBulkCopy.ColumnMappings.Add("dCluster", "block")
         sqlBulkCopy.ColumnMappings.Add("dDrcode", "drcode")
         sqlBulkCopy.ColumnMappings.Add("dOrdNo", "order_no")
         sqlBulkCopy.ColumnMappings.Add("dOrdAmt", "ord_amt")
         sqlBulkCopy.ColumnMappings.Add("dRemarks", "remarks")
       con.Open()
       Try
         sqlBulkCopy.WriteToServer(dtu)
         lblMsg.Text = "Data successfully updated into database."
         lblMsg.ForeColor = System.Drawing.Color.Green
       Catch ex As Exception
         lblMsg.Text = "Exception occured during upload: " & ex.Message
         lblMsg.ForeColor = System.Drawing.Color.Red
       End Try
       con.Close()
     End Using
   End Using
 End If
 End Sub


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why