9 Replies Latest reply on Dec 6, 2007 12:37 PM by paross1

    RecordCount Question

    newportri Level 1
      Greetings

      I don't know why this is not inserting the data if the maximum number of records (seats at a conference) has not been reached:

      <CFQUERY NAME="count_num" DATASOURCE="whatever">
      SELECT attend_session
      FROM main
      WHERE attend_session = #Form.attend_session#
      </cfquery>

      <cfoutput query="count_num">

      <cfif #count_num.RecordCount# GTE 30>
      <cflocation url="full.cfm" addtoken="No">

      <cfelse>


      <cfquery name="add_attendee" datasource="whatever">
      INSERT INTO main
      VALUES ('#Form.attend_lname#',
      '#Form.attend_fname#',
      #Form.attend_session#)
      </cfquery>
      Thank you...etc.
      </cfif></cfoutput>

      Any help with this otherwise simple task :>( would be appreciated.
        • 1. Re: RecordCount Question
          cf_dev2 Level 1
          Instead of returning all records in the table you can count the total seats like this

          <!--- or possibly SELECT COUNT(*) AS NumberAttending .... --->
          <CFQUERY NAME="count_num" DATASOURCE="whatever">
          SELECT COUNT(Attend_Session) AS NumberAttending
          FROM main
          WHERE attend_session = #Form.attend_session#
          </CFQUERY>

          You don't need to loop through the query. Just use

          <cfif count_num.NumberAttending LT 30>
          do the insert
          <cfelse>
          <cflocation url="full.cfm" addtoken="No">
          </cfif>

          Though you should use cfqueryparam and use a column list in your insert (if you're not doing that already)
          • 2. Re: RecordCount Question
            newportri Level 1
            Thanks- this works now...

            It did not work at first using the <cfquery>INSERT INTO but works fine using <CFINSERT>

            Don't know why but thanks again.

            rinorman
            • 3. Re: RecordCount Question
              cf_dev2 Level 1
              What was the error?

              Personally I would avoid CFINSERT. A regular sql INSERT is the way to go.
              • 4. Re: RecordCount Question
                newportri Level 1
                Probably my mistake in the INSERT (see in first message) but the error is:
                "Number of query values and destination fields are not the same. "
                • 5. Re: RecordCount Question
                  cf_dev2 Level 1
                  Use a column list in your insert statement. In other words, explicitly specify which values you're inserting into which columns. Its not always required, but its a good practice. It helps avoid inserting values into the wrong columns.

                  INSERT INTO main ( TheLastNameColumn, TheFirstNameColumn, TheSessionColumn)
                  VALUES ('#Form.attend_lname#', '#Form.attend_fname#', #Form.attend_session#)
                  • 6. Re: RecordCount Question
                    cf_dev2 Level 1
                    Also, consider using <cfqueryparam> for your form values.
                    • 7. Re: RecordCount Question
                      Dan Bracuk Level 5
                      quote:

                      Originally posted by: newportri
                      Probably my mistake in the INSERT (see in first message) but the error is:
                      "Number of query values and destination fields are not the same. "

                      You do understand that message, right?
                      • 8. Re: RecordCount Question
                        newportri Level 1
                        Yes- I did not use column lists - or yes, I will consider using <cfqueryparam> .
                        • 9. Re: RecordCount Question
                          paross1 Level 2
                          You should always use column lists, because if you don't, and the database table is modified by adding a new column, etc., all of your insert statements will fail. If you do use explicit column lists, then a column may be added to a table (if it is given a default value) without invalidating your INSERT statments. Of course, an insert would need to be modified to add the column if you need to insert values to that column with that insert statement, but it is just a good practice to explicitly list your columns so that you can match them up with the inserted values.

                          Phil