Re: Reading database and inserting fields into another database
cfset commalist = valuelist(GetAlias.env_var)
cfquery name=Insert datasource=data2 dbtype=ODBC
INSERT into profs
(strField1)
VALUES
('#commalist#')
WHERE strUsername=cfqueryparam value=#auth#
cfsqltype=CF_SQL_VARCHAR
/cfquery
My apologies for the late response to this thread, but I couldn't
help noticing the WHERE clause in this INSERT statement. If there
is already a value in strUsername, then an UPDATE statement
should be used rather than an INSERT.
~Dina
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
Reading database and inserting fields into another database
HI, I need to read in the field env_var from one database (data1) and insert the results into another database as a comma-delimited text. cfquery name=GetAlias datasource=data1 dbtype=ODBC SELECT env_var FROMdata1 /cfquery Output example: file1 file2 file3 file4 file5 etc... Insert results of GetAlias.env_var into database (data2) into field strVar as a comma-delimited field. strVar = file1,file2,file3,file4,file5,etc... Can I do this with just --- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba --- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: Reading database and inserting fields into another database
cfquery name=GetAlias datasource=data1 dbtype=ODBC SELECT env_var FROMdata1 /cfquery cfquery name=GetAlias datasource=data2 dbtype=ODBC INSERT INTO DATABASE2TABLENAME ( STRVAR ) VALUES ( '#GetAlias.env_var#' ) /cfquery Bryan F. Hogan Director of Internet Development Macromedia Certified ColdFusion MX Developer Digital Bay Media, Inc. 1-877-72DIGITAL -Original Message- From: FlashGuy [mailto:flashmx;rogers.com] Sent: Tuesday, October 29, 2002 12:13 PM To: CF-Talk Subject: Reading database and inserting fields into another database HI, I need to read in the field env_var from one database (data1) and insert the results into another database as a comma-delimited text. cfquery name=GetAlias datasource=data1 dbtype=ODBC SELECT env_var FROMdata1 /cfquery Output example: file1 file2 file3 file4 file5 etc... Insert results of GetAlias.env_var into database (data2) into field strVar as a comma-delimited field. strVar = file1,file2,file3,file4,file5,etc... Can I do this with just --- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba --- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Reading database and inserting fields into another database
use cfset strVar = Valuelist(GetAlias.env_var) to create a comma delimited list. -Original Message- From: FlashGuy [mailto:flashmx;rogers.com] Sent: 29 October 2002 17:13 To: CF-Talk Subject: Reading database and inserting fields into another database HI, I need to read in the field env_var from one database (data1) and insert the results into another database as a comma-delimited text. cfquery name=GetAlias datasource=data1 dbtype=ODBC SELECT env_var FROMdata1 /cfquery Output example: file1 file2 file3 file4 file5 etc... Insert results of GetAlias.env_var into database (data2) into field strVar as a comma-delimited field. strVar = file1,file2,file3,file4,file5,etc... Can I do this with just --- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba --- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: Reading database and inserting fields into another database
naming the queries the same is liable to cause problems... I'd also recommend using cfqueryparam cfsqltype=cf_sql_varchar value=#ValueList(GetAlias.env_var)# -- use cf_sql_longvarchar if the values of the files may be more than about 8000 characters. cfquery name=GetAlias datasource=data1 dbtype=ODBC SELECT env_var FROMdata1 /cfquery cfquery name=GetAlias datasource=data2 dbtype=ODBC INSERT INTO DATABASE2TABLENAME ( STRVAR ) VALUES ( '#GetAlias.env_var#' ) /cfquery Bryan F. Hogan Director of Internet Development Macromedia Certified ColdFusion MX Developer Digital Bay Media, Inc. 1-877-72DIGITAL -Original Message- From: FlashGuy [mailto:flashmx;rogers.com] Sent: Tuesday, October 29, 2002 12:13 PM To: CF-Talk Subject: Reading database and inserting fields into another database HI, I need to read in the field env_var from one database (data1) and insert the results into another database as a comma-delimited text. cfquery name=GetAlias datasource=data1 dbtype=ODBC SELECT env_var FROMdata1 /cfquery Output example: file1 file2 file3 file4 file5 etc... Insert results of GetAlias.env_var into database (data2) into field strVar as a comma-delimited field. strVar = file1,file2,file3,file4,file5,etc... Can I do this with just --- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba --- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: Reading database and inserting fields into another database
naming the queries the same is liable to cause problems... I'd also recommend using cfqueryparam cfsqltype=cf_sql_varchar value=#ValueList(GetAlias.env_var)# -- use cf_sql_longvarchar if the values of the files may be more than about 8000 characters. True except for valuelist(), query returns comma delimited anyway, why the extra function? Bryan F. Hogan Director of Internet Development Macromedia Certified ColdFusion MX Developer Digital Bay Media, Inc. 1-877-72DIGITAL -Original Message- From: S. Isaac Dealey [mailto:info;turnkey.to] Sent: Tuesday, October 29, 2002 12:32 PM To: CF-Talk Subject: RE: Reading database and inserting fields into another database naming the queries the same is liable to cause problems... I'd also recommend using cfqueryparam cfsqltype=cf_sql_varchar value=#ValueList(GetAlias.env_var)# -- use cf_sql_longvarchar if the values of the files may be more than about 8000 characters. cfquery name=GetAlias datasource=data1 dbtype=ODBC SELECT env_var FROMdata1 /cfquery cfquery name=GetAlias datasource=data2 dbtype=ODBC INSERT INTO DATABASE2TABLENAME ( STRVAR ) VALUES ( '#GetAlias.env_var#' ) /cfquery Bryan F. Hogan Director of Internet Development Macromedia Certified ColdFusion MX Developer Digital Bay Media, Inc. 1-877-72DIGITAL -Original Message- From: FlashGuy [mailto:flashmx;rogers.com] Sent: Tuesday, October 29, 2002 12:13 PM To: CF-Talk Subject: Reading database and inserting fields into another database HI, I need to read in the field env_var from one database (data1) and insert the results into another database as a comma-delimited text. cfquery name=GetAlias datasource=data1 dbtype=ODBC SELECT env_var FROMdata1 /cfquery Output example: file1 file2 file3 file4 file5 etc... Insert results of GetAlias.env_var into database (data2) into field strVar as a comma-delimited field. strVar = file1,file2,file3,file4,file5,etc... Can I do this with just --- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba --- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: Reading database and inserting fields into another database
True except for valuelist(), query returns comma delimited anyway, why the extra function? If that's the case : INSERT INTO data2 (strVal) SELECT env_var FROM data1 will do the trick. Stephen ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: Reading database and inserting fields into another database
Forgot to mention that the update of the field in database (data2) has to be inserted
into the proper field based on username which I'm saving to the auth varaible.
The INSERT is not working properly. I'm sure of the proper syntax of this line #auth#
= 'strUsername'
If the user is Bill which is a field (strUsername) in database (data2) then insert the
results from #commalist# into field strField1
cfquery name=GetAlias datasource=data1 dbtype=ODBC
SELECT env_var
FROMalias
/cfquery
cfset commalist = valuelist(GetAlias.env_var)
cfquery name=Insert datasource=data2 dbtype=ODBC
INSERT into profs
(strField1)
VALUES
('#commalist#')
WHERE #auth# = 'strUsername'
/cfquery
On Tue, 29 Oct 2002 17:24:58 -, Dave Wilson wrote:
use cfset strVar = Valuelist(GetAlias.env_var) to create a comma delimited
list.
-Original Message-
From: FlashGuy [mailto:flashmx;rogers.com]
Sent: 29 October 2002 17:13
To: CF-Talk
Subject: Reading database and inserting fields into another database
HI,
I need to read in the field env_var from one database (data1) and insert
the results into another database as a comma-delimited text.
cfquery name=GetAlias datasource=data1 dbtype=ODBC
SELECT env_var
FROMdata1
/cfquery
Output example:
file1 file2 file3 file4 file5 etc...
Insert results of GetAlias.env_var into database (data2) into field
strVar as a comma-delimited field.
strVar = file1,file2,file3,file4,file5,etc...
Can I do this with just
---
Colonel Nathan R. Jessop
Commanding Officer
Marine Ground Forces
Guatanamo Bay, Cuba
---
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Re: Reading database and inserting fields into another database
You need :
WHERE #auth# = '#strUsername#'
Stephen
- Original Message -
From: FlashGuy [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Tuesday, October 29, 2002 5:46 PM
Subject: RE: Reading database and inserting fields into another database
Forgot to mention that the update of the field in database (data2) has to
be inserted into the proper field based on username which I'm saving to the
auth varaible.
The INSERT is not working properly. I'm sure of the proper syntax of this
line #auth# = 'strUsername'
If the user is Bill which is a field (strUsername) in database (data2)
then insert the results from #commalist# into field strField1
cfquery name=GetAlias datasource=data1 dbtype=ODBC
SELECT env_var
FROMalias
/cfquery
cfset commalist = valuelist(GetAlias.env_var)
cfquery name=Insert datasource=data2 dbtype=ODBC
INSERT into profs
(strField1)
VALUES
('#commalist#')
WHERE #auth# = 'strUsername'
/cfquery
On Tue, 29 Oct 2002 17:24:58 -, Dave Wilson wrote:
use cfset strVar = Valuelist(GetAlias.env_var) to create a comma
delimited
list.
-Original Message-
From: FlashGuy [mailto:flashmx;rogers.com]
Sent: 29 October 2002 17:13
To: CF-Talk
Subject: Reading database and inserting fields into another database
HI,
I need to read in the field env_var from one database (data1) and
insert
the results into another database as a comma-delimited text.
cfquery name=GetAlias datasource=data1 dbtype=ODBC
SELECT env_var
FROMdata1
/cfquery
Output example:
file1 file2 file3 file4 file5 etc...
Insert results of GetAlias.env_var into database (data2) into field
strVar as a comma-delimited field.
strVar = file1,file2,file3,file4,file5,etc...
Can I do this with just
---
Colonel Nathan R. Jessop
Commanding Officer
Marine Ground Forces
Guatanamo Bay, Cuba
---
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
RE: Reading database and inserting fields into another database
The INSERT is not working properly. I'm sure of the proper syntax of this
line #auth# = 'strUsername'
This is saying that you have a column in your database named Bill that has
a value of strUsername. I don't think that is what you are looking for.
Turn them around and say:
where strUsername='#Auth#'
Also as S. Isaac Dealey stated don't forget to use cfqueryparam
cfquery name=GetAlias datasource=data1 dbtype=ODBC
SELECT env_var
FROMalias
/cfquery
cfset commalist = valuelist(GetAlias.env_var)
cfquery name=Insert datasource=data2 dbtype=ODBC
INSERT into profs
(strField1)
VALUES
('#commalist#')
WHERE strUsername=cfqueryparam value=#auth#
cfsqltype=CF_SQL_VARCHAR
/cfquery
Note IMO don't set your list with valuelist() your select statement returns
in a comma delimited list anyways so there is no need to redo something that
is already done.
Bryan F. Hogan
Director of Internet Development
Macromedia Certified ColdFusion MX Developer
Digital Bay Media, Inc.
1-877-72DIGITAL
-Original Message-
From: FlashGuy [mailto:flashmx;rogers.com]
Sent: Tuesday, October 29, 2002 12:47 PM
To: CF-Talk
Subject: RE: Reading database and inserting fields into another database
Forgot to mention that the update of the field in database (data2) has to be
inserted into the proper field based on username which I'm saving to the
auth varaible.
The INSERT is not working properly. I'm sure of the proper syntax of this
line #auth# = 'strUsername'
If the user is Bill which is a field (strUsername) in database (data2) then
insert the results from #commalist# into field strField1
cfquery name=GetAlias datasource=data1 dbtype=ODBC
SELECT env_var
FROMalias
/cfquery
cfset commalist = valuelist(GetAlias.env_var)
cfquery name=Insert datasource=data2 dbtype=ODBC
INSERT into profs
(strField1)
VALUES
('#commalist#')
WHERE #auth# = 'strUsername'
/cfquery
On Tue, 29 Oct 2002 17:24:58 -, Dave Wilson wrote:
use cfset strVar = Valuelist(GetAlias.env_var) to create a comma
delimited
list.
-Original Message-
From: FlashGuy [mailto:flashmx;rogers.com]
Sent: 29 October 2002 17:13
To: CF-Talk
Subject: Reading database and inserting fields into another database
HI,
I need to read in the field env_var from one database (data1) and
insert
the results into another database as a comma-delimited text.
cfquery name=GetAlias datasource=data1 dbtype=ODBC
SELECT env_var
FROMdata1
/cfquery
Output example:
file1 file2 file3 file4 file5 etc...
Insert results of GetAlias.env_var into database (data2) into field
strVar as a comma-delimited field.
strVar = file1,file2,file3,file4,file5,etc...
Can I do this with just
---
Colonel Nathan R. Jessop
Commanding Officer
Marine Ground Forces
Guatanamo Bay, Cuba
---
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
RE: Reading database and inserting fields into another database
He is looking to input data from one database into another database not one table into another table. Bryan F. Hogan Director of Internet Development Macromedia Certified ColdFusion MX Developer Digital Bay Media, Inc. 1-877-72DIGITAL -Original Message- From: Stephen Moretti [mailto:stephen;cfmaster.co.uk] Sent: Tuesday, October 29, 2002 12:51 PM To: CF-Talk Subject: Re: Reading database and inserting fields into another database True except for valuelist(), query returns comma delimited anyway, why the extra function? If that's the case : INSERT INTO data2 (strVal) SELECT env_var FROM data1 will do the trick. Stephen ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: Reading database and inserting fields into another database
naming the queries the same is liable to cause problems... I'd also recommend using cfqueryparam cfsqltype=cf_sql_varchar value=#ValueList(GetAlias.env_var)# -- use cf_sql_longvarchar if the values of the files may be more than about 8000 characters. True except for valuelist(), query returns comma delimited anyway, why the extra function? Because if you just use #GetAlias.env_var# you'll only return one row ( the first if you're not inside a cfloop or cfoutput of the query ) of the query and not a comma delimited list of the values in that column. Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
RE: Reading database and inserting fields into another database
Correct. On Tue, 29 Oct 2002 13:04:15 -0500, Bryan F. Hogan wrote: He is looking to input data from one database into another database not one table into another table. Bryan F. Hogan Director of Internet Development Macromedia Certified ColdFusion MX Developer Digital Bay Media, Inc. 1-877-72DIGITAL -Original Message- From: Stephen Moretti [mailto:stephen;cfmaster.co.uk] Sent: Tuesday, October 29, 2002 12:51 PM To: CF-Talk Subject: Re: Reading database and inserting fields into another database True except for valuelist(), query returns comma delimited anyway, why the extra function? If that's the case : INSERT INTO data2 (strVal) SELECT env_var FROM data1 will do the trick. Stephen ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Reading database and inserting fields into another database
I stand corrected :) Bryan F. Hogan Director of Internet Development Macromedia Certified ColdFusion MX Developer Digital Bay Media, Inc. 1-877-72DIGITAL -Original Message- From: S. Isaac Dealey [mailto:info;turnkey.to] Sent: Tuesday, October 29, 2002 1:23 PM To: CF-Talk Subject: RE: Reading database and inserting fields into another database naming the queries the same is liable to cause problems... I'd also recommend using cfqueryparam cfsqltype=cf_sql_varchar value=#ValueList(GetAlias.env_var)# -- use cf_sql_longvarchar if the values of the files may be more than about 8000 characters. True except for valuelist(), query returns comma delimited anyway, why the extra function? Because if you just use #GetAlias.env_var# you'll only return one row ( the first if you're not inside a cfloop or cfoutput of the query ) of the query and not a comma delimited list of the values in that column. Isaac Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
