CFLOOP, Updating a field in Access database

[Frec]

I need to update a field in an Access database. The numbe rneeds to have four
characters and increment by one. Currently I can update all of the fields with
my code. However, I would like to update one field with one number and then
the next field with a new number increment by one. For example,

0001
0002
0003
0004

Currently, it's doing this

0002
0002
0002
0002

Here's my code.

<CFSET IDNUM='#IncrementValue("1")#'>
<FORM ACTION="cfs_modification_act.cfm" METHOD="Post">

<CFQUERY NAME="UpdateRecords" DATASOURCE="CFACCESS">
UPDATE tblFinal SET FIELD3='#NumberFormat(IDNUM, '0000')#' WHERE Field0='D'
</CFQUERY>

<CFLOOP QUERY="UpdateRecords" DATASOURCE="CFACCESS">
</CFLOOP>

[philh]

Hi Frec,

You're incrementing the value only once, since it's not in your loop. Matter
of fact, there's nothing in your loop.

Since UPDATEs don't produce a recordset, you can't use them in CFOUTPUT or
CFLOOP tags.

Also, if you use "WHERE Field0 = 'D'" in the update, field3 will be
overwritten every time with the incremented value.

I'm not sure what you're trying to accomplish here.

[Frec]

I have a table with data already in it. I want to add a unique number to
Field3. I will do this once a month and it's okay if it's overwritten each
time I do it.

The unique number need to be formatted with 4 characters (i.e 0001) but I want
in to increment by one for each field I update.

I thought maybe a loop will fix this.

[BKBK]

Hi Frec,

Is this what you're looking for?



<CFQUERY NAME="NumberOfRecords" DATASOURCE="CFACCESS">
SELECT tblFinal where Field0='D'
</CFQUERY>

<CFLOOP QUERY="NumberOfRecords" DATASOURCE="CFACCESS">
<CFSET IDNUM='#IncrementValue("1")#'>
<CFQUERY NAME="UpdateRecords" DATASOURCE="CFACCESS">
UPDATE tblFinal SET FIELD3='#NumberFormat(IDNUM, '0000')#' WHERE Field0='D'
</CFQUERY>
</CFLOOP>

[Frec]

Hi BKBK,

I ran your code (I removed the datasource attribute from CFLOOP). It completed
however the field3 has the same values in it.

For example,

0002
0002
0002
0002

I would like it to

0001
0002
0003
0004

I'm still going over you code. Just letting you know thanks for the help.

Thanks.

[Frec]

BKBK ,

Maybe I'm running it wrong also. The CFLOOP is in the CFFORM. I have a submit button to run the updates. Was there another way to run it?

Frec

[Dan Bracuk]

Assuming your table has a single field primary key, start with this

<cfquery name="q1">
select primarykeyfield
from tblFinal
where field0 = 'D'
</cfquery>

<cfscript>
iterations = q1.recordcount;
numbers = "";
for (j = 1, j lte iterations; j = j + 1)
numbers = listappend(numbers, j);
records = valuelist(q1.primarykeyfield;
</cfscript>

<cfloop from = "1", to = iterations index = j>
<cfscript>
thisnumber = numberformat(listgetat(numbers, j), "0000");
thisrecord = listgetat(records, j);
</cfscript>
<cfquery>
update tblfinal
set field3 = '#thisnumber#'
where primarykeyfiled = #thisrecord#
</cfquery>
</cfloop>

Hope you don't have very many records, because this will be slow.
Originally posted by: Frec
BKBK ,

Maybe I'm running it wrong also. The CFLOOP is in the CFFORM. I have a submit
button to run the updates. Was there another way to run it?

Frec

[BKBK]

>Hi BKBK,

> I ran your code (I removed the datasource attribute from CFLOOP). It

completed however the field3

> has the same values in it.

> For example,

> 0002
> 0002
> 0002
> 0002

Sorry, man. Mi fail to initialize di darn ting. Me try again.


<CFQUERY NAME="NumberOfRecords" DATASOURCE="CFACCESS">
SELECT tblFinal where Field0='D'
</CFQUERY>
<CFSET IDNUM= 0>
<CFLOOP QUERY="NumberOfRecords" DATASOURCE="CFACCESS">
<CFSET IDNUM=IDNUM+1>
<CFQUERY NAME="UpdateRecords" DATASOURCE="CFACCESS">
UPDATE tblFinal SET FIELD3='#NumberFormat(IDNUM, '0000')#' WHERE Field0='D'
</CFQUERY>
</CFLOOP>

[Frec]

BKBK,

That didn't work. '0052' populates the field3. How did this number get there? That's strange and the number stills the same it doesn't increment.

Thanks again for your help.

[JMGibson3]

I'm guessing you have 52 rows on file, right?
The UPDATE statement sets every Field0 = 'D' row to the latest IDNUM, so they
weere all 1, then all 2, then all 3, etc.
UPDATE tblFinal SET FIELD3='#NumberFormat(IDNUM, '0000')#' WHERE
Field0='D'

You need to expand/change the WHERE clause to whatever is needed to
distinguish one row from the other 51.

[Frec]

Hi JM Gibson,

Yes, there are 52 two rows with 'D' in Field1. Thanks for your message.
Shouldn't the loop stop after the records are complete.

I extended my WHERE clause but this didn't work.

WHERE Field0='D' AND Field1 <> Field2

[Dan Bracuk]

You are trying to compare two fields in the same row. You have to compare the
same field in two different rows. By the way, that's really hard.

Originally posted by: Frec
Hi JM Gibson,

Yes, there are 52 two rows with 'D' in Field1. Thanks for your message.
Shouldn't the loop stop after the records are complete.

I extended my WHERE clause but this didn't work.

WHERE Field0='D' AND Field1 <> Field2

[Frec]

Shouldn't the loop stop after the rows are complete? It seems like the only way to rewrite the Field3 is if I run the query again.

Thanks for your information.

[philh]

Hi Frec,

The root cause of this problem is that you don't have a primary, unique identifier for your records, do you?

[Frec]

Hi Philh,

There is no primary key. I'm able to update Field3 if Field0 but I can't update each row in field three with a incrementing value. Do I need a primary key to do this?

[philh]

Hi Frec,

You need a way to identify each record uniquely. If more than one record has
the same attribute, as in "field1 = 'D'", then you can't use the approach
you've been trying.

You should consider adding a primary key to the table. It would make things a
heck of a lot easier.

[Frec]

Hi philh

Couldn't I consider the number I'm adding a primary key?

Thanks for the time you took to answer this.

[Dan Bracuk]

no

Originally posted by: Frec
Hi philh

Couldn't I consider the number I'm adding a primary key?

Thanks for the time you took to answer this.

[philh]

As Dan says, no, because the value is not a pre-existing element of the record.
You must have a primary key that exists at the time you issue the query, or
else you're going to drive yourself crazy trying to identify individual
records. What if, for example, you have more than one record with the exact
same information in each field? How would you uniquely identify these records?

[Frec]

Thanks Phil,

I have added a primary key. Do you have any recommendations for the WHERE query.

FREC