You need to do two updates, one for each table.
Depending on the type of database you're using you should wrap the two updates in a <cftransaction>
DateTime fields in Access need to be fed ODBC formatted date/times either using CreateODBCDate/CreateODBCDateTime or <cfqueryparam>
You don't need to wrap odbc datetimes in quotes.
Regards
Stephen
James Buckingham wrote:
Morning guys,
I'm sure this is a very basic thing for all you CF gurus out there ;) but as
a newbie this is turning into a bit of a nightmare for me. I'm currently
building my first Content Management System and at the moment I'm working on
the 'Amend record' form.
The problem I've got is that I need to update two records in two tables at
the same time. Here's my query so far:
<cfif isDefined('form.TitleTxt')>
<cfquery datasource="#application.datasource#" name="update_record">
UPDATE Tour_data,Tour_dates
SET Tour_data.title = '#form.TitleTxt#'
, Tour_data.subtitle = '#form.SubtitleTxt#'
, Tour_data.resorttitle = '#form.ResortTitleTxt#'
, Tour_data.resort = '#form.ResortTxt#'
, Tour_data.description = '#form.descriptionTxt#'
, Tour_data.bulletlist = '#form.BulletlistTxt#'
, Tour_data.numberofdays = '#form.NoOfDaysTxt#'
, Tour_data.departsfrom = '#form.DepartsFromTxt#'
, Tour_data.returnsto = '#form.ReturnsToTxt#'
, Tour_data.farefrom = '#form.FareFromTxt#'
, Tour_data.supplements = '#form.SupplementTxt#'
, Tour_data.region = '#form.RegionTxt#'
, Tour_data.miscellaneous = '#form.MiscTxt#'
, Tour_dates.departuredates = '#form.DepartureDateTxt#'
, Tour_dates.returndates = '#form.ReturnsToTxt#'
, Tour_dates.fares = '#form.FaresTxt#'
WHERE Tour_data.Tour_ID=#url.ID# AND Tour_dates.Date_ID=#url.Tour_ID#
</cfquery>
</cfif>
The error message I get is:
ODBC Error Code = 22005 (Error in assignment) [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
expression. The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (8:2) to (8:69).
Query String: ID=568&Tour_ID=260
---------
I'm guessing it's one of two things. Either my SQL isn't right or I'm trying
to insert content into one of the fields which isn't the right type.
The majoirty of fields in the DB are Text or Memos apart from Depaturedates,
Returndates which are Date/Time. Would these need to be formatted before I
insert them into the DB or would Access accept them as strings?
Hope someone can help.
Cheers,
James
-- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
