this is my csharp code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace MutlitableDatasetApp
{
    public partial class MainForm : Form
    {
        //form wide dataset.
        private DataSet autoLotDS = new DataSet("AutoLot");

        //Make use of command builders to simplify data adapter configuration.
        private SqlCommandBuilder sqlCBInventory;
        private SqlCommandBuilder sqlCBCustomers;
        private SqlCommandBuilder sqlCBOrders;

        //our data adapters (for each table).
        private SqlDataAdapter invTableAdapter;
        private SqlDataAdapter custTableAdapter;
        private SqlDataAdapter ordersTableAdapter;

        //form wide connection string.
        private string cnStr = string.Empty;

        public MainForm()
        {
            InitializeComponent();

            //get connection string from *.config file.
            cnStr = ConfigurationManager.ConnectionStrings["AutoLotSqlProvider"].ConnectionString;

            //create adapters.
            invTableAdapter = new SqlDataAdapter("Select * from Inventory", cnStr);
            custTableAdapter = new SqlDataAdapter("Select * from Customers", cnStr);
            ordersTableAdapter = new SqlDataAdapter("Select * from Orders", cnStr);

            //autogenerate commands.
            sqlCBInventory = new SqlCommandBuilder(invTableAdapter);
            sqlCBCustomers = new SqlCommandBuilder(custTableAdapter);
            sqlCBOrders = new SqlCommandBuilder(ordersTableAdapter);

            //fill tables in DS
            invTableAdapter.Fill(autoLotDS, "Inventory");
            custTableAdapter.Fill(autoLotDS, "Customers");
            ordersTableAdapter.Fill(autoLotDS, "Orders");

            //build relations between tables.
            BuildTableRelationship();

            //bind to grids
            dataGridViewInventory.DataSource = autoLotDS.Tables["Inventory"];
            dataGridViewCustomers.DataSource = autoLotDS.Tables["Customers"];
            dataGridViewOrders.DataSource = autoLotDS.Tables["Orders"];
        }

        private void BuildTableRelationship()
        {
            // create customerorder data relation object.
            DataRelation dr = new DataRelation("CustomerOrder", autoLotDS.Tables["Customers"].Columns["CustID"],
                                                autoLotDS.Tables["Orders"].Columns["CustID"]);
            autoLotDS.Relations.Add(dr);

            //create inventoryorder data relation object.
            dr = new DataRelation("InventoryOrder", autoLotDS.Tables["Inventory"].Columns["CarID"],
                                   autoLotDS.Tables["Orders"].Columns["CarID"]);
            autoLotDS.Relations.Add(dr);
        }

        private void btnUpdateDatabase_Click(object sender, EventArgs e)
        {
            try
            {
                invTableAdapter.Update(autoLotDS, "Inventory");
                custTableAdapter.Update(autoLotDS, "Customers");
                ordersTableAdapter.Update(autoLotDS, "Orders");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}

i have 3 tables my problem is when i update the col CarID in Inventory table it should update the Orders CarID col too but it gives me an error
untitled28

as you can see on the image please help me i really want to learn this language its really cool and fun

If you carID column is a primary key field and it's a foreign key in another table i don't think you're allowed to update them because the foriegn key basically means it references the record in another table. For example if there is an ID of 1 and and it is changed to 3, anything that has a foreign key of the ID 1 can no longer find that ID and so you'll get that error i think.

So to sum up you can't/ shouldn't change primary key fields for consistancy and reliabilitry purposes.

Edited 4 Years Ago by ChrisHunter

oh thanks but if the carid column is not a primary key field then my code should work just fine?

is it a foreign key in another table or referenced it is still the same case i think. you would have to include the columns from the 2nd and 3rd tables in the update

Something like:

UPDATE table1.CarID, table2.CarID, table3.CarID VALUES (4,4,4) WHERE CarID = 4;

so you are updating CarID in the first table as well as the 2nd and 3rd table. The WHERE clause will define which row to update (so update the record WHERE the CarID is 4).

You should really use primary and foreign keys to avoid repatition and maintain consistancy throught the tables.

Edited 4 Years Ago by ChrisHunter

no im not updating the 2nd table just the 1st table inventory table the changes from table1 should reflect to table orders which is table 3 but thanks for the idea i will try your suggestion

Sorry if this sounds rude but have you had much experience with databases ? it might be a good idea to have a read of a few articles about relational databases as record IDs should be automaticalle incremented and shouldn't be changed once created so that you don't have the issue that you're currently facing.

i have basic knowledge in DB and im kind of new to the language so im very confuse you dont sound rude to me at all i know i still need references maybe you can give me a good begginers book title for csharp with database coz im only using 1 book and its kinda hard coz some idea isnt explaine by the author

To get a good idea of database concepts in general a book called Database principles and design.

Once you have an idea of the different concepts and designs the books would depend on what you use to create your databases as there are different database management systems which may use different variations of SQL. Judging by the image above your making your's in Visual studeos which is MicroSoft SQL i think so i would suggest you look at mcts self-paced training kit (exam 70-433) microsoft sql server 2008 - database development but only after you understand the types of databases available as it focuses more on functionality such as creating and deleting tables and other functions to manipulate your database using Microsoft SQL code.

Good luck and if you have anymore problems just ask.

Edited 4 Years Ago by ChrisHunter

This article has been dead for over six months. Start a new discussion instead.