ColdFusion 4.5 - How to loop thru query results and append to another table

what kind of datasource is it that you are storing your records in?

both are MS Access databases.  The first is from data that is uploaded to the server.  The process is to upload the data, and append the records to the master database.

That works great.  Only problem I'm having is getting the Date field moved over.  I know it is a reserved word (yes, they used reserved words as field names), but I tried it using an alias and that didn't work.  Is there a way to pull in the Date field?  Also, I can't pull in DateStamp_Submit field.  I tried using '#evaluate(#DateStamp_Submit#)#' but got an error on that, too.

even tho using reserved words as field names is very bad....

to make it work in your sql statemtn is to surrond the field name with [ ]  brackets

so something like this should work:


SELECT col1, col2, [date]
FROM table1

I did that.  That didn't work either

could you paste your code and the error pls

The 'Date' field type is Date/Time with default in the database of Now()

Code:
<cfquery name="getdataA" datasource="#session.datasource2#">
      Select AutoNumID, DATESTAMP_Submit, Test, [Date] as PDate
FROM tbl_A
</cfquery>

<cfloop query="getDDPers">
      
      <cfquery name="insertdata" datasource="#session.datasource1#">
            Insert into tbl_A(AutoNumID,DATESTAMP_Submit,Test, [Date])
            Values (#AutoNumID#, '#DATESTAMP_Submit#', '#Test#', '#PDate#')
            
            </cfquery>
      </cfloop>


Error:

ODBC Error Code = 22005 (Error in assignment)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (18:3) to (18:64).

try taking away the column names like so....

<cfloop query="getDDPers">
     
     <cfquery name="insertdata" datasource="#session.datasource1#">
          Insert into tbl_A
          Values (#AutoNumID#, '#DATESTAMP_Submit#', '#Test#', '#PDate#')
         
          </cfquery>
     </cfloop>

I've gone thru removing all the fields and adding them one at a time.  The only field that throws the error is the Date field;

ODBC Error Code = 22005 (Error in assignment)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

The DateStamp_Submit field was the same until I changed it by adding '_Submit' to the end of the name.  But I can't change the field names in the databases, so I am trying to use the alias.

If you can help me figure it out, I will add 50 points.  :-)

when I changed '#PDate#' to '#evaluate(PDate)#', I got this error:

An error occurred while evaluating the expression:
#evaluate(PDate)#
Error near line 20, column 26.
--------------------------------------------------------------------------------

An error has occurred while processing the expression:

   2004-11-09 08:44:12

Invalid parser construct found on line 1 at position 12. ColdFusion was looking at the following text:

08
Invalid expression format. The usual cause is an error in the expression structure.

The error occurred while processing an element with a general identifier of (#evaluate(PDate)#), occupying document position (20:25) to (20:41).

oh okay... one thing you can do first is to dump the contents of your query...

<CFOUTPUT>
   <CFDUMP var = #getDDPers#>
</CFOUTPUT>

this will allow you to see what you are getting...

another thing.. if the date column is a date data type.... ie, it doesn't hold any other then dates... ie if you tried to enter 'some sort of text'  then it would give you an error

note:  in the cmd line for the db that you are using.. u can type in "DESC table_name"  to find out the structure

if thats' the case (ur Date table column is defined as a date date type) then when you are inserting the date then you might need to make sure its in the right format.. see if this owrks


<cfquery name="getdataA" datasource="#session.datasource2#">
     Select AutoNumID, DATESTAMP_Submit, Test, to_char ([Date], 'mm/DD/yyyy hh24:MI') as PDate
FROM tbl_A
</cfquery>

<cfloop query="getDDPers">
     
     <cfquery name="insertdata" datasource="#session.datasource1#">
          Insert into tbl_A(AutoNumID,DATESTAMP_Submit,Test, [Date])
          Values (#AutoNumID#, '#DATESTAMP_Submit#', '#Test#', to_date('#PDate#','YYYY-DD-MM HH24:MI:SS'))
         
          </cfquery>
     </cfloop>

are to_char, to_date and CFDump in ColdFusion 5.0?  I'm using 4.5 and they give me errors.

you would not use a cfloop!

You would use either

INSERT INTO tableName1
SELECT     *
FROM         tableName2

or you would do a select and then a Query of Query if the tbales were in different database

<cfquery name="tableName2".......>
SELECT *
FROM tableName1
</cfquery>

<cfquery dbtype="query" .....>
INSERT INTO tableName1
SELECT     *
FROM         tableName2
</cfquery>

Hi ptslv,

to_date is a server-side function, and may or may not work for you, depending on your database platform.

ditto for to_char.

cfdump and query-of-a-query will not work on CF 4.5.

Use CF's createodbcdatetime() function to insert a date into your table.  This will work across db platforms:

     <cfquery name="insertdata" datasource="#session.datasource1#">
          Insert into tbl_A(AutoNumID,DATESTAMP_Submit,Test, [Date])
          Values (#AutoNumID#, '#DATESTAMP_Submit#', '#Test#', #createodbcdatetime(PDate)#)          
        </cfquery>

Hope this helps.

Regards,

Seth Bienek

Seth,

I tried #createodbcdatetime(PDate)#  and got the following error:

ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''{ts '2004-11-09 08:44:12'}''.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (19:3) to (19:64).

My Code:

<cfquery name="insertPers" datasource="#session.datasource1#">
      Insert into tbl_Personnel(AutoNumID,DATESTAMP)
      Values (#AutoNumID#, '#createodbcdatetime(DATESTAMP)#')          
</cfquery>

ptslv

Hey ptslv,

You don't need quotes when using createodbcdatetime()..

Try this:
<cfquery name="insertPers" datasource="#session.datasource1#">
      Insert into tbl_Personnel(AutoNumID,DATESTAMP)
      Values (#AutoNumID#, #createodbcdatetime(DATESTAMP)#)          
</cfquery>

Regards,

Seth

ptslv did you have a look at my suggestions, or was it a waste of my time?

I think TacoBell and Seth are on the right track ptslv, let use know how it turns out

DAJED

Tacobell,

I looked at your comments, but as I am developing with 4.5, I can't use the query of query.  And I am using 2 different database connections.  Thanks for the info, tho.  When I get a chance, I will test it off the actual server, which is running 5.0.  Go figure.

Seth,

I was still getting errors without the quotes.  So I rechecked the data and found it was dropping out when it hit empty data in the date field.  And since it is auto filled with Now() whenthe user inputs the data, I just put dates in the dummy data where there were none and ran the query without using 'createodbcdatetime()' and had no problems!

Thanks for all the help, tho.  I appreciate it!

ptslv

all right, but we could have come up with another solution, looping and inserting is bad...

good luck..

I agree.  But I'm in a time crunch to demo.  I'll have time to fine-tune.  Thanks again.

I'm glad I could help out.
And guys I know my programming skills are not at par with you guys.. but I'm laerning
and I'm on here to help as many ppl as I can

cheers,
KDK