Code created by wizard needs tweaking?

Hi everyone,

I've created a set of pages for an administrative backend using the DataAssist Wizard. Among the pages created by the wizard is a results page which was supposed to display two things: 1) the name of the subcategories that are listed in the subcats table in the database and 2) the name of the related category. It displays the subcategory correctly but instead of displaying the category name, it's displaying the foreign key, eg.

{WADASubcats.SubcatCategoryID}

The wizard was working with my subcategories table when it created the pages so I wondered if this is why it's not displaying the category correctly, since the category name is listed in another table in the database.

Can someone tell me how to fix this problem? Did I take a wrong step when running through the wizard or do I have to manually fix this after having run the wizard. If yes, how would I do this?

I also noticed that the insert page that the wizard created correctly inserts data into the correct table in my database but the code created doesn't seem to block duplicates from being entered. Is this something that I have to fix manually or was there something I missed in the wizard that could have prevented this?

Appreciate any assistance.

When using a relational table, you will need to modify the recordset on the results page to pull the name in from the child table. you accomplish this by creating a join Query.

The syntax for a JOIN query is:

SELECT table1.*, table2*
FROM table1
INNER JOIN table2 ON table1.KeyColumn = table2.keycolumn

in your case, your talking about a subcategory table, probably relating to a catagory table using the subcategory.SubcatCategoryID = category.categoryID


SELECT subcategory.*, category*
FROM subcategory
INNER JOIN category ON subcategory.SubcatCategoryID = category.categoryID


then you can get the name by changing the SubcatCategoryID reference with the category name column.



the insert record behavior just inserts a record. It does not check if a column contains duplicate information.


You would need to create a recordset to look up the information being entered to determine if it already existed in the database, then change the Insert Record trigger to happen if that recordset is empty.

Thanks for this info.

I've just given it a try by altering the SQL code in the recordset to be as follows:

SELECT Subcats.*, productscategories*
FROM Subcats
INNER JOIN productcategories ON Subcats.SubcatCategoryID = productcategories.CategoryID
ORDER BY Subcat ASC

When I click the test button in the record set dialogue I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM Subcats INNER JOIN productcategories ON Subcats.SubcatCategoryID = productc' at line 1

So I changed the SQL to be as follows:

SELECT *
FROM Subcats INNER JOIN productcategories ON Subcats.SubcatCategoryID = productcategories.CategoryID
ORDER BY Subcat ASC

ie. just altering the first line. I clicked the test button and I got the results but when I clicked ok to go out of the recordset dialogue I got the following error:

"Making this change would require changing code that is locked by a template or a translator. The change will be discarded."

Any ideas why this is happening?

The first part of the SQL is missing a period:
SELECT Subcats.*, productscategories*


this should be:
SELECT Subcats.*, productscategories.*

Ok after revising the code to:

SELECT Subcats.*, productcategories.*
FROM Subcats
INNER JOIN productcategories ON Subcats.SubcatCategoryID = productcategories.CategoryID
ORDER BY Subcat ASC


... I clicked the test button in the record set dialogue and I was able to get the results, but again when I click ok to go out of the recordset dialogue I get the same error as before:

"Making this change would require changing code that is locked by a template or a translator. The change will be discarded."

Do you know what this means?

This is the bane of using Dreamweaver Templates ;). The work around to this error is to manually detach the page from the template temporarily, make the change, then reattach the template to the page.

You will need to use an external text editor for this.

close Dreamweaver and open the template in another text editor like note pad.

The line that attaches the template to the page will look like:
<html xmlns="http://www.w3.org/1999/xhtml"><!-- InstanceBegin template="/Templates/siteassistpro_template.dwt.asp" codeOutsideHTMLIsLocked="false" -->

Remove the '<!-- InstanceBegin template="/Templates/siteassistpro_template.dwt.asp" codeOutsideHTMLIsLocked="false" -->" part:
<html xmlns="http://www.w3.org/1999/xhtml">


This will detach the page from the template.


You can open Dreamweaver and make the change to the recordset. After changing the recordset, open the page in your text editor again and add back the template code.

Thanks very much - that fixed the problem.

I also have another question relating to one of the other pages created by the wizard if that's ok. The wizard created a search page that allows you to search by typing in the name of the subcategory and then choosing a category from a select menu. The search works correctly by returning the results to the Subcat_Results.php page, but it actually creates a problem when returning to the results page. To explain:

I have a subcategories menu item which when clicked links to the results page, (Subcats_Results.php) which is supposed to list all of the subcategories in the database, however if a search is done on the search page prior to clicking this menu item, the page displays the results of the previous search and not the entire subcategory list as it's supposed to.

Is there a way to modify this so that whenever the subcategories menu item is clicked, it always returns the full list?

I will need to look at your files to see where the problem may be.

I have started a support ticket for you so that I can do this.

To view and edit your support ticket, visit the support history page:
supporthistory.php