3 Replies - 7085 Views - Last Post: 23 November 2006 - 08:42 PM Rate Topic: -----

#1 wendy_w  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 02-June 05

INSERT INTO Error Access DB and Coldfusion MX

Posted 26 October 2006 - 05:24 AM


<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', '')



Is This A Good Question/Topic? 0
  • +

Replies To: INSERT INTO Error Access DB and Coldfusion MX

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: INSERT INTO Error Access DB and Coldfusion MX

Posted 26 October 2006 - 01:15 PM

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.
Was This Post Helpful? 0
  • +
  • -

#3 tody4me  Icon User is offline

  • Banned
  • member icon

Reputation: 12
  • View blog
  • Posts: 1,398
  • Joined: 12-April 06

Re: INSERT INTO Error Access DB and Coldfusion MX

Posted 26 October 2006 - 01:25 PM

...
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,")


Was This Post Helpful? 0
  • +
  • -

#4 Skinnyarms  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 18-November 06

Re: INSERT INTO Error Access DB and Coldfusion MX

Posted 23 November 2006 - 08:42 PM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1