9 Replies Latest reply on May 22, 2007 1:26 AM by jhutchdublin

    Inserting into Two Tables from one form

    jhutchdublin Level 1
      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

        • 1. Inserting into Two Tables from one form
          paross1 Level 2
          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
          • 3. Re: Inserting into Two Tables from one form
            jhutchdublin Level 1
            Sorry about the blank response. I tried using the code you sent and got the message that my booking_id (theNewId) doesn't exist.
            • 4. Re: Inserting into Two Tables from one form
              paross1 Level 2
              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
              • 5. Re: Inserting into Two Tables from one form
                jhutchdublin Level 1
                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.bo ok_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>


                • 6. Re: Inserting into Two Tables from one form
                  The Albino
                  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
                  • 7. Inserting into Two Tables from one form
                    paross1 Level 2
                    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
                    • 8. Re: Inserting into Two Tables from one form
                      jhutchdublin Level 1
                      Does this mean I add booking_id like you have above
                      • 9. Re: Inserting into Two Tables from one form
                        jhutchdublin Level 1
                        I got this working thanks for everyones help