Insert a list into an Access database?

[calista]

I'm sure this is possible, because I have other places that I store lists in an Access database field, but I can't seem to get this to work. Here is my insert:

<!--- Insert data into the database. --->
	<CFQUERY NAME=&quot;InsertData&quot;
			DATASOURCE=&quot;#Application.Datasource#&quot;
			DBTYPE=&quot;ODBC&quot;>
			INSERT INTO TimeTable
			(
			TimePersonID,
			TimeWeekending,
			TimeTodayIs
			)
			VALUES
			(
			'#Cookie.User.ID#',
			 #Form.Weekending#,
			 #TodayIs#
			)
	</CFQUERY>

The first two items insert fine, but I get the following error when I try to insert the value of "TodayIs". As you can see, TodayIs is a list of dates. I performed an IsSimpleValue test on TodayIs, and it returned "Yes", so I'm not sure why this insert doesn't work.

Error Diagnostic Information
ODBC Error Code = 21S01 (Insert value list does not match column list)


[Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.


SQL = "INSERT INTO TimeTable ( TimePersonID, TimeWeekending, TimeTodayIs ) VALUES ( 'DBC2FE2C-AF32-43A8-BE3E57780D3700BC', {ts '2001-08-04 00:00:00'}, {ts '2001-07-30 00:00:00'},{ts '2001-07-31 00:00:00'},{ts '2001-08-01 00:00:00'},{ts '2001-08-02 00:00:00'},{ts '2001-08-03 00:00:00'} )"

Calista :-X
Jedi Knight,
Champion of the Force

 

[calista]

Here's an update. "TodayIs" is the ONLY field that doesn't work. If I try without single quotes, as above, I get that same error. If I DO use single quotes, I get this error, which I would expect.

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


[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''{ts '2001-07-30 00:00:00'},{ts '2001-07-31 00:00:00'},{ts '2001-08-01 00:00:00'},{ts '2001-08-02 00:00:00'},{ts '2001-08-03 00:00:00'}''.


SQL = "INSERT INTO TimeTable ( TimePersonID, TimeWeekending, TimeTodayIs, TimeCode, TimeClass, TimeRegular, TimeOT, TimeDoubleOT ) VALUES ( 'DBC2FE2C-AF32-43A8-BE3E57780D3700BC', {ts '2001-08-04 00:00:00'}, '{ts '2001-07-30 00:00:00'},{ts '2001-07-31 00:00:00'},{ts '2001-08-01 00:00:00'},{ts '2001-08-02 00:00:00'},{ts '2001-08-03 00:00:00'}', 'DigiTest,DigiTest,DigiTest,DigiTest,DigiTest', '1,1,1,1,1', '8,8,8,8,8', '0,0,0,0,0', '0,0,0,0,0' )"

Calista :-X
Jedi Knight,
Champion of the Force

 

[JHoarty]

What's the datatype of the field TimeTodayIs? John Hoarty
jhoarty@quickestore.com

http://www.quickestore.com

 

[calista]

I've tried both "Memo" and "Date/Time". It's currently "Date/Time". Calista :-X
Jedi Knight,
Champion of the Force

 

[JHoarty]

I'd definitely go back to memo, and then try inserting the list by using

... VALUES
(
'#Cookie.User.ID#',
#Form.Weekending#,
#PreserveSingleQuotes(TodayIs)#
)
</CFQUERY>

I can't swear to this, but I think it might work. You may have to try it with and without single ticks around the pound signs as before. John Hoarty
jhoarty@quickestore.com

http://www.quickestore.com

 

[calista]

OK, I changed the field to &quot;Memo&quot;, and I wrapped it in the PreserveSingleQuotes function with and without single quotes, and it errored out the same as previously described. Calista :-X
Jedi Knight,
Champion of the Force

 

[JHoarty]

Hmm, okay, I gues the question becomes, &quot;how did this list get built to start with?&quot;

I mean, it looks to me like the problem is that you have a string that happens to contain single ticks and commas inside it. You really want it to look like

&quot;{ts '2001-07-30 00:00:00'},{ts '2001-07-31 00:00:00'},{ts '2001-08-01 00:00:00'},{ts '2001-08-02 00:00:00'},
{ts '2001-07-30 00:00:00'&quot;

I suppose, but you can't put double quotes around the variable that you are trying to insert. So, can you build the list such that it looks like:

<cfset list = &quot;#TodayIs#&quot;>

and then insert the variable '#PreserveSingleQuotes(list)#'?

I'm pretty sure the memo datatype is a good starting point. I really don't think Access would ever let you put more than one value in a datetime field.

Maybe you'll have to use DateFormat(variable, &quot;mm/dd/yy&quot or something to get rid of those single ticks in the timestamp.


John Hoarty
jhoarty@quickestore.com

http://www.quickestore.com

 

[calista]

Still couldn't get it to work. Oh, well, I guess I'll have to store the list as formated dates.

Thanks for your time! Calista :-X
Jedi Knight,
Champion of the Force

 

[strantheman]

hi calista,

Mmm, I guess the first question is do you need these dates stored as dates, or is the list delimited by commas fine with you and your purpose?

If you just want a list, im surprised your list of dates did not work. Just use dateFormat() to set your date mask to mm/dd/yyyy.

If you've got a list of dates you need to format to mm/dd/yyyy try this:

<CFSET newList = &quot;&quot;>
<CFLOOP from=&quot;1&quot; to=&quot;#listLen(TodayIs)#&quot; index=&quot;i&quot;>
<CFSET newdate = dateformat(i,&quot;mm/dd/yyyy&quot>
<CFSET newList = listappend(newlist,newdate)>
</CFLOOP>
<CFSET TodayIs = newList>

Make sure field TimeTodayIs is a memo field, or just long text 255, and #TodayIs# = mm/dd/yyyy,mm/dd/yyyy list of dates in this format.

<!--- Insert data into the database. --->
<CFQUERY NAME=&quot;InsertData&quot;
DATASOURCE=&quot;#Application.Datasource#&quot;
DBTYPE=&quot;ODBC&quot;>
INSERT INTO TimeTable
(
TimePersonID,
TimeWeekending,
TimeTodayIs
)
VALUES
(
'#Cookie.User.ID#',
#Form.Weekending#,
'#TodayIs#'
)
</CFQUERY>

good luck