UPDATE creates duplicate record???

[hubare]

I'm ot sure if this is the right place to post or if it's been asked before but I've been sat staring at my PC for about 2 hours (probably without blinking) and I'm close to murder!

from a web page... I am attempting to update a table with a composite primary key. These key fields are also linked to other tables... All i'm basically wanting to do is decrement a stock level by one like so...

UPDATE tblSourceType
SET Currentstock = CurrentStock-1
WHERE (SourceId = 'TWW1')
AND (MagazineId = 'HF1')
AND (TypeId = 1)

All three criteria 'WHERE' fields are the primary keys but i dont understand why it says it will create a duplicate record bearing in mind im not updating any of the primary fields... Also, when i put it directly into the Access Query window, it works fine

Any help.. or an explanation would be greatly appreciated

 

[masterkeedu]

sounds like your using access

get a linux server and host mysql

your query seems right, but access, though ive never used for web, can be very finicky.

 

[hubare]

Very true...

I would but its a total nightmare... the whole project was written in 97 with a Access VBA front end which they still use... we've tried linking to SQL Server but the front end wont connect properly... nightmare!]

Anyway, on another Access is finicky front... the problem was simply one of syntax (kind of). Instead of wrting the string, I created it in Access Query window and copied the string... it looks a little different

UPDATE TblSourceType SET TblSourceType.CurrentStock = "CurrentStock-1"
WHERE (((TblSourceType.SourceId)='TWW1') AND ((TblSourceType.MagazineId)='HF1') AND ((TblSourceType.TypeId)=1));

For some reason, this works, eventhough it is fundamentally identical to the one i did! I still dont understand WHY it didnt work other than what you mentioned

 

[hubare]

Also...

seeing as u've put ur spec down... i'll let u know what my company has supplied me with to run the following..

Photoshop CS2
Visual Studio 2003
Macromedia Flash 2004

Intel Pentium 3 500Mhz / 384 Mb sdram / unbelievably crappy on board Graphics / and 20Gb HDD... i have no CD/DVD drive because they had to use the drive space for a new HDD... before that i was working with 10Gb and my own External HDD linked to a... u guessed it... USB 1 connection.... imaging opening photoshop CS2 from an Ext HDD on USB 1... no wonder im 'slowly' going mental

 

[Cache-man]

That sounds quite painfully tedious hubare, running all those programs on that spec. I run them on a 3GHz P4 with a gig of DDR400 and some things are still slow.