Struggling to save DataSet to Database

[TarkiB]

Hi guys,

I'm using a very simple database and dataset and it appears to be working, except I can't actually save any data. Here's what I did:

I created an empty database with a corresponding empty dataset. I filled it in with 4 rows of dummy data (ID, name, address, etc). I dragged the table (called Lawyer) to the dataset. This created the update and insert methods.

I then created a class, which has two methods. One to count the records, and one to add new data. This should be simple, and I've done it before, but for some reason this compiles and runs perfectly fine, but it doesn't actually commit anything to either the dataset or database. I've got this class working with a GUI, and the GUI allows me to add rows, and shows me how many rows are in the table. I'm able to add rows and see the number of rows go up. However, once I quit the application and check the dataset and database, there are no new records.

Here is my class. Can anyone give me any hints as to why this may not be saving changes to the dataset and database?

Thanks!

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace UserManagement
{
    class AddLawyerHandler
    {
        DAL.LawyerDataSet dsLaw = new DAL.LawyerDataSet();
        DAL.LawyerDataSetTableAdapters.LawyerTableAdapter lawyerList = new DAL.LawyerDataSetTableAdapters.LawyerTableAdapter();
        DAL.LawyerDataSet.LawyerDataTable LawyerTable = new DAL.LawyerDataSet.LawyerDataTable();
        static SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Lawyer.mdf;Integrated Security=True");


        public int FindLawyerNumbers()
        {            
            int count = lawyerList.Fill(LawyerTable);
            return count;
        }

        public void AddLawyer()
        {
            int lawyerID = FindLawyerNumbers() + 1;
            LawyerTable.AddLawyerRow(lawyerID, "Johnno Matthews", "Matthews & Co", "16 Tynes St, Tyne", "06525844", "[e-mail]", "www.jm.com");
            lawyerList.Update(LawyerTable);
            dsLaw.AcceptChanges();

            lawyerID = 0;
        }

    }
}

[PerplexedB]

It looks like the update command was not created in lawyerList. Check lawyerList.UpdateCommand. If that is empty, then you should create an sqlCommandBuilder(lawyerList) and execute its GetUpdateCommand() method before your call to Update, or in a constructor.

Hope this help and good luck.

[TarkiB]

Thanks for the reply. I checked the UpdateCommand of the tableadaptor, it shows the Update command as follows:

UPDATE [dbo].[Lawyer] SET [Id] = @Id, [Name] = @Name, [Firm] = @Firm, [Address] = @Address, [Phone] = @Phone, [Email] = @Email, [Website] = @Website WHERE (([Id] = @Original_Id));
SELECT Id, Name, Firm, Address, Phone, Email, Website FROM Lawyer WHERE (Id = @Id)

My SQL is pretty weak but it looks like this should be sufficient?

I tried sqlCommandBuilder(lawyerList) but lawyerList is a TableAdapter, whereas it needs to be a SqlDataAdapter for that it work it seems.

[PerplexedB]

You're right. My assumption is wrong, the update command was created alright. No need to build it.
The problem I can think of is the way you handle the id field. Is this not some kind autoincrement field? I so, my guess is you're kind of confusing the db by setting it. Check the code of AddLawyersRow in the designer, and check if you need to supply a value for id. I doubt it, the generated code should handle it (the id field) for you.

[Momerath]

Are you running in debug mode in Visual Studio? Since you are using a file based database, Visual Studio makes a copy of it so you don't 'ruin' your original database while debugging.

[TarkiB]

The ID field doesn't appear to auto-increment unfortunately, I think I have to make sure I'm specifying the next ID.

I was running it in debug, changed to release and I'm still having the same issue. It's driving me crazy, it's such a simple program, I can't see where the issue is.

If anyone is curious, here's a link to the solution file to this post. The AddLawyerHandler.cs is where the problem seems to be.
https://www.dropbox.com/s/h6a4qghhelu0zcg/UserManagement.zip
If you run the program, it'll display a GUI, which allows you to add a specified row to the database, and check how many rows are there.

Cheers for any further help

[PerplexedB]

Right again... It's not the ID. :)

In fact Mormerath is right. Your problem is that your lawyer.mdf is part of the project. You should exclude it (I think I did that from the properties in the database explorer).

I'm not sure about what you need to do next, but I have code here that runs alright and that shows that I'm on the right track:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SqlClient;


namespace UserManagement
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        private SqlConnection conn = new System.Data.SqlClient.SqlConnection();

        public MainWindow()
        {
            InitializeComponent();
            testConnection();
        }

        public void Button_Click_1(object sender, RoutedEventArgs e)
        {
            AddLawyerHandler AddLaw = new AddLawyerHandler();
            testLbl.Content = AddLaw.FindLawyerNumbers();
        }

        private void btnAdd_Click(object sender, RoutedEventArgs e)
        {
            AddLawyerHandler AddLaw = new AddLawyerHandler();
            AddLaw.AddLawyer();
        }
        private void testConnection()
        {
            string c = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=Z:\_projects\140424 daniweb - dataadapter\UserManagement\UserManagement\Lawyer.mdf;Integrated Security=True;Connect Timeout=30";
            conn.ConnectionString = c;
            conn.Open();
            SqlCommand cmd = new SqlCommand("select * from lawyer", conn);
            SqlDataReader reader = cmd.ExecuteReader();
            string c1 = reader.RecordsAffected.ToString();
            int i = 0;
            while (reader.Read())
            {
                i++;
            }
            reader.Close();
            MessageBox.Show(i.ToString());
            SqlCommand cmdInsert = new SqlCommand("insert into lawyer (id,name,phone,email) values (@id,@name,@phone,@email) ", conn);
            cmdInsert.Parameters.Add("@id", i+1);
            cmdInsert.Parameters.Add("@name","Name 6");
            cmdInsert.Parameters.Add("@phone", "Phone 6");
            cmdInsert.Parameters.Add("@email", "Email 6");
            int recordsAffected = cmdInsert.ExecuteNonQuery();
            MessageBox.Show(recordsAffected.ToString());
        }


    }
}