Inserting a row in MS Access from c#

Question

my code is inserting some data in a Access DB. The code were fails is that (the code uses a library for download e-mails, but the error is not apparently related to that):

foreach (MimeData mime in email.Attachments)
{

    string name = NumEmail + mime.SafeFileName;
    string path = (@"\\pathrandom" + name);
    mime.Save(path);
    long length = new System.IO.FileInfo(path).Length;

    String my_querry2 = "INSERT INTO Table(id, field1, field2, field3, field4)VALUES('" + path + "','" + length + "','" + name+ "','" + value3 + "','" + date + "')";
    MessageBox.Show(my_querry2);
    OleDbCommand cmd2 = new OleDbCommand(my_querry, conn);
    cmd.ExecuteNonQuery();                    
}

(id, Date and value3 are variables defined out of the foreach with a foo string, date of today and foo string).

When I run this code , the insert does not occur and the ExecutedNonQuery() returns a error. It says the query did not run because it would create duplicate values. But the table is totally empty, so the 'id' can't be repeat. I tried to do the query in access and doing it:

INSERT INTO AchivosAdjuntos(id, field1, field2, field3, field4)VALUES('\\path','171','x.png','random','28/01/2015')

And it works perfectly (the values are the same that c# try to insert), inserting the values.

The connection with the DB seems fine, because before that insert I've done other that worked well.

So... what I'm doing wrong? :S

Please don't use string concatenation to build your SQL statements as you are leaving yourself wide open to SQL injection. Use Parameters instead. — Matt
– Matt, Commented Jan 28, 2015 at 10:05
It complains about duplicate keys. So you are trying to insert two records with the same primary key or a combination of field values clash with the definition of an Unique index in your table. Check what are the indexes and primary keys of your table — Steve
– Steve, Commented Jan 28, 2015 at 10:06
As I said, the only field with key is the 'id' and the table is totally empty, so It can't really be the problem. Further, I can insert the same data since sql, so I can't think is really problem of duplicate. I'm gonna try what Matt said and do it with parameters. — Nimrrod
– Nimrrod, Commented Jan 28, 2015 at 10:12
Another idea. Could you show your connectionstring? Are you sure that the database updated is the same that you are looking at as 'empty' — Steve
– Steve, Commented Jan 28, 2015 at 10:16

Aly Elhaddad · Accepted Answer · 2015-01-28 10:32:47Z

1

Use parameters

        string my_querry2 = "INSERT INTO Table(id, field1, field2, field3, field4)VALUES(@paramPath,@paramLength,@paramName,@paramValue3,@paramDate)";
        OleDbCommand cmd2 = new OleDbCommand(my_querry2, conn);
        cmd2.Parameters.Add(new OleDbParameter("@paramPath", path));
        cmd2.Parameters.Add(new OleDbParameter("@paramLength", length));
        cmd2.Parameters.Add(new OleDbParameter("@paramName", name));
        cmd2.Parameters.Add(new OleDbParameter("@paramValue3", value3));
        cmd2.Parameters.Add(new OleDbParameter("@paramDate", date));
        cmd2.ExecuteNonQuery();

answered Jan 28, 2015 at 10:32

Aly Elhaddad

1,9631 gold badge16 silver badges31 bronze badges

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

1 Comment

Nimrrod Over a year ago

Don't know why, but with parameters worked! thanks you and matt, and the other people who told it :P

Collectives™ on Stack Overflow

Inserting a row in MS Access from c#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related