none
Not able to insert data into sdf database

    Question

  • Hi,

    I am trying to insert records into sdf file which is at the root. But Nothing is get inserted neither any error.

    Here is my code:

    SqlCeConnection conn = new SqlCeConnection();
    conn.ConnectionString = "Data Source = Database1.sdf";
    
    conn.Open();
    
    SqlCeCommand cmd = new SqlCeCommand();
    cmd.Connection = conn;
    
    SqlCeDataAdapter sda = new SqlCeDataAdapter();
                
    cmd.CommandText = "INSERT INTO WFATable (name, id) VALUES (@name, @id)";
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@Name", textBox1.Text);
    cmd.Parameters.AddWithValue("@id", textBox2.Text);
    sda.InsertCommand = cmd;
    cmd.ExecuteNonQuery();
    conn.Close();
    MessageBox.Show("OK");

    Please help me.

    Thanks in advance

    Tuesday, June 05, 2012 9:34 AM

All replies

  • Hi learner,

    You cant use Parameters in SqlCe.

    wrong:- cmd.Parameters.AddWithValue("@Name", textBox1.Text);

    You have to do as follows in a simple way


    conn.Open();
    SqlCeCommand cmd = new SqlCeCommand("INSERT INTO WFATable (name, id) VALUES ('"+textBox1.Text+"','"+textBox2.Text+"')",conn);

    cmd.ExecuteNonQuery();


    PS.Shakeer Hussain

    Tuesday, June 05, 2012 9:40 AM
  • Hi,

    Thanks for reply. I use following code according your suggestion, but result is same, no insertion was made in the database.

    Here is my modified code:

    SqlCeConnection conn = new SqlCeConnection();
    conn.ConnectionString = "Data Source = Database1.sdf";
    
    conn.Open();
                
    SqlCeCommand cmd = new SqlCeCommand("INSERT INTO WFATable (name, id) VALUES ('" + textBox1.Text + "','" + textBox2.Text + "')", conn);
                
    cmd.ExecuteNonQuery();
    conn.Close();
    MessageBox.Show("OK");

    Don't know what to do
    Tuesday, June 05, 2012 10:15 AM
  • According to msdn library you should do something like this:

    SqlCeConnection conn = new SqlCeConnection();
    conn.ConnectionString = "Persist Security Info = False; Data Source = 'SalesData.sdf';" +
        "Password = '<password>'; File Mode = 'shared read'; " +
        "Max Database Size = 256; Max Buffer Size = 1024";
    conn.Open();

    And make insert's like Syed tell you is not the best way, you should avoid that. Maybe for you something like a

    SqlCeCommand cmd = new SqlCeCommand();
    cmd.Connection = conn;
    cmd.CommandText = string.Format("INSERT INTO WFATable (name, id)VALUES('{0}','{1}')",textbox1.Text,textbox2.Text);

    If your .sdf file is located in the same directory as the executing application you can try this:

    conn.ConnectionString = "Data Source=" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\MyData.sdf;Persist Security Info=False;";


    • Edited by Norkk Tuesday, June 05, 2012 12:31 PM
    Tuesday, June 05, 2012 12:27 PM
  • According to msdn library you should do something like this:

    SqlCeConnection conn = new SqlCeConnection();
    conn.ConnectionString = "Persist Security Info = False; Data Source = 'SalesData.sdf';" +
        "Password = '<password>'; File Mode = 'shared read'; " +
        "Max Database Size = 256; Max Buffer Size = 1024";
    conn.Open();



    Hi,

    Thanks a ton for the response. Did that, but ended with the same result - No insertion in database


    • Edited by learner.net Tuesday, June 05, 2012 12:40 PM
    Tuesday, June 05, 2012 12:39 PM
  • SqlCeConnection conn = null;
    
    try
    {
        conn = new SqlCeConnection("Data Source = MyDatabase.sdf; Password ='<pwd>'");
        conn.Open();
    
        SqlCeCommand cmd = conn.CreateCommand();
        cmd.CommandText = "INSERT INTO teste(bla, [Company bla) Values('a', 'b')";
        cmd.ExecuteNonQuery();
    
    finally
    {
        conn.Close();
    }
    
    
    If you don't pass any params, and make like this, any insertion occurs?

    Tuesday, June 05, 2012 12:43 PM
  • Hi,

    Did that. nothing happend, no insertion :(

    Don't know why this is happening?

    Tuesday, June 05, 2012 12:55 PM
  • Is the DB file part of your solution? If so, does it get copied to the output directory (e.g. bin\Debug)?

    Tuesday, June 05, 2012 1:00 PM
  • Is the DB file part of your solution? If so, does it get copied to the output directory (e.g. bin\Debug)?


    The sdf file is at the root of my solution. It does not get copied to any folder. I do not have any folder in my solution.
    Tuesday, June 05, 2012 1:05 PM
  • Try check this link

    I believe, that you having the same error.

    Tuesday, June 05, 2012 1:08 PM
  • Try check this link

    I believe, that you having the same error.

    Hi,

    yes you were right. It was in bin folder. But now the problem is it is showing only the last inserted data. Don't know if it's overriding or what.

    Please help me buddy as I am quite new to all this.

    Thanks for the help

    Tuesday, June 05, 2012 2:43 PM
  • But is the correct behavior, the insert clause doesnt return more than one row, just the row affected, what did u expect?
    Tuesday, June 05, 2012 2:52 PM
  • But is the correct behavior, the insert clause doesnt return more than one row, just the row affected, what did u expect?

    Hi,

    I went through below link:

    http://blogs.msdn.com/b/smartclientdata/archive/2005/08/26/456886.aspx

    It sayes :

    "If you select local database file in the Solution Explorer window, you will see a property called ‘Copy to Output’ in the Properties window. By default it is set to ‘Copy Always’ which means that on each build, the data files in the project folder will be copied to the output folder overwriting the existing data files if any"

    But mine is set to "Copy if newer", but still it is overriding the previous data.

    I just want that the table hold all the inserted data as happens with normal ASP.NET and SQL database


    Tuesday, June 05, 2012 3:03 PM
  • Hi learner, 

    I suspect, you are looking at wrong sdf file. I would suggest 

    1. Delete copied sdf files from bin / App_Data folder.
    2. Change property of sdf file "Copy to Output Directory" to Do not copy.
    3. Change at connection string, pointing to absolute path of sdf file.

    I hope this helps you...


    If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".

    Tuesday, June 05, 2012 5:30 PM