Newbie

I am new to Coldfusion and am trying to figure out a few things. Please bear with me as I'm under an extreme time crunch here. I have the following query and I want to set the return value to a variable and increase it by 1. What is the best way to do this?

<cfquery name="MaxEvalID" datasource="assoceval">
SELECT DISTINCTROW Max([EvaluationList].[EvaluationID]) AS [EvaluationID]
FROM EvaluationList;
</cfquery>

Thanks for any help and sorry if this question is too easy. I have searched and searched and can't figure it out.

select max(evaluationID) + 1 as evaluationID
from EvaluationList

What bif the database has no records yet? I want it to get the MAX value, if it exists and return it. If not I want it to set it to "1". How can I do that? This is an AutoNumber field but I can't figure out how to structure my INSERT query to handle this. I figured I would just make it insert a value there. BTW, I didn;t create the DB and am being forced to use it because someone spent "a lot of time on it".

Here is what I have already...

<cfquery name="MaxEvalID" datasource="assoceval">
SELECT Max(EvaluationID) + 1 AS newEvalID
FROM EvaluationList;
</cfquery>

<cfset newEvalID = #MaxEvalID.newEvalID#>

<CFQuery datasource="assoceval">
INSERT INTO EvaluationList VALUES('#MaxEvalID.newEvalID#','#FORM.Evaluated_By#', '#FORM.For#')
</cfquery>

On the INSERT INTO EvaluationList query, it seems to be passing a blank value. Here is the error code:

ODBC Error Code = 22005 (Error in assignment)


[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.


SQL = "INSERT INTO EvaluationList VALUES('','99998', '99999')"

Data Source = "ASSOCEVAL"


The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (9:1) to (9:32) in the template file E:\INTRANET\Associate Eval\confirm.cfm.


Any help would be greatly appreciated!!!

First, if the field is a number you can't put quotes around it in your insert statement. Second, if the field is already an autonumber field then you don't need to select the max id or insert it. The database will automatically populate the field with the next number when you insert a new record:

<CFQuery datasource="assoceval">
INSERT INTO EvaluationList (evaluated_by, for ) VALUES('#FORM.Evaluated_By#', '#FORM.For#')
</cfquery>

IT finally works. I guess I just had to hold my tongue just right.

Thanks for all the help.