ODBC 37000 error

[sage703]

:confused;
I'm getting the following error:


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

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

SQL = "INSERT INTO Healthcare_Catalog( Program_Name, Program_Web,
Program_Description, Provider_Name_Short, School, Location_City,
Location_State, Program_Notes, Program_Contact, Contact_Email, Contact_Phone,
Distance, Degree_Level, Attendance_Options, Length_of_Program,
Length_of_Program_Years, Occupational_Information, Rural_Opportunities,
Admission_Prerequisites, Rural, Focus_Program) VALUES ( 'testone', '', '',
'ACNM', 35, '', '', '', '', '', '', 'Didatic Only', 'AA', 'Full Time', '< year
(0-10 days)', , '', '', '', 'no', 'no')"

Data Source = "AKHC"

The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (3:2) to (3:37) in the template file
D:\Inetpub\dist-web\users\sagea\health_careers\sessions\program_in sert.cfm.

Apparently from this code:

<cfquery datasource="#data_source#">
INSERT INTO Healthcare_Catalog(
Program_Name,
Program_Web,
Program_Description,
Provider_Name_Short,
School,
Location_City,
Location_State,
Program_Notes,
Program_Contact,
Contact_Email,
Contact_Phone,
Distance,
Degree_Level,
Attendance_Options,
Length_of_Program,
Length_of_Program_Years,
Occupational_Information,
Rural_Opportunities,
Admission_Prerequisites,
Rural,
Focus_Program)
VALUES (
'#SESSION.ProgWiz.Program_Name#',
'#SESSION.ProgWiz.Program_Web#',
'#SESSION.ProgWiz.Program_Description#',
'#SESSION.ProgWiz.Provider_Name_Short#',
#SESSION.ProgWiz.School#,

'#SESSION.ProgWiz.Location_City#',
'#SESSION.ProgWiz.Location_State#',
'#SESSION.ProgWiz.Program_Notes#',
'#SESSION.ProgWiz.Program_Contact#',
'#SESSION.ProgWiz.Contact_Email#',
'#SESSION.ProgWiz.Contact_Phone#',
'#SESSION.ProgWiz.Distance#',
'#SESSION.ProgWiz.Degree_Level#',
'#SESSION.ProgWiz.Attendance_Options#',
'#SESSION.ProgWiz.Length_of_Program#',
#SESSION.ProgWiz.Length_of_Program_Years#,
'#SESSION.ProgWiz.Occupational_Information#',
'#SESSION.ProgWiz.Rural_Opportunities#',
'#SESSION.ProgWiz.Admission_Prerequisites#',
'#SESSION.ProgWiz.Rural#',
'#SESSION.ProgWiz.Focus#')
</cfquery>

I'm not sure what the problem is. Can anyone help me out?

Thanks,
S./

[mxstu]

For one thing it looks like you're missing a value ...

'< year (0-10 days)' , (missing ), '', '', '', 'no', 'no'

[sage703]

That happens because I don't put a value in the field for some reason (that's
something i can fix later i think). So this time I put a numeric value in the
field, as the database requires and yet I still got an error (very similar):

ODBC Error Code = 22005 (Error in assignment)

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

SQL = "INSERT INTO Healthcare_Catalog( Program_Name, Program_Web,
Program_Description, Provider_Name_Short, School, Location_City,
Location_State, Program_Notes, Program_Contact, Contact_Email, Contact_Phone,
Distance, Degree_Level, Attendance_Options, Length_of_Program,
Length_of_Program_Years, Occupational_Information, Rural_Opportunities,
Admission_Prerequisites, Rural, Focus_Program) VALUES ( 'TESTONE', '', '',
'ACNM', 35, '', '', '', '', '', '', 'Didatic Only', 'AA', 'Full Time', '1
year', 1, '', '', '', 'no', 'no')"

Data Source = "AKHC"

There appears to be the right number of fields (21 to insert into and 21
inserted). Also the only numeric fields (School, and Length_of_Program_Years
are both input correctly (without the single ' marks around the variables). I
seem to have a problem using INSERT INTO statements in general. When I use
CFINSERT I have no problems generally, although I cannot use CFINSERT herre I
don't think because I have multiple inserts from multiple forms (I'm using
session variables and have a multiple page form screen leading up to this
error).

Thanks for your help,
S./

[mxstu]

Check your other thread.