Unable to insert to SQL Server database

Question

using (Connection = new SqlConnection(connectionstring))                
{
    SqlCommand voegtoe = new SqlCommand("INSERT INTO Speler(Naam, Rugnummer, Leeftijd) VALUES(@Naam, @Rugnummer, @Leeftijd)");

    voegtoe.CommandType = CommandType.Text;
    voegtoe.Connection = Connection;
    voegtoe.Parameters.AddWithValue("@Naam", textBox1.Text);
    voegtoe.Parameters.AddWithValue("@Rugnummer", textBox2.Text);
    voegtoe.Parameters.AddWithValue("@Leeftijd", textBox3.Text);

    Connection.Open();
    voegtoe.ExecuteNonQuery();            
}

If I open my Database there's nothing added to it. I think it should add the text what the user puts in the textboxes.

The connection string is:

Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirector‌y|\Speler.mdf;Integr‌ated Security=True

Check if your situation is like the one described here http://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails/17147460#17147460 — Steve, Dec 09 '16 at 10:03
Surround it with a try-catch and set a breakpoint in the catch — Tim Schmelter, Dec 09 '16 at 10:04
Are you targeting the correct database? What is your connection string? Are you trying to store in a file-based database perhaps? — Panagiotis Kanavos, Dec 09 '16 at 10:05
connectionstring = ConfigurationManager.ConnectionStrings["KillerApp1.Properties.Settings.SpelerConnectionString"].ConnectionString; I found out that the problem is somewhere between the connection, because the database doesn't connect correctly — k.Sonnemans, Dec 09 '16 at 10:07
@k.Sonnemans that's *not* the connection string. It's the code you use to load the connection string from your settings. What is the connection string? Does it use `AttachDbFilename` perhaps, to load a local database? — Panagiotis Kanavos, Dec 09 '16 at 10:07
using System.Configuration; using System.Data.SqlClient; namespace KillerApp1 { public partial class Form1 : Form { SqlConnection Connection; string connectionstring; public Form1() { InitializeComponent(); connectionstring = ConfigurationManager.ConnectionStrings["KillerApp1.Properties.Settings.SpelerConnectionString"].ConnectionString; } — k.Sonnemans, Dec 09 '16 at 10:11
I repeat, that is **NOT** the connection string. That code loads the connection string from your app.config or web.config file. What is the connection string store in there? It's name is `SpelerCon‌nectionString` and also appears in your `Project > Properties > Settings` tab — Panagiotis Kanavos, Dec 09 '16 at 10:12
You mean this one: Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Speler.mdf;Integrated Security=True I'm sorry this is new to me so i dont know much about it.. — k.Sonnemans, Dec 09 '16 at 10:19
Yes. I wrote the answer while waiting, since `AttachDbFilename` is the culprit in almost all similar cases. — Panagiotis Kanavos, Dec 09 '16 at 10:20
https://www.youtube.com/watch?v=p2UeT7dBTEg i followed this YouTube guide but there's an error on the ExecuteNonQuery. It says: connection is not initialised — k.Sonnemans, Dec 09 '16 at 10:27

score 2 · Answer 1 · answered Dec 09 '16 at 10:19

There's nothing wrong with the code. I suspect that your connection string uses a User instance database, ie it has the AttachDBFilename and User Instance=true keywords:

Data Source=.\SQLEXPRESS; AttachDBFilename=|DataDirectory|\db.mdf; User Instance=true;...

That means that each user that tries to attach to the database file gets his own copy. When you try to check the data with SSMS you see a different copy of the database.

Apart from causing confusion, this feature is deprecated and will be removed in the future.

Just create a proper database in your database server and connect to it by specifying its name:

Data Source=.\SQLEXPRESS; Initial Catalog=MyDB;...

You can create the database either from the New Database menu in SSMS, or with a CREATE DATABASE MyDB command

You can find more information in Bad habits : Using AttachDBFileName by Aaron Bertrand

Unable to insert to SQL Server database

1 Answers1