C# Form / Access Database insert row with "autonumber" field

Question

The two following samples of C# code, writing info to an Access database, as far as I can tell, SHOULD produce the same output. However the first one works, and the second gives an error.

The first code: Without an AutoNumber primary key field, works great. But only until I try to add a row where any field is not different. I HAVE to have the AUTONUMBER unique ID, (for the obvious reason of a lack of unquity)

string vsql = string.Format("insert into Log values " +
    "('{0}','{1}',{2},{3},'{4}',#{5}#,'{6}')",
    comboBox1.Text,
    comboBox2.Text,
    int.Parse(textBox1.Text),
    int.Parse(textBox1.Text),
    textBox3.Text,
    textBox2.Text,
    addRemove
    );

The second code: returns the error message:

"Additional information: Number of query values and destination fields are not the same."

As far as I can see, they both have the same number of fields. And still, neither has a unique AUTONUMBER ID field, which I can't add because I don't know how to make the code "insert" or "not insert" anything into the autonumbering field. Again, I obviously need the field. ANY HELP IS APPRECIATED! Either code is acceptable, as long as I have an autonumber field that will update itself, when my form submits a new record.

string vsql = string.Format("INSERT INTO Log (" +
    "Location, " +
    "Drug, " +
    "Quantity, " +
    "Strength, " +
    "Initials, " +
    "'Date'," +
    "add_Remove" +
    ") VALUES (" +
    comboBox1.Text,
    comboBox2.Text,
    int.Parse(textBox1.Text),
    int.Parse(textBox1.Text),
    textBox3.Text,
    textBox2.Text,
    addRemove);

what database are you using? you know you could just put autoincrement option when you created database. — user123_456, Mar 22 '12 at 20:29
The first one will work, unedited, as long as there are no two fields that are alike. The second example seems like it would work as well, if I could get the error message fixed. — Geo, Mar 22 '12 at 20:30
@MilkyWayJoe is right; you're using `Format`, but have no format placeholders in the second version. The second one also doesn't properly quote character values or format the date value with the `#` characters like the first one does. — Ken White, Mar 22 '12 at 20:31
@KenWhite, then I suppose the first example will be the best way to go. Just getting the autoincremented id in there, is the only issue then. — Geo, Mar 22 '12 at 20:33
If you're doing an `INSERT`, you should be able to pass anything you want as a value to the auto-increment field; Access will ignore it anyway. IOW, if you specify that field by name in your column you have to pass something, but it doesn't matter what you provide. — Ken White, Mar 22 '12 at 20:57
I suggest you to go with this example: [Autonumber](http://support.microsoft.com/kb/816112) It is a step-by-step tutorial with code examples. I hope it will help you. Good luck! — user123_456, Mar 22 '12 at 20:36

score 3 · Accepted Answer · answered Mar 22 '12 at 20:38

3

Try this: right after your insert sql command, execute another sql call:

SELECT @@IDENTITY

The result returned will be the autonumber field associated with the record that was just added. Your original insert sql command should NOT be attempting to insert the autonumber field value. Just omit it.

Also, your second sql text has a problem. Reformat it to look like this:

string vsql = string.Format("insert into Log (Location,Drug,Quantity,"+
   "Strength,Initials,Date,add_Remove values" +
   "('{0}','{1}',{2},{3},'{4}',#{5}#,'{6}')",
     comboBox1.Text,
     comboBox2.Text,
     int.Parse(textBox1.Text),
     int.Parse(textBox1.Text),
     textBox3.Text,
     textBox2.Text,
     addRemove
     );

answered Mar 22 '12 at 20:38

code4life

15,655
7
50
82

Actually both `string.Format` versions should be dropped in lieu of using a parameterized query – Conrad Frix Mar 22 '12 at 20:48
@ConradFrix: I'm not sure if he's using DAO or ADO... Also not sure if DAO supports parameters. But in principle, you're 100% right. – code4life Mar 22 '12 at 20:50
When someone is using DAO or ADO from C# instead of System.Data.OleDb, parameterized query support is the least of their problems. – Conrad Frix Mar 22 '12 at 21:00

score 2 · Answer 2 · answered Mar 22 '12 at 20:39

I'm not sure if you're using String.Format correctly

Try to evolve this code:

string vsql = string.Format(
    "INSERT INTO Log ( Location, Drug, Quantity, Strength, Initials, [Date], add_Remove) "   +
    "VALUES ('{0}', '{1}', {2}, {3}, '{4}', '{5}', '{6}')",
    comboBox1.Text, comboBox2.Text, int.Parse(textBox1.Text), int.Parse(textBox1.Text),
    textBox3.Text, textBox2.Text, addRemove);

Note: I haven't tested Edit: added a missing coma

score 1 · Answer 3 · answered Apr 02 '12 at 19:52

I searched the web for some time, trying to wrap my head around the responses I got. All of which were great suggestions. This was a great example of "Occam's Razor". I inserted the following before the "string vsql" line, and it works like a charm.

Thanks to all that responded!

OleDbCommand dbCommand;
OleDbDataReader dbReader;
new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;data source=C:\MyDatabase.accdb");
dbCommand = new OleDbCommand("select count(*) as Record_Count from Log", vcon);
dbReader = dbCommand.ExecuteReader();
if (dbReader.Read() == true)
    rowCount = dbReader["Record_Count"].ToString();
else
    return;

C# Form / Access Database insert row with "autonumber" field

3 Answers3