Hi, Can someone give me some pointers please on this one.

I have a products table which has a relationship with a recipe table. A product can be assigned to a recipe (one - many). On my update and add product pages though I could only seem to be able to assign one product to one recipe using a drop down list menu. This works fine but I would like to be able to assign the product to many recipes. So I cahnged the drop down to a list menu and use Ctrl to select multiple recipes from the list. On submit however, I get a syntax error.

Can anyone advise on what I would need to be looking at doing to get this working

I really appreciate your help.

Thanks

i'm working on this. i'll post it later.

Thank you I appreciate it and look forward to seeing your suggestion. I am updating an Access db btw <img src=../images/dmxzone/forum/icon_smile_shock.gif border=0 align=middle> I am not very familiar with all this .. quite new to ASP and db's but I suspect my problem is not knowing what should happen and what parameter I can work wihin. Thanks again. G

online demo:
mspinay.somee.com/freescripts/asp/recipe/recipe.asp

zip file:
micah.web1000.com/freescripts/recipe.zip

here's the entire code: (sorry, i didn't provide with a proper layout. (",) )

RECIPE.ASP
===============
&lt;%

// Database Connection Procedures

Dim objConn
Dim strProvider

Sub OpenDb

strProvider = ""
strProvider = strProvider & "Provider=Microsoft.Jet.OLEDB.4.0;"
strProvider = strProvider & "Data Source=" & Server.MapPath("RECIPE.mdb" & ";"
strProvider = strProvider & "Persist Security Info=False"
Set objConn = Server.CreateObject("ADODB.Connection"

objConn.Open strProvider

End Sub

Sub CloseDb
objConn.Close
Set objConn = Nothing
End Sub

%&gt;

&lt;%

Dim rsProduct
Dim rsRecipe
Dim func

func = Request.Querystring("func"
// require db conn

CAll OpenDb()
%&gt;

&lt;%

If func = 1 Then

Dim strItem,strSQL,finalProd
Dim finalRecipe

intRecIDs = Replace(Request.Form("ID", "*", ""
arrRecIDs = Split(intRecIDs, ", "

If Request.Form("glRecipe" &lt;&gt; "" Then

For i = 0 to Ubound(arrRecIDs)

strItem = Replace(Request.Form("glProd_" & arrRecIDs(i)), "'", "''"

If strItem &lt;&gt; "" Then
ctr = ctr + 1
End If

Next

ctr = ctr -1

For i = 0 to Ubound(arrRecIDs)

strItem = Replace(Request.Form("glProd_" & arrRecIDs(i)), "'", "''"

If strItem &lt;&gt; "" Then

brkStop = brkStop + 1

If ctr &lt; brkStop Then
finalProd = finalProd + strItem
Else
finalProd = finalProd + strItem & ", "
End If

End If

Next

Set rsRecipe = ObjConn.Execute ("SELECT * FROM tblRecipe where recipeId='" & Request.Form("glRecipe" & "'"

finalRecipe = rsRecipe("recipeItem" & ", " & finalProd

rsRecipe.Close
set rsRecipe = Nothing

If finalProd &lt;&gt; "" Then
strSQL = "UPDATE tblRecipe SET recipeItem='" & finalRecipe & "' WHERE recipeId='" & Request.Form("glRecipe" & "'"
ObjConn.Execute(strSQL)
End If

Else

response.redirect "recipe.asp"

End If

CAll CloseDb()

response.redirect "recipe.asp"

Else

%&gt;


&lt;html&gt;
&lt;head&gt;
&lt;title&gt;One to Many&lt;/title&gt;
&lt;/head&gt;

&lt;body&gt;

&lt;form method="post" action="recipe.asp?func=1"&gt;

&lt;%

Set rsProduct = ObjConn.Execute("SELECT * FROM tblProd order by id ASC"

response.write "Product List" & "&lt;br&gt;&lt;br&gt;"

While NOT rsProduct.EOF
response.write "&lt;input type='hidden' size='5' readonly name='id' value=" & rsProduct("id" & "&gt;"
response.write "&lt;input type=CHECKBOX value='" & rsProduct("prodId" & "' name='glProd_" & rsProduct("id" & "'&gt;" & rsProduct("prodID" & "&lt;br&gt;"

rsProduct.MoveNext
WEnd

rsProduct.Close
set rsProduct = Nothing

%&gt;


&lt;%

Set rsRecipe = ObjConn.Execute("SELECT * FROM tblRecipe order by recipeId ASC"

response.write "&lt;br&gt;Add to: "
response.write "&lt;SELECT name='glRecipe'&gt;"
response.write "&lt;OPTION value=''&gt;-- Select Recipe --"

While NOT rsRecipe.EOF

response.write "&lt;OPTION value='" & rsRecipe("recipeId" & "'&gt;" & rsRecipe("recipeId"

rsRecipe.MoveNext
WEnd

response.write "&lt;/SELECT&gt;"

rsRecipe.Close
set rsRecipe = Nothing

response.write "&lt;br&gt;&lt;br&gt;&lt;input type='submit' name='Update' value='Update'&gt;"
response.write "&lt;br&gt;&lt;br&gt;"

Set rsRecipe = ObjConn.Execute("SELECT * FROM tblRecipe order by recipeId ASC"

response.write "&lt;br&gt;Recipe List&lt;br&gt;&lt;br&gt;"

While NOT rsRecipe.EOF

response.write "Recipe Name: " & rsRecipe("recipeId" & "&lt;br&gt;"
response.write "Ingredients: " & "&lt;br&gt;" & rsRecipe("recipeItem" & "&lt;br&gt;&lt;br&gt;"

rsRecipe.MoveNext
WEnd

rsRecipe.Close
set rsRecipe = Nothing

%&gt;

&lt;/form&gt;

&lt;/body&gt;
&lt;/html&gt;

&lt;% End If %&gt;

if you got any questions or suggestions, please let me know.

i used checkboxes instead... well, if you insist to use list item, we'll work on that.

hope this helps.

WOW Micah, that is probably the most comprehensive reply I have ever received. Thank you. It doesn't really help me though as I cannot see what it is that I am doing wrong with my site. I'm not sure where I should be looking. If you take a look at www.chalicefoods.co.uk/productDetail.asp?productID=51 you will see the display page. The link to the related recipe for that product takes you to one recipe. I want to be able to assign many recipes to a product.

The FK "recipeID" in the "products" table only takes one recipeID number. It needs to allow many? Or do I do this another way?

Am I confused or what <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>

Thanks

now, you're making me confused here.... lol(",)

i thought it was a one to many relationship not to many to many.

you specified this (one - many) so i thought of a single product would added to a recipe, and speaking of a product, i mistakenly represented it as an INGREDIENTS, if ever you wanted to add it on the current recipe. that' how i grasped your question here.

i guess, i was wrong in the representation. however, based on what I've seen to your site, the codes i made would definitely help you as your guide to attain whatever functions/procedures you are referring to. it only needs some adjustment, but, it will be now easy to reconstruct.

p.s.
next time, conceptualize first all your ideas.... such as functions/procedures before setting a goal for your site. u don't need to be confused or get in rush if you're carefully organizing your settings.

good luck!