Querying two datasources (MS ACCESS)

[RuBot]

Using a database that holds the job ticket information, I need to create an
email notification checking against a different database (employee table) to
see if the employee is set to receive notificaitons. I have the <cftransaction>
tag and it said only one datasource could be used within the tags. I removed
it, but was still unable to get it to work. I would get a ''notify'' variable
undefined, except that all fields are populated in the database.


<cftransaction>
<cfif AddRecord is "yes"><!--- Add New Ticket --->
<cfif #trim(nombre)# is not "">
<cfif #trim(emailto)# is not "">
<CFQUERY NAME="#Table_Name#_Insert_Query" DATASOURCE="HelpDesk">
INSERT Into #Table_Name# (Entry_Date, Priority_Level, Status,
Entry_Description, Name, Type, Location, Emailto, DueDate)
VALUES ('#Entry_Date#', '#Priority_Level#', '#Status#',
'#Entry_Description#', '#nombre#',
<cfif #trim(Type)# is "">Null
<cfelse>'#Type#'
</cfif>,
<cfif #trim(Location)# is "">Null
<cfelse>'#Location#'
</cfif>, '#emailto#','#DueDate#')
</cfquery>
<!--- WORKING ON EMAIL NOTIFICATIONS TO VOLUNTEERS --->

<cfset volunteer = "#emailto#">

<cfquery name = "notifyon" datasource="emp">
select fname, notify
from emptbl
where email = '#volunteer#'
</cfquery>
<cfif notify is "Yes">
<cfmail to = "#checkmail#" from = "HelpDesk@FirstBankAK.com" subject =
"#nombre# has created a ticket for your issue" server = "10.1.100.202">
#fname#, you have requested assistance from the Help Desk regarding the
following:
#Entry_Description#

A ticket has been created by #nombre# to address your issue.
</cfmail>
</cfif>

[MikerRoo]

Try the attached code.

The main thing is to scope the query variables "qVolunteerDetails...." etc.

Also note that I've removed some excessive # symbols added a few more trims,
etc.

Regards,
-- MikeR


<CFIF AddRecord is "yes"><!--- Add New Ticket --->
<CFIF Trim (nombre) is not ""> <!--- Nombre should be trimmed below,
too. --->
<CFIF Trim (emailto) is not "">
<CFSET volunteer = Trim (emailto)>

<CFQUERY name="#Table_Name#_Insert_Query" datasource="HelpDesk">
INSERT INTO
#Table_Name#
(
Entry_Date,
Priority_Level,
Status,
Entry_Description,
Name,
Type,
Location,
Emailto,
DueDate
)
VALUES
(
'#Entry_Date#',
'#Priority_Level#',
'#Status#',
'#Entry_Description#',
'#nombre#',
<CFIF Trim (Type) IS ""> NULL <CFELSE>'#Trim
(Type)#' </CFIF>,
<CFIF Trim (Location) IS ""> NULL <CFELSE>'#Trim
(Location)#' </CFIF>,
'#volunteer#',
'#DueDate#'
)
</CFQUERY>


<!--- WORKING ON EMAIL NOTIFICATIONS TO VOLUNTEERS --->

<CFQUERY name="qVolunteerDetails" datasource="emp">
SELECT fname,
notify <!--- Is notify string or boolean? --->
FROM emptbl
WHERE email = '#volunteer#'
</CFQUERY>

<CFIF qVolunteerDetails.RecordCount NEQ 1>
<CFOUTPUT> <h1>Oopsie!</h1> </CFOUTPUT>
<CFABORT>
</CFIF>

<CFIF qVolunteerDetails.notify>
<CFMAIL
to = "#checkmail#"
from = "HelpDesk@FirstBankAK.com"
subject = "#nombre# has created a ticket for your issue"
server = "10.1.100.202"

>

#qVolunteerDetails.fname#, you have requested assistance
from the Help Desk regarding the following:
#Entry_Description#

A ticket has been created by #nombre# to address your
issue.
</CFMAIL>
</CFIF>

[RuBot]

Thank you so much!

[RuBot]

Oh yeah, one thing: I had this segment within <cftransaction> tags. It happened
to prevent the record from being added if there was an error within the tags, I
believe. Having two datasources, I had to remove the <cftransaction> tags. Is
there a workaround for this?

[mxstu]

Originally posted by: RuBot
Oh yeah, one thing: I had this segment within <cftransaction> tags. It
happened to prevent the record from being added if there was an error within
the tags, I believe. Having two datasources, I had to remove the
<cftransaction> tags. Is there a workaround for this?

IMO, this is not a situation where you would need a CFTRANSACTION. You
already know the "volunteer" email, and you are not inserting/updating the
"emptbl". Just perform the "qVolunteerDetails" query first and show an error
message if the volunteer record was not found. Otherwise, perform the INSERT
and then CFMAIL (if needed).

[RuBot]

I'm not exactly sure where or what it was since I'm continually making so many
changes here and there, but I do know that when I have some errors, the record
will still be inserted.

Is there any way to get around this?

[mxstu]

Well, what I'm saying is that in this case I think you should control the
INSERT with CFIF statements instead of CFTRANSACTION. IMO, CFTRANSACTION is
not warranted here.

Based on your code, you should ...
- First verify that the selected "volunteer" exists in the "emptbl" table
- If the "volunteer" was not found, display an error message and do NOT
perform the INSERT
- Otherwise, perform the INSERT and CFMAIL (if needed)

You don't want to use CFTRANSACTION as some sort of global CFM error handler
or undo-button ;-) It is intended for use with multiple database queries. When
you use CFTRANSACTION, some level of database locking is involved, so you
should keep your CFTRANSACTION blocks as brief as possible and omit any code
that is unrelated to the database queries involved.

This article provides some good information about using CFTRANSACTION:
[url]http://www.macromedia.com/devnet/mx/coldfusion/articles/cftransaction.htm[/url]



<!---- psuedo-code ----->

<!--- Ensure that the "volunteer" exists --->
<CFQUERY name="qVolunteerDetails" ...>
... your SELECT statement here
</CFQUERY>

<!--- If the volunteer was not found, do NOT perform the INSERT --->
<CFIF qVolunteerDetails.RecordCount EQ 0>
... Display your error message here ....
<CFELSE>
<CFQUERY name="yourInsertQuery" ...>
... your INSERT statement here....
</CFQUERY>

<CFIF qVolunteerDetails.notify>
<CFMAIL ... >.....</CFMAIL>
</CFIF>
</CFIF>

[RuBot]

Yeah that makes sense, but I'm wondering this (first groggy thought of the
workday)

this doesn't protect against a syntax error or invalid data entry somewhere
right? because I think that's where I'm having the problems.

[mxstu]

No, but you should be designing your code to prevent forseeable or common
errors and handle unexpected errors (i.e. use validation on form or url
variables, use cfqueryparam in queries, use cftry/cfcatch blocks).

In any case, I do not think that is an issue here. With the new CFIF
structure, if there is a syntax error in the INSERT query nothing will be
inserted and the page will abort.