Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1005
  • Last Modified:

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

I have a table 'A' in session.datasource1 and need to append the records to table 'A' in session.datasource2.
Table structures are identical.  I have query "getA1Recs".  How do I loop thru the query results from "getA1Recs" and append them to table 'A' in session.datasource2?

ptslv  
0
ptslv
Asked:
ptslv
  • 10
  • 7
  • 3
  • +2
1 Solution
 
kkhippleCommented:
what kind of datasource is it that you are storing your records in?
0
 
ptslvAuthor Commented:
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.
0
 
kkhippleCommented:
if you want to append this ot the backend db

then loop thru the query and with each iteraction insert into the table

<CFQUERY DATASOURCE="#SESSION.datasource1#" NAME="queryOne">
    SELECT col1, col2, col3
    FROM table1
</CFQUERY>

<CFLOOP QUERY="queryOne">

    <!--- insert data --->
   <CFQUERY DATASOURCE="#SESSION.datasource2#" NAME="queryTwo">
       INSERT INTO table2 ('#col1#', '#col2#', '#col3#')
   </CFQUERY>

</CFLOOP>
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
ptslvAuthor Commented:
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.
0
 
kkhippleCommented:
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
0
 
ptslvAuthor Commented:
I did that.  That didn't work either
0
 
kkhippleCommented:
could you paste your code and the error pls
0
 
ptslvAuthor Commented:
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).
0
 
kkhippleCommented:
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>
0
 
ptslvAuthor Commented:
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.  :-)
0
 
ptslvAuthor Commented:
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).

0
 
kkhippleCommented:
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>
0
 
ptslvAuthor Commented:
are to_char, to_date and CFDump in ColdFusion 5.0?  I'm using 4.5 and they give me errors.
0
 
Tacobell777Commented:
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>
0
 
Seth_BienekCommented:
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
0
 
ptslvAuthor Commented:
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
0
 
Seth_BienekCommented:
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
0
 
Tacobell777Commented:
ptslv did you have a look at my suggestions, or was it a waste of my time?
0
 
DA_JEDCommented:
I think TacoBell and Seth are on the right track ptslv, let use know how it turns out

DAJED
0
 
ptslvAuthor Commented:
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
0
 
Tacobell777Commented:
all right, but we could have come up with another solution, looping and inserting is bad...

good luck..
0
 
ptslvAuthor Commented:
I agree.  But I'm in a time crunch to demo.  I'll have time to fine-tune.  Thanks again.
0
 
kkhippleCommented:
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
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 10
  • 7
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now