none
How to update sql table from datatable ? RRS feed

  • Question

  • I called a table from a sql database (Zir_Dar_m) and sent it to a datagrid.
    I edited rows of datagrid.
    Then I loaded the changed rows into the DataTable (DDtZir).
    I want to go through this DataTable (DDtZir)
     Update the original table (Zir_Dar_m).

    Dim CommandText As String = " select   id, cod, codd, coddd, codddd, datem, dat_sal, dat_m, kod_kala, namkala_t, tedad, vahed_n, sharh, n1, n2, n5, n7
     from Zir_dar_m "
                    Dim SQLConn As New SqlConnection(connectionS)
                    Dim ds As DataSet = New DataSet("NewDataSet")
                    Dim LoadAdapt As New SqlDataAdapter(CommandText, SQLConn)
                    SQLConn.Open()
                    LoadAdapt.FillSchema(ds, SchemaType.Source, "Zir_dar_m")
                    LoadAdapt.Fill(ds, "Zir_dar_m")
                    Dim cbSQLCOMB As New SqlCommandBuilder(LoadAdapt)
                    Dim mapping As DataTableMapping = LoadAdapt.TableMappings.Add("table", "DDtZir")
                    mapping.ColumnMappings.Add("id", "id")
                    mapping.ColumnMappings.Add("cod", "cod")
                    mapping.ColumnMappings.Add("codd", "codd")
                    mapping.ColumnMappings.Add("coddd", "coddd")
                    mapping.ColumnMappings.Add("codddd", "codddd")
                    mapping.ColumnMappings.Add("Datem", "Datem")
                    mapping.ColumnMappings.Add("dat_sal", "dat_sal")
                    mapping.ColumnMappings.Add("dat_m", "dat_m")
                    mapping.ColumnMappings.Add("kod_kala", "kod_kala")
                    mapping.ColumnMappings.Add("namkala_t", "namkala_t")
                    mapping.ColumnMappings.Add("vahed_n", "vahed_n")
                    mapping.ColumnMappings.Add("tedad", "tedad")
                    mapping.ColumnMappings.Add("sharh", "sharh")
                    mapping.ColumnMappings.Add("n1", "n1")
                    mapping.ColumnMappings.Add("n2", "n2")
                    mapping.ColumnMappings.Add("n5", "n5")
                    mapping.ColumnMappings.Add("n7", "n7")
                    LoadAdapt.Update(ds, "DDtZir")
                    SQLConn.Close()

    Error : Update unable to find TableMapping['DDtZir'] or DataTable 'DDtZir'

    My application's error is clear
    How can I define TableMapping for DataTable?

    Is there another way to solve this problem?

    Thank you 

    Wednesday, January 30, 2019 2:38 PM

Answers

  • Hi,

    You still need to fill the Dataset first.

    LoadAdapt.Update(ds, "DDtZir")

    or you can use following solution to update sql table

    Imports System.Data.SqlClient
    Public Class Form1
        Dim sda As SqlDataAdapter
        Dim CommandeSQLSelect As String
        Private Bind As New BindingSource
        Dim dt As DataTable = New DataTable
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
            Dim ConnectionSQL As SqlConnection = New SqlConnection(constr)
            CommandeSQLSelect = "Select * From Student"
            sda = New SqlDataAdapter(CommandeSQLSelect, ConnectionSQL)
            Dim SQLCommandBuild As SqlCommandBuilder = New SqlCommandBuilder(sda)
            sda.Fill(dt)
            Bind.DataSource = dt
            DataGridView1.DataSource = Bind
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            sda.Update(dt)
        End Sub
    End Class

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ahmadi.ahm Saturday, February 2, 2019 9:24 AM
    Thursday, January 31, 2019 2:56 AM

All replies

  • Hi,

    You still need to fill the Dataset first.

    LoadAdapt.Update(ds, "DDtZir")

    or you can use following solution to update sql table

    Imports System.Data.SqlClient
    Public Class Form1
        Dim sda As SqlDataAdapter
        Dim CommandeSQLSelect As String
        Private Bind As New BindingSource
        Dim dt As DataTable = New DataTable
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
            Dim ConnectionSQL As SqlConnection = New SqlConnection(constr)
            CommandeSQLSelect = "Select * From Student"
            sda = New SqlDataAdapter(CommandeSQLSelect, ConnectionSQL)
            Dim SQLCommandBuild As SqlCommandBuilder = New SqlCommandBuilder(sda)
            sda.Fill(dt)
            Bind.DataSource = dt
            DataGridView1.DataSource = Bind
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            sda.Update(dt)
        End Sub
    End Class

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ahmadi.ahm Saturday, February 2, 2019 9:24 AM
    Thursday, January 31, 2019 2:56 AM
  • Hi

    Based on your advice , I changed the letter to the following .

    My problem has been resolved.

                    Dim CommandText As String = " select   id, cod, codd, coddd, codddd, datem, dat_sal, dat_m, kod_kala, namkala_t, tedad, vahed_n, sharh, n1, n2, n5, n7
     from Zir_dar_m "
                    Dim SQLConn As New SqlConnection(connectionS)
                    Dim ds As DataSet = New DataSet("NewDataSet")
                    Dim LoadAdapt As New SqlDataAdapter(CommandText, SQLConn)
                    LoadAdapt.Fill(ds)
    
                    SQLConn.Open()
                    Dim cbSQLCOMB As New SqlCommandBuilder(LoadAdapt)
                    LoadAdapt.Update(DDtZir)
    I did not need to use TableMappings.
    Thank you for your guidance .
     ahmadi

    Saturday, February 2, 2019 9:38 AM