none
Insert command in ms access database using c# RRS feed

  • Question

  • there's a problem with data saving that hasbeen generated using insert command. once it show the updated after sometime/closing once the database. the new added has removed from the ms access database.

    Following is the code:

     


                try
                {
                    string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=application.accdb;Persist Security Info=True";
                    OleDbConnection conn = new OleDbConnection();
                    conn.ConnectionString = connStr;

                    OleDbDataAdapter adapter = new OleDbDataAdapter();
                    adapter.InsertCommand = new OleDbCommand();
                    adapter.InsertCommand.CommandText = "INSERT INTO login (username, [password], role)" + " VALUES('" + txtusr.Text + "', '" + txtpwd.Text + "', '" + txtrole.Text + "');";
                   conn.Open();
                    adapter.InsertCommand.Connection = conn;
                    adapter.InsertCommand.ExecuteNonQuery();
                    MessageBox.Show("Database hasbeen updated");
                }
                catch (OleDbException exp)
                {
                  
                    MessageBox.Show(exp.ToString());
                }

    Saturday, April 2, 2011 2:46 AM

Answers

All replies

  • Hello mukesh bera,

     

    Welcome to the MSDN Forum!

    According to your description, you mean that you can see the new record in the database, but once you close the database, the new added has removed from the database, right?

    And more, have you met some errors or exceptions? If so, could you show us the error messages? And note that your connection object isn't closed.

    I am looking forward to hearing from you!

     

    Good day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, April 5, 2011 3:28 AM
    Moderator
  • Hi mukesh,

    Welcome!

    According to your code, why do you use DataAdapter to excute your command?

    I think you can use OleDbCommand to insert you records directly(http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.aspx)

    OleDbCommand cmd = conn.CreateCommand();
    cmd.CommandText="XXXXXX";
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
    
    Have a nice day.
    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, April 13, 2011 7:33 AM
    Moderator
  • thanks mukesh bera thanku very much for this codig it would help me very much

     i am very tensed to insert the data into database. I search man time on google but no one can help me u did a great work for me.

    Monday, November 5, 2012 10:15 PM
  • Anyone tell me, what is wrong with that code?

    string str1 = "";
                string str2 = "";
                str1 = "Insert into Table1(id, Disease,Symptoms,Drugs,Side effects,Genes,Proteins) Values (@id, @Disease,@Symptoms,@Drugs,@Side effects,@Genes,@Proteins)";

               str2 = "Insert into Table1(Disease clr, side effects/Symptoms clr,drugs clr,genes color,proteins clr) Values ('1', '5' ,' 4' , '2 ',' 3')";

                OleDbCommand com1 = new OleDbCommand(str1, connection);
                OleDbCommand com2 = new OleDbCommand(str2, connection);
                //connection.Open();
                com1.Parameters.Add("@id", OleDbType.Integer, 5);
                //com1.Parameters.Add("@id", OleDbType.VarChar, 200);

                com1.Parameters.Add("@Disease", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Symptoms", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Drugs", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Side effects", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Genes", OleDbType.VarChar, 200);
                com1.Parameters.Add("@Proteins",OleDbType.VarChar,200);
                connection.Open();
                com1.Parameters["@id"].Value = System.Convert.ToInt32(textBox1.Text.ToString());
                //com1.Parameters["@id"].Value = textBox1.Text.ToString();

                com1.Parameters["@Disease"].Value = textBox2.Text.ToString();

                com1.Parameters["@Symptoms"].Value = textBox3.Text.ToString();
                com1.Parameters["@Drugs"].Value = textBox4.Text.ToString();
                com1.Parameters["@Side effects"].Value = textBox5.Text.ToString();            
                com1.Parameters["@Genes"].Value = textBox6.Text.ToString();            
                com1.Parameters["@Proteins"].Value = textBox7.Text.ToString();
                com1.ExecuteNonQuery();
                com2.ExecuteNonQuery();
                connection.Close();
                textBox1.Text = "";
                textBox2.Text = "";
                textBox3.Text = "";
                textBox4.Text = "";
                textBox5.Text = "";
                textBox6.Text = "";
                textBox7.Text = "";
                MessageBox.Show("Save Successfull ... ");

                           

    go2go_suvo

    Tuesday, January 22, 2013 7:57 AM
  • Hi,

    What error you are facing?. Please post your error.


    PS.Shakeer Hussain


    Tuesday, January 22, 2013 8:01 AM
  • It says 

    " Syntax error in INSERT INTO statement. "


    go2go_suvo

    Tuesday, January 22, 2013 8:08 AM