• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Inserting into Two Tables from one form

Guest
May 18, 2007 May 18, 2007

Copy link to clipboard

Copied

I have one form to capture customer information, which goes into the customer table in a MS SQL Express 2005 database. On the same form, I have booking information such as arrival date, departure date, etc, that needs to update the booking table.

I have it so the booking update is completed first, what I need to do then is find some way using ColdFusion to get the booking id and then pass it to the customer table in order to add the data to the customer table.

I have tried using two SQL Insert statements and even two <cfinsert> queries but get the following error.

Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][SQL Native Client][SQL Server]Cannot insert the value NULL into column 'booking_id', table 'Rayanne.dbo.customer'; column does not allow nulls. INSERT fails.

Any ideas how I can get this sorted. I need the booking ID not to be null

TOPICS
Advanced techniques

Views

663

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
May 18, 2007 May 18, 2007

Copy link to clipboard

Copied

Something like this?

<cfquery>
SET NOCOUNT ON

INSERT INTO yourFirstTable (colNames....)
Values(#colVals#.....)

SELECT SCOPE_IDENTITY() AS theNewId;

SET NOCOUNT OFF
</cfquery>


<cfquery>
INSERT INTO yourSecondTable (colNames....)
Values(#theNewId#.....)
</cfquery>

.....or...

You can use CFTRANSACTION tags to enclose the first insert, then a query to select MAX(firstIDval) from your first insert, then a second insert that uses the ID value seleced in the query.

Phil

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 18, 2007 May 18, 2007

Copy link to clipboard

Copied



Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 18, 2007 May 18, 2007

Copy link to clipboard

Copied

Sorry about the blank response. I tried using the code you sent and got the message that my booking_id (theNewId) doesn't exist.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
May 18, 2007 May 18, 2007

Copy link to clipboard

Copied

Since you didn't show your new code I am guessing, but you need scope the theNewId variable with the name of the first query.....

<cfquery>
INSERT INTO yourSecondTable (colNames....)
Values(#first_query_name.theNewId#.....)
</cfquery>

Phil

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 19, 2007 May 19, 2007

Copy link to clipboard

Copied

Hi Phil I used the following code

<cfquery name="qArrivalDates" datasource="rayannesql">
SET NOCOUNT ON

INSERT INTO booking (book_made, book_checkin_date, book_checkout_date, book_adults, book_children)
VALUES('#FORM.book_made#','#FORM.book_checkin_date#','#FORM.book_checkout_date#','#FORM.book_adults#','#FORM.book_children#')

SELECT SCOPE_IDENTITY() AS theNewId;

SET NOCOUNT OFF
</cfquery>



<cfquery name="qArrivalDates" datasource="rayannesql">
INSERT INTO Customer( firstname, lastname, address, address2, city, state, postalcode, country, phone, mobile, email, notes)
Values (#qArrivalDates.theNewId# '#FORM.firstname#', '#FORM.lastname#', '#FORM.address#', '#FORM.address2#', '#FORM.city#', '#FORM.state#', '#FORM.postalcode#', '#FORM.country#', '#FORM.phone#', '#FORM.mobile#', '#FORM.email#', '#FORM.notes#' )
</cfquery>

When I tried to complete the form, I got the following error

Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][SQL Native Client][SQL Server]Incorrect syntax near 'Fred'.

The error occurred in C:\Inetpub\wwwroot\rayanne\customerinsertsql.cfm: line 16

14 : <cfquery name="qArrivalDates" datasource="rayannesql">
15 : INSERT INTO Customer( firstname, lastname, address, address2, city, state, postalcode, country, phone, mobile, email, notes)
16 : Values (#qArrivalDates.theNewId# '#FORM.firstname#', '#FORM.lastname#', '#FORM.address#', '#FORM.address2#', '#FORM.city#', '#FORM.state#', '#FORM.postalcode#', '#FORM.country#', '#FORM.phone#', '#FORM.mobile#', '#FORM.email#', '#FORM.notes#' )
17 : </cfquery>


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
May 21, 2007 May 21, 2007

Copy link to clipboard

Copied

Well, one obvious problem is that in your second insert cfquery, you list 12 fields, but are inserting 13 values. If you notice, your first column listed is firstname, but you are attempting to insert the new ID value into that column (and there is no comma between this field and the firstname column value variable either). You need to list the booking_id colunm name if you want to insert a value. NOTE: This booking_id column can NOT be an IDENTITY field in the Customer table because the database will attempt to generate the next key value.

<cfquery name="qArrivalDates" datasource="rayannesql">
INSERT INTO Customer( firstname, lastname, address, address2, city, state, postalcode, country, phone, mobile, email, notes)
Values ( #qArrivalDates.theNewId# '#FORM.firstname#', '#FORM.lastname#', '#FORM.address#', '#FORM.address2#', '#FORM.city#', '#FORM.state#', '#FORM.postalcode#', '#FORM.country#', '#FORM.phone#', '#FORM.mobile#', '#FORM.email#', '#FORM.notes#' )
</cfquery>

probably should be

<cfquery name="qArrivalDates" datasource="rayannesql">
INSERT INTO Customer(booking_id, firstname, lastname, address, address2, city, state, postalcode, country, phone, mobile, email, notes)
Values (#qArrivalDates.theNewId#, '#FORM.firstname#', '#FORM.lastname#', '#FORM.address#', '#FORM.address2#', '#FORM.city#', '#FORM.state#', '#FORM.postalcode#', '#FORM.country#', '#FORM.phone#', '#FORM.mobile#', '#FORM.email#', '#FORM.notes#' )
</cfquery>

Phil

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 20, 2007 May 20, 2007

Copy link to clipboard

Copied

Going along with Phil's idea, and if SQL express acts anything like SQL 2000 or SQL 2K5 you could do the following:

<cfquery name="qry_insertRecord" datasource="#this.dsn#">
INSERT yourFirstTable (columnList)
VALUES (valueList);
SELECT @@IDENTITY as firstQueryPrimaryKey
</cfquery>

<cfset pk= insertinsertRecord.firstQueryPrimaryKey>

Then in your second query, just use pk as your variable for the primary key from the first query

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 22, 2007 May 22, 2007

Copy link to clipboard

Copied

Does this mean I add booking_id like you have above

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 22, 2007 May 22, 2007

Copy link to clipboard

Copied

LATEST
I got this working thanks for everyones help

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation