[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Simple Question

Posted on 2005-04-07
11
Medium Priority
?
196 Views
Last Modified: 2013-12-24
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>&nbsp;</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>            
0
Comment
Question by:adonis1976
  • 7
  • 4
11 Comments
 
LVL 17

Expert Comment

by:Tacobell777
ID: 13732930
UPDATE #form.table_name#
                    SET #form.col_name# = NEWID()
                    WHERE (#form.col_name# IS NULL)
0
 
LVL 11

Author Comment

by:adonis1976
ID: 13732979
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.
0
 
LVL 17

Expert Comment

by:Tacobell777
ID: 13733009
have you tried it?
0
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
LVL 11

Author Comment

by:adonis1976
ID: 13733030
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?
0
 
LVL 11

Author Comment

by:adonis1976
ID: 13733070
do you know how can i refer to the value in the currentrow?
0
 
LVL 17

Expert Comment

by:Tacobell777
ID: 13733169
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.
0
 
LVL 11

Author Comment

by:adonis1976
ID: 13733220
i tried that and it complaining that cannot convert complex values into simple values.. PLS help..
0
 
LVL 11

Author Comment

by:adonis1976
ID: 13733259
Is it possible to bulid the something like this

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

and loop over the list?
0
 
LVL 17

Accepted Solution

by:
Tacobell777 earned 2000 total points
ID: 13733367
try this
<cfloop query="getallrec">
               <cfquery datasource="#datasource#">
                    UPDATE #form.table_name#
                    SET #form.col_name# = '#createUUID()#'
                    where #form.pri_name# = #evaluate( "getallrec." & form.pri_name# (Here is the problem, i need the value of the currentrow but not the row number)
               </cfquery>
          </cfloop>      
0
 
LVL 11

Author Comment

by:adonis1976
ID: 13733391
Awesome.. worked..
0
 
LVL 11

Author Comment

by:adonis1976
ID: 13733392
thank u very much.. have a nice day..
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month19 days, 14 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question