0

I'm trying to save a note into the database by user input but my code isn't saving it to the Database.

I'm planning on checking the user input aswell with functions.

SqlConnection con = database.GetConnection();
SqlCommand command = new SqlCommand("INSERT INTO notities (notities_gebruiker, notities_datum, notities_doeldatum, notities_bericht) values(@notities_gebruiker, @notities_datum, @notities_doeldatum, @notities_bericht)", con);
command.Parameters.AddWithValue("@notities_gebruiker", this.gebruikerid.ToString());
command.Parameters.AddWithValue("@notities_datum", DateTime.Now);
command.Parameters.AddWithValue("@notities_doeldatum", DateTime.ParseExact(this.targetDate.Text, "dd/MM/yyyy", null));
command.Parameters.AddWithValue("@notities_bericht", this.Note.Text);
con.Open();
command.ExecuteNonQuery();
con.Close();
initialiseListBox();

What's happening is that whenever I close my application the database loses it's values saved and goes back to original state. I can add items manually though..

EDIT: It rolls back the rows I have added in via application but I am able to pull the data from the database I saved if I keep it running.

Script99
  • 101
  • 2
  • 7
  • It's hard to say where the problem comes from just with that info only... Don't you get any error message ? Cause the code I see here seems fine – Laurent S. Oct 17 '14 at 10:03
  • I get nothing ExecutenNonQuery even returns 1 but whenever I close my application the database is back to its former state. – Script99 Oct 17 '14 at 10:04
  • 3
    Sounds like you're recreating the database with every build... Check if that's the case, because code you've shown is ok. – walther Oct 17 '14 at 10:08
  • 1
    It may be a stupid question but are you sure you're looking into the proper database ? And what do you mean by "whenever I close my application the database is back to its former state" ? Do you mean you can at some point see the record added but then a rollback occurs ? – Laurent S. Oct 17 '14 at 10:08
  • He means he has never read the documentation and is not aware of the fact taht on every debug start the templacte database in localdb is copied to the output again, so he starts fresh. He also says he is not using help because this problem comes up over and over and over and over again. – TomTom Oct 17 '14 at 10:09
  • try defining the SqlDbType of 2nd and 3rd parameter to be datetime. Maybe thats why values can be stored. These to paramateres are handled as nvarchar in your code – apomene Oct 17 '14 at 10:09
  • If you are using attachment as `dbfile` , then it might be overwriting the file on every build! – Arindam Nayak Oct 17 '14 at 10:33
  • Another possibility is that you're doing this in a transaction that is not subsequently committed. – Richard Oct 17 '14 at 12:45

2 Answers2

1

You're forgetting to commit the data you've just inserted. Use the SqlTransaction class to begin a transaction:

SqlTransaction myTransaction = con.BeginTransaction();
command.ExecuteNonQuery();
myTransaction.Commit();

It would be a good idea to make use of the Using statement to make sure your connection, command and transaction are disposed of once the code block has been complete, rather than manually calling Close() methods:

Using (SqlConnection con = database.GetConnection())
   {
     con.Open();

     Using (SqlCommand command = new SqlCommand("sqlhere"))
       {

         Using (SqlTransaction myTransaction = con.BeginTransaction())
             {
               //your code here
             }

       }
   }

SqlTransaction class:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction(v=vs.110).aspx

Neat article on using statements:

http://www.codeproject.com/Articles/6564/Understanding-the-using-statement-in-C

jnhunter
  • 40
  • 1
  • 3
  • But even if you don't use `transaction` , `commit` , just use OP's question, it should work! – Arindam Nayak Oct 17 '14 at 10:23
  • Implicit commit must be switched off for OP's database. Check this post: http://stackoverflow.com/questions/1090240/how-do-you-set-autocommit-in-an-sql-server-session – jnhunter Oct 17 '14 at 10:40
0

Look for a copy of your database containing the data in your bin/debug folder.

Best way to avoid this is to use a full path in your connection string and avoid |DataDirectory| while debugging

ErikEJ
  • 40,951
  • 5
  • 75
  • 115