update database

[JPrice]

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

[Vbprog40]

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]

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" <webforumsuser@macromedia.com> wrote in message
news:dajogi$mkg$1@forums.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
>

[JMGibson3]

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

MaxDoNum = 0.0524017475978,

[JPrice]

Thanks! That did it.

"JMGibson3" <webforumsuser@macromedia.com> wrote in message
news:dak09c$4r2$1@forums.macromedia.com...

> Somehow an extra comma wound up in your version of VB's sample:
>
> MaxDoNum = 0.0524017475978,

[JPrice]

"Vbprog40" <webforumsuser@macromedia.com> wrote in message
news:dajogi$mkg$1@forums.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
>

[Vbprog40]

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.