School Assignment? Project Due Tomorrow? Chat LIVE With A Programming Expert!

Programming Help

Web Development



What's Here?

  • Members: 300,419
  • Replies: 826,027
  • Topics: 137,456
  • Snippets: 4,419
  • Tutorials: 1,148
  • Total Online: 1,500
  • Members: 84
  • Guests: 1,416

Welcome to Dream.In.Code
Become an Expert!

Join 300,419 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,500 people online right now. Registration is fast and FREE... Join Now!




CF SQL TYPES

 

CF SQL TYPES
iamjesus1342027

13 May, 2009 - 11:03 AM
Post #1

New D.I.C Head
*

Joined: 31 Mar, 2009
Posts: 41
i am still relatively new to cold fusion and so i just need some help with the cf sql types. the field report key is a randomly generated unique identifier. if anyone knows what i need to put in for the cfsqltype that will be greatly appreciated! or if you see another problem with my code let me know. thanks!!


This is the error that Im getting for the following code.
Invalid data [B9952407-09F1-7DB4-4BE57A21790A38BC] for CFSQLTYPE CF_SQL_VARCHAR.


CODE

<!--- This code publishes the randomly created Exploitation Draft Reports in the CIDNE database --->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfobject name="CIDNEGeoCoord" component="_components.geocoord">

<!--- Define Vars --->
<cfparam name="session.sysdb" default="CIDNE14">
<cfparam name="session.GEODB" default="postgres">
<cfparam name="url.groupname" default="Admin">
<html>
    <head>
        <title>Publish CIDNE PASS Test Data</title>
    </head>
    <body>
        <cfoutput>
            <cfloop list=& #34;ExploitationBio,ExploitationCache,ExploitationCaseFile,ExploitationChemical,
ExploitationDTK,ExploitationElectronic,ExploitationIDF,ExploitationIED,Exploitat
ionMFTER" index="ListItem" delimiters=",">
                <cfset reportName = #ListItem#>

                <cfquery name="published" datasource="#session.sysdb#">
                    SELECT ReportKey
                    FROM #reportName#PublishedReport
                </cfquery>

                <cfset keys = ArrayNew(1)>
                <cfloop query="published">
                    <cfset temp = ArrayAppend(#keys#,#ReportKey#)>
                </cfloop>

                

                <cfif ArrayToList(#keys#, ",") neq "">
                    <cfquery name="insert" datasource="#session.sysdb#">
                        INSERT INTO #reportName#PublishedReport & #40;ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabi
lityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishL
evel,OriginatorGroup)
                        VALUES ((SELECT & #40;ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabi
lityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishL
evel,OriginatorGroup)))
                        FROM #reportName#DraftReport
                        WHERE DatePosted = '5/11/2009 4:00:00 PM'
                        AND ReportKey NOT IN (<cfqueryparam value="#keys#" list="yes" separator="," cfsqltype="cf_sql_varchar">)
                    </cfquery>
                </cfif>
            </cfloop>    
        </cfoutput>
    </body>
</html>
User is offlineProfile CardPM
+Quote Post


iamjesus1342027

RE: CF SQL TYPES

13 May, 2009 - 11:16 AM
Post #2

New D.I.C Head
*

Joined: 31 Mar, 2009
Posts: 41
also when i try this code i receive this error.
[Macromedia][SQLServer JDBC Driver][SQLServer]Subqueries are not allowed in this context. Only scalar expressions are allowed.

in this code i set the array keys to a list before the if statement. then i use the list as the variable in the if statement and in the query insert.




CODE

<!--- This code publishes the randomly created Exploitation Draft Reports in the CIDNE database --->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfobject name="CIDNEGeoCoord" component="_components.geocoord">

<!--- Define Vars --->
<cfparam name="session.sysdb" default="CIDNE14">
<cfparam name="session.GEODB" default="postgres">
<cfparam name="url.groupname" default="Admin">
<html>
    <head>
        <title>Publish CIDNE PASS Test Data</title>
    </head>
    <body>
        <cfoutput>
            <cfloop list=& #34;ExploitationBio,ExploitationCache,ExploitationCaseFile,ExploitationChemical,
ExploitationDTK,ExploitationElectronic,ExploitationIDF,ExploitationIED,Exploitat
ionMFTER" index="ListItem" delimiters=",">
                <cfset reportName = #ListItem#>

                <cfquery name="published" datasource="#session.sysdb#">
                    SELECT ReportKey
                    FROM #reportName#PublishedReport
                </cfquery>

                <cfset keys = ArrayNew(1)>
                <cfloop query="published">
                    <cfset temp = ArrayAppend(#keys#,#ReportKey#)>
                </cfloop>
                <cfset List = ArrayToList(#keys#, ",")>
                

                <cfif #List# neq "">
                    <cfquery name="insert" datasource="#session.sysdb#">
                        INSERT INTO #reportName#PublishedReport & #40;ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabi
lityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishL
evel,OriginatorGroup)
                        VALUES ((SELECT & #40;ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabi
lityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishL
evel,OriginatorGroup)))
                        FROM #reportName#DraftReport
                        WHERE DatePosted = '5/11/2009 4:00:00 PM'
                        AND ReportKey NOT IN (<cfqueryparam value="#List#" list="yes" separator="," cfsqltype="cf_sql_varchar">)
                    </cfquery>
                </cfif>
            </cfloop>    
        </cfoutput>
    </body>
</html>
User is offlineProfile CardPM
+Quote Post

cfrobuster

RE: CF SQL TYPES

14 May, 2009 - 05:49 AM
Post #3

New D.I.C Head
*

Joined: 14 May, 2009
Posts: 1
I can help you with your problem. Please get a hold of me.



saundersje@gmail.com

QUOTE(iamjesus1342027 @ 13 May, 2009 - 11:03 AM) *

i am still relatively new to cold fusion and so i just need some help with the cf sql types. the field report key is a randomly generated unique identifier. if anyone knows what i need to put in for the cfsqltype that will be greatly appreciated! or if you see another problem with my code let me know. thanks!!


This is the error that Im getting for the following code.
Invalid data [B9952407-09F1-7DB4-4BE57A21790A38BC] for CFSQLTYPE CF_SQL_VARCHAR.


CODE

<!--- This code publishes the randomly created Exploitation Draft Reports in the CIDNE database --->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfobject name="CIDNEGeoCoord" component="_components.geocoord">

<!--- Define Vars --->
<cfparam name="session.sysdb" default="CIDNE14">
<cfparam name="session.GEODB" default="postgres">
<cfparam name="url.groupname" default="Admin">
<html>
    <head>
        <title>Publish CIDNE PASS Test Data</title>
    </head>
    <body>
        <cfoutput>
            <cfloop list=& #34;ExploitationBio,ExploitationCache,ExploitationCaseFile,ExploitationChemical,
ExploitationDTK,ExploitationElectronic,ExploitationIDF,ExploitationIED,Exploitat
ionMFTER" index="ListItem" delimiters=",">
                <cfset reportName = #ListItem#>

                <cfquery name="published" datasource="#session.sysdb#">
                    SELECT ReportKey
                    FROM #reportName#PublishedReport
                </cfquery>

                <cfset keys = ArrayNew(1)>
                <cfloop query="published">
                    <cfset temp = ArrayAppend(#keys#,#ReportKey#)>
                </cfloop>

                

                <cfif ArrayToList(#keys#, ",") neq "">
                    <cfquery name="insert" datasource="#session.sysdb#">
                        INSERT INTO #reportName#PublishedReport & #40;ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabi
lityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishL
evel,OriginatorGroup)
                        VALUES ((SELECT & #40;ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabi
lityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishL
evel,OriginatorGroup)))
                        FROM #reportName#DraftReport
                        WHERE DatePosted = '5/11/2009 4:00:00 PM'
                        AND ReportKey NOT IN (<cfqueryparam value="#keys#" list="yes" separator="," cfsqltype="cf_sql_varchar">)
                    </cfquery>
                </cfif>
            </cfloop>    
        </cfoutput>
    </body>
</html>
User is offlineProfile CardPM
+Quote Post

iamjesus1342027

RE: CF SQL TYPES

14 May, 2009 - 05:59 AM
Post #4

New D.I.C Head
*

Joined: 31 Mar, 2009
Posts: 41
cfrobuster thanks for the response but i actually figured it out and it had nothing to do with the sql type. the problem was with the published query. i added a where clause and that fixed the problem. but thanks for your reply.



QUOTE(cfrobuster @ 14 May, 2009 - 05:49 AM) *

I can help you with your problem. Please get a hold of me.



saundersje@gmail.com

QUOTE(iamjesus1342027 @ 13 May, 2009 - 11:03 AM) *

i am still relatively new to cold fusion and so i just need some help with the cf sql types. the field report key is a randomly generated unique identifier. if anyone knows what i need to put in for the cfsqltype that will be greatly appreciated! or if you see another problem with my code let me know. thanks!!


This is the error that Im getting for the following code.
Invalid data [B9952407-09F1-7DB4-4BE57A21790A38BC] for CFSQLTYPE CF_SQL_VARCHAR.


CODE

<!--- This code publishes the randomly created Exploitation Draft Reports in the CIDNE database --->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<cfobject name="CIDNEGeoCoord" component="_components.geocoord">

<!--- Define Vars --->
<cfparam name="session.sysdb" default="CIDNE14">
<cfparam name="session.GEODB" default="postgres">
<cfparam name="url.groupname" default="Admin">
<html>
    <head>
        <title>Publish CIDNE PASS Test Data</title>
    </head>
    <body>
        <cfoutput>
            <cfloop list=& #34;ExploitationBio,ExploitationCache,ExploitationCaseFile,ExploitationChemical,
ExploitationDTK,ExploitationElectronic,ExploitationIDF,ExploitationIED,Exploitat
ionMFTER" index="ListItem" delimiters=",">
                <cfset reportName = #ListItem#>

                <cfquery name="published" datasource="#session.sysdb#">
                    SELECT ReportKey
                    FROM #reportName#PublishedReport
                </cfquery>

                <cfset keys = ArrayNew(1)>
                <cfloop query="published">
                    <cfset temp = ArrayAppend(#keys#,#ReportKey#)>
                </cfloop>

                

                <cfif ArrayToList(#keys#, ",") neq "">
                    <cfquery name="insert" datasource="#session.sysdb#">
                        INSERT INTO #reportName#PublishedReport & #40;ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabi
lityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishL
evel,OriginatorGroup)
                        VALUES ((SELECT & #40;ReportKey,UpdatedByName,UpdatedByUnit,Classification,ClassificationReleasabi
lityMark,GMTDelta,OriginatorName,OriginatorUnit,DatePosted,isDeprecated,PublishL
evel,OriginatorGroup)))
                        FROM #reportName#DraftReport
                        WHERE DatePosted = '5/11/2009 4:00:00 PM'
                        AND ReportKey NOT IN (<cfqueryparam value="#keys#" list="yes" separator="," cfsqltype="cf_sql_varchar">)
                    </cfquery>
                </cfif>
            </cfloop>    
        </cfoutput>
    </body>
</html>
User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic

Time is now: 11/7/09 11:56PM
Invision Power Board v2.1.7 © 2009  IPS, Inc.
Licensed to: MediaGroup1

Live Help!

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter Fan Us On Facebook

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month