none
MS Access Insert Problem... RRS feed

  • Question

  •  

    Hello All,

     

    I am trying to insert some values into a MS Access Table. In MS Access Table, there are few columns like,

     

    s_Generation,

    s_Lineage,

    s_GUID,

    Name,

    Address

     

    HERE is my INSERT statement...

     

    qString = "INSERT into tbl_DES VALUES ('','','','Name1', 'Address1')";

     

    But, it does not allow me to do that... It gives Data Mismatch Error...

     

    If I write, qString = "INSERT into tbl_DES VALUES ('Name1', 'Address1')";

     

    By ignoring, the first three columns, then it says that query fields and destination table fields are not the same...

     

    How can I solve this problem....

     

    I am using C# (Oledb, MS Access 2000)

     

    Plzzzzzz help...

    Tuesday, November 20, 2007 12:07 PM

Answers

  • It's bad practice to insert zero-length strings, use null instead.

    INSERT into tbl_DES VALUES (null,null,null,'Name1', 'Address1')";

    (no quotes around null)

     

    it's also better to specify to which fields you want to insert, else

    your code is dead when you start to altering the fields of your tables:

     

    insert into mytbl(fld1,fld2) values(2342,'quoted string')

    is better

     

    Good luck.

    (Try firebirdsql)

    Tuesday, November 20, 2007 12:58 PM

All replies

  • It's bad practice to insert zero-length strings, use null instead.

    INSERT into tbl_DES VALUES (null,null,null,'Name1', 'Address1')";

    (no quotes around null)

     

    it's also better to specify to which fields you want to insert, else

    your code is dead when you start to altering the fields of your tables:

     

    insert into mytbl(fld1,fld2) values(2342,'quoted string')

    is better

     

    Good luck.

    (Try firebirdsql)

    Tuesday, November 20, 2007 12:58 PM
  • Make certain that your columns are defined to accept zero length strings. Also, specify the column names in your SQL statement as the other answerer mentioned.

     

    BTW, "Name" is an Access reserved word so I would recommend enclosing it in brackets when used in the SQL statement or changing the name altogether. This might be what is causing the error.

     

    Tuesday, November 20, 2007 1:08 PM