Insert record into MS Access table

Question

I have a simple MS Access table* that I want to programmatically insert records into. I found some code via Googling, but don't know what should replace the ?s below, or if something entirely different is preferable:

*
ID - auto
Question - Memo
Answer - Memo
Answered - Yes/No, default = No

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\APE_MC\\APE_DB.mdb";

      OleDbConnection empConnection = new OleDbConnection(conString);

      string insertStatement = "INSERT INTO APE_MCs (Question, Answer) VALUES (@Question, @Answer)";

      ?.Parameters.Add("@Question", OleDbType.Char).Value = sQuestion;
      ?.Parameters.Add("@Answer", OleDbType.Char).Value = sAnswer;

      empConnection.Open();

      try
      {
        int count = insertCommand.ExecuteNonQuery();
      }
      catch (OleDbException ex)
      {
        MessageBox.Show(ex.Message);
      }
      finally
      {
        empConnection.Close();
      }

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

---

Writer / Photographer - http://www.feedbooks.com/userbook/3631

Answer 1

Hey,

You might wanna look at this video: http://msdn.microsoft.com/en-us/vbasic/cc707833.aspx
With help of this video i made one myself aswell by using Access Database =)
Might help you too! it's very simple and affective

Answer 2

After more googling around, I tried this:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\APE_MC\\APE_DB.mdb";

      string insertStatement =
        string.Format("INSERT INTO APE_MCs (Question, Answer) VALUES ([0], [1])", sQuestion, sAnswer);

      var empConnection = new OleDbConnection(conString);

      var cmdAdder = new OleDbCommand(
          insertStatement,
          empConnection);
      cmdAdder.Connection.Open();
      int nNoAdded = cmdAdder.ExecuteNonQuery();
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

...but on the cmdAdder.Connection.Open(); line I get:

System.Data.OleDb.OleDbException was unhandled
  Message=Could not use ''; file already in use.
  Source=Microsoft JET Database Engine
. . .

Does anybody know what I'm doing wrong here?

---

Writer / Photographer - http://www.feedbooks.com/userbook/3631

Answer 3

This is what I got to work:

    private void InsertRecord( string AQuestion, string AAnswer )
    {
      var aConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\APE_MC\\APE_DB.mdb;Persist Security Info=True");

      var sInsertRec =
        new OleDbCommand("INSERT INTO APE_MCs (Question, Answer) VALUES (@Question, @Answer)", aConnection);
      sInsertRec.Parameters.AddWithValue("@Question", AQuestion);
      sInsertRec.Parameters.AddWithValue("@Answer", AAnswer);
      aConnection.Open();
      try
      {
        sInsertRec.ExecuteNonQuery();
      }
      finally
      {
        aConnection.Close();
      }
}

---

Writer / Photographer - http://www.feedbooks.com/userbook/3631

Answer 4

If I am not mistaken, Jet OLEDB does not work with the named parameters and all the parameter values should be passed to the command in EXACT same order as they are declared inside of a query. In addition, the actual parameters inside of a query string should have ? instead of parameter names, like

var sInsertRec =
        new OleDbCommand("INSERT INTO APE_MCs (Question, Answer) VALUES (?, ?)", aConnection);

---

Val Mazur (MVP) http://www.xporttools.net

Answer 5

Well, it does work, so....

---

Writer / Photographer - http://www.feedbooks.com/userbook/3631