[wendy_w]

<CF_LOGIN> 
<cfquery name="PerInfo" datasource="PerDir">
SELECT * FROM PerInfo 
WHERE SPECCODE = '#SPECCODE#'
</cfquery>
<!--- ADD A TERMINATION To MSAccess PerDir.MDB's PerHist Table on TCEConnect --->
<cfoutput>
<CFQUERY DATASOURCE="PerDir">
	INSERT INTO perhist
	 (VAC, 
	 SPECCODE, 
	 SSN, UIN, 
	 DOB,
	 TITLE, FNAME, MI, LNAME, SUBTITLE, 
	 INTERNET, NICKNAME, PICTURE, PASSWORD,
	 NEO, LoginID, FID, MailBox, 
	 EffectDate,
	 LUpdated, 
	 Notes)
	 VALUES
	('#PerInfo.VAC#', 
	'#PerInfo.SPECCODE#', 
	'#PerInfo.SSN#', #PerInfo.UIN#, 
	<CFIF #PerInfo.DOB# EQ ('')>
	'#DateFormat(NullDate, "mm/dd/yyyy")#',
	<CFELSE>
	'#PerInfo.DOB#',
	</CFIF> 
	'#PerInfo.TITLE#','#PerInfo.FNAME#', '#PerInfo.MI#', '#PerInfo.LNAME#', '#PerInfo.SUBTITLE#', 
	'#PerInfo.INTERNET#', '#PerInfo.NICKNAME#', '#PerInfo.PICTURE#', '#PerInfo.PASSWORD#',
	'#PerInfo.NEO#', '#PerInfo.LoginID#', '#PerInfo.FID#', '#PerInfo.MailBox#',
	'#DateFormat(FORM.EffectDate, "mm/dd/yyyy")#', 
	'#DateFormat(TodayDate, "mm/dd/yyyy")#', 
	'#PerInfo.NOTES#')   <--- this shows as line 34 -->
</CFQUERY>

I keep getting an error on line 34 but I just do not see it. Maybe I have been looking at it for too long?? It is taking the info and inserting it into an Access db. We pretty much have two copies of a table, one is mysql and one is access. The access table is for a client that is an old school admin type lady that doesn't have the time to learn mysql. Anywho.... About a week ago my boss changed SSN and UIN from varchar to int (in our mysql table) so I changed them to number in the access table. And since that has happened I keep finding errors. Any clues to what I am just not seeing???? I have been staring at this page for days.


here is the sql part of the error I see :

 INSERT INTO perhist (VAC, SPECCODE, SSN, UIN, DOB, TITLE, FNAME, MI, LNAME, SUBTITLE, INTERNET, NICKNAME, PICTURE, PASSWORD, NEO, LoginID, FID, MailBox, EffectDate, LUpdated, Notes) VALUES ('', '', '', , '01/01/1900', '','', '', '', '', '', '', '', '', '', '', '', '', '08/30/2006', '10/26/2006', '')

[Amadeus]

Can you post the exact error message that you are receiving? On first glance it looks as if the insert statement is providing a default value of an empty string for SSN - if it has been changed to a numerical type in the database, this empty string will likely throw an error.

[tody4me]

...
And in Access, if you pass a blank to the numeric field, it will throw an error, but if you pass NULL to the field, it doesn't I wrote 2 replace statements for this case in VB so that if the user ever entered nothing in a field on a web page, or in a form, that the program doesn't throw an error. Something like

<command string variable name> = Replace(<command string variable name>, "''", "NULL")
<command string variable name> = Replace(<command string variable name>, ", ,", ", NULL,")

[Skinnyarms]

I also see a column named LoginID, which I would assume to be of numeric type, which I believe would need to inserted as the key word NULL.

In any case I strongly recommend using the cfqueryparam tag for all of your values, not only does it take care of the NULL issue, but it also is much more secure in protecting your site from sql injection.