Help with ODBC Error Code = 37000 (Syntax error or access violation)

dplr · 09-01-2006, 01:43 AM

I am VERY rusty at this. Any help would be greatly appreciated.
Deb

Error Diagnostic Information

ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (20:3) to (20:47) in the template file c:\websites\wwprsd\cms\clubs\signup.cfm.

This is the code:

<body>


<CFPARAM Name="FORM.club" Default="None Entered">
<CFPARAM Name="FORM.studentid" Default="None Entered">
<CFPARAM Name="FORM.firstname" Default="None Entered">
<CFPARAM Name="FORM.lastname" Default="None Entered">
<CFPARAM Name="FORM.team" Default="None Entered">

<cflock name="InsertNewRecord" type="exclusive" timeout="30">
<cftransaction>
<cfquery name="Addrecord" datasource="CLUBS">
INSERT INTO #FORM.club#(studentid, firstname, lastname, team)
VALUES(#FORM.studentid#,'#FORM.firstname#','#FORM.lastname#','#FORM.team#')
</cfquery>
</cftransaction>
</cflock>



<cfoutput>
<meta http-equiv="REFRESH" content="0;url="Complete.cfm">
</cfoutput>
</body>

cfxerious · 09-13-2006, 06:12 PM

i'd focus some attention on the dynamic table name issue. For example, you have <cfparam name="form.club" default="Not Entered">

My guess is based on form values dictates which table to insert into? With that, there needs to be a more failsafe method of determining which table needs to be in the INSERT statement based on passed form values.

Maybe something like the following:

Code:

<cfif IsDefined("form.club") AND form.club neq "" AND Len(form.club)>
  <!---- run your code here for the rest of the form values and insert.---->
  <!---- Maybe add a conditional that aids in the selection of the table for the insert statement to work with based on passed form values.----->
<cfelse>
<--- error message about the insert failing ---->
</cfif>

let me know if I can be of any additional assistance.

cfmatt · 02-18-2007, 10:09 PM

Can you post the form code as well please.