Database manipulation with linq lists

Question

2.50/5 (2 votes)

See more:

Hi All,
I'm new to C# and I'm trying to make an application that will import an excel file into a linq list which in turn will import that to a database. On the form itself i have a 2 buttons, Import and Cancel (the cancel button is for the background worker) and 4 datagridview. When you click the import button for the first time, it will run perfectly, everything is written into the database. but if you cancel, or import a new file, I get an error message:

The property 'ID' is part of the object's key information and cannot be modified.

I figured that the items in either the datagridview, list, or the objData4 still contains the items from the previous import, so I have a line of code that will clear everything from all sources, objData, list, datagridview, datasource, but i still get that error. I've been at this for almost 2 weeks now. if anyone can help me it would be so amazing!!!

Thanks in Advance
if you need to see the code, please let me know.

Update 6.27.11
I un-commented out the following lines, to ensure that everything was cleared before importing a new file:
itemMasterList.Clear();
objData1.Clear();

but then i get this new error at the dmConsumption.SaveChanges(); line:
The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: AcceptChanges cannot continue because the object's key values conflict with another object in the ObjectStateManager. Make sure that the key values are unique before calling AcceptChanges.
im totally at a loss, can someone please help me out?

heres the code ^^

C#

#region Excel Import Button
       private void button1_Click(object sender, EventArgs e)
       {
           pBarStat.Value = 0;
           dgView1.DataSource = null;

//with this uncommented i get this error:
//The property 'ID' is part of the object's key information and cannot be modified.
           //itemMasterList.Clear();
           //salesLineList.Clear();
           //itemLedgerList.Clear();
           //purchaseLineList.Clear();
           //objData4.Clear();
           //itemMasterList.Clear();

           #region Delete Statements

           //Sales Line
           foreach (var deleteMe in dmConsumption.SalesLines)
           {
               dmConsumption.SalesLines.DeleteObject(deleteMe);
           }


           //Purchase Line
           foreach (var deleteMe in dmConsumption.PurchaseLines)
           {
               dmConsumption.PurchaseLines.DeleteObject(deleteMe);
           }


           //Item Ledger
           foreach (var deleteMe in dmConsumption.ItemLedgers)
           {
               dmConsumption.ItemLedgers.DeleteObject(deleteMe);
           }


           //Item Master
           foreach (var deleteMe in dmConsumption.ItemMasters)
           {
               dmConsumption.ItemMasters.DeleteObject(deleteMe);
           }
           dmConsumption.SaveChanges();
           #endregion

           try
           {
               openFile.InitialDirectory = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
               openFile.Title = "Import Excel File From:";
               openFile.FileName = "";
               openFile.Filter = "Excel 97-2003|*.xls";

               if (openFile.ShowDialog() == DialogResult.OK)
               {
                   source = openFile.FileName;
                   if (IMWorker.IsBusy)
                   {
                       button1.Enabled = false;
                       Status.Text = "Cancelling...";
                       IMWorker.CancelAsync();
                   }
                   else
                   {
                       button1.Enabled = false;
                       button3.Enabled = true;
                       button3.Visible = true;
                       Status.Text = "Processing data...";

                       IMWorker.RunWorkerAsync();
                   }
               }
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.ToString());
           }
       }
       #endregion

C#

#region IMWorker

 #region IMWorker DoWork
 public void IMworker_DoWork(object sender, DoWorkEventArgs e)
{
    Thread.Sleep(1000);
     excelPop = ExcelConnection();
    objDA1.SelectCommand = excelPop[0];
    objDA1.Fill(objData1);
    increment = objData1.Tables[0].Rows.Count;

     pBarStat.ProgressBar.Invoke((MethodInvoker)delegate()
    {
        pBarStat.Maximum = increment;
    });

    if (dgView1.InvokeRequired)
    {
        dgView1.Invoke(new MethodInvoker(delegate { dgView1.DataSource = objData1.Tables[0].DefaultView; }));
    }
        foreach (DataRow row in objData1.Tables[0].Rows)
        {

            var IM = new itemMaster();
                try
                {
                    IM.ID = row["No#"].ToString();
                }
                catch (Exception ex)
                {
                    IM.IMError = "Error";
                }

                try
                {
                    IM.name1 = row["Description"].ToString();
                }
                catch (Exception ex)
                {
                    IM.IMError = "Error";
                }

                try
                {
                    IM.name2 = row["Description 2"].ToString();
                }
                catch (Exception ex)
                {
                    IM.IMError = "Error";
                }

                try
                {
                    IM.unitPrice = double.Parse(row["Unit Cost"].ToString());
                }
                catch (Exception ex)
                {
                    IM.unitPrice = 0;
                    IM.IMError = "Error";
                }

                itemMasterList.Add(IM);
                pBarStat.ProgressBar.Invoke((MethodInvoker)delegate()
                {
                    pBarStat.Value = increment;
                });
                //itemMasterWorker.ReportProgress(xy);
        }


        foreach (var imItem in itemMasterList)
        {
            ItemMaster newItemMaster = new ItemMaster();
            newItemMaster.ID = imItem.ID.ToString();
            newItemMaster.Generic = imItem.name1.ToString();
            newItemMaster.Brand = imItem.name2.ToString();
            newItemMaster.UnitPrice = decimal.Parse(imItem.unitPrice.ToString());

            dmConsumption.ItemMasters.AddObject(newItemMaster);
        }
    dmConsumption.SaveChanges();
//i get this error here {"Violation of PRIMARY KEY constraint 'PK_ItemMasters'. Cannot insert duplicate key in object 'dbo.ItemMasters'.\r\nThe statement has been terminated."}
}
 #endregion

 #region IMWorker Complete
 private void IMWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
 {
     if (e.Error != null)
     {
         MessageBox.Show(e.Error.Message);
     }

     button3.Visible = false;
     button3.Enabled = false;
     button1.Enabled = true;
     button1.Visible = true;

     if (e.Cancelled)
     {
         MessageBox.Show("Operation Cancelled", "Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
         Status.Text = "Ready...";
         return;
     }
     else
     {
         Status.Text = "Operation Successfull!";
         pBarStat.Value = increment;
     }
 }
 #endregion

Posted 23-Jun-11 20:03pm

Jester69

Updated 26-Jun-11 15:56pm

v4

Add a Solution

Comments

[no name] 24-Jun-11 2:07am

Please post your code then only we can guide you where you were struck up.

Jester69 24-Jun-11 2:42am

posted ^^

RakeshMeena 24-Jun-11 2:10am

If you can post the code as well!

3 solutions

Solution 2

Try to insert this line of code before your code:

foreach (var imItem in itemMasterList)
{
ItemMaster newItemMaster = new ItemMaster();
newItemMaster.ID = imItem.ID.ToString();
newItemMaster.Generic = imItem.name1.ToString();
newItemMaster.Brand = imItem.name2.ToString();
newItemMaster.UnitPrice = decimal.Parse(imItem.unitPrice.ToString());
dmConsumption.ItemMasters.AddObject(newItemMaster);
}

This is to insure that there is no empty value in
your itemMasterList row["No#"] column.
Also for sure that there is no duplication
trace/debug and see the value on below for loop row["No#"] value...

// Delete all rows on row["No#"] column having empty value

C#

for (int i = 0; i < itemMasterList.Rows.Count; i++)
{
  if (itemMasterList.Rows[i]row["No#"].ToString() == string.Empty)
  {
       itemMasterList.Rows[i].Delete();
  }
}
itemMasterList.AcceptChanges();

Hope this could help..

Regards,

Algem

Posted 26-Jun-11 16:32pm

Al Moje

Solution 3

forgot to close the connection. now i feel stupid. thanks for all the replies and help ^^

Posted 26-Jun-11 17:51pm

Jester69

Add a Solution

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

Al Moje · Accepted Answer · 2011-06-23T21:40:00

Hi,

Maybe I could suggest you to check the table structure of ItemMaster.
If I am not mistaken, the field ID identification specification structure
was define as Identity Increment by 1, and this is the Primary Key.

If this is the case, You should not asign the value on it to save in your table.
This could create an Violation of PRIMARY KEY

I suggest you to review your code below:

foreach (var imItem in itemMasterList)
{
ItemMaster newItemMaster = new ItemMaster();
newItemMaster.ID = imItem.ID.ToString(); newItemMaster.Generic = imItem.name1.ToString();
newItemMaster.Brand = imItem.name2.ToString();
newItemMaster.UnitPrice = decimal.Parse(imItem.unitPrice.ToString());
dmConsumption.ItemMasters.AddObject(newItemMaster);
}

dmConsumption.SaveChanges();

I suspected error goes in dmConsumption.SaveChanges();
when trigered...

I notice that you're manipulating several tables in a transaction.
I sugest you to have a management transaction in your code.
like:
SqlConnection aConn = new SqlConnection(yourStrConnfromConfig);
SqlTransaction tran;
SqlCommand cmd;
try
{
aConn.Open();
tran = aConn.BeginTransaction();
}
catch (Exception)
{
return "Connection to database failed.";
}

//1. Sales Line
string yourQueryString1; // This is the Sales Line query
cmd = new SqlCommand(yourQueryString1, aConn);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();

//2. Purchase Line
string yourQueryString2; // This is the Purchase Line query
cmd = new SqlCommand(yourQueryString2, aConn);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();

//3. Item Ledger
string yourQueryString3; // This is the Item Ledger query
cmd = new SqlCommand(yourQueryString3, aConn);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();

//4. Item Master
string yourQueryString4; // This is the Item Master query
cmd = new SqlCommand(yourQueryString4, aConn);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();

try
{
tran.Commit(); // commit four Transactions
}
catch (Exception)
{
tran.Rollback(); // Roll back Transactions
return "Process aborted, Transaction were rolled back";
}
finally
{
aConn.Close();
tran.Dispose();
}

Hope this could help...

Regards,

Algem

Database manipulation with linq lists

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Database manipulation with linq lists

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us