none
How To Insert Into Multiple Tables In Dataset RRS feed

  • Question

  •  

    I've the following code
    i've Access db in 2 tables
    t1&t2 which has a relation in the column patientNo
    the code run successfuly and insert data into first table
    but when trying to enter data to second table it throw Exception


    Syntax error in INSERT INTO statement

    Here is the code

     
    OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb");
    
    ds =new DataSet();
        t1DataAdapter = new OleDbDataAdapter(new OleDbCommand("SELECT * FROM t1", con));
        t1Command = new OleDbCommandBuilder(t1DataAdapter);
        t1DataAdapter.FillSchema(ds, SchemaType.Source);
        pTable = ds.Tables["Table"];
        pTable.TableName = "t1";
               
        t2DataAdapter=new OleDbDataAdapter(new OleDbCommand("Select * From t2",con));
        t2Command=new OleDbCommandBuilder(t2DataAdapter);
        t2DataAdapter.FillSchema(ds,SchemaType.Source);
        pTable = ds.Tables["Table"];
        pTable.TableName = "t2";
                
      
        ds.Relations.Add(new DataRelation("Pnumber",ds.Tables["t1"].Columns["patientNo"],ds.Tables["t2"].Columns["patientNo"]));
                
                                  
                   rowT1=ds.Tables["t1"].NewRow();
                   rowT1["patientNo"]="15";
                   rowT1["pName"]="MyName";
                   rowT1["age"]="22";
                   rowT1["address"]="Home";
                   ds.Tables["t1"].Rows.Add(rowT1);
    
                                
                   rowT2=ds.Tables["t2"].NewRow();
                   rowT1["patientNo"]="15";
                   rowT2["grade"]="very Good";
                   rowT2["year"]="2007";
                                  
                   rowT2.SetParentRow(rowT1);
                   ds.Tables["t2"].Rows.Add(rowT2);
                                  
                   t1DataAdapter.Update(ds,"t1");
                             
                   t2DataAdapter.Update(ds,"t2");
                                 
                   con.Close();
     


    I 've tried the same code but with only one table and it run
    successfuly but in 2 tables it doesn't
    plz help me for determining where the error
    and if there another way to insert into
    multiple tables in the DataSet
    plz tell us
    Thank You

    Sunday, September 30, 2007 8:00 PM

All replies

  • Ref : http://www.c-sharpcorner.com/UploadFile/mahesh/DataTable2011172005235038PM/DataTable20.aspx?ArticleID=c1a6bcf4-3f38-4e44-8110-73850ba9738e

     

    Reading data from multiple tables

    using (SqlConnection connection = new SqlConnection(connectionString))

    {

       string str = string.Empty;

      DataSet ds = new DataSet(); 

       // Create the Command and Adapter

       SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", connection);

      SqlDataAdapter adapter = new SqlDataAdapter(cmd); 

       // Create a DataTable and fill it

       DataTable dtCustomers = new DataTable("Customers");

       adapter.Fill(dtCustomers);

       // Add table to the DataSet

       ds.Tables.Add(dtCustomers);

       // Select another table

       adapter.SelectCommand = new SqlCommand("SELECT * FROM Orders", connection);

       // Fill the DataSet

      adapter.Fill(ds, "Orders"); 

        // Create the DataTableReader (it is disconnected)

        using (DataTableReader dtReader = ds.CreateDataReader())

        {

            do

            {

               while (dtReader.Read())

               {

                                 // Get data and do something with it

                      str = dtReader.GetValue(0).ToString();

                }

              }

        while (dtReader.NextResult());

          }

    }

    Ref : http://www.akadia.com/services/dotnet_databinding.html

    how to make relationship between 2 tables with in single dataset

    using System;
    using System.Drawing;
    using System.Collections;
    using System.ComponentModel;
    using System.Windows.Forms;
    using System.Data;
    using System.Data.SqlClient;

    namespace Akadia
    {
        // Shows Master-Detail, Table-Mapping, Fill a Combobox
        public class MasterDetail : System.Windows.Forms.Form
        {
            .....

            // Fields
            private String ConnectionString;
            private DataViewManager dsView;
            private DataSet ds;

            public MasterDetail()
            {
                // Create Components
                InitializeComponent();

                // Setup DB-Connection
                ConnectionString = "data source=xeon;uid=sa;password=manager;database=northwind";
                SqlConnection cn = new SqlConnection(ConnectionString);

                // Create the DataSet
                ds = new DataSet("CustOrders");

               
    // Fill the Dataset with Customers, map Default Tablename
                // "Table" to "Customers".

                SqlDataAdapter da1 = new SqlDataAdapter("SELECT * FROM Customers",cn);
                da1.TableMappings.Add("Table","Customers");
                da1.Fill(ds);

               
    // Fill the Dataset with Orders, map Default Tablename
                // "Table" to "Orders".

                SqlDataAdapter da2 = new SqlDataAdapter("SELECT * FROM Orders",cn);
                da2.TableMappings.Add("Table","Orders");
                da2.Fill(ds);

               
    // Fill the Dataset with [Order Details], map Default Tablename
                // "Table" to "OrderDetails".

                SqlDataAdapter da3 = new SqlDataAdapter("SELECT * FROM [Order Details]",cn);
                da3.TableMappings.Add("Table","OrderDetails");
                da3.Fill(ds);

                // Show created Tablenames within the Dataset
                string myMessage = "Table Mappings: ";
                for(int i=0; i < ds.Tables.Count; i++)
                {
                    myMessage += i.ToString() + " "
                        + ds.TablesIdea.ToString() + " ";
                }

               
    // Establish the Relationship "RelCustOrd"
                // between Customers ---< Orders

                System.Data.DataRelation relCustOrd;
                System.Data.DataColumn  colMaster1;
                System.Data.DataColumn  colDetail1;
                colMaster1 = ds.Tables["Customers"].Columns["CustomerID"];
                colDetail1 = ds.Tables["Orders"].Columns["CustomerID"]; relCustOrd = new system.Data.DataRelation("RelCustOrd",colMaster1,colDetail1);
                ds.Relations.Add(relCustOrd);

               
    // Establish the Relationship "RelOrdDet"
                // between Orders ---< [Order Details]

                System.Data.DataRelation relOrdDet;
                System.Data.DataColumn  colMaster2;
                System.Data.DataColumn  colDetail2;
                colMaster2 = ds.Tables["Orders"].Columns["OrderID"];
                colDetail2 = ds.Tables["OrderDetails"].Columns["OrderID"];
                relOrdDet = new System.Data.DataRelation("RelOrdDet",colMaster2,colDetail2);
                ds.Relations.Add(relOrdDet);

                // Show created Relations within the Dataset
                myMessage += "Relation Mappings: ";
                for(int i=0; i < ds.Relations.Count; i++)
                {
                    myMessage += i.ToString() + " "
                        + ds.RelationsIdea.ToString() + " ";
                }
                txtMessage.Text = myMessage;

               
    // The DataViewManager returned by the DefaultViewManager
                // property allows you to create custom settings for each
                // DataTable in the DataSet.

                dsView = ds.DefaultViewManager;

                // Grid Databinding
                grdOrders.DataSource = dsView;
                grdOrders.DataMember = "Customers.RelCustOrd";

                grdOrderDetails.DataSource = dsView;
                grdOrderDetails.DataMember = "Customers.RelCustOrd.RelOrdDet";

                // Combobox Databinding
                cbCust.DataSource = dsView;
                cbCust.DisplayMember = "Customers.CompanyName";
                cbCust.ValueMember = "Customers.CustomerID";

                // Text Columns Databinding
                txtContact.DataBindings.Add("Text",dsView,"Customers.ContactName");
                txtPhoneNo.DataBindings.Add("Text",dsView,"Customers.Phone");
                txtFaxNo.DataBindings.Add("Text",dsView,"Customers.Fax");
            }

            // Position to prev Record in Customer
            private void btnPrev_Click(object sender, System.EventArgs e)
            {
                if (this.BindingContext[dsView,"Customers"].Position > 0)
                {
                    this.BindingContext[dsView,"Customers"].Position--;
                }
            }

            // Position to next Record in Customer
            private void btnNext_Click(object sender, System.EventArgs e)
            {
                CurrencyManager cm = (CurrencyManager)this.BindingContext[dsView,"Customers"];
                if (cm.Position < cm.Count - 1)
                {
                    cm.Position++;
                }
            }

            // The main entry point for the application.
            static void Main()
            {
                Application.Run(new MasterDetail());
            }
        }
    }

    Friday, October 5, 2007 10:20 AM