Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#3.5
I have implemented TableAdapter-Dataset Structure through code in my form.
My code is:
 

public partial class frmInspToProdV2 : PPCV1.App_Code.frmBase
    {
        DataSet dsInspProd = new DataSet();
        DataTable tblMst = new DataTable();
        DataTable tblDetails = new DataTable();
        SqlDataAdapter AdaptMst = new SqlDataAdapter();
        SqlDataAdapter ADaptDetails = new SqlDataAdapter();
        BindingSource bsMaster=new BindingSource();
        BindingSource bsDetails=new BindingSource();
 
        GetAllData gadMst = new GetAllData();
        GetAllData gadDtls = new GetAllData();
        
        public frmInspToProdV2()
        {
            InitializeComponent();
        }
        private void frmInspToProdV2_Load(object sender, EventArgs e)
        {
            //LoadData();
            InitAdapters();
            InitCommonData();
        }
        private void InitAdapters()
        {
            AdaptMst.SelectCommand = new SqlCommand("SELECT * FROM Iss_Mst WHERE FromDeptID=8 AND ToDeptID<>8 ORDER BY DATE DESC", baseConnection);
            ADaptDetails.SelectCommand = new SqlCommand("SELECT ID,IssMstID,ToOprID,ToDrgId,BatchNo,Qty,FromOprID,Rew,Back FROM Iss_Dtl", baseConnection);
        }
        
        private void LoadDataV2()
        {
            try
            {
               AdaptMst.SelectCommand = new SqlCommand("SELECT * FROM Iss_Mst WHERE FromDeptID=8 AND ToDeptID<>8 ORDER BY DATE DESC", baseConnection);
                baseConnection.Open();
                AdaptMst.Fill(tblMst);
                dsInspProd.Tables.Add(tblMst);
                dsInspProd.Tables[0].TableName = "tblMst";
                dsInspProd.Tables[0].Columns["ID"].AutoIncrement = true;
                dsInspProd.Tables[0].Columns["ID"].Unique = true;
                GetDetailsData();
                      
                InitCommonData();
                SetData();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
            finally
            {
                baseConnection.Close();
            }
        }
        private void GetMasterData()
        {
            try
            {
                tblMst = gadMst.GetByQuery("SELECT * FROM Iss_Mst WHERE FromDeptID=8 AND ToDeptID<>8 ORDER BY DATE DESC", ref tspbBase,ref tsBaseLabel);
                dsInspProd.Tables.Add(tblMst);
                dsInspProd.Tables[0].TableName = "tblMst";
                dsInspProd.Tables[0].Columns["ID"].AutoIncrement = true;
                dsInspProd.Tables[0].Columns["ID"].Unique = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
        }
        private void GetDetailsData()
        {
            try
            {
                ADaptDetails.Fill(dsInspProd, "tblDetails");
                dsInspProd.Tables.Add(tblDetails);
                dsInspProd.Tables[1].TableName = "tblDetails";
                dsInspProd.Tables[1].Columns["ID"].AutoIncrement = true;
                dsInspProd.Tables[1].Columns["ID"].Unique = true;
                
                ADaptDetails.TableMappings.Add(tblDetails.TableName, dsInspProd.Tables[1].TableName);
                dsInspProd.EnforceConstraints = false;
                DataRelation dr = new DataRelation("IssDtl", dsInspProd.Tables[0].Columns["ID"], dsInspProd.Tables[1].Columns["IssMstID"]);
                dr.Nested = true;
                dsInspProd.Relations.Add(dr);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
        }
        private void InitCommonData()
        {
            DataView dvToOperations = new DataView(Operations.Table);
            DataView FromDrawings = new DataView(Drawings.Table);
            DataView RcvdBy = new DataView(Employees.Table);
            DataView ToDepts = new DataView(Departments.Table);
 
            cmbFromDept.DataSource = Departments;
            cmbFromDept.DisplayMember = "Name";
            cmbFromDept.ValueMember = "ID";
 
            cmbToDept.DataSource = ToDepts;
            cmbToDept.DisplayMember = "Name";
            cmbToDept.ValueMember = "ID";
 
            cmbIssBy.DataSource = Employees;
            cmbIssBy.DisplayMember = "FullName";
            cmbIssBy.ValueMember = "ID";
 
            cmbRcvdBy.DataSource = RcvdBy;
            cmbRcvdBy.DisplayMember = "FullName";
            cmbRcvdBy.ValueMember = "ID";
 
            ((DataGridViewComboBoxColumn)dgvInspProd.Columns["Operation"]).DataPropertyName = "ToOprID";
            ((DataGridViewComboBoxColumn)dgvInspProd.Columns["Operation"]).DisplayMember = "Name";
            ((DataGridViewComboBoxColumn)dgvInspProd.Columns["Operation"]).ValueMember = "ID";
            ((DataGridViewComboBoxColumn)dgvInspProd.Columns["Operation"]).DataSource = dvToOperations;
 
            ((DataGridViewComboBoxColumn)dgvInspProd.Columns["Drawing"]).DataPropertyName = "ToDrgID";
            ((DataGridViewComboBoxColumn)dgvInspProd.Columns["Drawing"]).DisplayMember = "Code";
            ((DataGridViewComboBoxColumn)dgvInspProd.Columns["Drawing"]).ValueMember = "ID";
            ((DataGridViewComboBoxColumn)dgvInspProd.Columns["Drawing"]).DataSource = Drawings;
 
            ((DataGridViewTextBoxColumn)dgvInspProd.Columns["BatchNo"]).DataPropertyName = "BatchNo";
            ((DataGridViewTextBoxColumn)dgvInspProd.Columns["Qty"]).DataPropertyName = "Qty";
 
            ((DataGridViewComboBoxColumn)dgvInspProd.Columns["FromOperation"]).DataPropertyName = "FromOprID";
            ((DataGridViewComboBoxColumn)dgvInspProd.Columns["FromOperation"]).DisplayMember = "Name";
            ((DataGridViewComboBoxColumn)dgvInspProd.Columns["FromOperation"]).ValueMember = "ID";
            ((DataGridViewComboBoxColumn)dgvInspProd.Columns["FromOperation"]).DataSource = Operations;
        }
        private void SetData()
        {
            bsMaster.DataSource = dsInspProd;
            bsMaster.DataMember = "tblMst";
            bsMaster.AllowNew = true;
            bsDetails.DataSource = bsMaster;
            bsDetails.DataMember = "IssDtl";
            bsDetails.AllowNew = true;
 
            bnBase.BindingSource = bsMaster;
            txtSlipNo.DataBindings.Add("Text", bnBase.BindingSource, "SlipNo");
            dtpDate.DataBindings.Add("Text", bnBase.BindingSource, "Date");
 

            cmbFromDept.DataBindings.Add("SelectedValue", bnBase.BindingSource, "FromDeptID");
            cmbFromDept.Enabled = false;
            cmbToDept.DataBindings.Add("SelectedValue", bnBase.BindingSource, "ToDeptID");
            cmbIssBy.DataBindings.Add("SelectedValue", bnBase.BindingSource, "IssBy");
            cmbRcvdBy.DataBindings.Add("SelectedValue", bnBase.BindingSource, "RcvdBy");
 
            dgvInspProd.AutoGenerateColumns = false;
            dgvInspProd.DataSource = bsDetails;
            
        }
        private void dgvInspProd_DataError(object sender, DataGridViewDataErrorEventArgs e)       
        {}
                protected override void tsbtnBaseAdd_Click(object sender, EventArgs e)
        {
            base.tsbtnBaseAdd_Click(sender, e);
            try
            {
                
                SqlCommandBuilder InsMst = new SqlCommandBuilder(AdaptMst);
                AdaptMst.InsertCommand = InsMst.GetInsertCommand();
                dsInspProd.AcceptChanges();
            }
            catch (Exception ex)
            {
                tsBaseLabel.Text = ex.Message.ToString();
            }
        }
        protected override void tsbtnBaseDel_Click(object sender, EventArgs e)
        {
            base.tsbtnBaseDel_Click(sender, e);
        }
        protected override void tsbtnBaseSave_Click(object sender, EventArgs e)
        {
            base.tsbtnBaseSave_Click(sender, e);
            try
            {
                //dsInspProd.AcceptChanges();
                SqlCommandBuilder Updt = new SqlCommandBuilder(AdaptMst);
                AdaptMst.UpdateCommand = Updt.GetUpdateCommand();
                
                AdaptMst.Update(dsInspProd, "tblMst");
                SqlCommandBuilder UpdtDetails = new SqlCommandBuilder(ADaptDetails);
                ADaptDetails.UpdateCommand = UpdtDetails.GetUpdateCommand();
                //ADaptDetails.Update(dsInspProd, "tblDetails");
       
                SqlCommandBuilder InsertDetails = new SqlCommandBuilder(ADaptDetails);
                ADaptDetails.InsertCommand = InsertDetails.GetInsertCommand();
                
                ADaptDetails.Update(dsInspProd, "tblDetails");
            
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message.ToString());
                tsBaseLabel.Text = ex.Message.ToString();
            }
        }
        protected override void tsbtnBaseRetrieve_Click(object sender, EventArgs e)
        {
            base.tsbtnBaseRetrieve_Click(sender, e);
            LoadDataV2();
        }
        protected override void tsbtnBaseEdit_Click(object sender, EventArgs e)
        {
            base.tsbtnBaseEdit_Click(sender, e);
        }
        protected override void tsbtnBaseEdit_MouseUp(object sender, MouseEventArgs e)
        {
            base.tsbtnBaseEdit_MouseUp(sender, e);
        }
        protected override void tsbtnBaseEdit_CheckedChanged(object sender, EventArgs e)
        {
        }
        protected override void bsBase_CurrentItemChanged(object sender, EventArgs e)
        {
            base.bsBase_CurrentItemChanged(sender, e);
        }
     
    }
 

 
My Prob is Insertion is not possible from DataGridView...
 
it gives an error..
Violation of Primary key constraint .... Cannot insert duplicate key in object dbo.issdtl...
Posted 24-Apr-12 0:20am

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

It looks like one of the fields (which is a primary key in your DB for that table) that you are trying to insert already exists and since it is the primary key, it does not allow you as it will be duplicate. Check your queries and make sure you are not inserting a row in table whose primary key is already there.
  Permalink  
Comments
BhavinBhatt at 25-Apr-12 0:04am
   
Which Query?
I have only design Select Statement for both the adapters..
 
when Saving.. I am taking Select/update commands at Retrieve Button event (override..)..
Sandeep Mewara at 25-Apr-12 0:45am
   
In your Update, you might be trying to update PK field as duplicate. Check your data in database and then check your query. You should figure it out.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 480
1 mhegazy94 460
2 Ravi Bhavnani 190
3 Kornfeld Eliyahu Peter 185
4 Shemeemsha RA 160
0 Sergey Alexandrovich Kryukov 7,135
1 OriginalGriff 6,801
2 CPallini 5,350
3 George Jonsson 3,619
4 Gihan Liyanage 2,797


Advertise | Privacy | Mobile
Web01 | 2.8.140922.1 | Last Updated 24 Apr 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100