Database not updating C#

Question

I'm trying to save data from textboxes to a database, the new data appears in the grid, but it doesn't appear in the database table. When I close the application and restart it, the data which appeared in the grid disappears. There is no error, but no data is uploaded. It works if I hardcode the path but I wanted to use the DataDirectory so that it can be used on other computers. The database is set to Copy if newer (for the copy to output directory). Could someone help?

if (dialogResult == DialogResult.Yes)
        {
            SqlCommand cmd;
            SqlConnection con;

            con = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = |DataDirectory|\TestDatabase.mdf;Integrated Security=True");
            con.Open();
            cmd = new SqlCommand("INSERT INTO Patient (FirstName, LastName, DOB) VALUES (@FirstName,@LastName,@DOB)", con);

            cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
            cmd.Parameters.AddWithValue("@LastName", txtLastName.Text);
            cmd.Parameters.AddWithValue("@DOB", dateTimePicker1.Text);
           
            cmd.ExecuteNonQuery();
            con.Close();
           
            SqlDataAdapter SqlDA = new SqlDataAdapter("SELECT * FROM Patient", con); 
            DataTable dtbl = new DataTable();
            SqlDA.Fill(dtbl);

            dataGridView1.DataSource = dtbl;
}

Please clarify. Are you inserting into the [Patient] table but querying from the [Users] table. are they related? — Glenn Ferrie, May 14 '21 at 13:39
looks like your database get overwritten everytime you build — Amr Elgarhy, May 14 '21 at 13:39
If the row isn't showing up in the [Patient] table, I'd say the most likely culprit is that the text formatted datetime that you are getting from `dateTimePicker1.Text` is not formatted is a way that acceptable to SQL. You may find better results adding `@DOB` as a `datetime` Parameter and then convert the text from `dateTimePicker1` to `DateTime?` before you set the parameter value — Glenn Ferrie, May 14 '21 at 13:43
Inserting into patient table, and querying patient table (just updated it) — Rubik, May 14 '21 at 13:43
It works if I hardcode the directory, but not if I use DataDirectory — Rubik, May 14 '21 at 13:44
Are you sure this is working at all? You are closing your connection before your try to read the data back out. Do you have a tool to look at the table in your database? You might find that the records are there but you just aren't selecting anything with the SqlDataAdapter. — Cᴏʀʏ, May 14 '21 at 13:45
Never use both "Data Source" and AttachDbFilename is a connection string. The Data Source is when mdf file is attached to a SQL Server. AttachDbFilename is used when you mdf file is not attached. Remove the "Data Source". — jdweng, May 14 '21 at 14:09
@Cᴏʀʏ `SqlDataAdapter` also opens the connection itself, so this would work without calling `con.Open();` after `con.Close();`. — WSC, May 14 '21 at 14:41
Does this answer your question? [what's the issue with AttachDbFilename](https://stackoverflow.com/questions/11178720/whats-the-issue-with-attachdbfilename) It creates a copy of the database, and deletes it when you close the connection. Incidentally you should dispose your connecton and command etc objects with `using`, and avoid `AddWithValue` instead specify parameter types — Charlieface, May 14 '21 at 17:27

Database not updating C#

0 Answers0