6 Replies - 4852 Views - Last Post: 15 December 2004 - 09:50 AM Rate Topic: -----

#1 wannab  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 08-December 04

Help Incrementing Idfield In Database

Posted 14 December 2004 - 08:20 AM

I am working on a form that adds a new recipe to a database. When the user inputs the new recipe, the recipeID is unknown, so I am trying to create a new ID when the user inserts a new record.(The RecipeID starts like this: R01, so I am using the 'Right' function to add 1 to the two digits to the right of the recipeID in order to concatenate it with the "R") This is what I have:

<cfquery datasource="recipes" name="getKey">
SELECT Max(Right(RecipeID,2)) + 1 AS newRecipeID
FROM Recipes
</cfquery>

<cfquery datasource="recipes" name="getKey">
INSERT INTO Recipes
(RecipeID,RecipeName)
VALUES ('R#(getKey.newRecipeID)#'
,'#FORM.RecipeName#')
</cfquery>

Here is the error that I receive:
[Macromedia][Oracle JDBC Driver][Oracle]ORA-00904: "RIGHT": invalid identifier

Is This A Good Question/Topic? 0
  • +

Replies To: Help Incrementing Idfield In Database

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Help Incrementing Idfield In Database

Posted 14 December 2004 - 08:53 AM

Can you tell us what the getkey function is returning? What value do you get? Is it giving you waht you want? It may be that since Right traditionally is used with string variables and max with numeric variables that the Max function is unable to recognize the result of the Right function. If this is the case, you'll most likely have to get the return from the Right call, then convert the result into a number.

I could be way off here, it may be something much simpler. I'm not that familiar with Coldfusion.
Was This Post Helpful? 0
  • +
  • -

#3 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1865
  • View blog
  • Posts: 20,278
  • Joined: 17-March 01

Re: Help Incrementing Idfield In Database

Posted 14 December 2004 - 10:10 AM

Grr, don't have my oracle book with me right now.

Is there a reason you are using a string value in the id field? Traditionally the id field is just a number. The way you are using RIGHT seems to be... well... wrong (no pun intended). I would imagine you need to convert the string to a number to use MAX as well.
Was This Post Helpful? 0
  • +
  • -

#4 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Help Incrementing Idfield In Database

Posted 14 December 2004 - 11:16 AM

I'm sorry, I'm an idiot...the problem is almost certainly that Max expects a field name (although it further expects that field name to transdlate numerically).
Was This Post Helpful? 0
  • +
  • -

#5 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1865
  • View blog
  • Posts: 20,278
  • Joined: 17-March 01

Re: Help Incrementing Idfield In Database

Posted 15 December 2004 - 09:14 AM

I would agree, after reading the documentation on max, it does expect a column, the best way would be to get rid of that R in your ID column. If you can't do that, let us know and we'll try to come up with a better solution.
Was This Post Helpful? 0
  • +
  • -

#6 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Help Incrementing Idfield In Database

Posted 15 December 2004 - 09:43 AM

If you want to keep the R o the ID, your best solution may be to use the Recordset object. Skyhawk or sloth know ColdFusion way better than I, so I'm not sure of the object declaration, but they can probably tell you. The idea being, declare the recordset object, and either select the last entry, or select all entries and do a Recordset.MoveLast, taking you to the last entry. Grab the ID of the last one, amiplate to get the new one, enter it as the next record.
Was This Post Helpful? 0
  • +
  • -

#7 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Help Incrementing Idfield In Database

Posted 15 December 2004 - 09:50 AM

Depending on the db, you may also want to try
SELECT LAST(column) AS [expression]
FROM table



from W3 Schools
Another idea is to order by an orderable column (preferably something like date).
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1