Insert command in ms access database using c#

Question

_{Saturday, April 2, 2011 2:46 AM | 1 vote}

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());
            }

All replies (6)

_{Wednesday, April 13, 2011 7:33 AM ✅Answered | 1 vote}

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.

_{Tuesday, April 5, 2011 3:28 AM}

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.

_{Monday, November 5, 2012 10:15 PM}

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.

_{Tuesday, January 22, 2013 7:57 AM}

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 8:01 AM}

Hi,

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

PS.Shakeer Hussain

_{Tuesday, January 22, 2013 8:08 AM}

It says 

" Syntax error in INSERT INTO statement. "

go2go_suvo