Data parse error

You guys have helped me this week figure out this form, and it's working perfectly except for the last step, which is actually putting the form responses into a database. I'm getting the following error: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker driver][FileMaker]Parse Error in SQL <br>The error occurred on line 487. I'm not sure how to resolve this. Could it be settings in the actual database tables? How can I get around something like this? Thanks again for your continued help!

You all have been able to help me so much this week with a form I've been working on, and it's nearly complete. The final step is to get the data entered into the database without throwing an error. The error is: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker driver][FileMaker]Parse Error in SQL <br>The error occurred on line 487. The SQL code in my document is: <cfquery datasource="filemaker_srmastaff" name="newRegistration"> INSERT INTO SRAStaff (First, Last, MI, EmpID, CurrentSchool, CurrentSubjectOrGr, PhoneHome, PhoneCell, PhoneWork, Voicemail, Address, City, State, Zip, Email, ApplyPosition, ApplySchool, SchoolApplyOther, OnlineSubjectGr, OnlineSubjectGrPreferred, YrsPartic, TshirtSize) VALUES ('#Form.First#', '#Form.Last#', '#Form.MI#', '#Form.EmpID#', '#Form.CurrentSchool#', '#Form.CurrentSubjectorGr#', '#Form.PhoneHome#', '#Form.PhoneCell#', '#Form.PhoneWork#', '#Form.Voicemail#', '#Form.Address#', '#Form.City#', '#Form.State#', '#Form.Zip#', '#Form.Email#', '#Form.ApplyPosition#', '#Form.SchoolApply#', '#Form.SchoolApplyOther#', '#Form.SubjectGradePreferred#', '#Form.SubjectGradeOther#', '#Form.YrsPartic#', '#Form.TshirtSize#') </cfquery> Could it be that setting within the actual database for data type, etc. could be causing the issue? If so, how do I get around this? Thanks again for any help you can offer!

Maybe try giving cfinsert a spin? :Den -- Only a god can save us. Martin Heidegger ----- Excess quoted text cut - see Original Post for more -----

names of some of your db columns - like EmpID - suggest that those are integer (or similar) datatype. however, you have ALL the values being inserted into db enclosed in ' (single quotes) - like '#Form.EmpID#' - which is incorrect for integer-based columns. only string-based data should be enclosed in single quotes. on a related subject, using <cfqueryparam> tag will be a good idea... Azadi On 18/03/2010 03:42, Steven Sprouse wrote: ----- Excess quoted text cut - see Original Post for more -----

Use <cfqueryparm> and set the datatype for each field to match the datatype in the database.  Make sure that any fields that are set to not null in the database actually contain data before trying to insert them.  Make sure the value of any key field field is unique and does not already exist. If you have debug on, you should be able to see the actual SQL that is causing the error. > > You all have been able to help me so much this week with a form I've been working on, and it's nearly complete. The final step is to get the data entered into the database without throwing an error. > > The error is: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker driver][FileMaker]Parse Error in SQL <br>The error occurred on line 487.

I'm sorry, I'm just not getting it. I've spent a week on this form and I'm just about ready to throw in the towel. I tried this change, but still got the parse error. Not even sure if this is the correct use of cfqueryparam .... <cfquery datasource="filemaker_srmastaff" name="newRegistration"> INSERT INTO SRAStaff (First, Last, MI, EmpID, CurrentSchool, CurrentSubjectOrGr, PhoneHome, PhoneCell, PhoneWork, Voicemail, Address, City, State, Zip, Email, ApplyPosition, ApplySchool, SchoolApplyOther, OnlineSubjectGr, OnlineSubjectGrPreferred, YrsPartic, TshirtSize) VALUES ("<cfqueryparam value="#Form.First#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.Last#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.MI#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.EmpID#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.CurrentSchool#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.CurrentSubjectorGr#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.PhoneHome#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.PhoneCell#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.PhoneWork#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.Voicemail#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.Address#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.City#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.State#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.Zip#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.Email#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.ApplyPosition#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.SchoolApply#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.SchoolApplyOther#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.SubjectGradePreferred#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.SubjectGradeOther#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.YrsPartic#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.TshirtSize#" cfsqltype="cf_sql_varchar">") </cfquery>

Remove all the quotes around the cfqueryparams.  ColdFusion will deal with the quotes for you when you use the cfqueryparam tag. I'm sorry, I'm just not getting it. I've spent a week on this form and I'm just about ready to throw in the towel. I tried this change, but still got the parse error. Not even sure if this is the correct use of cfqueryparam .... <cfquery datasource="filemaker_srmastaff" name="newRegistration"> INSERT INTO SRAStaff (First, Last, MI, EmpID, CurrentSchool, CurrentSubjectOrGr, PhoneHome, PhoneCell, PhoneWork, Voicemail, Address, City, State, Zip, Email, ApplyPosition, ApplySchool, SchoolApplyOther, OnlineSubjectGr, OnlineSubjectGrPreferred, YrsPartic, TshirtSize) VALUES ("<cfqueryparam value="#Form.First#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.Last#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.MI#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.EmpID#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.CurrentSchool#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.CurrentSubjectorGr#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.PhoneHome#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.PhoneCell#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.PhoneWork#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.Voicemail#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.Address#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.City#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.State#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.Zip#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.Email#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.ApplyPosition#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.SchoolApply#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.SchoolApplyOther#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.SubjectGradePreferred#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.SubjectGradeOther#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.YrsPartic#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.TshirtSize#" cfsqltype="cf_sql_varchar">") </cfquery>

The double quotes are your problem.  This will NOT work: "<cfqueryparam value="#Form.First#" cfsqltype="cf_sql_varchar">" This is all you need: <cfqueryparam value="#Form.First#" cfsqltype="cf_sql_varchar"> The cfqueryparam already handles the correct quotes based on the cfsqltype for each element. I'm sorry, I'm just not getting it. I've spent a week on this form and I'm just about ready to throw in the towel. I tried this change, but still got the parse error. Not even sure if this is the correct use of cfqueryparam .... <cfquery datasource="filemaker_srmastaff" name="newRegistration"> INSERT INTO SRAStaff (First, Last, MI, EmpID, CurrentSchool, CurrentSubjectOrGr, PhoneHome, PhoneCell, PhoneWork, Voicemail, Address, City, State, Zip, Email, ApplyPosition, ApplySchool, SchoolApplyOther, OnlineSubjectGr, OnlineSubjectGrPreferred, YrsPartic, TshirtSize) VALUES ("<cfqueryparam value="#Form.First#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.Last#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.MI#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.EmpID#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.CurrentSchool#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.CurrentSubjectorGr#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.PhoneHome#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.PhoneCell#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.PhoneWork#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.Voicemail#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.Address#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.City#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.State#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.Zip#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.Email#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.ApplyPosition#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.SchoolApply#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.SchoolApplyOther#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.SubjectGradePreferred#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.SubjectGradeOther#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.YrsPartic#" cfsqltype="cf_sql_varchar">", "<cfqueryparam value="#Form.TshirtSize#" cfsqltype="cf_sql_varchar">") </cfquery>

Do I need the commas? I've tried it without the quotes and with single quotes and am still getting the same error. Parse error in SQL. This is a filemaker database and all of the datatypes are TEXT. Does that mean anything?

Steven, You will still need the commas. Chapter 7 of this:http://www.filemaker.com/downloads/pdf/fm10_odbc_jdbc_guide_en.pdf may help you out as well. On Thu, Mar 18, 2010 at 10:37 AM, Steven Sprouse <ssprouse@ccboe.com> wrote: > > Do I need the commas? I've tried it without the quotes and with single quotes and am still getting the same error. Parse error in SQL. > > This is a filemaker database and all of the datatypes are TEXT. Does that mean anything?

Well, I'd say I'm officially stuck then. Looks like "Text" datatype in filemaker converts to varchar, which is what I have. I've removed the quotes around the cfqueryparam statements. The form is still not putting the data into the database. Would dropdown or check boxes convert the data type for some reason? I'm just thinking out loud. I really am not sure beyond this point where to go.

Steven, The form will only post values not datatypes. I'd go back to the database and make sure of the receiving datatypes. Could you repost the error that ColdFusion is throwing? Thanks sas On Thu, Mar 18, 2010 at 11:42 AM, Steven Sprouse <ssprouse@ccboe.com> wrote: > > Well, I'd say I'm officially stuck then. Looks like "Text" datatype in filemaker converts to varchar, which is what I have. I've removed the quotes around the cfqueryparam statements. The form is still not putting the data into the database. Would dropdown or check boxes convert the data type for some reason? I'm just thinking out loud. I really am not sure beyond this point where to go.

Scott, The error is: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker driver][FileMaker]Parse Error in SQL <br>The error occurred on line 473. I tried simplifying the insert query to just the first item, and I'm still getting an error. The first item is just the first name. This leads me to believe that it HAS to be something with the actual database somewhere, right? The reason I ask is because I know that this database used to exist as a Filemaker 4 database, and the person did some work to convert it to a filemaker 10. I feel like I've quadruple checked the INSERT query and it's syntax is correct.

Steven, Try simplifying your insert query to troubleshoot the problem.  Remove all but the first field from the query and test.  Add one more field and test, rinse and repeat.  Also, enable debugging in CF Administrator, and make sure that you add your computers IP address to the debug list. Carl Steven Sprouse wrote: > Well, I'd say I'm officially stuck then. Looks like "Text" datatype in filemaker converts to varchar, which is what I have. I've removed the quotes around the cfqueryparam statements. The form is still not putting the data into the database. Would dropdown or check boxes convert the data type for some reason? I'm just thinking out loud. I really am not sure beyond this point where to go.

Try outputting your results, past it into a sql window and execute it <cfoutput> INSERT INTO SRAStaff (First, Last, MI, EmpID, CurrentSchool, CurrentSubjectOrGr, PhoneHome, PhoneCell, PhoneWork, Voicemail, Address, City, State, Zip, Email, ApplyPosition, ApplySchool, SchoolApplyOther, OnlineSubjectGr, OnlineSubjectGrPreferred, YrsPartic, TshirtSize) VALUES ('#Form.First#', '#Form.Last#', '#Form.MI#', '#Form.EmpID#', '#Form.CurrentSchool#', '#Form.CurrentSubjectorGr#', '#Form.PhoneHome#', '#Form.PhoneCell#', '#Form.PhoneWork#', '#Form.Voicemail#', '#Form.Address#', '#Form.City#', '#Form.State#', '#Form.Zip#', '#Form.Email#', '#Form.ApplyPosition#', '#Form.SchoolApply#', '#Form.SchoolApplyOther#', '#Form.SubjectGradePreferred#', '#Form.SubjectGradeOther#', '#Form.YrsPartic#', '#Form.TshirtSize#') </cfoutput> On Thu, Mar 18, 2010 at 12:07 PM, Carl V > > Steven, > > Try simplifying your insert query to troubleshoot the problem.  Remove > all but the first field from the query and test.  Add one more field and > test, rinse and repeat.  Also, enable debugging in CF Administrator, and > make sure that you add your computers IP address to the debug list.