6 Replies - 3463 Views - Last Post: 14 August 2014 - 02:40 PM Rate Topic: -----

#1 C.Andrews   User is offline

  • D.I.C Head
  • member icon

Reputation: 15
  • View blog
  • Posts: 169
  • Joined: 18-October 12

Having trouble saving changes in Dataset to SQL LocalDB

Posted 14 August 2014 - 02:03 PM

I'm writing an application that will be used to collect data in poultry houses which don't have access to any kind of connectivity, so I'm storing the data locally in a SQL LocalDB database. I set up the database inside VS2013, (and I'm able to add data directly to the DB from the provided interface in the IDE) but I cannot get data into it programmatically despite all my contortions trying to get it to work. I've read and re-read the MSDN articles relating to using the DataSet, tableAdapterManager, bindingSources, and tableAdapters the IDE autogenerates when you add the DB as a datasource to the project, but I feel like I have it set up correctly and it should just work.

Even if I leave the databound DataGridView controls the IDE generates in the program and try to add data directly to those using the BindingNavigator thing, I still can't get the data into the database. I can see that my dataset in memory is being updated and manipulated according to my whims, but when I try to update the DB, it silently fails with no exceptions. I've discovered that no matter what I do to the in-memory dataset, DataSet.HasChanges() method always returns false.

Here's how I'm adding rows to the dataset and attempting to update them at the moment:

    DataRow newFlockRow = this.localFlockData.FlockDemographics.NewRow();
    newFlockRow.BeginEdit();
    newFlockRow["Internal_ID"] = internal_ID;
    newFlockRow["Flock_ID"] = Convert.ToInt64(txtFlockID.Text);    
    newFlockRow["Active"] = true;    
    newFlockRow["Farm"] = cmbFarm.Text;
    newFlockRow["House"] = cmbHouse.Text;
    newFlockRow["Pen"] = cmbPen.Text;
    newFlockRow["Male_Breed"] = cmbMaleBreed.Text;
    newFlockRow["Female_Breed"] = cmbFemaleBreed.Text;
    newFlockRow["Placement_Date"] = DateTime.Now.Date;
    newFlockRow["Placement_Age"] = Convert.ToInt32(txtAge.Text);
    newFlockRow["Sale_Age"] = Convert.ToInt32(txtSaleAge.Text);
    newFlockRow["Male_Inventory"] = Convert.ToInt32(txtMaleInv.Text);
    newFlockRow["Female_Inventory"] = Convert.ToInt32(txtFemaleInv.Text);
    newFlockRow["Notes"] = rtbNotes.Text;
    //add the row to the dataset and finalize it
    this.localFlockData.FlockDemographics.Rows.Add(newFlockRow);
    newFlockRow.AcceptChanges();
    newFlockRow.EndEdit();

    this.flockDemographicsBindingSource.EndEdit();
    this.localFlockData.FlockDemographics.AcceptChanges();

    if (localFlockData.HasChanges(DataRowState.Added))
    {
        Console.WriteLine("Yes");
    }
    else
    {
        Console.WriteLine("No");
    }
    
    tableAdapterManager.UpdateAll(localFlockData);

    //flockDemographicsTableAdapter.Insert(
    //    internal_ID,
    //    Convert.ToInt64(txtFlockID.Text),
    //    true,
    //    cmbFarm.Text,
    //    cmbHouse.Text,
    //    cmbPen.Text,
    //    cmbMaleBreed.Text,
    //    cmbFemaleBreed.Text,
    //    DateTime.Now.Date,
    //    Convert.ToByte(txtAge.Text),
    //    Convert.ToByte(txtSaleAge.Text),
    //    Convert.ToInt16(txtMaleInv.Text),
    //    Convert.ToInt16(txtFemaleInv.Text),
    //    rtbNotes.Text
    //    );



So, how do I get the DataSet to admit that it has changes and allow me to update the actual DB on the hard disk? Any insight would be very helpful, thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: Having trouble saving changes in Dataset to SQL LocalDB

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15790
  • View blog
  • Posts: 63,277
  • Joined: 12-June 08

Re: Having trouble saving changes in Dataset to SQL LocalDB

Posted 14 August 2014 - 02:11 PM

Have you tried the normal UPDATE/INSERT recommended in the tutorials?


SQL Basics In VB.Net

OleDb Basics in VB.Net

A Really Simple Database Create a Database using Access & VB.net Express 2008

Snippet:
Basic SQL Adapter Setup

Basic SQL Command Setup
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw   User is offline

  • palpable absurdity
  • member icon

Reputation: 6905
  • View blog
  • Posts: 28,565
  • Joined: 12-December 12

Re: Having trouble saving changes in Dataset to SQL LocalDB

Posted 14 August 2014 - 02:18 PM

It is possible your changes are made to a newly created copy of the database each time.

This article refers to ASP but the principles are the same:

Dealing with Local Databases, or Why Your Updates Don't Stick

magazine said:

To get that to happen, you'll need to change the Copy to Output Directory property on the database file in your project's root. The default setting for the property is "Copy Always." The setting you probably want is "Copy If Newer". This will copy the database from the project root only if you make some change to the database and build your project. Now, if you make some changes in a test run, those changes will still be there in your next test run.

Was This Post Helpful? 0
  • +
  • -

#4 C.Andrews   User is offline

  • D.I.C Head
  • member icon

Reputation: 15
  • View blog
  • Posts: 169
  • Joined: 18-October 12

Re: Having trouble saving changes in Dataset to SQL LocalDB

Posted 14 August 2014 - 02:25 PM

View Postmodi123_1, on 14 August 2014 - 09:11 PM, said:



Actually, I was trying to work from here:
http://www.dreaminco...ndingsource-iv/

...as this setup is closest to the structure I set up in my application. In every application I've written prior to this, I just constructed my own parameterized SQL statements and did my updating more or less by hand that way, but I really wanted to simplify the code for this application and it looked like it would be easier than it's turning out to be.

I'll read through your links and muck about some more.

View Postandrewsw, on 14 August 2014 - 09:18 PM, said:

It is possible your changes are made to a newly created copy of the database each time.

This article refers to ASP but the principles are the same:

Dealing with Local Databases, or Why Your Updates Don't Stick

magazine said:

To get that to happen, you'll need to change the Copy to Output Directory property on the database file in your project's root. The default setting for the property is "Copy Always." The setting you probably want is "Copy If Newer". This will copy the database from the project root only if you make some change to the database and build your project. Now, if you make some changes in a test run, those changes will still be there in your next test run.


I thought of that right off, but I don't think that's the problem I'm having for two reasons:

1. When I try to update a row that I've just added, I get a concurrency exception like the row doesn't exist, but since I just added the row it would be there even in a newly copied version of the stored Db, right?

2. I already tried setting the Db to Copy if New, and I also tried changing the connection string at runtime to a specific location elsewhere on the hard disk
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is offline

  • palpable absurdity
  • member icon

Reputation: 6905
  • View blog
  • Posts: 28,565
  • Joined: 12-December 12

Re: Having trouble saving changes in Dataset to SQL LocalDB

Posted 14 August 2014 - 02:25 PM

Quote

> However, we should never check HasChanges property after calling
> AccesptChagnes() method. DataSet checks for changes according to the
> RowState property for each DataRow object. If you call AcceptChanges()
> method, all the RowState will be set to Unchanged, and the HasChanges
> property will always return false.

This is logical. If you have accepted changes then a call to HasChanges should be false (regardless as to whether the changes have been successfully committed to the database or not).
Was This Post Helpful? 1
  • +
  • -

#6 C.Andrews   User is offline

  • D.I.C Head
  • member icon

Reputation: 15
  • View blog
  • Posts: 169
  • Joined: 18-October 12

Re: Having trouble saving changes in Dataset to SQL LocalDB

Posted 14 August 2014 - 02:29 PM

View Postandrewsw, on 14 August 2014 - 09:25 PM, said:

This is logical. If you have accepted changes then a call to HasChanges should be false (regardless as to whether the changes have been successfully committed to the database or not).



Thought of that too; commenting out my "Accept Changes" lanes still produces a "No"

...So I'm retrying these steps as I go to make sure I'm not telling you lies, and it turns out I'm telling you lies. I actually can get a "Yes" out of "HasChanges" by killing the AcceptChanges line; however, it still doesn't save my bleeding data into the local DB.

Further edit:
Ok, I got it working, it turns out it was the .AcceptChanges() that was mucking me up after all! I only thought the row hadn't saved due to an error in my recall logic that made it fail to find the row I was looking for. Thanks a bundle for straigtening me out!

This post has been edited by C.Andrews: 14 August 2014 - 02:38 PM

Was This Post Helpful? 0
  • +
  • -

#7 andrewsw   User is offline

  • palpable absurdity
  • member icon

Reputation: 6905
  • View blog
  • Posts: 28,565
  • Joined: 12-December 12

Re: Having trouble saving changes in Dataset to SQL LocalDB

Posted 14 August 2014 - 02:40 PM

If you get a concurrency exception how do you know that the new row isn't rejected?

I would comment out all AcceptChanges, etc., and just test with BeginEdit, EndEdit, and UpdateAll.

Quote

Thought of that too; commenting out my "Accept Changes" lanes still produces a "No"

I would have thought it would be the other way round, ignoring the HasChanges value.

http://www.dreaminco...ndingsource-iv/

That's a great tutorial ;), it cannot be the problem :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1