Updating Database through c# code

Question

:) I've started with project of creating some program for Library. I mean, it is a simple program which has login form and some features for admin. Also, I've done database (everything is done with Visual Studio 2013), SQL Service-based Database. I followed some Youtube tutorials and now when I added button for adding users in database I run into "problem". This is code for Add button:

private void btnAdd_Click(object sender, EventArgs e)
        {
        string connectionString = ConfigurationManager.ConnectionStrings["Library.Properties.Settings.KnjiznicaDBConnectionString"].ConnectionString;
            string query = "INSERT INTO User VALUES(@Name,@Surname,@Year,@Mail)";
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = new SqlCommand(query, connection);
            {
                connection.Open();
                command.Parameters.AddWithValue("@Name", txtName.Text.Trim());
                command.Parameters.AddWithValue("@Surname", txtSurname.Text.Trim());
                command.Parameters.AddWithValue("@Year", txtYear.Text.Trim());
                command.Parameters.AddWithValue("@Mail", txtMail.Text.Trim());
                command.ExecuteScalar();
            }
        }

Now, I know that user is added into database, because I can see it in DataGridView which is also in Form, but when I check directly in Database (I mean in Server Explorer->Database_name->Tables->User (Show Table Data)), that new user is actually not added there. And no matter if I exit Visual Studio, as long as I don't shut down computer, that new User will be in DataGridView but not in "Database". And when I restart computer and start Visual Studio again, new user won't be even in DataGridView. Only permanent users are those that I add directly through Show Table Data or with New Query also in Server Explorer. I would like to know what should I correct so all users added through the code and Form are implemented in "real Database" not just DataGridView. Thanks :)

Can you show the actual connectionstring? Does it contains the shortcut DataDirectory? — Steve, May 07 '17 at 19:56
The code seems correct. Double check your connection string to make sure you're hitting the database you think you're hitting. — BFree, May 07 '17 at 20:01
@Steve Here it is: Data Source=(LocalDB)\v11.0;AttachDbFilename="C:\Users\Bega\documents\visual studio 2013\Projects\Knjiznica\Knjiznica\KnjiznicaDB.mdf";Integrated Security=True ----------If this is it at all :D — i542, May 07 '17 at 20:07
Please check the connectionstring used by the Server Explorer and the one stored in your app.config. — Steve, May 07 '17 at 20:13
@Steve Here it is from Server Explorer: "Data Source=(LocalDB)\v11.0;AttachDbFilename="C:\Users\Bega\documents\visual studio 2013\Projects\Knjiznica\Knjiznica\KnjiznicaDB.mdf";Integrated Security=True" And here it is from app.config: "Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\KnjiznicaDB.mdf;Integrated Security=True" — i542, May 07 '17 at 20:29
As supposed. Then look at this answer http://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails/17147460#17147460 — Steve, May 07 '17 at 20:36
If I get it right, the only problem is to change Copy To Output Directory to Copy if newer, which I did on the beginning of the project. Now, is it maybe a problem that this is (LocalDB)\v11.0. Database is .mdf extension and it is fully done in VIsual Studio, not in SQL Server Management Studio? — i542, May 07 '17 at 21:49

score 0 · Answer 1 · answered May 07 '17 at 19:55

0

Wrong command

You need SqlCommand.ExecuteNonQuery

And you would be wrapping open and command in Using blocks

answered May 07 '17 at 19:55

paparazzo

44,497
23
105
176

2

While you are correct that `ExecuteNonQuery` is the more correct method to call, `ExecuteScalar` runs the query against the db all the same, and would work as well. – BFree May 07 '17 at 20:00
@BFree Then I don't know. – paparazzo May 07 '17 at 20:03
I've tried that aswell, and no effect :/ – i542 May 07 '17 at 20:09

Updating Database through c# code

1 Answers1