-1

my problem is currently that I add a player to the database and that player is visible only as long as the program runs.

The test() method should add a player to the database.

private static void Test()
{
    SqlConnection connection;
    string connectionString = ConfigurationManager.ConnectionStrings["Footbal.Properties.Settings.cn"].ConnectionString;

    connection = new SqlConnection(connectionString);
    connection.Open();
    string query = "INSERT INTO Players(id, player_name, player_price, player_rating) VALUES(@id, @player_name, @player_price, @player_rating)";
    string name = "Ronaldo";

    SqlCommand testInsert = new SqlCommand(query, connection);
    testInsert.Parameters.AddWithValue("@id", 43);
    testInsert.Parameters.AddWithValue("@player_name", name);
    testInsert.Parameters.AddWithValue("@player_price", 34);
    testInsert.Parameters.AddWithValue("@player_rating", 54);

    testInsert.ExecuteScalar();

    connection.Close();
}

After this method the DataGridView is filled. (playersTableAdapter acts as a bridge between DataSet(db_PlayersDataSet.Players) and database)

playersTableAdapter.Fill(db_PlayersDataSet.Players);

DataGridView

So far everything works as expected. And now when I close the program, the added data is gone.

Database

What is the problem?

Shiraq Chicago
  • 17
  • Show us the _exact_ connection string. 95% chance your database is being overwritten on every build / deploy. – mjwills Aug 20 '21 at 23:18
  • Perhaps this will help: https://stackoverflow.com/questions/30945800/ado-net-insert-not-inserting-data – Svein Terje Gaup Aug 20 '21 at 23:23
  • connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\DB\db_Players.mdf;Integrated Security=True" @mjwills – Shiraq Chicago Aug 20 '21 at 23:33
  • 1
    Does this answer your question? [what's the issue with AttachDbFilename](https://stackoverflow.com/questions/11178720/whats-the-issue-with-attachdbfilename) – Charlieface Aug 21 '21 at 23:49

3 Answers3

2

I suspect you're using an attached-when-the-program-runs database, meaning that when the project is run a new database (copied from the project folder) is output and attached

Attach your db permanently and adjust your connstr, or just accept the behavior; it'll be fine when the app is deployed

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • This is my connstr now: `string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\DB\db_Players.mdf;Integrated Security=True";` – Shiraq Chicago Aug 21 '21 at 12:05
  • https://learn.microsoft.com/en-us/sql/relational-databases/databases/attach-a-database?view=sql-server-ver15 – Caius Jard Aug 21 '21 at 18:00
-1

In your test() method, you need to use ExecuteNonQuery() instead of ExecuteScalar(). Just change below line in above code and it should work.

Change

testInsert.ExecuteScalar();

to

testInsert.ExecuteNonQuery();
Brijesh Kumar Tripathi
  • 1,762
  • 15
  • 25
-2

Is "Id" Primary Key in the database? Did you try to insert without "Id", or to use different "Id" for new player?

Polat Dundar
  • 76
  • 9