No Primary Key error

We have created a script for v14 using the database functionality now available in eggPlant. We are simply reading contents from the db and assigning them to a variable, then writing a new value to one of the fields.

The db is in Access and it DOES have a primary key set to the first column.

We are able to read from the database and display the contents correctly in the Log display using the following code:
//Setting the taxNum variable
set taxNum to "000112222"
//Setting up the DB
set myDB to (type:"odbc",DSN:"eggPlantDSN",user:"",password:"")
//Puts table into infoTable variable
put table "CreatNewAccount" of myDB into infoTable
//Finds the table record that matches the variable taxNum
set clientInfo to the record of infoTable where TaxID is taxNum
//Shows the clientInfo record
log clientInfo

But when we attempt to write to the database using this command (which would appear immediately after the code above), we receive the attached error:
//Puts date into the Birthdate field of the client record
put "1/1/1966" into clientInfo's Birthdate

I am also attaching a pic of the db in design mode showing the primary key set to the 'ID' Field Name.

Thanks for your help!

Comments

  • SenseTalkDougSenseTalkDoug ForumAdmin admin
    What does the RecordInfo property of the table show? Try adding this at the end of your first script:
    log infoTable's recordInfo
    
    Please post the result here, but my guess is that none of the columns will show any key information. If that's the case then apparently the ODBC interface to Access doesn't provide that information and we'll need to investigate further to see if there's a way to get that.
  • Here is the log entry from the 'log infoTable's recordInfo' command:


    ((Name:"YearsatAddress", Type:"number"),(Name:"BirthLocation", Type:"string"),(Name:"LastName", Type:"string"),(Name:"Birthdate", Type:"string"),(Name:"MaritalStatus", Type:"string"),(Name:"FederalTaxCert", Type:"string"),(Name:"State", Type:"string"),(Name:"TaxID", Type:"string"),(Name:"Smoker", Type:"string"),(Name:"City", Type:"string"),(Name:"FirstName", Type:"string"),(Name:"ID", Type:"integer"),(Name:"ResetID", Type:"number"),(Name:"Zip", Type:"number"),(Name:"Sex", Type:"string"))


    Is one of these supposed to say "Primary" or something along those lines?
  • SenseTalkDougSenseTalkDoug ForumAdmin admin
    Yes, we would expect the ID column to show up as (KeyType:"Primary", Name:"ID", Type:"integer") if it's the primary key. For some reason the ODBC database connection isn't giving us that information.

    So your only option for updating the database in that case is to (carefully!) use the "delete record" command to delete the old record and then an "add record" command to add the updated record back into the table. Those commands can be used on a table without a primary key.
  • This worked!

    We changed our script to the following: it reads the record to a variable, removes the record, adds the new info to the record, and then adds the record back to the DB.
    set taxNum to "000112222" -- Setting the taxNum variable
    set myDB to (type:"odbc",DSN:"eggPlantDSN",user:"",password:"") -- Setting up the DB
    put table "CreatNewAccount" of myDB into infoTable -- Puts table into infoTable variable
    set clientInfo to the record of infoTable where TaxID is taxNum -- Finds the record that matches taxNum
    
    delete the record of infoTable where TaxID is taxNum -- Deletes the record from the table
    
    log clientInfo -- Shows the clientInfo record
    
    try -- The Try/Catch keeps the No Primary Key error from happening
    	set clientInfo's Birthdate to "12/11/1966" -- Puts the date into the Birthdate field of the record
    catch failedUpdateExceptions -- Catches any exceptions generated
    end try
    
    add record clientInfo to infoTable -- Adds the record back into the infoTable
    
    log clientInfo -- Shows the new clientInfo record 
    

    We appreciate your help! :D
  • SenseTalkDougSenseTalkDoug ForumAdmin admin
    Perfect! I'm glad that worked for you.

    For future reference there are a couple of other ways you can avoid the exception. One is to copy the record into another variable, which will no longer have a connection to the database, before updating any values, like this:
    put clientInfo into clientInfoCopy -- make a copy not connected to the db
    set clientInfoCopy's Birthdate to "12/11/1966"
    add record clientInfoCopy to infoTable
    
    Another approach would be to set the table's autoSaveUpdates property to false:
    set infoTable's autoSaveUpdates to false
    
    or create the table that way in the first place:
    put table "CreatNewAccount" of myDB with (autoSaveUpdates:NO) into infoTable
    
    Then you can update properties in a record without SenseTalk automatically trying (and failing) to save them.
Sign In or Register to comment.