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 - '<value>' WHERE BarcodeNumber = '<AnotherValue>' 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: [email protected] [mailto:[email protected]] On Behalf Of Svein Erling Tysvær Sent: 04 February 2013 2:32 PM To: '[email protected]' 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 - '<value>' WHERE BarcodeNumber = '<AnotherValue>' AND StockControlYN = '1' The problem I see here, is that <value> 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
