sqlBulkCopy: la valeur donnée de type String de la source de données ne peut pas être convertie en type int de la colonne cible spécifiée

asp.net datacolumn gridview sqlbulkcopy vb.net

Question

J'essaie de stocker les valeurs des lignes de la grille dans la colonne de données, puis d'utiliser sqlBulkCopy pour insérer les données dans ma base de données.

Ma conception de gridview définit comme suit:

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

Ma grille gridview stocke les données comme suit:

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

Voici comment j'essaie d'introduire des données dans la base de données, de la ligne gridview à la colonne de données et de la colonne de données à la base de données:

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

Et le type de données de la table comme suit:

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]

Il affiche maintenant l'erreur sous la forme "Une exception s'est produite lors du téléchargement: la valeur donnée de type String de la source de données ne peut pas être convertie en type int de la colonne cible spécifiée"

Avez-vous une idée de ce qui cause cette erreur?

Réponse populaire

Utilisez le code ci-dessous. Peut-être que ça vous aide. J'ai changé le type de données de la chaîne de colonne dCluster en entier.

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


Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi