Passing Parameters to SQL

[khurram]

I am having the following problem. When a customer initally chooses to display our product, I want to pass an initialization parameter ("DEFAULT&quot to an sp in SQL 7 to display the default settings. Once the customer makes some modifications using drop-down boxes, he clicks the update button and then when the form posts, the sp will be pass the modifications as one parameters and make the changes accordingly.

<CFSTOREDPROC procedure=&quot;CL_SystemConfiguration&quot; datasource=&quot;builder&quot;>
<CFIF FORM.operation IS &quot;Update&quot;>
<CFPROCPARAM type=&quot;In&quot; cfsqltype=&quot;CF_SQL_VARCHAR&quot; dbvarname=&quot;@CustomOptions&quot; value=&quot;SelectOption&quot; null=&quot;No&quot;>
<CFELSE>
<CFPROCPARAM type=&quot;In&quot; cfsqltype=&quot;CF_SQL_VARCHAR&quot; dbvarname=&quot;@CustomOptions&quot; value=&quot;DEFAULT&quot; null=&quot;No&quot;>
</CFIF>

 

[GunJack]

So what is the problem you're having? If CF is throwing an error, I would try moving the <cfif> tags outside of the <cfproc></cfcproc> tags as below. I don't use <cfstoredproc> so I'm not familiar with it but I would suspect it may not allow embedded <cfif> tags. I assume you do have a terminating </cfstoredproc> tag as well.

GJ

<CFIF FORM.operation IS &quot;Update&quot;>

<CFSTOREDPROC procedure=&quot;CL_SystemConfiguration&quot;
datasource=&quot;builder&quot;>

<CFPROCPARAM type=&quot;In&quot; cfsqltype=&quot;CF_SQL_VARCHAR&quot;
dbvarname=&quot;@CustomOptions&quot; value=&quot;SelectOption&quot; null=&quot;No&quot;>
</cfstoredproc>

<CFELSE>

<CFSTOREDPROC procedure=&quot;CL_SystemConfiguration&quot;
datasource=&quot;builder&quot;>

<CFPROCPARAM type=&quot;In&quot; cfsqltype=&quot;CF_SQL_VARCHAR&quot;
dbvarname=&quot;@CustomOptions&quot; value=&quot;DEFAULT&quot; null=&quot;No&quot;>
</cfstoredproc>
</CFIF>

 

[khurram]

The problem is that I need to have the page execute the CFELSE statement when the product is viewed initally and execute the CFIF statment once some modifications are made and the Update button is pressed. The Update button is on this page.

 

[GunJack]

I still may not understand what you're trying to do but I think you might could use <cfif isdefined(&quot;form.update&quot> .... <cfelse> .... </cfif>. It sounds like you're saying the page is executed in two different contexts.

GJ

 

[khurram]

I've got the perfect example. When you go to Dell.com's website, you can configure a computer for yourself. When you decide on the system you want to configure, you are given some initial choices (a PIII, 20GB HD, etc.). When you make some changes and then click Update, you're given an updated price, etc.

That's what I need to do. When the customer click Update, I want an updated price, otherwise, just show the initial choices.

Hope this helps and I appreciate the effort.

 

[GunJack]

Ok, then I think my last post would be accurate. The first time they load the page, there will not be a form.update passed in so the second part of the <cfif> would execute. Clicking the update button would then pass in your form variable operation and the <cfif isdefined(&quot;form.operation&quot> would evalute to true and execute the first part. I realized that I mis-labled your form variable in my last post as &quot;form.update&quot; when you had it as &quot;form.operation&quot;. I'm assuming this is either the name of your submit button or a hidden input variable.

Let me know if this works.
GJ

 

[khurram]

Thanks GJ. It looks it works. I not in front of the development server but I ran a little test and it looks fine.

Appreciate it.

 

[khurram]

Now I am running into a different problem. On loading initially, everything runs okay. When you update some items and press Update, I get the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'SelectOption' to a column of data type int.

The error occurred while processing an element with a general identifier of (CFSTOREDPROC), occupying document position (7:1) to (7:70).

The funny thing is that the input parameter in the SQL sp is varchar and NOT int. I don't know where this is coming from.

I've included some of the code that I think will be relevant:

<CFSTOREDPROC procedure=&quot;CL_SystemConfiguration&quot; datasource=&quot;builder&quot;>
<CFIF ISDEFINED(&quot;FORM.operation&quot>
<CFPROCPARAM type=&quot;In&quot; cfsqltype=&quot;CF_SQL_VARCHAR&quot; dbvarname=&quot;@CustomOptions&quot; value=&quot;SelectOption&quot; null=&quot;No&quot;>
<CFELSE>
<CFPROCPARAM type=&quot;In&quot; cfsqltype=&quot;CF_SQL_VARCHAR&quot; dbvarname=&quot;@CustomOptions&quot; value=&quot;DEFAULT&quot; null=&quot;No&quot;>
</CFIF>
<CFPROCRESULT name=&quot;SystemConfiguration&quot;></CFSTOREDPROC>
<CFFORM action=&quot;system.cfm&quot; method=&quot;POST&quot; enablecab=&quot;Yes&quot;>
<CFOUTPUT query=&quot;SystemConfiguration&quot; group=&quot;MainOptionID&quot;>

... and so on ...

 

[GunJack]

I don't do a lot with SPs but I think your error has to do with the coding inside the stored proc. It looks like you're doing something once you pass the variable in that's causing the error. Is it possible you're comparing it to or trying to insert it into a field which is an int?

GJ