update, delete, add info in 3 tables over the web

[karren]

I have an access database with 3 different tables that all have the same field names (productid, producttitle, colour, size) but just for different products ie: one for tops, one for trousers, and one for hats. I have used the <cfswitch> statement that allows queries for each individual page to be read. But I want to be able to delete, add, and update the information in each individual table over my webpage. Also, I am using one form for all three applications and this is working out fine when it comes to displaying the contents of each table. The application is reading the content from each table but now I need to be able to delete, add, update this information from my website.

Any clue on how i could do this???

Thank you!!!!

here is the code for the cfswitch statement:

<cfswitch expression=&quot;#url.category#&quot;>
<cfcase value=&quot;Tops&quot;>
<cfquery name=&quot;read_apparel&quot; datasource=&quot;kgillitgirl&quot;>
SELECT productid, producttitle, productdescription, colour, size, price, quantity
FROM Tops
</cfquery>

<cfoutput query=&quot;read_apparel&quot;>

<tr>
<td>

<a href=&quot;index.cfm?page=edit&category=tops&productid=#productid#&producttitle=#producttitle#&productdescription=#productdescription#&colour=#colour#&size=#size#&price=#price#&quantity=#quantity#&quot;>edit</a>
<a href=&quot;index.cfm?page=delete&category=tops&producttitile=#producttitle#&quot;>delete</a>
</td>
<td>#productid#</td>
<td>#producttitle#</td>
<td>#productdescription#</td>
<td>#colour#</td>
<td>#size#</td>
<td>#price#</td>
<td>#quantity#</td>
</tr>
</cfoutput>
</cfcase>
</table>


<cfcase value=&quot;Trousers&quot;>
<cfquery name=&quot;read_trousers&quot; datasource=&quot;kgillitgirl&quot;>
SELECT productid, producttitle, productdescription, colour, size, price, quantity
FROM trousers
</cfquery>

<cfoutput query=&quot;read_trousers&quot;>
<tr>
<td>

<a href=&quot;index.cfm?page=edit&category=trousers&productid=#productid#&producttitle=#producttitle#&productdescription=#productdescription#&colour=#colour#&size=#size#&price=#price#&quantity=#quantity#&quot;>edit</a>
<a href=&quot;index.cfm?page=delete&category=trousers&producttitle=#producttitle#&quot;>delete</a>
</td>
<td>#productid#</td>
<td>#producttitle#</td>
<td>#productdescription#</td>
<td>#colour#</td>
<td>#size#</td>
<td>#price#</td>
<td>#quantity#</td>
</tr>
</cfoutput>
</cfcase>
</table>


</table>
</cfswitch>

 

[webmigit]

SELECT productid, producttitle, productdescription, colour, size, price, quantity FROM #url.category#

Wouldn't that be better? and just to ensure no fancy query recoding... is going on in the url

<CFIF len(URL.category) lte 10>
<cfquery name=&quot;read_apparel&quot; datasource=&quot;kgillitgirl&quot;>
SELECT productid, producttitle, productdescription, colour, size, price, quantity
FROM #url.category#
</cfquery></CFIF>

And then you might just use a hidden form field with the value of url.category for your delete/insert/update queries..

Tony Hicks
[ Founder of <A href=&quot;

http://www.bibleclicks.com&quot;>Bibleclicks.com</A>,

online bible. ]

 

[karren]

thanks so much for the tip Tony, its good of you to help me. it works fine when reading the database but i am still unable to delete, edit or add items to each individual table. when i go to edit something, all the appropriate information from the table i want it to read appears on the form, but i press the &quot;update&quot; or &quot;insert&quot; button on the form to perform this action, then the update and insert pages are unable to read the variables on the form..they don't recognize these variables for some reason that i can't figure out why. I have defensive coding on the update, delete and insert pages:

<cfif not isDefined(&quot;form.productID&quot>
<cflocation url=&quot;index.cfm&quot;>
<cfelse>
<cfset producttitle=&quot;#form.producttitle#&quot;>
<productdescription=&quot;#form.productdescription#&quot;>
<cfset colour=&quot;#form.colour#&quot;>
<cfset size=&quot;#form.size#&quot;>
<cfset price=&quot;#form.price#&quot;>
<cfset quantity=&quot;#form.quantity#&quot;>
<cfset category=&quot;#url.category#&quot;>

</cfif>

so instead of updating or inserting the informatino from the form, i am redirected back to the index page because the update/insert/delete pages do not recognize the variable as defined.

what should i do???? i am new to this and i am so frustrated that its not wroking!!

thanks again,

karren