Replace

SQLCommand = SQLCommand & "CURRENTLEVELS = CURRENTLEVELS - '" & Format(Readin(3), "#####0") & "' "

for

SQLCommand = SQLCommand & "CURRENTLEVELS = CURRENTLEVELS -" & Format(Readin(3), "#####0")

Best Regards
=========
|| ISMAEL ||
=========

----- Original Message -----
From: Andy Gable
To: firebird-support@yahoogroups.com
Sent: Monday, February 04, 2013 9:56 AM
Subject: RE: [firebird-support] Firebird Verison of this MySQL command



Hi,

CURRENTLEVELS is set as a integer
BARCODENUMBER is set as a VarChar(13) as that is the max number of a barcode
STOCKCONTROLYN is set to VarChar(1) as it is is either 1 or 0

And yes

UPDATE ProductTable
SET CurrentLevels = CurrentLevels - ' '
WHERE BarcodeNumber = ' '
AND StockControlYN = '1'

That is what I want to do but Firebird is not happy is this function not
supported in Firebird?

Andy

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: 04 February 2013 2:32 PM
To: firebird-support@yahoogroups.com'
Subject: RE: [firebird-support] Firebird Verison of this MySQL command

>I am trying to update a coloum in Firebird via the following SQL
>command
>
>SQLCommand = vbNullString
>SQLCommand = SQLCommand & "UPDATE ProductTable "
>SQLCommand = SQLCommand & "SET "
>SQLCommand = SQLCommand & "CURRENTLEVELS = CURRENTLEVELS - '" &
Format(Readin(3), "#####0") & "' "
>
>SQLCommand = SQLCommand & "WHERE "
>SQLCommand = SQLCommand & "BARCODENUMBER='" & Readin(2) & "' and "
>SQLCommand = SQLCommand & "STOCKCONTROLYN='1';"
>
>ReadIn(3) is a value that comes from the data file
>
>My problem is this The above SQL command works on MySQL, MSSQL,
>PostgreSQL But it will not work on Firebird
>
>My application that is doing the processing crashes when it run agenst
>Firebird
>
>So what is the Firebird version of this?
>
>basically I want to update the CURRENTLEVELS orginal value but take
>away the
>Readin(3) value

Hi Andy!

Basically, your SQL call is something like:

UPDATE ProductTable
SET CurrentLevels = CurrentLevels - ' '
WHERE BarcodeNumber = ' '
AND StockControlYN = '1'

The problem I see here, is that seems to be a string - there should
not be ' around the value if it is a number. Firebird does not support
deducting from a string, at least not this way (there may be UDFs or similar
that can do it). If BarcodeNumber and StockControl are CHAR or VARCHAR, then
it is of course correct to use ' around them.

HTH,
Set

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the main
(top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links





[Non-text portions of this message have been removed]