using Service Based Database in VS2010 Ultimate

Question

I have been working on a project related to database (.mdf). I have created some windows forms in visual studio using C#. Basically these forms work together to store, update and delete data from the Service Based Database i created. The problem is when i build the project, it builds fine, no errors. It inserts a data provided from textboxes to the datagridview too as intended. But as soon as i stop the current debugging, and then rerun it again, all the data provided previously is lost from the datagridview!! I cant understand why this is happening. anyone please help me. Im totally new to this stuff.. a bit of guidance would be heartily appreciated.

when i had previously used MySQL for the same purpose, the updated data would be permanently stored to the database, but since i migrated from the MySQL to SQL Server's Service Based Database, i get such confusing error.

    ......
    void loadData()
    {
        SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["baikalpik_bidhut_sewaConnectionString"].ToString());
        SqlCommand cmd = new SqlCommand("SELECT SNo,Customer_ID, Citizenship_No, Name, Subscription_Date, Phone_No, Location,Locality,Bulbs,Deposit,Monthly_Charge FROM customerinformation;", con);
        try
        {
            SqlDataAdapter adp = new SqlDataAdapter();
            adp.SelectCommand = cmd;
            DataTable dt = new DataTable();
            adp.Fill(dt);
            dataGridViewCustomerInformation.DataSource = dt;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

    private void buttonAdd_Click(object sender, EventArgs e)
    {
        try
        {
            float m_chrg = Convert.ToInt64(textBoxBulbs.Text)*500;
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["baikalpik_bidhut_sewaConnectionString"].ToString());
            SqlCommand cmd = new SqlCommand("INSERT INTO customerinformation(SNo,Customer_ID,Citizenship_No,Name,Subscription_Date,Location,Locality,Bulbs,Deposit,Phone_No,Monthly_Charge) values('" + textBoxSNo.Text + "','" + textBoxCustomerID.Text + "','" + textBoxCitizenshipNumber.Text + "','" + textBoxName.Text + "','" + textBoxSubscriptionDate.Text + "','" + textBoxLocation.Text + "','" + textBoxLocality.Text + "','" + textBoxBulbs.Text + "','" + textBoxDeposit.Text + "','" + textBoxPhoneNumber.Text + "','" + m_chrg + "')", con);

            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            dt = new DataTable();
            dt.Load(reader);
            con.Close();
            dataGridViewCustomerInformation.DataSource = dt;

            loadData();
            MessageBox.Show("Entry Added!");
            fillListbox();

            textBoxSNo.Clear();
            textBoxBulbs.Clear();
            textBoxCitizenshipNumber.Clear();
            textBoxCustomerID.Clear();
            textBoxDeposit.Clear();
            textBoxLocality.Clear();
            textBoxLocation.Clear();
            textBoxPhoneNumber.Clear();
            textBoxName.Clear();
            textBoxSubscriptionDate.Clear();



        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

Do you have your MDF listed in your project files? Check the property `Copy To Output Directory` and verify it is not set to `Copy Always` — Steve, May 04 '13 at 17:16
Sir,your suggested requirements are partially met in my project.I have the mdf listed in the project file as i have added it by right clicking project and adding it as new item. The property of the output directory is set to Copy Always however. What do i have to change it to?? Thank you for your quick response. :) — nipunshakya, May 04 '13 at 17:25
Why are you calling ExecuteReader on an INSERT command? INSERT does not return any data, so that might cause a problem. — RBarryYoung, May 04 '13 at 17:34

score 0 · Answer 1 · edited May 23 '17 at 11:57

0

If you have your MDF listed in your project files then the property Copy To Output Directory handles how your MDF file is copied to the output directory (BIN\DEBUG or BIN\RELEASE).

If this property is set to Copy Always, then when you run your program a fresh copy of your database file is copied from the project folder to the output effectively destroying every data that you have inserted, modified, deleted in the previous run of your program.

The best solution to this dilemma is to add the MDF file as a permanent database inside your current install of Sql Server and adjust your connection string. And of course set the property to Copy Never

In alternative you could set to Copy if newer. This will allow to change the database schema inside the Server Explorer and let the Visual Studio IDE copy the new structure only after you have made changes.

UPDATE BUT IMPORTANT Not related to your actual question, but your insert query is a serious problem. Do not use string concatenation to build a sql command text. Particularly if the partial text comes from user input. You could face syntax errors (if someone places a single quote inside a text box) or worst, a Sql Injection problem (see here for a funny explanation)
To find good examples of Insert search for 'parametrized query'

edited May 23 '17 at 11:57

Community

1
1

answered May 04 '13 at 17:34

Steve

213,761
22
232
286

Sir, setting the property of "Copy to Output Directory" as "Copy if newer" did not work out for me. So i went with "Copy Never" property. But as i try to insert a data, it throws me an exception stating that the attempt to attach an auto-named database for file failed due to existence of database with same file name in the path, or specified file cannot be opened, or is located on UNC share. The MDF file isn't added to the SQL Server.i want to make the database act more like a localDB so at time of deployment, it can be stored to local machine without need of server. – nipunshakya May 04 '13 at 17:46
Could you post your connection string? Without passwords of course – Steve May 04 '13 at 17:52
name="baikalpik_bidhut_sewaConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\baikalpik_bidhut_sewa.mdf;Integrated Security=True;User Instance=True" The above is my connection string sir. – nipunshakya May 04 '13 at 17:58
Thank you for your remainder for 'parametrized query' – nipunshakya May 04 '13 at 18:00
Sorry for the delay, try to add the `Initial Catalog=your_database_name;` – Steve May 04 '13 at 19:33
Now the previous error has vanished and a new error is generated stating that the login failed. The login for \ failed it says.. ive set property to Do Not Copy. – nipunshakya May 04 '13 at 19:46

using Service Based Database in VS2010 Ultimate

1 Answers1