Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have implemented TableAdapter-Dataset Structure through code in my form.
My code is:


C#
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

1 solution

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.
 
Share this answer
 
Comments
BhavinBhatt 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 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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900