RE: [cftalk] RE: Importing CSV into Database - Update
That would be dependent on locale/ODBC settings. In locales where comma is a decimal separator, semi-colon would be used as a field delimiter instead (in that case, you might have commas in the fields, if there are figures with decimals). /Hugo - Hugo Ahlenius E-Mail: [EMAIL PROTECTED] Project Officer Phone:+46 8 230460 UNEP GRID-Arendal Fax: +46 8 230441 Stockholm Office Mobile: +46 733 467111 WWW: http://www.grida.no - | -Original Message- | From: CFUG Spain [mailto:[EMAIL PROTECTED] | Sent: Friday, February 13, 2004 08:46 | To: CF-Talk | Subject: Re: [cftalk] RE: Importing CSV into Database - Update | | A problem I had with importing CSV at least CSV created by | excel is that the files it creates are not always comma | delimited, they are sometimes semicolon delimited. | | So your code that looks for the second value in a comma | delimited list would never get found as it is in fact | delimited by semicolons. | | Try a ListLen with commas and if it is only 1 try a ListLen | with semicolon (;) and if you get 12 then change the delimiters. | | Hope this may help. | | Allan Cliff | - Original Message - | From: Pascal Peters | To: CF-Talk | Sent: Friday, February 13, 2004 8:32 AM | Subject: [cftalk] RE: Importing CSV into Database - Update | | | It looks like you have an incorrect (maybe empty) line you try to | import. Check for ListLen() of your line and if it isn't 21 | you have a | problem. Maybe you can ignore the import then and write an error | message. | | As you use chr(13) as a dilimiter, I assume you are on windows. Try | using chr(13)chr(10) as list delimiter, it may solve your problem. | | As a side note, can read a csv file and convert it into a | query. It could make your life easy. There are also some custom tags | that do it if I remember correctly. | | Pascal | | > -Original Message- | > From: Jillian Carroll [mailto:[EMAIL PROTECTED] | > Sent: donderdag 12 februari 2004 19:04 | > To: CF-Talk | > Subject: RE: Importing CSV into Database - Update | > | > I've played with this a little more. The error I am now | > getting (after removing the cftry so I could see) is this: | > | > *** *** *** | > | > Error Diagnostic Information | > An error occurred while evaluating the _expression_: | > #ListGetAt(l, 2)# | > | > Error near line 56, column 8. | > | > In function ListGetAt(list, index [, delimiters]) the value | > of index, which is 2, is not a valid index for the list given | > as a the first argument (this list has 1 elements). Valid | > indexes are in the range 1 through the number of elements in | > the list The error occurred while processing an element with | > a general identifier of (#ListGetAt(l, 2)#), occupying | > document position (56:7) to (56:23). | > | > *** *** *** | > | > Where should I go from here? Should I be taking a | different approach? | > | > -- | > Jillian | > | > -Original Message- | > From: Jillian Carroll [mailto:[EMAIL PROTECTED] | > Sent: February 12, 2004 10:57 AM | > To: CF-Talk | > Subject: Importing CSV into Database | > | > | > Good morning, | > | > I am having a heck of a time with this little import script. | > When it runs, | > all I receive are the messages that 'An error occurred while | > updating the | > database.' (as I indicated it should do if the query fails) | > | > Can anybody point me in the right direction? | > | > Thanks!! | > | > -- | > Jillian | > | > *** *** *** | > | > | > | > variable="csv_import"> | > | > | > | > | > | > | > | > | > | > | > | > | > | > INSERT INTO person_temp ( | > person_id, | > salutation_id, | > first_name, | > last_name, | > title, | > qualification, | > organization_id, | > department_id, | > office_id, | > status, | > role_id, | > org_position, | > national_board, | > userid, | > password, | > | > work_phone, | > work_phone_ext, | > fax_phone, | > cell_phone, | > pager_phone, | > email_wireless, | > lung_family, | > email | > ) | > VALUES ( | > | > nextval('"person_person_id_seq"'::text), | > #ListGetAt(l, 1)#, | > '#ListGetAt(l, 2)#', | > '#ListGetAt(l, 3)#', | > #ListGetAt(l, 4)#, | > '#
Re: [cftalk] RE: Importing CSV into Database - Update
A problem I had with importing CSV at least CSV created by excel is that the files it creates are not always comma delimited, they are sometimes semicolon delimited. So your code that looks for the second value in a comma delimited list would never get found as it is in fact delimited by semicolons. Try a ListLen with commas and if it is only 1 try a ListLen with semicolon (;) and if you get 12 then change the delimiters. Hope this may help. Allan Cliff - Original Message - From: Pascal Peters To: CF-Talk Sent: Friday, February 13, 2004 8:32 AM Subject: [cftalk] RE: Importing CSV into Database - Update It looks like you have an incorrect (maybe empty) line you try to import. Check for ListLen() of your line and if it isn't 21 you have a problem. Maybe you can ignore the import then and write an error message. As you use chr(13) as a dilimiter, I assume you are on windows. Try using chr(13)chr(10) as list delimiter, it may solve your problem. As a side note, can read a csv file and convert it into a query. It could make your life easy. There are also some custom tags that do it if I remember correctly. Pascal > -Original Message- > From: Jillian Carroll [mailto:[EMAIL PROTECTED] > Sent: donderdag 12 februari 2004 19:04 > To: CF-Talk > Subject: RE: Importing CSV into Database - Update > > I've played with this a little more. The error I am now > getting (after removing the cftry so I could see) is this: > > *** *** *** > > Error Diagnostic Information > An error occurred while evaluating the _expression_: > #ListGetAt(l, 2)# > > Error near line 56, column 8. > > In function ListGetAt(list, index [, delimiters]) the value > of index, which is 2, is not a valid index for the list given > as a the first argument (this list has 1 elements). Valid > indexes are in the range 1 through the number of elements in > the list The error occurred while processing an element with > a general identifier of (#ListGetAt(l, 2)#), occupying > document position (56:7) to (56:23). > > *** *** *** > > Where should I go from here? Should I be taking a different approach? > > -- > Jillian > > -Original Message- > From: Jillian Carroll [mailto:[EMAIL PROTECTED] > Sent: February 12, 2004 10:57 AM > To: CF-Talk > Subject: Importing CSV into Database > > > Good morning, > > I am having a heck of a time with this little import script. > When it runs, > all I receive are the messages that 'An error occurred while > updating the > database.' (as I indicated it should do if the query fails) > > Can anybody point me in the right direction? > > Thanks!! > > -- > Jillian > > *** *** *** > > > > variable="csv_import"> > > > > > > > > > > > > > > INSERT INTO person_temp ( > person_id, > salutation_id, > first_name, > last_name, > title, > qualification, > organization_id, > department_id, > office_id, > status, > role_id, > org_position, > national_board, > userid, > password, > > work_phone, > work_phone_ext, > fax_phone, > cell_phone, > pager_phone, > email_wireless, > lung_family, > email > ) > VALUES ( > > nextval('"person_person_id_seq"'::text), > #ListGetAt(l, 1)#, > '#ListGetAt(l, 2)#', > '#ListGetAt(l, 3)#', > #ListGetAt(l, 4)#, > '#ListGetAt(l, 5)#', > #ListGetAt(l, 6)#, > #ListGetAt(l, 7)#, > #ListGetAt(l, 8)#, > '#ListGetAt(l, 9)#', > #ListGetAt(l, 10)#, > #ListGetAt(l, 11)#, > #ListGetAt(l, 12)#, > > '#ListGetAt(l, 13)#', > '#ListGetAt(l, 14)#', > > '#ListGetAt(l, 15)#', > '#ListGetAt(l, 16)#', > '#ListGetAt(l, 17)#', > '#ListGetAt(l, 18)#', > '#ListGetAt(l, 19)#', > > '#ListGetAt(l, 20)#', > '1', > '#ListGetAt(l, 21)#', > ) > > > > > Database update completed. > > > > > An error occured while updating the > database. > > > > > > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Importing CSV into Database - Update
It looks like you have an incorrect (maybe empty) line you try to import. Check for ListLen() of your line and if it isn't 21 you have a problem. Maybe you can ignore the import then and write an error message. As you use chr(13) as a dilimiter, I assume you are on windows. Try using chr(13)chr(10) as list delimiter, it may solve your problem. As a side note, can read a csv file and convert it into a query. It could make your life easy. There are also some custom tags that do it if I remember correctly. Pascal > -Original Message- > From: Jillian Carroll [mailto:[EMAIL PROTECTED] > Sent: donderdag 12 februari 2004 19:04 > To: CF-Talk > Subject: RE: Importing CSV into Database - Update > > I've played with this a little more. The error I am now > getting (after removing the cftry so I could see) is this: > > *** *** *** > > Error Diagnostic Information > An error occurred while evaluating the _expression_: > #ListGetAt(l, 2)# > > Error near line 56, column 8. > > In function ListGetAt(list, index [, delimiters]) the value > of index, which is 2, is not a valid index for the list given > as a the first argument (this list has 1 elements). Valid > indexes are in the range 1 through the number of elements in > the list The error occurred while processing an element with > a general identifier of (#ListGetAt(l, 2)#), occupying > document position (56:7) to (56:23). > > *** *** *** > > Where should I go from here? Should I be taking a different approach? > > -- > Jillian > > -Original Message- > From: Jillian Carroll [mailto:[EMAIL PROTECTED] > Sent: February 12, 2004 10:57 AM > To: CF-Talk > Subject: Importing CSV into Database > > > Good morning, > > I am having a heck of a time with this little import script. > When it runs, > all I receive are the messages that 'An error occurred while > updating the > database.' (as I indicated it should do if the query fails) > > Can anybody point me in the right direction? > > Thanks!! > > -- > Jillian > > *** *** *** > > > > variable="csv_import"> > > > > > > > > > > > > > > INSERT INTO person_temp ( > person_id, > salutation_id, > first_name, > last_name, > title, > qualification, > organization_id, > department_id, > office_id, > status, > role_id, > org_position, > national_board, > userid, > password, > > work_phone, > work_phone_ext, > fax_phone, > cell_phone, > pager_phone, > email_wireless, > lung_family, > email > ) > VALUES ( > > nextval('"person_person_id_seq"'::text), > #ListGetAt(l, 1)#, > '#ListGetAt(l, 2)#', > '#ListGetAt(l, 3)#', > #ListGetAt(l, 4)#, > '#ListGetAt(l, 5)#', > #ListGetAt(l, 6)#, > #ListGetAt(l, 7)#, > #ListGetAt(l, 8)#, > '#ListGetAt(l, 9)#', > #ListGetAt(l, 10)#, > #ListGetAt(l, 11)#, > #ListGetAt(l, 12)#, > > '#ListGetAt(l, 13)#', > '#ListGetAt(l, 14)#', > > '#ListGetAt(l, 15)#', > '#ListGetAt(l, 16)#', > '#ListGetAt(l, 17)#', > '#ListGetAt(l, 18)#', > '#ListGetAt(l, 19)#', > > '#ListGetAt(l, 20)#', > '1', > '#ListGetAt(l, 21)#', > ) > > > > > Database update completed. > > > > > An error occured while updating the > database. > > > > > > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Importing CSV into Database - Update
I've played with this a little more. The error I am now getting (after removing the cftry so I could see) is this: *** *** *** Error Diagnostic Information An error occurred while evaluating the _expression_: #ListGetAt(l, 2)# Error near line 56, column 8. In function ListGetAt(list, index [, delimiters]) the value of index, which is 2, is not a valid index for the list given as a the first argument (this list has 1 elements). Valid indexes are in the range 1 through the number of elements in the list The error occurred while processing an element with a general identifier of (#ListGetAt(l, 2)#), occupying document position (56:7) to (56:23). *** *** *** Where should I go from here? Should I be taking a different approach? -- Jillian -Original Message- From: Jillian Carroll [mailto:[EMAIL PROTECTED] Sent: February 12, 2004 10:57 AM To: CF-Talk Subject: Importing CSV into Database Good morning, I am having a heck of a time with this little import script. When it runs, all I receive are the messages that 'An error occurred while updating the database.' (as I indicated it should do if the query fails) Can anybody point me in the right direction? Thanks!! -- Jillian *** *** *** variable="csv_import"> INSERT INTO person_temp ( person_id, salutation_id, first_name, last_name, title, qualification, organization_id, department_id, office_id, status, role_id, org_position, national_board, userid, password, work_phone, work_phone_ext, fax_phone, cell_phone, pager_phone, email_wireless, lung_family, email ) VALUES ( nextval('"person_person_id_seq"'::text), #ListGetAt(l, 1)#, '#ListGetAt(l, 2)#', '#ListGetAt(l, 3)#', #ListGetAt(l, 4)#, '#ListGetAt(l, 5)#', #ListGetAt(l, 6)#, #ListGetAt(l, 7)#, #ListGetAt(l, 8)#, '#ListGetAt(l, 9)#', #ListGetAt(l, 10)#, #ListGetAt(l, 11)#, #ListGetAt(l, 12)#, '#ListGetAt(l, 13)#', '#ListGetAt(l, 14)#', '#ListGetAt(l, 15)#', '#ListGetAt(l, 16)#', '#ListGetAt(l, 17)#', '#ListGetAt(l, 18)#', '#ListGetAt(l, 19)#', '#ListGetAt(l, 20)#', '1', '#ListGetAt(l, 21)#', ) Database update completed. An error occured while updating the database. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Mass Database update
Chad, DTS will allow you to import from a .CSV. To dummy proof it use the "column transformations" tool in the package designer and write little validation routines in VB script. The little IDE makes it pretty easy. You can validate for length, numeric, date etc. (the date validation is tricky). I've always liked this approach rather than CF for file import. CF is ok if you have a limited number of rows, but long running HTTP requests are not usually the way to go - my .02. -mk -Original Message- From: Chad [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 9:37 AM To: CF-Talk Subject: Mass Database update We have a client that wants to drop a database table and replace it with a new one every once and a while. Is there any dummy proof ways of doing this? Have them upload a CSV, use CFFILE to loop over the information and import it? The data will start as an Excel table. __ 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 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Mass Database update
If you're using SQL server then you can do this much more efficiently with a DTS. http://www.sqlteam.com/item.asp?ItemID=10627 HTH, -- Howie Hamlin - inFusion Project Manager On-Line Data Solutions, Inc. - www.CoolFusion.com - 631-737-4668 x101 *** Please vote for iMS here: http://www.sys-con.com/coldfusion/readerschoice2002/nominationform.cfm *** inFusion Mail Server (iMS) - The Award-winning, Intelligent Mail Server >>> Find out how iMS Stacks up to the competition: >http://www.coolfusion.com/imssecomparison.cfm - Original Message - From: "Chad" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, September 25, 2002 10:36 AM Subject: Mass Database update > We have a client that wants to drop a database table and replace it with > a new one every once and a while. > > Is there any dummy proof ways of doing this? > > Have them upload a CSV, use CFFILE to loop over the information and > import it? > > The data will start as an Excel table. > > > __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Mass Database update
You'll probably not want to tackle this with ColdFusion. Use a transformation tool native to your DB if it's truly 'massive'. This will give you the most integrity/performance. If you want to automate the process using ColdFusion, that would be fine. Have them upload the Excel spreadsheet via CFFILE, and then have CF fire a StoredProcedure on your DB to import the information properly from Excel. You could pass the Stored Procedure the excel file name and make it truly dynamic/automated. Adam. > -Original Message- > From: Chad [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, September 25, 2002 9:37 AM > To: CF-Talk > Subject: Mass Database update > > > We have a client that wants to drop a database table and > replace it with > a new one every once and a while. > > Is there any dummy proof ways of doing this? > > Have them upload a CSV, use CFFILE to loop over the information and > import it? > > The data will start as an Excel table. > > > __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Mass Database update
BCP? > -Original Message- > From: Joshua Tipton [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, September 25, 2002 9:38 AM > To: CF-Talk > Subject: RE: Mass Database update > > run it all in a dts package truncate the table and then use bcp to import > the csv file. > > -Original Message- > From: Chad [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, September 25, 2002 10:37 AM > To: CF-Talk > Subject: Mass Database update > > > We have a client that wants to drop a database table and replace it with > a new one every once and a while. > > Is there any dummy proof ways of doing this? > > Have them upload a CSV, use CFFILE to loop over the information and > import it? > > The data will start as an Excel table. > > > > __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Mass Database update
run it all in a dts package truncate the table and then use bcp to import the csv file. -Original Message- From: Chad [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 10:37 AM To: CF-Talk Subject: Mass Database update We have a client that wants to drop a database table and replace it with a new one every once and a while. Is there any dummy proof ways of doing this? Have them upload a CSV, use CFFILE to loop over the information and import it? The data will start as an Excel table. __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Mass Database update
We have a client that wants to drop a database table and replace it with a new one every once and a while. Is there any dummy proof ways of doing this? Have them upload a CSV, use CFFILE to loop over the information and import it? The data will start as an Excel table. __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: mass database update
You know, I think that I need a little electro shock thereapy for each time I do this. I made this same mistake a couple of weeks ago. Man, these 15 h our days are getting to me (I know, I am a slacker). -- Original Message -- From: "Tyler M. Fitch" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] Date: Tue, 15 Jan 2002 13:59:38 -0800 Bruce, If all your text fields on your form are named count then when you put data in them and submit the form all the fields with the same name are concatenated together to form a comma seperated list of the values submitted. What I'd suggest is naming the fields count#ID# or something that will make then unique and identifiable on the action page. This will separate out their values individually for processing. Another option would be to use your listGetAt() for your count variable as well, because it is a list too. So you have two options I guess. HTH, Tyler M. Fitch Certified Advanced ColdFusion 5 Developer ISITE Design, Inc. -Original Message- From: Bruce Sorge [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 1:57 PM To: CF-Talk Subject: Re: mass database update Count is a quantity amount for an item, and ID is the ID primary key of the item being updated. This is fed from a form that allows the user to update inventory quantities for any number of items. Rather than go in and update one item at a time, I want them to have the ability to do all the items at once. -- Original Message -- From: "Bryan Stevenson" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] Date: Tue, 15 Jan 2002 10:01:15 -0800 # 5 needs more input... ;-) What is the relationship between "count" and "ID" and where does "count" ge t set? Bryan Stevenson VP & Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Original Message - From: "Bruce Sorge" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, January 15, 2002 3:54 AM Subject: mass database update > I am doing a mass update of a table. The query looks like this: > name="query_InsertItems" datasource="firehook1"> Update Inv1 > Set Count = #Count# > Where ID = #ListGetAt(ID, thisItem)# > > > > What I am getting is this: > Update Inv1 > Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 > Where ID = 2077 > > I know that I have to associate a quantity (count) with an ID, but I > am not sure how to go about it. I tried nesting loops and creating > arrays and such with no luck. Any assitance would be greatly > appreciated. > > Thanks, > > _ > Bruce Sorge > > > __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: mass database update
well then, you should name the count field on the form something like count_appendProductID. then when you do the insert you could do something like: Update Inv1 Set Count = #evaluate("form.Count_" & ID)# Where ID = #ListGetAt(ID, thisItem)# hope that helps :) phillip -Original Message- From: Bruce Sorge [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 4:57 PM To: CF-Talk Subject: Re: mass database update Count is a quantity amount for an item, and ID is the ID primary key of the item being updated. This is fed from a form that allows the user to update inventory quantities for any number of items. Rather than go in and update one item at a time, I want them to have the ability to do all the items at once. -- Original Message -- From: "Bryan Stevenson" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] Date: Tue, 15 Jan 2002 10:01:15 -0800 # 5 needs more input... ;-) What is the relationship between "count" and "ID" and where does "count" ge t set? Bryan Stevenson VP & Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Original Message - From: "Bruce Sorge" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, January 15, 2002 3:54 AM Subject: mass database update > I am doing a mass update of a table. The query looks like this: > > > Update Inv1 > Set Count = #Count# > Where ID = #ListGetAt(ID, thisItem)# > > > > What I am getting is this: > Update Inv1 > Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 > Where ID = 2077 > > I know that I have to associate a quantity (count) with an ID, but I am > not sure how to go about it. I tried nesting loops and creating arrays > and such with no luck. > Any assitance would be greatly appreciated. > > Thanks, > > _ > Bruce Sorge > > > __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: mass database update
here is what I would do name all of the text boxes that are used to specify amounts similar to each other. with the primarykey part of the name TEXTBOX_#primaryKey# then loop the form colection UPDATE TABLE SET column = evaluate(x) WHERE primarykey = #listLast(x, "_")# let me know if you need any more clarification -chris.alvarado [application developer] 4|Guys Interactive, Inc. http://www.4guys.com - Original Message - From: "Bruce Sorge" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, January 15, 2002 3:57 PM Subject: Re: mass database update > Count is a quantity amount for an item, and ID is the ID primary key of the > item being updated. This is fed from a form that allows the user to update > inventory quantities for any number of items. Rather than go in and update > one item at a time, I want them to have the ability to do all the items at > once. > > -- Original Message -- > From: "Bryan Stevenson" <[EMAIL PROTECTED]> > Reply-To: [EMAIL PROTECTED] > Date: Tue, 15 Jan 2002 10:01:15 -0800 > > # 5 needs more input... ;-) > > What is the relationship between "count" and "ID" and where does "count" ge > t set? > > Bryan Stevenson > VP & Director of E-Commerce Development > Electric Edge Systems Group Inc. > p. 250.920.8830 > e. [EMAIL PROTECTED] > - > Macromedia Associate Partner > www.macromedia.com > > ----- Original Message - > From: "Bruce Sorge" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Tuesday, January 15, 2002 3:54 AM > Subject: mass database update > > > > I am doing a mass update of a table. The query looks like this: > > > > > > Update Inv1 > > Set Count = #Count# > > Where ID = #ListGetAt(ID, thisItem)# > > > > > > > > What I am getting is this: > > Update Inv1 > > Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 > > Where ID = 2077 > > > > I know that I have to associate a quantity (count) with an ID, but I am > > not sure how to go about it. I tried nesting loops and creating arrays > > and such with no luck. > > Any assitance would be greatly appreciated. > > > > Thanks, > > > > _ > > Bruce Sorge > > > > > > > > __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: mass database update
Bruce, If all your text fields on your form are named count then when you put data in them and submit the form all the fields with the same name are concatenated together to form a comma seperated list of the values submitted. What I'd suggest is naming the fields count#ID# or something that will make then unique and identifiable on the action page. This will separate out their values individually for processing. Another option would be to use your listGetAt() for your count variable as well, because it is a list too. So you have two options I guess. HTH, Tyler M. Fitch Certified Advanced ColdFusion 5 Developer ISITE Design, Inc. -Original Message- From: Bruce Sorge [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 1:57 PM To: CF-Talk Subject: Re: mass database update Count is a quantity amount for an item, and ID is the ID primary key of the item being updated. This is fed from a form that allows the user to update inventory quantities for any number of items. Rather than go in and update one item at a time, I want them to have the ability to do all the items at once. -- Original Message -- From: "Bryan Stevenson" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] Date: Tue, 15 Jan 2002 10:01:15 -0800 # 5 needs more input... ;-) What is the relationship between "count" and "ID" and where does "count" ge t set? Bryan Stevenson VP & Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Original Message - From: "Bruce Sorge" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, January 15, 2002 3:54 AM Subject: mass database update > I am doing a mass update of a table. The query looks like this: > name="query_InsertItems" datasource="firehook1"> Update Inv1 > Set Count = #Count# > Where ID = #ListGetAt(ID, thisItem)# > > > > What I am getting is this: > Update Inv1 > Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 > Where ID = 2077 > > I know that I have to associate a quantity (count) with an ID, but I > am not sure how to go about it. I tried nesting loops and creating > arrays and such with no luck. Any assitance would be greatly > appreciated. > > Thanks, > > _ > Bruce Sorge > > > __ Why Share? Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: mass database update
Count is a quantity amount for an item, and ID is the ID primary key of the item being updated. This is fed from a form that allows the user to update inventory quantities for any number of items. Rather than go in and update one item at a time, I want them to have the ability to do all the items at once. -- Original Message -- From: "Bryan Stevenson" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] Date: Tue, 15 Jan 2002 10:01:15 -0800 # 5 needs more input... ;-) What is the relationship between "count" and "ID" and where does "count" ge t set? Bryan Stevenson VP & Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Original Message - From: "Bruce Sorge" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, January 15, 2002 3:54 AM Subject: mass database update > I am doing a mass update of a table. The query looks like this: > > > Update Inv1 > Set Count = #Count# > Where ID = #ListGetAt(ID, thisItem)# > > > > What I am getting is this: > Update Inv1 > Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 > Where ID = 2077 > > I know that I have to associate a quantity (count) with an ID, but I am > not sure how to go about it. I tried nesting loops and creating arrays > and such with no luck. > Any assitance would be greatly appreciated. > > Thanks, > > _ > Bruce Sorge > > > __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: mass database update
At 05:54 AM 01/15/2002 -0600, you wrote: > I am doing a mass update of a table. The query looks like this: > > >Update Inv1 >Set Count = #Count# >Where ID = #ListGetAt(ID, thisItem)# > > > >What I am getting is this: >Update Inv1 >Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 >Where ID = 2077 I don't see where count is defined, but if I understand the problem, but it sounds like the problem is with the Count variable. If you are trying to set every value to 9, you are complicating it greatly (but I suspect you are not trying to do that) -- Jeffry Houser | mailto:[EMAIL PROTECTED] AIM: Reboog711 | ICQ: 5246969 | Fax / Phone: 860-223-7946 -- DotComIt: Database Driven Web Data My Book: Instant ColdFusion 5 | http://www.instantcoldfusion.com My New Book: ColdFusion: A Beginner's Guide February 2002 -- Far Cry Fly, Alternative Folk Rock http://www.farcryfly.com | http://www.mp3.com/FarCryFly __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
re: mass database update
use the value of "thisItem". phillip __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: mass database update
use the value of "thisItem". phillip -Original Message- From: Bruce Sorge [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 15, 2002 6:55 AM To: CF-Talk Subject: mass database update I am doing a mass update of a table. The query looks like this: Update Inv1 Set Count = #Count# Where ID = #ListGetAt(ID, thisItem)# What I am getting is this: Update Inv1 Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 Where ID = 2077 I know that I have to associate a quantity (count) with an ID, but I am not sure how to go about it. I tried nesting loops and creating arrays and such with no luck. Any assitance would be greatly appreciated. Thanks, _ Bruce Sorge __ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: mass database update
# 5 needs more input... ;-) What is the relationship between "count" and "ID" and where does "count" get set? Bryan Stevenson VP & Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] - Macromedia Associate Partner www.macromedia.com - Original Message - From: "Bruce Sorge" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, January 15, 2002 3:54 AM Subject: mass database update > I am doing a mass update of a table. The query looks like this: > > > Update Inv1 > Set Count = #Count# > Where ID = #ListGetAt(ID, thisItem)# > > > > What I am getting is this: > Update Inv1 > Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 > Where ID = 2077 > > I know that I have to associate a quantity (count) with an ID, but I am > not sure how to go about it. I tried nesting loops and creating arrays > and such with no luck. > Any assitance would be greatly appreciated. > > Thanks, > > _ > Bruce Sorge > > > __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
mass database update
I am doing a mass update of a table. The query looks like this: Update Inv1 Set Count = #Count# Where ID = #ListGetAt(ID, thisItem)# What I am getting is this: Update Inv1 Set Count = 9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9 Where ID = 2077 I know that I have to associate a quantity (count) with an ID, but I am not sure how to go about it. I tried nesting loops and creating arrays and such with no luck. Any assitance would be greatly appreciated. Thanks, _ Bruce Sorge __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Dynamic Fieldnames & database update
Erica, This should do what you want: UPDATE interview SET ivr_key = '#Evaluate("form.fieldname" & counter)#' WHERE ... Nelson - Original Message - From: <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, August 29, 2001 6:44 PM Subject: Dynamic Fieldnames & database update > > I am working on a dynamic form that allows me to update multiple records at > once. > We use the primary key from the table as a hidden field to identify the > record, and append a unique number to all fields from each record via a > counter. > For example, the form would look something like this > > < input key#counter#> < input fieldname#counter#> < input fieldname#counter#> > > > Problem is, when we go to update the database, how do we specify the > formfields? I'd assumed that we would need to loop over our counter again, > but I don't know how to make the dynamic fieldnames in the update. How do I > get coldfusion to recognize that I want #form.fieldname1# not > "form.fieldname1" and not "value of fieldname1" the first time I loop over > something like > UPDATE interview > SET ivr_key = '#form.fieldname##counter#' > > Any help would be appreciated! > > > Here's the form so far: > > > SELECT * > FROM interview INNER JOIN interviewer on interview.ivr_key = > interviewer.ivr_key > WHERE interview.ivr_key = #cookie.interviewerid# > AND sch_name = '#cookie.schoolsession#' > > > > > SELECT * > FROM Interviewer > > > > > > Scheduled Interviews for > #getinterviewdata.ivr_firstname# > #getinterviewdata.ivr_lastname# > > > > > ="post"> > > > > cellpadding="0"> > > Interviewer > Start Time > End Time > Location > > > > > > > > >cookie.InterviewerID eq GetIvrs.ivr_key>selected> > #getivrs.ivr_FirstName# #getivrs.ivr_LastName# > > > > > > #TimeFormat(getinterviewdata.int_StartTime, 'hh:mm tt')# > > #TimeFormat(getinterviewdata.int_EndTime, 'hh:mm tt')# > > #getinterviewdata.loc_Name# > value="#getinterviewdata.int_Code#"> > > > > ="#counter#"> > > > > > > > > > > > > > > > > > Thanks! > Erica > > > Erica Vitina > Web Developer > Abbott Laboratories > [EMAIL PROTECTED] > > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Dynamic Fieldnames & database update
use the evaule function. in your loop, do this (lets say the counter is equal to 3) #Evaluate('form.dateofbirth'& counter)# coldfusion will read that as #form.dateofbirth3# Pooh Bear Web App Developer Picture Below (chicks dig my head!) http://www.geocities.com/kickerazn_2000/sniffles_138x92.jpg";> >From: [EMAIL PROTECTED] >Reply-To: [EMAIL PROTECTED] >To: CF-Talk <[EMAIL PROTECTED]> >Subject: Dynamic Fieldnames & database update >Date: Wed, 29 Aug 2001 17:44:00 -0500 > > >I am working on a dynamic form that allows me to update multiple records at >once. >We use the primary key from the table as a hidden field to identify the >record, and append a unique number to all fields from each record via a >counter. >For example, the form would look something like this > >< input key#counter#> < input fieldname#counter#> < input >fieldname#counter#> > > >Problem is, when we go to update the database, how do we specify the >formfields? I'd assumed that we would need to loop over our counter again, >but I don't know how to make the dynamic fieldnames in the update. How do I >get coldfusion to recognize that I want #form.fieldname1# not >"form.fieldname1" and not "value of fieldname1" the first time I loop over >something like >UPDATE interview >SET ivr_key = '#form.fieldname##counter#' > >Any help would be appreciated! > > >Here's the form so far: > > > SELECT * > FROM interview INNER JOIN interviewer on interview.ivr_key = >interviewer.ivr_key > WHERE interview.ivr_key = #cookie.interviewerid# > AND sch_name = '#cookie.schoolsession#' > > > > > SELECT * > FROM Interviewer > > > > > > Scheduled Interviews for >#getinterviewdata.ivr_firstname# >#getinterviewdata.ivr_lastname# > > > > >="post"> > > > > cellpadding="0"> > > Interviewer > Start Time > End Time > Location > > > > > > > > > cookie.InterviewerID eq GetIvrs.ivr_key>selected> >#getivrs.ivr_FirstName# #getivrs.ivr_LastName# > > > > > >#TimeFormat(getinterviewdata.int_StartTime, 'hh:mm tt')# > >#TimeFormat(getinterviewdata.int_EndTime, 'hh:mm tt')# > >#getinterviewdata.loc_Name# >name="intkey#counter#" >value="#getinterviewdata.int_Code#"> > > > > ="#counter#"> > > > > > > > > >Value="Reset"> > > > > > > > >Thanks! >Erica > > >Erica Vitina >Web Developer >Abbott Laboratories >[EMAIL PROTECTED] > > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Dynamic Fieldnames & database update
Why exactly are you appending the counter variable to all of your form fields? I'm not sure I quite understand what you're trying to do... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 29, 2001 6:44 PM To: CF-Talk Subject: Dynamic Fieldnames & database update I am working on a dynamic form that allows me to update multiple records at once. We use the primary key from the table as a hidden field to identify the record, and append a unique number to all fields from each record via a counter. For example, the form would look something like this < input key#counter#> < input fieldname#counter#> < input fieldname#counter#> Problem is, when we go to update the database, how do we specify the formfields? I'd assumed that we would need to loop over our counter again, but I don't know how to make the dynamic fieldnames in the update. How do I get coldfusion to recognize that I want #form.fieldname1# not "form.fieldname1" and not "value of fieldname1" the first time I loop over something like UPDATE interview SET ivr_key = '#form.fieldname##counter#' Any help would be appreciated! Here's the form so far: SELECT * FROM interview INNER JOIN interviewer on interview.ivr_key = interviewer.ivr_key WHERE interview.ivr_key = #cookie.interviewerid# AND sch_name = '#cookie.schoolsession#' SELECT * FROM Interviewer Scheduled Interviews for #getinterviewdata.ivr_firstname# #getinterviewdata.ivr_lastname# Interviewer Start Time End Time Location selected> #getivrs.ivr_FirstName# #getivrs.ivr_LastName# #TimeFormat(getinterviewdata.int_StartTime, 'hh:mm tt')# #TimeFormat(getinterviewdata.int_EndTime, 'hh:mm tt')# #getinterviewdata.loc_Name# Thanks! Erica Erica Vitina Web Developer Abbott Laboratories [EMAIL PROTECTED] ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Dynamic Fieldnames & database update
I am working on a dynamic form that allows me to update multiple records at once. We use the primary key from the table as a hidden field to identify the record, and append a unique number to all fields from each record via a counter. For example, the form would look something like this < input key#counter#> < input fieldname#counter#> < input fieldname#counter#> Problem is, when we go to update the database, how do we specify the formfields? I'd assumed that we would need to loop over our counter again, but I don't know how to make the dynamic fieldnames in the update. How do I get coldfusion to recognize that I want #form.fieldname1# not "form.fieldname1" and not "value of fieldname1" the first time I loop over something like UPDATE interview SET ivr_key = '#form.fieldname##counter#' Any help would be appreciated! Here's the form so far: SELECT * FROM interview INNER JOIN interviewer on interview.ivr_key = interviewer.ivr_key WHERE interview.ivr_key = #cookie.interviewerid# AND sch_name = '#cookie.schoolsession#' SELECT * FROM Interviewer Scheduled Interviews for #getinterviewdata.ivr_firstname# #getinterviewdata.ivr_lastname# Interviewer Start Time End Time Location selected> #getivrs.ivr_FirstName# #getivrs.ivr_LastName# #TimeFormat(getinterviewdata.int_StartTime, 'hh:mm tt')# #TimeFormat(getinterviewdata.int_EndTime, 'hh:mm tt')# #getinterviewdata.loc_Name# Thanks! Erica Erica Vitina Web Developer Abbott Laboratories [EMAIL PROTECTED] ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: [RE: [database update?]]
Peter, If these guys give you an update once a week they can wait 15 more minutes for the information to be available! Tell them the facts of life. Sometimes users have to be told NO! If they need faster updates let them ODBC into your database more frequently and directly update your tables. 15 minutes is nothing in the grand scheme of things. Wally Randall Date: Fri, 22 Dec 2000 14:05:29 -0500 From: Peter Benoit <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: RE: [RE: [database update?]] Message-ID: <[EMAIL PROTECTED]> Well it kinda can't be done that way. Essentially they throw a whole new DB at me once a week and ask that the new info be presented immediately. Currently it takes about 15 mins for the new info to appear. Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: [RE: [database update?]]
Peter, One way to do it would be to create an ODBC connection to a blank database. Then go into that db and link the tables to the database that's being uploaded. Go under FILE -> GET EXTERNAL DATA -> LINK TABLES. Then choose the uploaded database, and select all the relevant tables. Then when the database needs updating you don't have to worry about breaking the odbc connection etc, just replace the database. hth, larry -- Larry C. Lyons ColdFusion/Web Developer EBStor.com 8870 Rixlew Lane, Suite 201 Manassas, Virginia 20109-3795 tel: (703) 393-7930 x253 fax: (703) 393-2659 http://www.ebstor.com http://www.pacel.com email: [EMAIL PROTECTED] Chaos, panic, and disorder - my work here is done. -- Peter Benoit wrote: > > Well it kinda can't be done that way. Essentially they throw a whole new DB > at me once a week and ask that the new info be presented immediately. > Currently it takes about 15 mins for the new info to appear. > > -Original Message- > From: Alex [mailto:[EMAIL PROTECTED]] > Sent: Friday, December 22, 2000 2:00 PM > To: CF-Talk > Subject: Re: [RE: [database update?]] > > replace it over FTP at night. this has nothing to do with CF > > Peter Benoit <[EMAIL PROTECTED]> wrote: > It's a whole new access DB > > -Original Message- > From: Alex [mailto:[EMAIL PROTECTED]] > Sent: Thursday, December 21, 2000 3:08 PM > To: CF-Talk > Subject: Re: [database update?] > > do you update the database (SQL statement) or replace the database with new > access DB? > > Peter Benoit <[EMAIL PROTECTED]> wrote: > I have an ODBC connection to my access database. Once a week I update that > connection with a new database. It seems to take roughly 15 mins or so for > that update to take place. Is there a reason for that, and can I shorten > the length? > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: [RE: [RE: [database update?]]]
see if you can compress it somehow. Peter Benoit <[EMAIL PROTECTED]> wrote: Well it kinda can't be done that way. Essentially they throw a whole new DB at me once a week and ask that the new info be presented immediately. Currently it takes about 15 mins for the new info to appear. -Original Message- From: Alex [mailto:[EMAIL PROTECTED]] Sent: Friday, December 22, 2000 2:00 PM To: CF-Talk Subject: Re: [RE: [database update?]] replace it over FTP at night. this has nothing to do with CF Peter Benoit <[EMAIL PROTECTED]> wrote: It's a whole new access DB -Original Message- From: Alex [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 21, 2000 3:08 PM To: CF-Talk Subject: Re: [database update?] do you update the database (SQL statement) or replace the database with new access DB? Peter Benoit <[EMAIL PROTECTED]> wrote: I have an ODBC connection to my access database. Once a week I update that connection with a new database. It seems to take roughly 15 mins or so for that update to take place. Is there a reason for that, and can I shorten the length? ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: [database update?]
Depending on how big the database is, if it is not too big why not just loop over each record in the new database and update the old records. Checking in each loop to see of the record is new, if so, Insert it. This is assuming that they do not change the database structure on you. This whole process would be made faster if the database that they feed you was designed to put a timestamp on each record any time a record was added or updated. That way you could just query the database for anything that has a timestamp after your last update. You could make it super easy on yourself by providing the person who gives it to you a url to go to and upload the new database to. The web site would automatically update the database, and the updater would know when the new data is up right away. jon - Original Message - From: "Peter Benoit" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, December 22, 2000 1:38 PM Subject: RE: [database update?] > It's a whole new access DB > > -Original Message- > From: Alex [mailto:[EMAIL PROTECTED]] > Sent: Thursday, December 21, 2000 3:08 PM > To: CF-Talk > Subject: Re: [database update?] > > > do you update the database (SQL statement) or replace the database with new > access DB? > > Peter Benoit <[EMAIL PROTECTED]> wrote: > I have an ODBC connection to my access database. Once a week I update that > connection with a new database. It seems to take roughly 15 mins or so for > that update to take place. Is there a reason for that, and can I shorten > the length? > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: [RE: [database update?]]
Well it kinda can't be done that way. Essentially they throw a whole new DB at me once a week and ask that the new info be presented immediately. Currently it takes about 15 mins for the new info to appear. -Original Message- From: Alex [mailto:[EMAIL PROTECTED]] Sent: Friday, December 22, 2000 2:00 PM To: CF-Talk Subject: Re: [RE: [database update?]] replace it over FTP at night. this has nothing to do with CF Peter Benoit <[EMAIL PROTECTED]> wrote: It's a whole new access DB -Original Message- From: Alex [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 21, 2000 3:08 PM To: CF-Talk Subject: Re: [database update?] do you update the database (SQL statement) or replace the database with new access DB? Peter Benoit <[EMAIL PROTECTED]> wrote: I have an ODBC connection to my access database. Once a week I update that connection with a new database. It seems to take roughly 15 mins or so for that update to take place. Is there a reason for that, and can I shorten the length? ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: [RE: [database update?]]
replace it over FTP at night. this has nothing to do with CF Peter Benoit <[EMAIL PROTECTED]> wrote: It's a whole new access DB -Original Message- From: Alex [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 21, 2000 3:08 PM To: CF-Talk Subject: Re: [database update?] do you update the database (SQL statement) or replace the database with new access DB? Peter Benoit <[EMAIL PROTECTED]> wrote: I have an ODBC connection to my access database. Once a week I update that connection with a new database. It seems to take roughly 15 mins or so for that update to take place. Is there a reason for that, and can I shorten the length? ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: [database update?]
It's a whole new access DB -Original Message- From: Alex [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 21, 2000 3:08 PM To: CF-Talk Subject: Re: [database update?] do you update the database (SQL statement) or replace the database with new access DB? Peter Benoit <[EMAIL PROTECTED]> wrote: I have an ODBC connection to my access database. Once a week I update that connection with a new database. It seems to take roughly 15 mins or so for that update to take place. Is there a reason for that, and can I shorten the length? ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: [database update?]
do you update the database (SQL statement) or replace the database with new access DB? Peter Benoit <[EMAIL PROTECTED]> wrote: I have an ODBC connection to my access database. Once a week I update that connection with a new database. It seems to take roughly 15 mins or so for that update to take place. Is there a reason for that, and can I shorten the length? ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
database update?
I have an ODBC connection to my access database. Once a week I update that connection with a new database. It seems to take roughly 15 mins or so for that update to take place. Is there a reason for that, and can I shorten the length? ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists