SQL Server Express database table not updating

Question

I wonder why my SQL Server Express database table doesn't get updated when the method below executes successfully?

public void addUser(User user)
{
    string query = "INSERT INTO users (username, password, firstname, lastname, isactive, accesslevel) VALUES (@usr,  @psw, @fname, @lname, @status, @access)";

    pSqlConn = new SqlConnection(pConnectingString);

    SqlCommand cmd = new SqlCommand(query, pSqlConn);

    SqlParameter pmtrUsername = new SqlParameter("@usr", user.Username);
    SqlParameter pmtrPassword = new SqlParameter("@psw", user.Password);
    SqlParameter pmtrFirstname = new SqlParameter("@fname", user.Firstname);
    SqlParameter pmtrLastname = new SqlParameter("@lname", user.Lastname);
    SqlParameter pmtrStatus = new SqlParameter("@status", user.IsActive);
    SqlParameter pmtrAccessLevel = new SqlParameter("@access", user.AccessLevel);

    cmd.Parameters.Add(pmtrFirstname);
    cmd.Parameters.Add(pmtrLastname);
    cmd.Parameters.Add(pmtrUsername);
    cmd.Parameters.Add(pmtrPassword);
    cmd.Parameters.Add(pmtrStatus);
    cmd.Parameters.Add(pmtrAccessLevel);

    pSqlConn.Open();
    cmd.ExecuteNonQuery();

    System.Windows.Forms.MessageBox.Show("Success!");
    pSqlConn.Close();
}

private void btnSave_Click(object sender, EventArgs e) { DatabaseConnection DB = new DatabaseConnection(); DB.ConnectingString = Properties.Settings.Default.ConnectingString; DB.addUser(user); } — Joel, Apr 25 '20 at 05:08
And what **value** is strong in the settings for your connection string?? — marc_s, Apr 25 '20 at 05:16
Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\storemanagerdb.mdf;Integrated Security=True;Connect Timeout=30 — Joel, Apr 25 '20 at 05:22
Please [edit] additional information directly into your question. — Dale K, Apr 25 '20 at 06:17

score 1 · Answer 1 · answered Apr 25 '20 at 05:23

The whole User Instance and AttachDbFileName= approach is flawed - at best! When running your app in Visual Studio, it will be copying around the .mdf file (from your App_Data directory to the output directory - typically .\bin\debug - where you app runs) and most likely, your INSERT works just fine - but you're just looking at the wrong .mdf file in the end!

If you want to stick with this approach, then try putting a breakpoint on the myConnection.Close() call - and then inspect the .mdf file with SQL Server Mgmt Studio - I'm almost certain your data is there.

The real solution in my opinion would be to

install SQL Server Express (and you've already done that anyway)
install SQL Server Management Studio Express
create your database in SSMS Express, give it a logical name (e.g. StoreManagerDB)
connect to it using its logical database name (given when you create it on the server) - and don't mess around with physical database files and user instances. In that case, your connection string would be something like:
```
Data Source=.\\SQLEXPRESS;Database=StoreManagerDB;Integrated Security=True
```
and everything else is exactly the same as before...

Also see Aaron Bertrand's excellent blog post Bad habits to kick: using AttachDbFileName for more background info.

And another [discussion of the substitution string usage](https://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails) in your connection and why things seem to not "work", — SMor, Apr 25 '20 at 13:01

score 0 · Answer 2 · answered Apr 25 '20 at 05:07

0

Make sure the database connection variable is the right syntax and i advise you include an error syntax to show when the db is not connected

answered Apr 25 '20 at 05:07

Damola

1
1

What you mean by the right syntax? This code _pSqlConn.Open();_ executes without error. – Joel Apr 25 '20 at 05:19

SQL Server Express database table not updating

2 Answers2