select exisiting data and change one value in another related table

[hanzie]

Hello, I have a database with 3 tables: "Bedrijven", "Tekeningen", "Datums".
The tables have relations with each other.
I get values from these tables and combine them in a dataset. The dataset then has a datatable with these values.

Know I wan't to add a new row to one of the tables in the database.

I do this by selecting existing table-values with the help of comboboxes.
After that i add a new row to the datasettable which contains these selected values.

But when I wanna update the values i get the error that i can't update the existing primary key.

I have the following code:

    Private Sub BtnDatumOpslaan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDatumOpslaan.Click

        'Create your query as you already have done  
        Dim strsql As String = "SELECT Tekeningen.BedrijvenID, Tekeningen.Bladnummer, Tekeningen.Naam, Tekeningen.TekeningId, max(Datums.Datum) AS Datum FROM Tekeningen INNER JOIN Datums ON Tekeningen.TekeningID=Datums.TekeningID WHERE Tekeningen.TekeningID=@TekeningID GROUP BY Tekeningen.BedrijvenID, Tekeningen.Bladnummer, Tekeningen.Naam, Tekeningen.TekeningID ORDER BY Bladnummer"
        'Get your connection string (You've done this right)  
        Dim strconnectionstring As String = My.Settings.DocRegDataConnectionString
        'Create your SqlConnection (Done)  
        Dim objconnection As New SqlConnection(strconnectionstring)
        objconnection.Open()
        'Create your SqlCommand (done)  
        Dim objcommandDatum As New SqlCommand()

        Dim TekenID As Integer
        TekenID = NaamComboBox.SelectedValue

        '**Set your command properties**  
        With objcommandDatum
            .CommandText = strsql
            .CommandType = CommandType.Text
            .Parameters.AddWithValue("@TekeningID", TekenID)
            .Connection = objconnection
        End With

        'Create a new SqlDataAdapter 
        Dim objdataAdapter As New SqlDataAdapter
        objdataAdapter.SelectCommand = objcommandDatum

        'Load Sql updatecommand
        Dim autogen As New SqlCommandBuilder(objdataAdapter)

        'Create a new DataSet  
        Dim objDataSetDatums As New DataSet

        'Fill the DataSet using the SqlDataAdapter  
        objdataAdapter.Fill(objDataSetDatums, "Datumset")

        Dim bindingsourcedatum As New BindingSource
        bindingsourcedatum.DataSource = objDataSetDatums.Tables("Datumset")

        DataGridView1.AutoGenerateColumns = True
        DataGridView1.DataSource = bindingsourcedatum

        'create and fill datarow
        Dim objdatarow As DataRow
        objdatarow = objDataSetDatums.Tables("Datumset").NewRow()
        objdatarow("BedrijvenID") = objDataSetDatums.Tables("Datumset").Rows(0).Item("BedrijvenID")
        objdatarow("TekeningID") = objDataSetDatums.Tables("Datumset").Rows(0).Item("TekeningID")
        objdatarow("Naam") = objDataSetDatums.Tables("Datumset").Rows(0).Item("Naam")
        objdatarow("Bladnummer") = objDataSetDatums.Tables("Datumset").Rows(0).Item("Bladnummer")
        objdatarow("Datum") = DatumPickerBestaand.Value

        'add datarow
        objDataSetDatums.Tables("Datumset").Rows.Add(objdatarow)

        'update dataset-table
        objdataAdapter.Update(objDataSetDatums.Tables("Datumset"))

        objconnection.Close()

    End Sub

ANY HELP?

 

[cjard]

i'm confused. please rewrite your post in standard Customers-Orders mentality, adjusting Datum, Tekningen, and any other foreign names so that youre presenting clearly the data you have before (in customers - orders style) and the data you want to have after

 

[hanzie]

closed

Hello, thnx for the reply.I wanna close this post. I'm trying to do it completly different now.