Skip to main content
A
Anonymous
May 18, 2007
Question

Inserting into Two Tables from one form

  • May 18, 2007
  • 7 replies
  • 742 views
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

This topic has been closed for replies.

7 replies

A
Anonymous
May 22, 2007
I got this working thanks for everyones help

A
Anonymous
May 22, 2007
Does this mean I add booking_id like you have above
A
Anonymous
May 21, 2007
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
A
Anonymous
May 19, 2007
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>


P
paross1
Participating Frequently
May 21, 2007
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
A
Anonymous
May 18, 2007
Sorry about the blank response. I tried using the code you sent and got the message that my booking_id (theNewId) doesn't exist.
P
paross1
Participating Frequently
May 18, 2007
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
A
Anonymous
May 18, 2007


P
paross1
Participating Frequently
May 18, 2007
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
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices