2

here is the code im working on ;

 public partial class Form2 : Form
{
    SqlConnection sc = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True");
    SqlDataAdapter sda;
    SqlCommand command;
    SqlCommand commands;
    public Form2()
    {
        InitializeComponent();
    }

    private void button2_Click(object sender, EventArgs e)
    {
        this.Close();
        Form1 f1 = new Form1();
        f1.Show();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        command = new SqlCommand(@"SELECT * FROM [Table] WHERE email='" + textBox4.Text + "'", sc);
        sda = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        int i = ds.Tables[0].Rows.Count;
        if (i == 1)

            MessageBox.Show("Email Already Taken");
        else
        {
            sc.Open();
            command = new SqlCommand("INSERT INTO [Table](name,surname,yearofbirth,adress_home_city,adress_home_block,adress_home_street,adress_work_city,adress_work_block,adress_work_street,email,password) VALUES('"+textBox1.Text+"','"+textBox2.Text+ "','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "','" + textBox7.Text + "','" + textBox10.Text + "','" + textBox11.Text + "','" + textBox8.Text + "','" + textBox9.Text + "','" + textBox6.Text + "') ", sc);

            command.ExecuteNonQuery();
            sc.Close();
            MessageBox.Show("Success");

        }

its working when im debugging it , its a register form and i can login with the information i give in this form.

but when closed , this insert command i do doesnt get saved in my database.

// additional info ;

when in register form , i get the success message and if i try again with same information , i get the email is already taken message

M.Karavural
  • 21
  • 7
  • 3
    i hope you are checking the correct database, "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True" , this is not the database which is opened from management studio directly. – Abhimanyu Ghei May 17 '17 at 17:33
  • 1
    Use parameters to avoid SQL injection and formatting errors. Do you really have a table named "Table"? ExecuteNonQuery will return the number of rows affected if it was successful. You are probably looking at a copy of the database — look at your connection property settings. – LarsTech May 17 '17 at 17:33
  • how is it that it is working but not saving?. Isn´t that just not working? – NicoRiff May 17 '17 at 17:35
  • 1
    i have really named my table "table" :D and , its the correct database . i have made some test rows in my database and they are also working on my login screen . – M.Karavural May 17 '17 at 17:35
  • 1
    AttachDbFilename=|DataDirectory|\Database1.mdf; is your actual database name which you need to attach to sql server management studio to check the values in the table, or you can change the database source to some database which is already attached to SSMS – Abhimanyu Ghei May 17 '17 at 17:35
  • i dont use ssms , i directly created a service based database in adding a file option , created my columns in there and connected to my code . and @NicoRiff , its working as i wanted right now when working , registering a user , and with that new users information i can log in . but when i close and reopen my application , these new users arent saved . – M.Karavural May 17 '17 at 17:44
  • In VS 2015 Is Commom Language Runtime Exceptions is checked? you can check it navigate to Debug>>Windows>>Exception Settings>> if Commom Language Runtime Exceptions is not checked so check it and try to debug, is there any exception? – Jonathan Applebaum May 17 '17 at 17:50
  • @jonathana its checked , and no exceptions , not any error is recieved . – M.Karavural May 17 '17 at 17:52
  • is it possible for you to use profiler to check if any delete command is being sent when you relaunch the application?. Are you using entity framework? – Abhimanyu Ghei May 17 '17 at 17:59
  • @LarsTech okay , i might be really just copying the db , using it when its running and destroying it when closed , it makes sense . but in what should i write in my sqlconnection ? – M.Karavural May 17 '17 at 18:02
  • @AbhimanyuGhei im a beginner and cant use sorry . but i think its not deleting anything , i may be just made a mistake in my connection string but cant solve :( – M.Karavural May 17 '17 at 18:04
  • You say "when I try again I get with the same information I get the already taken message". On the second attempt the record is on the database from the first attempt for the same email gving the "already taken" message. Sorry if I'm reading this incorrectly. – Johnny Fitz May 17 '17 at 18:21
  • @JohnnyFitz yeah , exactly . the email already taken message is a proof of saving . but when closed , its not showing on my database. – M.Karavural May 17 '17 at 18:29
  • are you able to re register using same email id after closing and then relaunching the application? – Abhimanyu Ghei May 17 '17 at 18:51
  • @AbhimanyuGhei yup , i can register with the same information – M.Karavural May 17 '17 at 19:09
  • SqlConnection should not be a field. It should be a local variable, and you should follow one of the two correct patterns for an [IDisposable](https://msdn.microsoft.com/en-us/library/system.idisposable(v=vs.110).aspx) object. – mason May 17 '17 at 19:41
  • @M.Karavural please check the answer – Abhimanyu Ghei May 17 '17 at 19:47

1 Answers1

1

The issue is because your mdf file is getting replaced by a blank one on rebuild i.e. your mdf file is being copied to your debug folder everytime you rebuild your application and so removing all the data that was inserted previously.

Solution

  1. Do not include the mdf file in solution.
  2. Provide full path (like AttachDbFilename=C:\Database1.mdf;) or relative path of mdf file in your connection string

SqlConnection sc = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Database1.mdf;Integrated Security=True");

  1. If you are including the mdf file to solution then change to property "Copy to Output Directory" as "Do not copy"
  2. Use database from your local MSSQL server instance instead of pointing to a mdf file if possible (MS SQLExpress is free).

This issue will only happen if you launch your application from visual studio, if you are launching the application multiple times from debug/release folder directly the application will work fine as it is not being rebuild and so mdf file is not replaced.

Abhimanyu Ghei
  • 166
  • 1
  • 6