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:02 PM

Answers

  •  

    The first thing you should do when writing an insert statement is to include the names of the columns you are inserting into.  For example,

     

    Insert into tbl_DES(s_Generation, s_Lineage, s_GUID, Name, Address) Values(...)

     

    This is because the order of the columns is not guaranteed to be the same as you are expecting and just by providing the values, then it could potentially put them in the wrong columns (or get type errors).

     

    If you want to miss out the first three columns, then you should do

     

    Insert into tbl_DES(Name, Address) Values( 'Name1', 'Address1')

     

    Without knowing the types of s_Generation, s_Lineage and s_GUID, then it is hard to say exactly what the problem is.  Perhaps they are integer types in the table definition, but you are providing strings.  Or they might be not nullable, etc.

     

    Hope this helps.

    Wednesday, November 21, 2007 12:28 PM
  • Hi AProgrammer,

    For column whose type is not “text/memo”, you can’t set its value to “”. If you don’t want to insert anything into it, you must set inserted value to null.

     

    Assume that s_Generation, s_Lineage, s_GUID are of number types(such as byte, integer, long integer, single, double, decimal and so on). You can modify your insert string to “insert into tbl_DES values(null, null,null,’Name1’,'Address1')”.

     

    Notice: 1. You can’t insert null value to non-null columns.

                2. You needn’t insert value to “AutoNumber” columns.

               3. null is not equal to “”. null means no value. “” means the value is “”.

     

    Thursday, November 22, 2007 3:09 AM

All replies

  •  

    The first thing you should do when writing an insert statement is to include the names of the columns you are inserting into.  For example,

     

    Insert into tbl_DES(s_Generation, s_Lineage, s_GUID, Name, Address) Values(...)

     

    This is because the order of the columns is not guaranteed to be the same as you are expecting and just by providing the values, then it could potentially put them in the wrong columns (or get type errors).

     

    If you want to miss out the first three columns, then you should do

     

    Insert into tbl_DES(Name, Address) Values( 'Name1', 'Address1')

     

    Without knowing the types of s_Generation, s_Lineage and s_GUID, then it is hard to say exactly what the problem is.  Perhaps they are integer types in the table definition, but you are providing strings.  Or they might be not nullable, etc.

     

    Hope this helps.

    Wednesday, November 21, 2007 12:28 PM
  • Hi AProgrammer,

    For column whose type is not “text/memo”, you can’t set its value to “”. If you don’t want to insert anything into it, you must set inserted value to null.

     

    Assume that s_Generation, s_Lineage, s_GUID are of number types(such as byte, integer, long integer, single, double, decimal and so on). You can modify your insert string to “insert into tbl_DES values(null, null,null,’Name1’,'Address1')”.

     

    Notice: 1. You can’t insert null value to non-null columns.

                2. You needn’t insert value to “AutoNumber” columns.

               3. null is not equal to “”. null means no value. “” means the value is “”.

     

    Thursday, November 22, 2007 3:09 AM