none
DataTable / Dataset not updating database RRS feed

  • Question

  • Hi,

     I want to solve the problem that datatable is not updating my database. I tried to do it with dataset too .. but the solution is not working. Now it is showing me some problems. It say's "Child List for field can_edu cannot be created" ... and clicking same button twice it gives error some what like this "The DataTableMapping.SourceTable" is required to be unique, 'can_edu' already exist in the collection"

     

    So could you help me to solve the problems. I am a student as well as beginner ... Please give me the solution to this problem ...

    You could even find the whole project over here 

    http://www.mediafire.com/?7t1rqcdv8lnfhak 

    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Data.Common
    
    
    
    
    
    
    Public Class candidate_education
    
      Dim c1 As String
      Dim canf As String
      Dim dt As New DataTable
    
      Dim dval1 As Date
      Dim dval2 As Date
    
    
      'GUID
      Dim g As String
      Dim cedu As String 
      'SQL
      Dim ds As DataSet
      Dim da As SqlDataAdapter
      Dim dr As SqlDataReader
      Dim con As SqlConnection
      Dim cmd As New SqlCommand
    
    
      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
       
    
    
        Dim cmd As SqlCommand = Nothing
    
    
        Dim con = New SqlConnection("Data Source=GAYATRIS-PC\SQLSERVERJDC;Initial Catalog=companyhrms;Integrated Security=True")
    
    
        'cmd = New SqlCommand("dbo.storedProcedure1", con)
    
    
        'cmd.Parameters.Add("@cedu", SqlDbType.UniqueIdentifier).Value = System.Data.SqlTypes.SqlGuid.Parse(cedu.ToString())
    
    
        'cmd.Parameters.Add(New SqlParameter("@qual", SqlDbType.NVarChar))
        ' cmd.Parameters("@qual").Value = qualification.SelectedText
    
    
        ' cmd.Parameters.Add(New SqlParameter("@degree", SqlDbType.NVarChar))
        'cmd.Parameters("@degree").Value = degree.Text
    
        ' cmd.Parameters.Add(New SqlParameter("@dval1", SqlDbType.Date))
        ' cmd.Parameters("@dval1").Value = dval1
    
        ' cmd.Parameters.Add(New SqlParameter("@dval2", SqlDbType.Date))
        'cmd.Parameters("@dval2").Value = dval2
    
        'cmd.Parameters.Add(New SqlParameter("@per", SqlDbType.NVarChar))
        'cmd.Parameters("@per").Value = percentage.Text
    
        'cmd.Parameters.Add(New SqlParameter("@skills", SqlDbType.NVarChar))
        ' cmd.Parameters("@skills").Value = skills.Text
    
        ' cmd.Parameters.Add("@g", SqlDbType.UniqueIdentifier).Value = System.Data.SqlTypes.SqlGuid.Parse(g.ToString())
        Dim cmdbuilder As New SqlCommandBuilder(da)
        Dim i As Integer
        Try
    
          dt = DataGridView1.DataSource
    
          'by original value first
          Dim mapping As New DataTableMapping
          mapping.SourceTable = "can_edu"
    
          mapping.DataSetTable = dt.ToString
    
    
          da.TableMappings.Add("can_edu", "can_education")
          mapping.ColumnMappings.Add("Qualification ID", "can_edu_id")
          mapping.ColumnMappings.Add("Qualification", "can_qual")
          mapping.ColumnMappings.Add(" Name of the Degree", "degree")
          mapping.ColumnMappings.Add("Year start", "ystart")
          mapping.ColumnMappings.Add("Year end", "yend")
          mapping.ColumnMappings.Add("Percentage", "percentage")
          mapping.ColumnMappings.Add("skills", "Skills")
          mapping.ColumnMappings.Add("Candidate ID", "can_id")
    
          ' mapping.ColumnMappings.Add("can_edu_id", "Qualification ID")
          ' mapping.ColumnMappings.Add("can_qual", "Qualification")
          'mapping.ColumnMappings.Add("degree", " Name of the Degree")
          ' mapping.ColumnMappings.Add("ystart", "Year start")
          ' mapping.ColumnMappings.Add("yend", "Year end")
          ' mapping.ColumnMappings.Add("percentage", "Percentage")
          ' mapping.ColumnMappings.Add("skills", "Skills")
          ' mapping.ColumnMappings.Add("can_id", "Candidate ID")
    
          
    
          For i = 0 To ds.Tables(0).Rows.Count - 1
            cedu = ds.Tables(0).Rows(i).Item(0)
            qualification.Text = ds.Tables(0).Rows(i).Item(1)
            degree.Text = ds.Tables(0).Rows(i).Item(2)
            dval1 = ds.Tables(0).Rows(i).Item(3)
            dval2 = ds.Tables(0).Rows(i).Item(4)
            percentage.Text = ds.Tables(0).Rows(i).Item(5)
            skills.Text = ds.Tables(0).Rows(i).Item(6)
            g = ds.Tables(0).Rows(i).Item(7)
    
    
          Next
    
          da.Fill(ds)
    
          Me.BindingContext(ds, "can_edu").EndCurrentEdit()
    
          Me.da.Update(ds)
    
    
          MessageBox.Show("record upddates=" & i)
    
          ' ds = New DataSet
          ' ds.Tables.Add(dt)
    
    
          ' Dim mapping As ITableMapping =
          'da.TableMappings.Add("can_edu", "can_edu")
    
          ' mapping.ColumnMappings.Add("Qualification ID", "can_edu_id")
          ' mapping.ColumnMappings.Add("Qualification", "can_qual")
          ' mapping.ColumnMappings.Add(" Name of the Degree", "degree")
          ' mapping.ColumnMappings.Add("Year start", "ystart")
          ' mapping.ColumnMappings.Add("Year end", "yend")
          ' mapping.ColumnMappings.Add("Percentage", "percentage")
          ' mapping.ColumnMappings.Add("skills", "Skills")
          ' mapping.ColumnMappings.Add("Candidate ID", "can_id")
    
    
          ' da.Fill(ds, "can_edu")
    
          ' Me.da.Update(Me.ds.Tables("can_edu"))
    
    
    
          MessageBox.Show("candidate Education is saved succesfully!!!")
        Catch ex As Exception
          MessageBox.Show(ex.Message)
    
          con.Close()
    
    
        End Try
    
      End Sub
    
      Private Sub firstname_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles firstname.SelectedIndexChanged
    
    
    
      End Sub
    
     
      Private Sub candidate_education_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
        'for combobox value load
    
    
        Dim con = New SqlConnection("Data Source=GAYATRIS-PC\SQLSERVERJDC;Initial Catalog=companyhrms;Integrated Security=True")
        '  Dim cnf As String
        canf = "select can_fname from can_info "
        cmd = New SqlCommand(canf, con)
        da = New SqlDataAdapter()
        ds = New DataSet
    
    
        da.SelectCommand = cmd
    
    
        con.Open()
        da.Fill(ds)
    
    
        firstname.DataSource = ds.Tables(0)
    
        firstname.DisplayMember = "can_fname"
        firstname.SelectedIndex = 0
    
    
        'for grid view
    
        ' cnf = "select * from can_edu"
        ' cmd = New SqlCommand(cnf, con)
        ' da.SelectCommand = cmd
    
        ' da.Fill(ds, "can_info")
        'DataGridView1.DataSource = ds.Tables("can_info")
    
    
        'code by me
    
        cmd = New SqlCommand("Select * from can_edu", con)
        da = New SqlDataAdapter(cmd)
    
        da.Fill(ds, "can_edu")
        Me.DataGridView1.DataSource = ds
        Me.DataGridView1.DataMember = "can_edu"
    
       
    
    
      End Sub
    
      Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim q_select As String
    
        Dim con = New SqlConnection("Data Source=GAYATRIS-PC\SQLSERVERJDC;Initial Catalog=companyhrms;Integrated Security=True")
    
    
        q_select = "select can_qual ,degree ,ystart ,yend percentage ,skills ,can_fname from can_edu,can_info where can_id='{" + g + "}' "
    
        cmd.Parameters.Add("@cedu", SqlDbType.UniqueIdentifier).Value = System.Data.SqlTypes.SqlGuid.Parse(cedu.ToString())
    
    
        cmd.Parameters.Add(New SqlParameter("@fname", SqlDbType.NVarChar))
        cmd.Parameters("@fname").Value = firstname.SelectedText
    
        cmd = New SqlCommand(q_select, con)
    
        da = New SqlDataAdapter()
        ds = New DataSet()
        da.SelectCommand = cmd
    
        Try
          con.Open()
          da.Fill(ds)
    
          DataGridView1.DataSource = ds
    
          con.Close()
    
        Catch ex As Exception
          MessageBox.Show(ex.Message)
    
    
        End Try
      End Sub
    
      Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim q_delete As String
        Dim con = New SqlConnection("Data Source=GAYATRIS-PC\SQLSERVERJDC;Initial Catalog=companyhrms;Integrated Security=True")
    
    
        q_delete = "delete can_edu WHERE degree=@degree AND can_id='" + g + "' "
    
    
    
        cmd = New SqlCommand(q_delete, con)
    
        cmd.Parameters.Add(New SqlParameter("@degree", SqlDbType.NVarChar))
        cmd.Parameters("@degree").Value = degree.Text
    
    
    
    
        Try
          con.Open()
          Dim r As DialogResult
          r = MessageBox.Show("Do you want to delete this record?? ", "confirm Delete", MessageBoxButtons.YesNo)
          If (r = Windows.Forms.DialogResult.Yes) Then
            cmd.ExecuteNonQuery()
            ' MessageBox.Show("candidate deleted succesfully")
            con.Close()
    
          End If
    
    
        Catch ex As Exception
          MessageBox.Show(ex.Message)
    
    
        End Try
    
      End Sub
    
      Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
        Menu_screen.Show()
    
      End Sub
    
     
     
      Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
    
        'for combobox selection
    
    
        dval1 = ystart.Value
    
        dval2 = yend.Value
    
    
        Dim con = New SqlConnection("Data Source=GAYATRIS-PC\SQLSERVERJDC;Initial Catalog=companyhrms;Integrated Security=True")
    
        c1 = "select can_id='{" + g + "}' from can_info WHERE can_fname=@fname"
    
        cmd = New SqlCommand(c1, con)
    
        cmd.Parameters.Add(New SqlParameter("@fname", SqlDbType.NVarChar))
        cmd.Parameters("@fname").Value = firstname.SelectedItem.ToString
    
        da = New SqlDataAdapter()
        ds = New DataSet()
        da.SelectCommand = cmd
    
        Try
          con.Open()
          da.Fill(ds)
    
        Catch ex As Exception
          MessageBox.Show(ex.Message)
    
        End Try
    
    
    
        'for grid view
    
    
        cedu = New String(Guid.NewGuid.ToString())
    
        DataGridView1.ClearSelection()
    
       
        Dim dt As New DataTable
        Dim drow As DataRow = Nothing
    
    
        dt.Columns.Add("Qualification ID")
        dt.Columns.Add("Qualification")
        dt.Columns.Add("Name of the Degree")
        dt.Columns.Add("Year start")
        dt.Columns.Add("Year end")
        dt.Columns.Add("Percentage")
        dt.Columns.Add("Skills")
        dt.Columns.Add("Candidate ID")
    
    
    
        'dt.Rows.Add()
    
        drow = dt.NewRow()
    
    
        drow("Qualification ID") = cedu
        drow("Qualification") = qualification.SelectedItem.ToString
        drow("Name of the Degree") = degree.Text
        drow("Year start") = dval1
        drow("Year end") = dval2
        drow("Percentage") = percentage.Text
        drow("Skills") = skills.Text
        drow("Candidate ID") = ds.Tables(0)
        dt.Rows.Add(drow)
        DataGridView1.DataSource = dt
    
        ds.Tables.Add(dt)
    
    
    
    
    
    
      End Sub
    End Class

    Friday, March 4, 2011 9:32 AM

Answers

All replies