Inserting data into a access database using parameters

Question

What am I not understanding in the following code:

using (OleDbConnection connectionFail = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Fail_DB.mdb"))
            {
             connectionFail.Open();
              var cmd = new SqlCommand("INSERT INTO SUMMARY (A, B, C) VALUES (@A, @B, @C)");
                cmd.Parameters.Add("@A", SqlDbType.NVarChar, 50).Value = "A";
                cmd.Parameters.Add("@B", SqlDbType.NVarChar, 50).Value = "B";
                cmd.Parameters.Add("@C", SqlDbType.NVarChar, 50).Value = "C";
                try
                {
                cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                MessageBox.Show(ex.ToString());
                }
                finally
                {
                connectionFail.Close();
                cmd.Dispose();
                }
                connectionFail.Close();
                }


          }

I just want to add data to a SUMMARY table. I get ExecuteNonQuery:Connection property has not been initialized. What am I not understanding?

You never set the command's connection. Anyway, SqlCommand is for SQL Server. You should use OleDbCommand — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 21, 2017 at 8:31
FYI, never call Dispose like that, instead initialize the SqlCommand in a nested using block. That will handle cleanup. — Aluan Haddad
– Aluan Haddad, Commented Sep 21, 2017 at 8:33
Because IDisposables need to be disposed and because you can forget to do so, or do so incorrectly, C# has the using statement which automates the process. This improves maintainability, raises the abstraction level slightly, is idiomatic, and does not hijack exception handling machinery to perform expected cleanup. — Aluan Haddad
– Aluan Haddad, Commented Sep 21, 2017 at 21:43

Panagiotis Kanavos · Accepted Answer · 2017-09-21 08:41:51Z

1

The connection was created but never set on the command object. BTW SqlCommand is a SQL Server Client command.

Instead of :

var cmd = new SqlCommand("INSERT INTO SUMMARY (A, B, C) VALUES (@A, @B, @C)");

Use

var cmd = new OleDbCommand("INSERT INTO SUMMARY (A, B, C) VALUES (?, ?, ?)",connection);

Access doesn't support named parameters in SQL queries. The parameter values will be substitued by position.

edited Sep 21, 2017 at 8:41

answered Sep 21, 2017 at 8:34

Panagiotis Kanavos

134k16 gold badges211 silver badges271 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inserting data into a access database using parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related