asked on

Simple Question

I have form in a cfm page as follows:

<cfform name="upd" action="" method="post">

<table width="100%" border="0" cellpadding="4" cellspacing="0">
<tr>
<td width="18%">Table Name: </td>
<td width="82%">
            <cfinput type="text" name="table_name" required="yes" message="Table Name Required">
      </td>
</tr>
<tr>
<td>Column to be updated:: </td>
<td>
            <cfinput type="text" name="col_name" required="yes" message="Column Name Required.">
      </td>
</tr>
<tr>
<td>Primary Column Name : </td>
<td><cfinput type="text" name="pri_name" required="yes" message="Primary Column Name Required."></td>
</tr>
<tr>
<td> </td>
<td>
            <input type="submit" name="submit" value="update">
      </td>
</tr>
</table>
</cfform>

The user will input the table name, column name to be updated and primary key column name.

I want to update all the rows having the column value as NULL to be populated with CF UUId function.

This is what i have right now for the action ...

<cfif isDefined('form.submit')>
            <cftry>
                  <cfquery name="getallrec" datasource="#datasource#">
                        SELECT #form.pri_name#
                        FROM #form.table_name#
                        WHERE #form.col_name# IS NULL
                  </cfquery>
                  <cfcatch type="database">
                        <h3>A Database exception has occured because you did not type the column names properly</h3>
                  <cfabort>
                  </cfcatch>
            </cftry>

            <cfloop query="getallrec" startrow="1" endrow="#getallrec.RecordCount#">
                  <cfquery datasource="#datasource#">
                        UPDATE #form.table_name#
                        SET #form.col_name# = '#createUUID()#'
                        where #form.pri_name# = #CURRENTROW# (Here is the problem, i need the value of the currentrow but not the row number)
                  </cfquery>
            </cfloop>

Tacobell777

UPDATE #form.table_name#
SET #form.col_name# = NEWID()
WHERE (#form.col_name# IS NULL)

adonis1976

ASKER

that will put the same value for all the rows. I want distict values. thats why i'm using createuuid. If i use createuuid with your statement, it puts the same value to all the rows having null value.

Tacobell777

have you tried it?

adonis1976

ASKER

i just tried it.. it did work. i apologize

but the deal is NewID() generates a 36 character string and createUUId() generates a 35 character string. and my db has only 35 allowed.. do know what i can do?

adonis1976

ASKER

do you know how can i refer to the value in the currentrow?

Tacobell777

easiest and quickest way is to change your db to allow 36 chars..

Why do you have startrow="1" endrow="#getallrec.RecordCount#" in the cfloop?

<cfloop query="getallrec">
<cfquery datasource="#datasource#">
UPDATE #form.table_name#
SET #form.col_name# = '#createUUID()#'
where #form.pri_name# = #getallrec[ form.pri_name]# (Here is the problem, i need the value of the currentrow but not the row number)
</cfquery>
</cfloop>

But its wrong, you are looping, looping is bad, by bad I mean you need to avoid it when possible.

adonis1976

ASKER

i tried that and it complaining that cannot convert complex values into simple values.. PLS help..

adonis1976

ASKER

Is it possible to bulid the something like this

cfset mylist = valuelist(getallrec.#form.pri_name#)>

and loop over the list?

ASKER CERTIFIED SOLUTION

Tacobell777

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial