Populating a ColdFusion Web Edit Form using a Stored Proc 1

[Ceal]

Has anyone successfully created a Stored Procedure which populates a web form using Macromedia's Studio MX (ColdFusion and Dreameweaver MX? (The end result should be an web Edit screen which the user can edit and then pass the changes back to SQL Server.).

 

[FALCONSEYE]

yes yes
all you gotta do is

<cfoutput>
<form....>
<input type="someField" name="someName" value="#YourQueryName.someValue#">
...
</form>
</cfoutput>


hope it helps

http://www.jetansolutions.com

 

[Ceal]

Falconseye,

Thanks for your reply. No luck. Probably something basic I'm doing wrong. Don't blame you if you don't want to review this (I tried it using the SQL Server Northwinds databas) but I just wanted to get back to you.

Ceal

This is my Stored Proc
CREATE PROCEDURE dbo.Update_Northwind_Customers_Table5
@CustomerID_2 nchar(5) = ALFKI,
@CompanyName_2 nvarchar(40) = CompanyName
AS
UPDATE [Northwind].[dbo].[Update_Northwind_Customers_Table5]
SET [CompanyName] = @CompanyName_2
WHERE [CustomerID] = @CustomerID_2
GO

This is my ColdFusion Action Page
<CFSTOREDPROC procedure="dbo.Update_Northwind_Customers_Table5" datasource="Northwind">
<CFPROCPARAM type="IN" dbvarname="@CustomerID_2" value="Form.CustomerID" cfsqltype="CF_SQL_VARCHAR">
<CFPROCPARAM type="IN" dbvarname="@CompanyName_2" value="Form.CompanyName" cfsqltype="CF_SQL_VARCHAR">
</CFSTOREDPROC>
<cfquery name="Recordset_to_Populate_Form" datasource="Northwind">
SELECT dbo.Customers.CustomerID,
dbo.Customers.CompanyName
FROM dbo.Customers
WHERE CustomerID = '#Form.CustomerID#'
</cfquery>

This is my ColdFusion Form Page
<cfoutput>
<FORM action="Action_Page_to_Populate_Form_Fields_Using_Stored_Proc.cfm" method="post" name="FORM" id="FORM">
<input type="hidden" value="ALFKI" name="CustomerID">
<input name="CompanyName" value="#Recordset_to_Populate_Form.CompanyName#" type="text"> CompanyName
<input name="Submit" value="Submit" type="submit">
</form>
</cfoutput>

 

[pigsie]

What error are you getting? Have you checked that there is actually a record in the database already with the customer id of ALFKI also enclose your proc values in pound signs :

#Form.CustomerID# etc.

Does that work?

 

[Ceal]

Pigsie,

Thanks for your reply. I'm getting a fairly consistent
Element COMPANYNAME is undefined in RECORDSET_TO_POPULATE_FORM. but as you can see, I have defined it in the CFQUERY (unless I am supposed to define it in a CFPROCRESUT which didn't work either when I tried it).

I enclosed my parameters in ## (as well as "##" and ""!)and double-checked my ALFKI customerID with a recordset.

I also changed the WHERE CustomerID = '#Form.CustomerID#'in the Action Page to '#URL.CustomerID#'and called it in the browser by

http://vmladwp02a/corpsafetytest/Fo...Fields_Using_Stored_Proc.cfm?CustomerID=ALFKI

as well as pressing F5 in StudioMX.

Ceal

 

[Pattycake245]

You are wanting to populate this form when it is loaded initially, then have the ability to edit the company name and submit and reload the form with the new value.

first Cold Fusion page will include your query to select the data and dispolay the results inside of a form. You'll have to set up the username and password to access the database:

<!--- FormPage.cfm --->
<cfquery name="Recordset_to_Populate_Form" datasource="Northwind">
  SELECT dbo.Customers.CustomerID CustomerID, 
  dbo.Customers.CompanyName CompanyName
  FROM dbo.Customers
  WHERE CustomerID = 'ALFKI' 
</cfquery>

<cfoutput>
<FORM action="Action_Page_to_Populate_Form_Fields_Using_Stored_Proc.cfm" method="post" name="FORM" id="FORM"></cfoutput>
  <cfoutput query="Recordset_to_Populate_Form">
  <input type="hidden" value="#Recordset_to_Populate_Form.CustomerID#" name="CustomerID">
  <input name="CompanyName" value="#Recordset_to_Populate_Form.CompanyName#" type="text">
<input name="Submit" value="Submit" type="submit">
</form>
</cfoutput>

This form action will call a second page to do the update and then call the form page again. Again you need to set up username and password:

<!--- Action_Page_to_Populate_Form_Fields_Using_Stored_Proc.cfm --->
<CFSTOREDPROC procedure="dbo.Update_Northwind_Customers_Table5" 
			  datasource="Northwind">
  <CFPROCPARAM type="IN" cfsqltype="CF_SQL_VARCHAR" value="Form.CustomerID">
  <CFPROCPARAM type="IN" cfsqltype="CF_SQL_VARCHAR" value="Form.CompanyName">
</CFSTOREDPROC>

<cfinclude template="FormPage.cfm">

Hope this helps,
Tim

 

[Ceal]

Tim,

Wow! It worked! The field was actually populated. I'm so excited. Yesterday I had begun to believe that doing this was mainly theoretical like third normal form. Everybody else around here does CFML for updates. I'm the only one who's tried Stored Procs. Could I trouble you to borrow code for the password page (if you have it?) I've never done that before and it would be great to do this before tomorrow (it's my day off). If not, don't worry about it.

Ceal

 

[Pattycake245]

I actually just meant that in your cfquery and your cfstoredprocedure calls you usually have to pass the username and password to access the database:

<CFSTOREDPROC procedure="dbo.Update_Northwind_Customers_Table5" 
              datasource="Northwind"
              [COLOR=red]username="#username#"
              password="#password#"[/color]>
  <CFPROCPARAM type="IN" cfsqltype="CF_SQL_VARCHAR" value="Form.CustomerID">
  <CFPROCPARAM type="IN" cfsqltype="CF_SQL_VARCHAR" value="Form.CompanyName">
</CFSTOREDPROC>

And, if I may suggest it is better from a performance standpoint to create a stored procedure and call it from the Cold Fusion side rather than creating a SQL query inside of cfquery tags. So something like:

<!--- FormPage.cfm --->
<cfquery name="Recordset_to_Populate_Form"  datasource="Northwind"
username="#username#"
password="#password#">
  [COLOR=red]exec Recordset_to_Populate_Form[/color]
</cfquery>

<cfoutput>
<FORM action="Action_Page_to_Populate_Form_Fields_Using_Stored_Proc.cfm" method="post" name="FORM" id="FORM"></cfoutput>
  <cfoutput query="Recordset_to_Populate_Form">
  <input type="hidden" value="#Recordset_to_Populate_Form.CustomerID#" name="CustomerID">
  <input name="CompanyName" value="#Recordset_to_Populate_Form.CompanyName#" type="text">
<input name="Submit" value="Submit" type="submit">
</form>
</cfoutput>

And then on the SQL side create a stored proc for you select:

if exists (select * from dbo.sysobjects where id = object_id(N'Recordset_to_Populate_Form) and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure Recordset_to_Populate_Form
go

create procedure Recordset_to_Populate_Form
	
AS
  SELECT dbo.Customers.CustomerID CustomerID, 
  dbo.Customers.CompanyName CompanyName
  FROM dbo.Customers
  WHERE CustomerID = 'ALFKI'

Hope that helps

Tim

 

[Ceal]

Tim,

I think it worked perfectly and I sent you a star post. That is, the form came up without any errors and was populated. When I attempted to edit the populated form, I naturally received the error of Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Procedure Recordset_to_Populate_Form has no parameters and arguments were supplied.

Naturally, because I had no update statement in my Stored Procedure.

However, the reason I'm writing is I'm unclear about something you said in your first post. You said "This form action will call a second page to do the update and then call the form page again."

Did you mean that I call the Stored Proc to populate a form. Then create another Stored Proc to do the Update? I actually didn't need the password and username but I thought you meant that it was used as some kind of trigger. Or did you mean that I have two forms with one Action page? Or ?

I am now going to put the Update and Select in one proc but it just doesn't make sense that it would work. Maybe the submit is the trigger?

Ceal

 

[Pattycake245]

The way I wrote that was that I was assuming a few things. There are many ways to go about it depending on what you want to do. I just assumed that when the page loads you would want to display the current companyname for that id and then have the ability to modify it. Your first Cold Fusion page will run the select query and populate the form fields. The action part in the form definition:

<FORM action="Action_Page_to_Populate_Form_Fields_Using_Stored_Proc.cfm" method="post" name="FORM" id="FORM"></cfoutput>

...will call the second cold fusion page which will do the update. At the bottom of the second cold fusion page I call the first cold fusion page which again will run the select query and then populate the form to display the form with the changes made. You would not put both the select and the update statements in the same stored proc.

Without knowing what your final product is it is tough to give a complete answer(ie. is this part of a bigger program). I work with Cold Fusion every day and basically what usually happens is I have a Cold Fusion form or cgi page that has textfields in it. Those are then passed to a stored proc to select the data and then displayed on another cold fusion page. If this page is used then for doing updates you would enclose everything inside a form, as you have done, and put an action on it to call another cold fusion program with a stored procedure in it to update or whatever. You can then refer back to the orginal entry screen or, like I did before, call the form page again to display the data with updated changes.

Hope this is clearer to you.

 

[Ceal]

I've read over your e-mail so many times I think I can quote it by heart (four score and seven years, oops. wrong one) and I'm still not sure what I want can even be done. It's not the Stored Proc that I want to have update the fields - it's the user who edits it.

If you go to

http://www.macromedia.com

and click on Your Account, then click on Account Details, the Macromedia Account form will come up fully populated. You can make an edit yourself, press submit and the edited fields will appear.

Do you think Macromedia is using Stored Procs to bring up the data and then a CFTransaction to update the fields or Stored Procs to do both?

I really feel bad about taking up your time like this.

 

[Ceal]

Tim,

Is my question above really dumb? I keep thinking that if Stored Procs do everything else (and I did get them to update a field without the population - it was just a blank text field and I entered information), then this shouldn't be a problem. However, I've also confused misleading marketing from Macromedia with what the product can actually do and spent weeks on a deadend. I've found Macromedia will almost never admit their products can't do everything so it's no use asking them.

Ceal

 

[Pattycake245]

No questions are dumb. You are obviously quite new to Cold Fusion and are trying to figure out what benefits it has. The information on Macromedia's site when you sign in will be databased, so I would assume that either SQLserver or some other database will certainly be holding your account information. If you go and make changes to say, your address, and submit that, Cold Fusion is passing all those values to a procedure to update those fields in their database.

Sorry if my explanations sound confusing. It's hard not to go into a lot of detail when trying to explain what Cold Fusion can do. We use it for everything from displaying reports to interactive update and add screens linking to SQLserver databases to update tables and query tables and report on the results.

Tim