sqlBulkCopy:データソースのString型の指定された値を、指定されたターゲット列の型intに変換できません

asp.net datacolumn gridview sqlbulkcopy vb.net

質問

グリッド行からデータ列に値を格納し、sqlBulkCopyを使用してデータをデータベースに挿入しようとしています。

私のgridviewデザインは、次のように設定されます:

    <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>

私のgridview Rowは以下のようにデータを保存します:

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

これは、グリッドビューの行からデータ列とデータ列をデータベースに読み込む方法です。

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

そして、次のようなテーブルのデータ型:

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]

"アップロード中に例外が発生しました:データソースからのString型の指定された値は、指定されたターゲット列の型intに変換できません"

このエラーの原因は何ですか?

人気のある回答

以下のコードを使用してください。それがあなたを助けるかもしれない。私は、dClusterカラム文字列のデータ型を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


ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ
ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ