Source language: Translate to:

Errors when deleting records without a key

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Errors when deleting records without a key

Postby Enigman » Sat Jan 18, 2014 11:49 pm

Hello,

I am writing an inventory and sales control application based on an Access Database. It will have three tables; Inventory, Sales and Archive. Right now I am writing the code to move obsolete inventory records to the archive and back again.

This is an example of a button code on the Inventory screen to move the record to the archive. Both tables are identical in structure.

Code: Select all
SystemSound "SystemQuestion"
CustomWindow "" "873" "696" "ConfirmBox" "NoBorder+Exclusive"
IfEx "[Confirmed] = True"
     dbpRecordToVar "db" "Inventory" "[CurrentRecordCSV]" "Delimiter=[#9];IncludeHidden=No;ForceQuotes=No"
     dbpOpenTable "db" "Archive" ""
     dbpAddRecord "db" "Archive"
     dbpVarToRecord "db" "Archive" "[CurrentRecordCSV]" "Delimiter=[#9];IncludeHidden=No"
     dbpCloseTable "db" "Archive"
.........Delete inventory record command goes here
Endif


I am having a presistent problem with trying to delete the record once it has been moved. My desire was to automatically delete the record inside the code, but that doesn't work. I get the following error:

Image
(Key column information is insufficient or incorrect. Too many rows were affected by update)

After the first time this error occurs I also get constant pop-ups saying "Book mark invalid".

When I first tried to perform the move, it could not complete because the tables had a primary key which could not be modified programmatically. It generated an error saying the key could not be changed and the dbpVarToRecord failed. So I removed the key fields from the tables and the move works. BUT, without the key fields, deleting a record becomes unstable and I get the error above. Sometimes it deletes ALL the records. Other times it deletes okay, Sometimes I get the error but nothing is deleted. It would seem that without a primary key field, record deleting does not work well. I have a manual delete record button on my forms using dbpDeleteRecord and that and the one on a grid view show the same problem.

As far as the key goes, the problem with dbpVarToRecord is that it has the key value in the delimited variable and it tries to overwrite the key field, which it can't. the alternative is to write much more code to move the data field by field. Then with tables that have a primary key, I would assume that deleting records would be stable.

1) Has anyone seen this behavior before, and 2) Does anyone know a better way to move records from table to table where the tables have primary keys?

Thanks.
User avatar
Enigman
 
Posts: 314
Joined: Tue Apr 12, 2005 3:57 pm
Location: Foothill Ranch, CA

Re: Errors when deleting records without a key

Postby Gaev » Sun Jan 19, 2014 7:34 am

Enigman:
When I first tried to perform the move, it could not complete because the tables had a primary key which could not be modified programmatically. It generated an error saying the key could not be changed and the dbpVarToRecord failed.

As far as the key goes, the problem with dbpVarToRecord is that it has the key value in the delimited variable and it tries to overwrite the key field, which it can't.

The Help file for dbpRecordToVar says that ...
Field visibility can be changed using the dbpSetColumnOrder action. By default, all fields are visible.
So, with proper "visibility", you should be able to copy a record that contains a "primary key"
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Errors when deleting records without a key

Postby Enigman » Sun Jan 19, 2014 9:15 am

Gaev,

Ahhhhhhh, I thought that the key would be copied no matter what. I just ran an experiment with the key set to hidden and changed the RecordToVar and VarToRecord commands everywhere to ignore hidden fields and it worked.

It's not that I'm not reading the help file. I really did. But I did an early attempt at the function where it didn't seem to care and always wanted to rewrite the key. Who knows why the code didn't work with all the adding and dropping of the table key and rewriting code over and over. It was late and the end of a 15 hour day. What can I say?

Anyway, revisiting that today as you suggested worked. So now with a table key, deleting within the function works and I added a confirmation dialog to either delete the original record or not.

So far I have only been using DBPro for a couple of days, but it seems like a great add-on and sooooo much better than wrangling text files.

Thanks.
User avatar
Enigman
 
Posts: 314
Joined: Tue Apr 12, 2005 3:57 pm
Location: Foothill Ranch, CA

Re: Errors when deleting records without a key

Postby Gaev » Sun Jan 19, 2014 10:28 am

Enigman:

Yes, I ran into this problem when attempting to import records from a CSV file to a Table with an AutoInc field ... Dave pointed this workaround to me ... and AGAIN two years later (call it a senior moment on my part :oops: )
User avatar
Gaev
 
Posts: 3738
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest

cron