DYNAMIC SQL QUESTION

[alexfusion]

Hello everyone!
I must say thank you for the excellent help I get every time I post a question.
I will try to be clear:
I have a search form for an Intranet.The employee fill the form field entering for example the name of a customer.The action page is a new form that checks if the customer exists in the database.If this condition is true the form in the action page generate dinamically the fields containing the information about the customer,such as first and last name,address,etc.That is fine.
The problem is when the customer is not in the database.Because of the form fields are generated dinamically the fields don't appear.
Example code:
Action page:
<cfquery name=&quot;checkcustomer&quot; datasource=&quot;datasourcename&quot; maxrows=1 dbtype=&quot;ODBC&quot;>
SELECT customername FROM company_table
WHERE customername='#form.search#'
</cfquery>
<! as you can see search is the form field name of the search form ----->
<cfoutput query=&quot;checkcustomer&quot;>
<input type=&quot;text&quot;name=&quot;customername&quot; value=&quot;#nombre_cliente#&quot;>
<!-----as you can see here &quot;value&quot; is the content of the database field------------->
</ccfoutput>
Here is the problem:if the customer is not present in the database the field is not generated.
I need that if the customer is not in the database the field appears empty,that is ,the HTML attribute &quot;value&quot; would be nothing or blank.
I know that this is very complex to explain but I am trying to be clear.
Could you help me to find a solution?

Thank you everyone !!!

alexfusion

 

[tlhawkins]

Hello,
I would try something like this:

<cfquery name=&quot;checkcustomer&quot; datasource=&quot;datasourcename&quot; maxrows=1 dbtype=&quot;ODBC&quot;>
SELECT customername FROM company_table
WHERE customername='#form.search#'
</cfquery>
<CFIF #checkcustomer.recordcount# is 0>
<CFOUTPUT>Search Returned no Results Hit Back to try another Search Or...
<input type=&quot;text&quot; name=&quot;customername&quot; value=&quot;Enter Name&quot;></CFOUTPUT>
<!--- Those Are just Suggestions, Whatever you Need to do when the Search Returns Nothing Can GO in HERE --->
<CFELSE>
<cfoutput query=&quot;checkcustomer&quot;>
<input type=&quot;text&quot;name=&quot;customername&quot; value=&quot;#nombre_cliente#&quot;>
</cfoutput>
</CFIF>

Hope it helps. (I hope I understood the Question)

Have fun ...

 

[bhargava]

hi,

in simple plain terms

use CFIF construct to check if the query returns results, if it does then assign the required value to the variable and use that in the input statement, else not then assign nothing to the variable and display that in the inout stmt.

----------------

<CFIF #MyQuery.RecordCount# NEQ 0>
<CFSET nombre_client = 'Enter the value'>
<CFELSE>
<CFOUTPUT QUERY=#MyQuery#>
<CFSET nombre_client = #field_value#
</CFOUTPUT>
</CFIF>

<CFOUTPUT QUERY=#MyQuery#>
<INPUT name='customername' TYPE='text' value=#nombre_client#>
</CFOUTPUT>

-----------------

Hope this helps.

Amit