Help needed!

• Daniel Matthews

  Grasshopper

  Points: 19

  More actions

  February 28, 2004 at 7:40 am

  #178230

  Hi

  I am trying to add a new record into my database (MS Access) using SQL statements and ASP.

  I have to increment the primary key by 1 every time i add a new record. As this is for a uni project we are not allowed to use access' auto-number feature for the PK. Here is my code so far

  personalID = "SELECT MAX(Person_ID) As LastNumber FROM resident"

  nextNo = LastNumber + 1

  indexNo = "INSERT INTO resident (Person_ID, Name) VALUES (" & nextNo & ", 'SumName')"

  'Execute SQL commands

  connection.Execute(indexNo)

  However when this is ran i get an error saying that the changes were not made because of the primary key field and duplicates or something???

  Any help would be appreciated

  Thanx

  Dan

   

• noeld

  SSC Guru

  Points: 96590

  More actions

  February 28, 2004 at 10:47 am

  #496331

  Is that  your actual code?

  How did you get the Max(Person_ID) to run?

  shoulden't you do something like:

  strQuery = "INSERT INTO resident (Person_ID, Name)

  SELECT (SELECT MAX(Person_ID) + 1  FROM resident),'YourName' "

  connection.Execute(strQuery&nbsp

  Note: the above was just meant to help you but I recommend you to convert all that to real ADO code

  Just My $0.02 

  
  * Noel