Professional Web Applications Themes

update database - Coldfusion Database Access

I have a database where I need to input values that I calculate. I only need to do this once, so I wanted to write a template to do this quickly. I am new to CF, so I'm totally confused. I have a query: <cfquery name="qu_1" datasource="Drugs"> SELECT dose_country.uid, drugs.CompoundName AS Compound, drugs.Lowest AS LowestSolubility, Dose_Country.MinDose AS MinimumDose, Dose_Country.MaxDose AS MaximumDose, Country_ref.CountryName AS Country, Dose_Country.MinDoNum AS MinDo, Dose_Country.MaxDoNum AS MaxDO FROM ((drugs LEFT JOIN Dose_Country ON Dose_Country.CompoundName=drugs.ID) LEFT JOIN Country_ref ON Country_ref.ID=Dose_Country.country) </cfquery> Then I calculate my values and display them on the screen: <cfoutput query="qu_1"> <cfif #qu_1.LowestSolubility# EQ ""> ...

  1. #1

    Default update database

    I have a database where I need to input values that I calculate. I only need to
    do this once, so I wanted to write a template to do this quickly. I am new to
    CF, so I'm totally confused.

    I have a query:
    <cfquery name="qu_1" datasource="Drugs">
    SELECT dose_country.uid, drugs.CompoundName AS Compound, drugs.Lowest AS
    LowestSolubility, Dose_Country.MinDose
    AS MinimumDose, Dose_Country.MaxDose AS MaximumDose, Country_ref.CountryName
    AS Country, Dose_Country.MinDoNum AS MinDo, Dose_Country.MaxDoNum AS MaxDO
    FROM ((drugs LEFT JOIN Dose_Country ON Dose_Country.CompoundName=drugs.ID)
    LEFT JOIN Country_ref ON Country_ref.ID=Dose_Country.country)
    </cfquery>

    Then I calculate my values and display them on the screen:

    <cfoutput query="qu_1">
    <cfif #qu_1.LowestSolubility# EQ "">
    <cfset MinDoNum="">
    <cfset maxDoNum="">
    <cfelseif #qu_1.country# EQ "JP">
    <cfset MinDoNum=#qu_1.MinimumDose#/150/#qu_1.LowestSolubility#>
    <cfset maxDoNum=#qu_1.MaximumDose#/150/#qu_1.LowestSolubility#>
    <cfelse>
    <cfset
    MinDoNum=#qu_1.MinimumDose#/250/#qu_1.LowestSolubility#>
    <cfset maxDoNum=#qu_1.MaximumDose#/250/#qu_1.LowestSolubility#>
    </cfif>
    <tr>
    <td>#qu_1.Compound#</td>
    <td>#qu_1.LowestSolubility#</td>
    <td>#qu_1.MinimumDose#</td>
    <td>#qu_1.MaximumDose#</td>
    <td>#qu_1.Country#</td>
    <td>#MinDoNum#</td>
    <td>#maxDoNum#</td>
    </tr>
    </cfoutput>

    In my table, I have zeros for the MinDoNum and MaxDoNum. I want to replace the
    zeros with the calculated ones. What do I need to do to update my table?

    Thanks,
    Judy

    JPrice Guest

  2. #2

    Default Re: update database

    So you would just like a basic update script.

    <CFQUERY NAME="update_table" datasource="Drugs">
    UPDATE Dose_Country
    SET MinDoNum = #MinDoNum#,
    MaxDoNum = #MaxDoNum#,
    WHERE ID = #qu_1.UNIQUE RECORD ID#
    </CFQUERY>

    You will need to replace the WHERE ID with whatever you use to uniquely
    identify your records. Also I asummed you had MinDoNum and MaxDoNum set as
    Number Format in database. If this is not the case add ? ? around the value.

    Hope this helps

    Vbprog40 Guest

  3. #3

    Default Re: update database

    I tried putting this in, but got a syntax error in the update statement. It
    didn't like the WHERE statement. I have a primary key in my Dose_Country
    table, called UID. So I put
    WHERE ID=#qu_1.uid#

    This is the error:
    SQL UPDATE Dose_Country SET MinDoNum = 0.0524017475978, MaxDoNum =
    0.0524017475978, WHERE ID = 44
    DATASOURCE Drugs
    VENDORERRORCODE -3503
    SQLSTATE 42000


    "Vbprog40" <webforumsusermacromedia.com> wrote in message
    news:dajogi$mkg$1forums.macromedia.com...
    > So you would just like a basic update script.
    >
    > <CFQUERY NAME="update_table" datasource="Drugs">
    > UPDATE Dose_Country
    > SET MinDoNum = #MinDoNum#,
    > MaxDoNum = #MaxDoNum#,
    > WHERE ID = #qu_1.UNIQUE RECORD ID#
    > </CFQUERY>
    >
    > You will need to replace the WHERE ID with whatever you use to uniquely
    > identify your records. Also I asummed you had MinDoNum and MaxDoNum set as
    > Number Format in database. If this is not the case add ? ? around the
    value.
    >
    > Hope this helps
    >

    JPrice Guest

  4. #4

    Default Re: update database

    Somehow an extra comma wound up in your version of VB's sample:

    MaxDoNum = 0.0524017475978,
    JMGibson3 Guest

  5. #5

    Default Re: update database

    Thanks! That did it.

    "JMGibson3" <webforumsusermacromedia.com> wrote in message
    news:dak09c$4r2$1forums.macromedia.com...
    > Somehow an extra comma wound up in your version of VB's sample:
    >
    > MaxDoNum = 0.0524017475978,

    JPrice Guest

  6. #6

    Default Re: update database

    "Vbprog40" <webforumsusermacromedia.com> wrote in message
    news:dajogi$mkg$1forums.macromedia.com...
    > So you would just like a basic update script.
    >
    > <CFQUERY NAME="update_table" datasource="Drugs">
    > UPDATE Dose_Country
    > SET MinDoNum = #MinDoNum#,
    > MaxDoNum = #MaxDoNum#,
    > WHERE ID = #qu_1.UNIQUE RECORD ID#
    > </CFQUERY>
    >
    > You will need to replace the WHERE ID with whatever you use to uniquely
    > identify your records. Also I asummed you had MinDoNum and MaxDoNum set as
    > Number Format in database. If this is not the case add ? ? around the
    value.
    >
    Pardon my ignorance, but what did you mean by adding ?? around the value?
    I've got another update which happens to be a string, and I'm getting a
    syntax error.
    > Hope this helps
    >

    JPrice Guest

  7. #7

    Default Re: update database

    Thats cool. With SQL Statments you don't add ' ' around number / data-time
    formated columns.

    Example. If you have a column:
    MyIDNUMBER = #MinDoNum# And this column is Number formated in the database
    you would write it like this.

    If you ahve a column like this:
    MyFIRSTNAME = '#FNAME#' And this column is a non-number format, you would
    add ' ' around the value.

    Hope this helps.

    Vbprog40 Guest

Similar Threads

  1. scheduled update of database
    By vasanth kumar in forum ASP Database
    Replies: 4
    Last Post: August 6th, 02:03 PM
  2. Database Update Error
    By tamimi in forum ASP Database
    Replies: 2
    Last Post: December 9th, 08:37 PM
  3. Can a web service update a database ?
    By moko in forum ASP.NET Web Services
    Replies: 1
    Last Post: December 6th, 09:24 AM
  4. Update database using dataset
    By Tracey in forum ASP.NET General
    Replies: 0
    Last Post: July 29th, 10:09 AM
  5. update from between database
    By Thomas in forum ASP.NET General
    Replies: 2
    Last Post: July 28th, 11:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139