Not inserting data into database and not getting any error

Question

I am not sure what I am doing wrong, If I use the connection string shown here, my application works fine.

SqlConnection conn = new SqlConnection();

string DbPath = Application.StartupPath;

DbPath = DbPath.Substring(0, DbPath.LastIndexOf("\\bin"));
DbPath = DbPath + "\\MyDatabase.mdf";
conn.ConnectionString = "Data Source=.\\EXPRESS2008;AttachDbFilename="  + DbPath + ";Integrated Security=True;User Instance=True";

but if I use connection string here, it's not inserting data into MyDatabase table

conn.ConnectionString = Properties.Settings.Default.MyDatabaseConnectionString;

My app.config is

<startup> 
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup> 
<connectionStrings>
     <add name="ERPSystem.Properties.Settings.MyDatabaseConnectionString"
          connectionString="Data Source=.\EXPRESS2008;  AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Integrated 
Security=True;User Instance=True"
          providerName="System.Data.SqlClient" />
</connectionStrings>

INSERT statement and preceding code:

comm = new SqlCommand("CreateUser", MyConnection.MyConn("Open")); 
comm.CommandType = CommandType.StoredProcedure; 

comm.Parameters.Add("@UserName", SqlDbType.VarChar).Value = userName.Text; 
comm.Parameters.Add("@Password", SqlDbType.VarChar).Value = userPassword.Text; 
comm.Parameters.Add("@UserRole", SqlDbType.VarChar).Value = UserRole.SelectedItem.ToString();     

comm.ExecuteNonQuery(); 

This is the code to get the connection

class MyConnection
{
    public static SqlConnection MyConn(string str)
    {
        SqlConnection conn = new SqlConnection();

        try
        {
            //get application path
            string DbPath = Application.StartupPath;

            if (Program.RunFrEn == true) //bool var
                //remove string after bin folder
                DbPath = DbPath.Substring(0, DbPath.LastIndexOf("\\bin"));
                //add database name with new path
                DbPath = DbPath + "\\MyDatabase.mdf";
                //generate new connection string for database
                conn.ConnectionString = "Data Source=.\\EXPRESS2008;AttachDbFilename="
                                                    + DbPath
                                                    + ";Integrated Security=True;User Instance=True";
                //conn.ConnectionString = Properties.Settings.Default.MyDatabaseConnectionString;

            if (str == "Open")
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
            }
            else
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            MessageBox.Show(ex.Message);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

        return conn;
    }
}

I am not getting any error

Thank you

Answer 1

The whole 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 Express - I'm almost certain your data is there.

The real solution in my opinion would be to

1. install SQL Server Express (and you've already done that anyway)

2. install SQL Server Management Studio Express

3. create your database in SSMS Express, give it a logical name (e.g. MyDatabase)

4. 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=MyDatabase;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.

Answer 2

I also had fighted long time with same problem. And I saw many same questions & answers.

You're using |DataDirectory|. I assume you can get values from the DB file and you don't get error to run insert command but the values are not inserted into the DB file.

This is absolutely my private idea and my private conclusion is that this behavior is normal as |DataDirectory| does. I mean a data file of an application should be protected from manipulation once after deployment. The 'Data' file should provide data inside the file so that we can read the data.

Therefore, I coded to create a localDB .MDF file (SQL Server 2014) from users' side so that my applications can utilize the localDB to write and read data. My application automatically downloads data from cloud server which are we need to update frequently. On the other side, I put big and already fixed data into |DataDirectory| .MDF file, I mean inserted big data for read only and add the .MDF file to my project before deployment.

Hope my experience helps.. But, please keep in mind again that this is really my private opinion and I might be totally wrong and my experience is limited only to localDB. But again, I couldn't find a Microsoft's official document mentioning this behavior.

Do you have only 1 option like |DataDirectory|? Did this work to insert before? Is this code by you wrote on your own? If possible, try to find another option rather than |DataDirectory| to connect to the SQL Server database. I use a cloud SQL server with IP address but I can't understand why you use |DataDirectory|. There might be many various options as connection strings to SQL Server Express.