Conditional Inserts

[khurram]

I trying to insert information in a SQL database. A client can either select an option from a drop-down box and click add and it will be inserted into the database or type the information into text boxes and insert it.

I am also using a radio option box (SelectSubOption) to specify which method the customer will use.

Here's the code I have but it doesn't seem to work.

<CFIF #SelectSubOption# IS &quot;New&quot;>
<CFINSERT datasource=&quot;builder&quot; tablename=&quot;SubOptionsDetails&quot; dbtype=&quot;ODBC&quot; formfields=&quot;SystemID, MainOptionID, Qty, VN_ComboKey, SubOptionDescription, DefaultValue, Visible, SubOptionSortOrder&quot;>
<CFELSEIF #SelectSubOption# IS &quot;Existing&quot;>
<CFINSERT datasource=&quot;builder&quot; tablename=&quot;SubOptionsDetails&quot; dbtype=&quot;ODBC&quot; formfields=&quot;SystemID, MainOptionID, Qty, SelectVN_ComboKey, SelectSubOptionDescription, DefaultValue, Visible, SubOptionSortOrder&quot;>
</CFIF>

 

[GunJack]

Hey khurram,

When you say it doesn't work, does it give an error or just not insert correctly? If it's not inserting correctly, does it work one way and not the other? In other words, does if work if they choose the select box, the text box, or does it not insert either way?

If it's not generating an error, then I suspect the problem may be in the form which submits it. Could you post the code from <form> to </form> in this case?

GJ

 

[khurram]

Hi GJ,

Here's the code you asked for. In terms of it not working, I don't get an error message but I doesn't insert anything at all, whether I select an option or just type a new one.

Thanks for you help.

<CFFORM action=&quot;edit.cfm?systemid=#urlencodedformat(systemid)#&quot; method=&quot;POST&quot; enablecab=&quot;Yes&quot;>
<P><B>1. Select Existing Sub-Option:
</B><BR><BR>
<CFSELECT name=&quot;MainOptionID&quot;>
<CFOUTPUT query=&quot;GetMainOption&quot;>
<OPTION value=&quot;#GetMainOption.MainOptionID#&quot;>#GetMainOption.MainOptionDescription#</OPTION>
</CFOUTPUT>
</CFSELECT><!-- Select box for choosing an item if its already in the database -->
<CFQUERY name=&quot;GetSubOption&quot; datasource=&quot;builder&quot; dbtype=&quot;ODBC&quot;>
SELECT SubOptionsDetails.VN_ComboKey AS SelectVN_ComboKey,
SubOptionsDetails.SubOptionDescription AS SelectSubOptionDescription,
MainOptions.MainOptionSortOrder,
SubOptionsDetails.SubOptionSortOrder
FROM SubOptionsDetails INNER JOIN
MainOptions ON
SubOptionsDetails.MainOptionID = MainOptions.MainOptionID
ORDER BY MainOptions.MainOptionSortOrder, SubOptionsDetails.SubOptionSortOrder
</CFQUERY>
<CFSELECT name=&quot;ChooseSubOption&quot;>
<CFOUTPUT query=&quot;GetSubOption&quot;>
<OPTION value=&quot;#GetSubOption.SelectVN_ComboKey#&quot;>#GetSubOption.SelectSubOptionDescription#</OPTION>
</CFOUTPUT>
</CFSELECT>
<P>&amp;nbsp;OR&amp;nbsp;
<P><B>2. Create New Sub-Option:
</B><BR><BR>
<TABLE>
<TR>
<TD>SysID
</TD>
<TD>QTY
</TD>
<TD>Product Code
</TD>
<TD>Description
</TD>
<TD>DV
</TD>
<TD>VS
</TD>
<TD>Sort Order
</TD>
</TR>
<TR>
<TD>
<CFINPUT type=&quot;Text&quot; name=&quot;SystemID&quot; value=&quot;#URL.systemid#&quot; required=&quot;No&quot; size=&quot;5&quot; maxlength=&quot;5&quot;>
</TD>
<TD>
<CFINPUT type=&quot;Text&quot; name=&quot;Qty&quot; value=&quot;1&quot; required=&quot;No&quot; size=&quot;5&quot; maxlength=&quot;5&quot;>
</TD>
<TD>
<CFINPUT type=&quot;text&quot; name=&quot;VN_ComboKey&quot; size=&quot;20&quot; maxlength=&quot;50&quot;>
</TD>
<TD>
<CFINPUT type=&quot;Text&quot; name=&quot;SubOptionDescription&quot; required=&quot;No&quot; size=&quot;50&quot; maxlength=&quot;100&quot;>
</TD>
<TD>
<CFINPUT type=&quot;Checkbox&quot; name=&quot;DefaultValue&quot; checked=&quot;No&quot;>
</TD>
<TD>
<CFINPUT type=&quot;Checkbox&quot; name=&quot;Visible&quot; checked=&quot;Yes&quot;>
</TD>
<TD>
<CFINPUT type=&quot;Text&quot; name=&quot;SubOptionSortOrder&quot; required=&quot;No&quot; size=&quot;5&quot; maxlength=&quot;5&quot;>
</TD>
</TR>
</TABLE>
<P>
<INPUT type=&quot;submit&quot; name=&quot;submitform&quot; value=&quot;Add&quot;></CFFORM>

 

[GunJack]

I don't see the radio button &quot;SelectSubOption&quot; you mentioned in your first post which dictates which of the two inserts occurs. Without this input, neither one of the inserts will occur. Am I missing someting?

GJ

 

[khurram]

Sorry about that. I removed some code so I could continue working. Here's everything in one shot:

<CFIF #SelectInputType# IS &quot;new&quot;>
<CFQUERY name=&quot;ExistingVN_ComboKey&quot; datasource=&quot;builder&quot;>
SELECT VN_ComboKey
FROM SubOptions
WHERE VN_ComboKey = '#Form.VN_ComboKey#'
</CFQUERY>
<CFIF ExistingVN_ComboKey.RecordCount IS 0>
<CFINSERT datasource=&quot;builder&quot; tablename=&quot;SubOptions&quot; dbtype=&quot;ODBC&quot; formfields=&quot;VN_ComboKey&quot;>
</CFIF>
<CFINSERT datasource=&quot;builder&quot; tablename=&quot;SubOptionsDetails&quot; dbtype=&quot;ODBC&quot; formfields=&quot;SystemID, MainOptionID, Qty, VN_ComboKey, SubOptionDescription, DefaultValue, Visible, SubOptionSortOrder&quot;>
<CFELSEIF #SelectInputType# IS &quot;existing&quot;>
<CFINSERT datasource=&quot;builder&quot; tablename=&quot;SubOptionsDetails&quot; dbtype=&quot;ODBC&quot; formfields=&quot;SystemID, MainOptionID, Qty, SelectVN_ComboKey, SelectSubOptionDescription, DefaultValue, Visible, SubOptionSortOrder&quot;>
</CFIF>
<!--Accept inputs for new option -->
<CFFORM action=&quot;edit.cfm?systemid=#urlencodedformat(systemid)#&quot; method=&quot;POST&quot; enablecab=&quot;Yes&quot;>
<P>
<CFINPUT type=&quot;Radio&quot; name=&quot;SelectInputType&quot; value=&quot;existing&quot;><B>1. Select Existing Sub-Option:
</B><BR><BR>
<CFSELECT name=&quot;MainOptionID&quot;>
<CFOUTPUT query=&quot;GetMainOption&quot;>
<OPTION value=&quot;#GetMainOption.MainOptionID#&quot;>#GetMainOption.MainOptionDescription#</OPTION>
</CFOUTPUT>
</CFSELECT><!-- Select box for choosing an item if its already in the database -->
<CFQUERY name=&quot;GetSubOption&quot; datasource=&quot;builder&quot; dbtype=&quot;ODBC&quot;>
SELECT SubOptionsDetails.VN_ComboKey AS SelectVN_ComboKey,
SubOptionsDetails.SubOptionDescription AS SelectSubOptionDescription,
MainOptions.MainOptionSortOrder,
SubOptionsDetails.SubOptionSortOrder
FROM SubOptionsDetails INNER JOIN
MainOptions ON
SubOptionsDetails.MainOptionID = MainOptions.MainOptionID
ORDER BY MainOptions.MainOptionSortOrder, SubOptionsDetails.SubOptionSortOrder
</CFQUERY>
<CFSELECT name=&quot;ChooseSubOption&quot;>
<CFOUTPUT query=&quot;GetSubOption&quot;>
<OPTION value=&quot;#GetSubOption.SelectVN_ComboKey#&quot;>#GetSubOption.SelectSubOptionDescription#</OPTION>
</CFOUTPUT>
</CFSELECT>
<P>&amp;nbsp;OR&amp;nbsp;
<P><B>
<CFINPUT type=&quot;Radio&quot; name=&quot;SelectInputType&quot; value=&quot;new&quot;>2. Create New Sub-Option:
</B><BR><BR>
<TABLE>
<TR>
<TD>SysID
</TD>
<TD>QTY
</TD>
<TD>Product Code
</TD>
<TD>Description
</TD>
<TD>DV
</TD>
<TD>VS
</TD>
<TD>Sort Order
</TD>
</TR>
<TR>
<TD>
<CFINPUT type=&quot;Text&quot; name=&quot;SystemID&quot; value=&quot;#URL.systemid#&quot; required=&quot;No&quot; size=&quot;5&quot; maxlength=&quot;5&quot;>
</TD>
<TD>
<CFINPUT type=&quot;Text&quot; name=&quot;Qty&quot; value=&quot;1&quot; required=&quot;No&quot; size=&quot;5&quot; maxlength=&quot;5&quot;>
</TD>
<TD>
<CFINPUT type=&quot;text&quot; name=&quot;VN_ComboKey&quot; size=&quot;20&quot; maxlength=&quot;50&quot;>
</TD>
<TD>
<CFINPUT type=&quot;Text&quot; name=&quot;SubOptionDescription&quot; required=&quot;No&quot; size=&quot;50&quot; maxlength=&quot;100&quot;>
</TD>
<TD>
<CFINPUT type=&quot;Checkbox&quot; name=&quot;DefaultValue&quot; checked=&quot;No&quot;>
</TD>
<TD>
<CFINPUT type=&quot;Checkbox&quot; name=&quot;Visible&quot; checked=&quot;Yes&quot;>
</TD>
<TD>
<CFINPUT type=&quot;Text&quot; name=&quot;SubOptionSortOrder&quot; required=&quot;No&quot; size=&quot;5&quot; maxlength=&quot;5&quot;>
</TD>
</TR>
</TABLE>
<P>
<INPUT type=&quot;submit&quot; name=&quot;submitform&quot; value=&quot;Add&quot;></CFFORM>

 

[GunJack]

I assume you renamed the radio button from &quot;SelectSubOption&quot; to &quot;SelectInputType&quot;. I took your form and removed the queries and inserts and basicly it's working correctly. When I choose either new or existing, it executes the proper section of the <cfif> so I suspect the problem is with your query. I would take each of the two insert blocks and move them outside of the <cfif>..<cfelse>...</cfif> section one at a time. Take the section for &quot;new&quot; first and put it directly before the <cfif> so you know it will execute. Then submit the page just as you would for a &quot;new&quot; entry. If it still doesn't insert a record, I would change the <cfinsert> to a <cfquery> and write out the insert manually. If there is some problem keeping cfinsert from putting the data into the database, you should then see the error returned from the odbc driver. If it still doesn't insert with an insert from within <cfquery> and you don't get an error message, I would then change the insert so that you have hard coded values like this
<cfquery ....
insert into SubOptions
(VN_ComboKey)
values(2) <!--- or some appropriate value --->
</cfquery>

Let me know what happens.
GJ

 

[khurram]

I am going to give it a try but I had a better idea. Is it possible to select an existing item and have it populate the input fields of the new item? This way, the customer can save the time on typing but if the option needs to changed slightly, it can be.

Thanks.

 

[GunJack]

You could but then you would have to do a query to get the existing values for all fields in question, compare each one against the form values, and based on the results, do a cfinsert if they are different. This probably wouldn't be easier to process but would make for an easier to understand form for the user.

If, however, you are going to do an insert though even if they don't change anything, then this would probably be the best route. You would just populate the fields and then insert the values whether they changed them or not.

GJ

 

[khurram]

Exactly what I was thinking. The way I think it shoud be done is by having a submit button and then (this where I get confused) having the values in the select boxes populate the input fields.

Thanks again.