none
I cannot insert a new record using ASP.NET 3.5 into an Access DB table that uses autonumber for the primary key. RRS feed

  • Question

  • I have already removed the ID column and the associated parameter from the SQL Insert statement and the <InsertParameter> list. This has left me with the problem of the following error: Number of query values and destination fields are not the same. If I cannot insert an AutoNumber field with a null value so that it is automatically numbered and I cannot insert a record that has less than the number of named fields so that the AutoNumber'ed field is automatically numbered... How in the world do I use an AutoNumber primary key? Should I even bother with figuring this out because I do not plan on using Access ever again with ASP.NET or will I run into the same problem with other DB's?
    Monday, March 14, 2011 9:55 PM

Answers

  • It sounds like there was something wrong with the SQL statement. You do not need to include all table columns in the statement (only those configured as required in the database) and you definitely cannot include an autonumber column in an INSERT statement. The value is generated automatically by the database engine upon insert. The below link should help explain and contains examples for SQL Server and Microsoft Access databases:

    http://msdn.microsoft.com/en-us/library/ks9f57t0(VS.71).aspx

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, March 24, 2011 12:16 PM

All replies

  • We would need to see what the SQL INSERT query looks like. If you built the INSERT query using the Data Designer I would delete it and recreate it (without specifying the Autonumber column).

    You cannot provide a value (or Null) for an Autonumber column. If you need to do this then use a different column with a different data type.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, March 15, 2011 2:38 PM
  • Hi Cletus,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 
     
    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 22, 2011 1:26 AM
    Moderator
  • Paul and Alan,

    I did use the designer to develope the Insert statement. I do not have an example of the Insert statement any longer. I actually ended up converting the column to another datatype that did not have this problem. Since I cannot provide a value of my own or a null value, how does one insert data into the column? I tried to eleminate that column from the insert statement entirely but I received an error which stated that the number of columns to be inserted did not match the number of columns in the database. Can this be accomplished by reconfiguring the datasource and editing the insert statement during configuration? Do I have to create another datasource/control combination to handle record insertions? Should I instead point the datasource to a data class? I apologize for being so queryful but it confuses me that the datasource does not handle "autonumber" type fields better. In this instance I am likely to be moving over to an SQL Server or MySQL database for the expanded room that will provide for the database to grow. I will likely be using a data class that calls a stored proceedure with either of these two options. Thank you for your time and consideration.

     

    HH

     

     

    Wednesday, March 23, 2011 8:14 PM
  • It sounds like there was something wrong with the SQL statement. You do not need to include all table columns in the statement (only those configured as required in the database) and you definitely cannot include an autonumber column in an INSERT statement. The value is generated automatically by the database engine upon insert. The below link should help explain and contains examples for SQL Server and Microsoft Access databases:

    http://msdn.microsoft.com/en-us/library/ks9f57t0(VS.71).aspx

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, March 24, 2011 12:16 PM