CF - Form submit with session ID

well first off change the default to this

<cfparam name="form.employee_data.ID" default="#SESSION.ID#">

second if you have a session variable you can reuse it anywhere in your code. read the life spam of the session variable

And if you trying to pass it to the next page just do this

 <cfqueryparam value="#session.ID#" cfsqltype="cf_sql_integer">

It is actually bombing on this line

<cfqueryparam value="#reason#" cfsqltype="CF_SQL_VARCHAR">

which is one below this

<cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer">

with this error

Invalid data '' for CFSQLTYPE CF_SQL_INTEGER.

The table with #reason# is nvarchar(max) - i don't see where the sql_integer is coming from

what the form field name associated with this name (reason), and if it was blank does the database allows null values

It had text in it and the table does allow null - here is the form field


          <td><cftextarea name="Reason"></cftextarea></td>

well then try changing your cfqueryparam to this

<cfqueryparam value="#form.reason#" cfsqltype="CF_SQL_VARCHAR">.

I had tried that before - I forgot to add the form part in my EE posting - same result.  It must be the session.id value instead of the number for the ID from the table - I thought I was passing it wrong

It must be trying to send the session.id value I mean

Enable coldfusion debugger in coldfsuion administrator and is what causing the issue

I enabled the options under Enable AJAX Debug Log Window and Enable Request Debugging Output but I still get the same error

what is error message telling you now can you post your/ insert/update statement here so I can see the syntax

Invalid data '' for CFSQLTYPE CF_SQL_INTEGER.  
 
 
The error occurred in C:\inetpub\wwwroot\maxibillion_CF\send_maxi.cfm: line 33
 
31 :         <cfqueryparam value="#form.SendMaxi#" cfsqltype="cf_sql_integer"> ,
32 :         <cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer"> ,
33 :          <cfqueryparam value="#form.reason#" cfsqltype="CF_SQL_VARCHAR">.
34 :     )
35 :  
 



<cfif structKeyExists(FORM, "submitButton")>
  <cfquery name="enterdetails" datasource="IntranetDB_Coldfusion">
    INSERT INTO maxibillions (RecordID, to_id, from_id, maxi_reason)
    VALUES
    (
        <cfqueryparam value="#form.RecordID#" cfsqltype="cf_sql_integer"> ,
        <cfqueryparam value="#form.SendMaxi#" cfsqltype="cf_sql_integer"> ,
        <cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer"> ,
         <cfqueryparam value="#form.reason#" cfsqltype="CF_SQL_VARCHAR">.
    )
   
    </cfquery>
</cfif>

what does coldfusion debugger tells you. IS table have a primary key and it set to identity

I enabled the options under Enable AJAX Debug Log Window and Enable Request Debugging Output but I still get the same error

Yes the identity is on

I commented out the reason line and it now bombs on this

 <cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer"> with the same error - the ID does not seem to be sending

Wait - what is the syntax to do an insert when the table has identity turned on?  There is no value for Form.Record.ID - that is the auto increment field in the table so I don't have anything to insert

that is correct , you said that the result is the required filed and you took it away. put some default value there

I gave this value
<cfparam name="form.RecordID" default="1">

and it bombed on line 33


Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver]Invalid parameter binding(s).  
 
The error occurred in C:\inetpub\wwwroot\maxibillion_CF\send_maxi.cfm: line 33
 
31 :        <cfqueryparam value="#form.SendMaxi#" cfsqltype="cf_sql_integer"> ,
32 :        <cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer"> '
33 :       <cfqueryparam value="#form.reason#" cfsqltype="CF_SQL_VARCHAR"> '
34 :     )

hi,

just do one thing.

use
<cfdump var="#form#">
<cfdump var="#session#">
above query.

check the form value then compare with given datatype.

you will get the answer.


- Bhavesh

I think I see what the problem is - this select

<cfselect name="DisplayMaxi"><cfoutput query="getSendMaxi">
              <cfif #maxi_limit# NEQ 0>
                <cfloop index="i" from="1" to = "#maxi_limit#">
                  <option value="#i#">#i#</option>
                </cfloop>
                <cfelse>
                None
              </cfif>
            </cfoutput></cfselect>

It is looping through the table and displaying how many maxi's are available to send - then it is supposed to insert a single to_id, from_id, maxi_reason to the table for each maxi that was selected.  So if 5 were selected in the drop down - then five records with the from id and the to id and the reason would be sent to the table.  The way it is now it is sending the value '5' to the table and that is why there is an error - how would this be done in CF?  Inserting one record for each maxi selected?

<cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer"> ,

The type  cf_sql_integer requires the value to be an integer.  So if the value field is empty it would fail and if the form field is not a number it will fail.

You can ensure a numeric value by adding val() around the variable

 <cfqueryparam value="#val(form.employeeID)#" cfsqltype="cf_sql_integer"> ,

Of course this may hide a bug if your code if you have a string or empty value being converted to a 0 (zero) by the val() function.


From your last post, you suggest that the DisplayMaxi value is a list of integers if someone selected more than one value from the SELECT tag.   However, your select tag is not defined to accept more than one value.  As written, it should only be accepting one value.

So, the problem is likely that the form field for an integer is empty or non-numeric.

Simply adding a dump before the processing should show you the values and you can see which are not numeric..

Put this in the form-processing area of your code, at the top so no errros
<cfdump var="#form#">
<cfexit>


To answer your question, if you do change your SELECT to allow multiple entries and need to enter one per record, you would add a CFLOOP to loop the list and enter one at a time.

By the way, this code doesn't look right.   You have two nested loops inside your CFSELECT which would lead to duplicate values in the list.

For example, let's say your maxi_limit is 10,  you will see on the SELECT list 1...through..10, but you will see that repeated for every record in getSendMaxi.  So if there are 5 records in that query, you will see

1
2
3
..
10
1
2
3
..
10
1
2
3
..
10
1
2
3
..
10
...... five times

<cfselect name="DisplayMaxi"><cfoutput query="getSendMaxi">
              <cfif #maxi_limit# NEQ 0>
                <cfloop index="i" from="1" to = "#maxi_limit#">
                  <option value="#i#">#i#</option>
                </cfloop>
                <cfelse>
                None
              </cfif>
            </cfoutput></cfselect>

Select allOpen in new window

Ok - the dump is displaying the correct data - if I choose five maxi's it tries to insert the value '5' - I want it to loop through one for each - something like this.  But CF does not know what #maxi_limit# is because I can't reference the query

  <cfquery name="enterdetails" datasource="IntranetDB_Coldfusion">
   INSERT INTO maxibillions (to_id, from_id, maxi_reason)
    VALUES
     <cfloop from="1" to="#maxi_limit#" index="i">
    (
       <cfqueryparam value="#form.SendMaxi#" cfsqltype="cf_sql_integer"> ,
       <cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer"> '
      <cfqueryparam value="#form.reason#" cfsqltype="CF_SQL_VARCHAR"> '
    )
    </cfloop>
    </cfquery>
</cfif>

This looks close to what I want to do but still can't get my head around it for my example

http://coldfusionhell.blogspot.com/2009/04/multiple-records-insert-cfloop.html

The nested loop was an example of what I am trying to do - just to insert the from id - to id and reason the same amount of times the maxi amount in the drop box is - if 5 is selected then loop 5 times.  This is the way the program works now in asp and at this time I am supposed to duplicate the process since we are dealing with live data and not rewrite it to a more logical process which I wish I could do

Ok, so each employee will have one or more child records in a table called maxBillions

in addition to the employee's id (column: from_id) there are two columns of that table are to_id, and reason.  

It looks like the CFSELECT "DisplayMaxi" is just the COUNT of the TO_ID and REASONS you are entering, is that right?

So, where are the fields for TO_ID and REASON?

You should have a list of those fields and have a counter part of the name..

this is the idea.,.. but of course you want it formatted and complete..

<cfloop from=1 to=5 index="kk">
  <input type="to_id#kk#" value="">
  <input type="reason#kk#" value="">
</cfloop>

Correct -

to_id is the person being sent to - form value sendMaxi
from_id is the person sending - form value  employeeID
maxi_reason is form value reason

In your example - <cfloop from=1 to=5 index="kk">  I need it to look at what was selected in the
<cfselect name="DisplayMaxi"><cfoutput query="getSendMaxi">
              <cfif #maxi_limit# NEQ 0>
                <cfloop index="i" from="1" to = "#maxi_limit#">
                  <option value="#i#">#i#</option>
                </cfloop>
                <cfelse>
                None
              </cfif>
            </cfoutput></cfselect>

> In your example - <cfloop from=1 to=5 index="kk">  I need it to look at what was selected in the   <cfselect name="DisplayMaxi">

Is this supposed to be the number of entries the user will make?

What is the max number possible?  If it's fairly small, I would skip this step to reduce the complexity of your code (you will have to refresh your page or use js)

Your screen should show a loop that looks like this..  where X is the max number of entries you would allow..  just the other fields to their approprite types SELECT or Text, checkbox,etc..


<cfloop from=1 to=X   index="kk">
  <input type="TO_id#kk#" value="">
  <input type="reason#kk#" value="">
</cfloop>

It is easier to understand if you think of it as money since that is what they are exchanged for.  5 maxi's = $5

Generally people give 5 to 10 when someone at work has done them a favor so it is not a larger number but it can be as little as 1.  Everyone has a different amount to give so that is why the select box code loops through to display the amount available to give.  I wanted that value to be inserted x number of times in the insert statement.  This is how the ASP version worked so i did not think it would be so difficult to get the select box value passed to the insert statement.

I was trying to understand what you want the form to look like.   You have shown a single select statement and you've also indicated you want an array style entry of multiple records at once.   You've indicated you need to choose a TO_ID and a REASON field, but you aren't showing those fields; you only show one select called DisplayMaxi.. which doesn't correspond to either of the fields that need to be saved.    I have shown you the way to do multiple form field records like an array.   Perhaps that's not correct.

As far as I can tell, you should have a form with multiple child records to enter the TO and REASON..

Form May Look like this...

Employee:  [ Bob Smith ]

Department: [ Engineering ]
..etc..

Given To:            Reason:
[  John Smith ]   [ Reimbursement ]
[ Ellen Rose   ]   [ Being Nice         ]
[ Stan Green ]   [  Being green      ]
[                    ]   [                           ]


This is my understanding of what your form would look like based on the data base table you are trying to populate.

If this is not accurate, please clarify how you are going to populate the maxBillions table with what fields from the form and what the form would look like.

The lines 31 to 33 are the form fields

31 :         <cfqueryparam value="#form.SendMaxi#" cfsqltype="cf_sql_integer"> ,
32 :         <cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer"> ,
33 :          <cfqueryparam value="#form.reason#" cfsqltype="CF_SQL_VARCHAR">.

It fails on the SendMaxi - that is a dropdown on the form that displays the amount available to send.  I want the insert statement to loop through and insert 1 record for each maxi selected in the dropdown.  If 3 is selected from the dropdown then I want the insert to loop 3 times - inserting 3 records into the database.  Your array example does not get the value from the dropdown.


<cfif structKeyExists(FORM, "submitButton")>
  <cfquery name="enterdetails" datasource="IntranetDB_Coldfusion">
    INSERT INTO maxibillions (to_id, from_id, maxi_reason)
    VALUES
  **  <cfloop from="1" to="#maxi_limit#" index="i"> ** some sort of loop to do the inserts?
    (
       
        <cfqueryparam value="#form.SendMaxi#" cfsqltype="cf_sql_integer"> ,
        <cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer"> ,
         <cfqueryparam value="#form.reason#" cfsqltype="CF_SQL_VARCHAR">.
    )
   
    </cfquery>
</cfif>

Ok - change of plan - I told them that it can't be just  like the asp version.  I am not going to worry about looping - just insert the value from the form to the table.  I keep getting a Invalid parameter binding - some of the fields in the table are for example a auto id field and two others that fill on the default value like date fills with (getdate()) which is the current date.  Do I have to still account for the field in the insert statement?  I checked the data types and they are int when they need to be etc

Thanks

> It fails on the SendMaxi - that is a dropdown on the form that displays the amount available to send.  I want the insert statement to loop through and insert 1 record for each maxi selected in the dropdown.  If 3 is selected from the dropdown then I want the insert to loop 3 times - inserting 3 records into the database.  Your array example does not get the value from the dropdown.


I would not for the count in the SELECT tag.   The only way this is applicable is if you redraw the screen to show the number of rows of fields that match what they have selected.  For example, if they select 5, you have to redraw the screen to show 5 rows of fields.  You can do a page refresh to do that or javascript.  But it's a hassle that I usually avoid.

The easier way to do it is to simply show a handful of fields, more than you think they will need at any one time.   For example, if you think a person will make 3 entries at once, show 5 fields.   Put the 5 in a HIDDEN text field... I call it RecordCount, then loop through those.

You field names have to be named with a counter and then on your action script you have to get the values from the form variables based on that counter.

As I have shown above.. this is the gist..
<cfset recordCount =5>

<cfloop from=1 to="#recordCount#"   index="kk">
  <input type="TO_id#kk#" value="">
  <input type="reason#kk#" value="">
</cfloop>
<input type="hidden" name="recordCount"  value="#recordCount#">


Then on the action side just loop again for record count..
Accept this time, grab the values..

<cfloop from=1 to="#recordCount#"   index="kk">
  <cfset TO_ID = form["to_id" & kk]>
  <cfset REASON = form["reason" & kk]>

  .. check to see if the values are empty, if not then..
  ... insert/update here....

</cfloop>

> I keep getting a Invalid parameter binding - some of the fields in the table are for example a auto id field and two others that fill on the default value like date fills with (getdate()) which is the current date.  Do I have to still account for the field in the insert statement?  I checked the data types and they are int when they need to be etc


Totally blind without seeing code, but I am guess you might be using cfqueryparam on this and that's where the error is from??

If so, an empty variable will cause this error because cfqueryparam will not pass an empty value as an Integer.   There are two ways around it, you can use val() around your variable to force it to be zero 0.. .or if you want to enter NULL (empty) into the database, then add the NULL paramter of cfqueryparam like this..

  NULL="#not len(myNumber)#"    where myNumber is your variable

I am not doing the loop any more - asp is outdated and there is no reason to do it that way.  I am just trying to submit the values from the form one time as selected.  The dump shows the correct values -  but I keep getting a Invalid parameter binding - some of the fields in the table are for example an auto id field and two others that fill on the default value like date fills with (getdate()) which is the current date.  Do I have to still account for the field in the insert statement?  I checked the data types and they are int when they need to be etc

So with this

NULL="#not len(myNumber)#"    where myNumber is your variable

<cfqueryparam NULL="#not len(fieldintable)#" > ,

you reference the field in the table that has the field that is going to fill in a default value?

<cfquery name="enterdetails" datasource="IntranetDB_Coldfusion">
   INSERT INTO maxibillions (to_id, from_id, maxi_reason,maxi_amount)
    VALUES
    (
      <cfqueryparam value="#form.SendMaxi#" cfsqltype="cf_sql_integer"> ,
      <cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer"> '
      <cfqueryparam value="#form.reason#" cfsqltype="CF_SQL_VARCHAR"> '
      <cfqueryparam value="#form.DisplayMaxi#" cfsqltype="cf_sql_integer"> ,

    )
    </cfquery>
</cfif>
TableDesign.jpg

Here is the other code

<cfquery name="getMembers" datasource="IntranetDB_Coldfusion">
SELECT  *
FROM  employee_data
</cfquery>

<cfquery name="getSendMaxi" datasource="IntranetDB_Coldfusion">
SELECT  *
FROM  employee_data
WHERE    employee_data.ID = '#SESSION.ID#'
</cfquery>

<body class="twoColElsLtHdr">
<div id="container">
<div id="header"> <img src="images/USGI_Masthead.gif" />
  <div id="mainContent">
    <table class="displayTable">
      <cfform action="send_maxi.cfm" method="POST" preloader="no">

        <tr>
        <tr>
          <th>&nbsp;</th>
          <th>&nbsp;</th>
        </tr>
      <tr>
          <td><cfselect name="SendMaxi"> <cfoutput query="getMembers">
              <option value="#ID#">#first_name#, #last_name#</option>
            </cfoutput> </cfselect></td>
            <cfinput type="hidden" name="employeeID" value="#employee_data.ID#" query="getSendMaxi">
        </tr>
        <tr>
          <td><cftextarea name="Reason"></cftextarea></td>
        </tr>
                <tr>
          <td><img src="images/maxibillion.jpg" width="275" height="112"></td>
          <td><cfselect name="DisplayMaxi"><cfoutput query="getSendMaxi">
              <cfif #maxi_limit# NEQ 0>
                <cfloop index="i" from="1" to = "#maxi_limit#">
                  <option value="#i#">#i#</option>
                </cfloop>
                <cfelse>
                None
              </cfif>
            </cfoutput></cfselect></td>
        </tr>

        <tr>
          <td><input type="submit" name="submitButton"  value="Submit"></td>
        </tr>
        <cfoutput query="getSendMaxi">
          <tr>
            <td>You have #Maxi_Limit# e-Maxibillions</td>
        </cfoutput>
      </cfform>
    </table>

Using this as an example...

<cfqueryparam value="#form.DisplayMaxi#" cfsqltype="cf_sql_integer">

Since the type is integer, the value in Form.DisplayMaxi MUST be an integer.  It cannot even be (empty).   If it is emtpy, cfqueryparam will through an error.

You can do this..   this will enter a 0 (zero) for all any empty value..

<cfqueryparam value="#val(form.DisplayMaxi)#" cfsqltype="cf_sql_integer">

Or you can do this...  this will enter NULL (empty) into the database..

<cfqueryparam value="#form.DisplayMaxi#" cfsqltype="cf_sql_integer" NULL="#NOT len(form.DisplayMaxi) #">

Every form field has a value - I see that from the cfdump - how do I reference the fields in the table that do not have a form field?  I choose 5 from the drop down for displaymaxi which is reflected in the dump
dump.jpg
Error.jpg

Wow - good catch - no idea where they came from.  Thanks for the help and I will be back with more - fair warning :)