2

[SOLVED]

The problem stands in the relative path. I should've used the mdf located in the project folder rather than the one in bin

I try to insert some data into a database through a Windows forms program. The problem is that if I use a relative path in the connection string, the data stays in the table only while the program is running. Otherwise, with an absolute path in the connection string, it works well, but I really need to use a relative path.

Here I set the relative path in the form's constructor.

public Form1()
{
        InitializeComponent();

        string executable = System.Reflection.Assembly.GetExecutingAssembly().Location;
        string path = (System.IO.Path.GetDirectoryName(executable));

        AppDomain.CurrentDomain.SetData("DataDirectory", path);

        update();
}

App.config:

<connectionStrings>
    <add name="musicConnString" 
         connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\music.mdf;Integrated Security=True"
         providerName="System.Data.SqlClient" />
</connectionStrings>

Finally, here I do the insert:

string connectionString = ConfigurationManager.ConnectionStrings["musicConnString"].ConnectionString;

using (SqlConnection sq = new SqlConnection(connectionString))
using (SqlCommand sc = new SqlCommand("INSERT INTO music VALUES(@artist, @album, @songs, @genre, @cover, @year)", sq))
{
    sq.Open();

    sc.Parameters.AddWithValue("@artist", artistTxt.Text.Trim());
    sc.Parameters.AddWithValue("@album" , albumTxt.Text.Trim());
    sc.Parameters.AddWithValue("@songs" , songsTxt.Text.Trim());
    sc.Parameters.AddWithValue("@genre" , genreTxt.Text.Trim());
    sc.Parameters.AddWithValue("@year"  , yearTxt.Text.Trim());
    sc.Parameters.AddWithValue("@cover" , imageToByte(albumCover));

    if (sc.ExecuteNonQuery() > 0)
    {
        this.Dispose();
    }
    else
    {
        MessageBox.Show("There was a problem with the insertion.");
    }
}
bagool
  • 21
  • 3
  • Do you get an error message (*please* dont say `There was a problem with the insertion.`) – Ňɏssa Pøngjǣrdenlarp Jan 01 '17 at 16:59
  • I don't get any error message. The data is inserted into the table and disappears right after the program ends. And I was going to change that message, it's just for the sake of knowing if it works or not at the moment – bagool Jan 01 '17 at 17:08
  • Possible duplicate of [Why saving changes to a database fails?](http://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails) – Ňɏssa Pøngjǣrdenlarp Jan 01 '17 at 17:16
  • You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... – marc_s Jan 01 '17 at 17:19
  • The problem is the same, but the solution didn't work for me – bagool Jan 01 '17 at 17:20
  • Interresting article. I've done accordingly, but still doesn't work... – bagool Jan 01 '17 at 17:38

1 Answers1

1

I'm wondering if you've got the Visual Studio project configured to "always copy" the music.mdf file. If so, then every time you launch the application from inside Visual Studio, it will copy over the prototype MDF file to the so-called DataDirectory.

If this is the case, it's a simple fix... just do a right click on the MDF file from the solution explorer, and change the "copy" property to "copy if newer"

egray
  • 390
  • 1
  • 4