many choices problem...

[mjonson]

i have a question about writing a many to many table
ne examples would be welcome

a user can choose a car and then choose accessories they would like for that car.

at the moment my page loops through the accessories table and lists all products.
at the end of each row is a yes/no dropdown to select requirement.

the options come from a table with yes having the id 1.

when the form is posted i would like all accessories with the yes combo value of 1 to have the id of the accessory and the id of the car written to the link table (below)

tbl_car
----------
carid
make

tbl_acclink
------------
acclinkid
carid
accid

tbl_acc
------------
accid
accessory

am i making sense?, my code so far is below
also i dont want to use a mutiple choice list box
as it looks naff
ne ideas welcome

<%
strsql = "SELECT * FROM `tbl_acc` ORDER BY tbl_acc.accdesc"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.cursorlocation = 3
rs.Open strsql, conn, 1, 2
%>
</p>
<table width="357" height="28" border="0" cellpadding="4" cellspacing="1" bgcolor="#CCCCCC">
<%
Do While (Not rs.Eof)
' Set row color
bgcolor = "#FFFFFF"
%>
<%

' Display alternate color for rows
If recCount Mod 2 <> 0 Then
bgcolor = "#F5F5F5"
End If
%>
<%

' Load Key for record
key = rs("accid")
If VarType(key) = 18 Or VarType(key) = 19 Then ' adUnsignedSmallInt/adUnsignedInt
If IsNull(key) Then
key = Null
Else
key = CInt(key)
End If
End If
x_accid = rs("accid")
x_accdesc = rs("accdesc")
x_acccost = rs("acccost")
%>
<tr bgcolor="#F5F5F5">
<td width="219" height="26"><span class="aspmaker"> <font size="1">
<font size="2"> <font color="#666666">
<%
Response.Write x_accdesc
Response.Write x_accid
%>

</font></font></font></span><font color="#666666" size="2">&nbsp;</font></td>
<td width="67"><div align="center"><span class="aspmaker"><font color="#666666" size="2">
<strong>
<%
Response.Write "£" & x_acccost
%>
</strong> </font> </span></div></td>
<td width="43"><span class="aspmaker">
<%
x_stockaccList = "<select name='x_stockacc'><option value=''>Select</OPTION>"
sqlwrk = "SELECT `ynid`, `yn` FROM `tbl_yn`"
Set rswrk = Server.CreateObject("ADODB.Recordset")
rswrk.Open sqlwrk, conn, 1, 2
If Not rswrk.Eof Then
datawrk = rswrk.GetRows
rowswrk = UBound(datawrk, 2)
For rowcntwrk = 0 To rowswrk
x_stockaccList = x_stockaccList & "<option value='" & datawrk(0, rowcntwrk) & "'"
If CStr(datawrk(0, rowcntwrk)&"") = CStr(x_stockacc&"") Then
x_stockaccList = x_stockaccList & " selected"
End If
x_stockaccList = x_stockaccList & ">" & datawrk(1, rowcntwrk) & "</option>"
Next
End If
rswrk.Close
Set rswrk = Nothing
x_stockaccList = x_stockaccList & "</select>"
Response.Write x_stockaccList
%>
</span></td>
</tr>
<%
rs.MoveNext
Loop
%>
</table>

 

[Tarwn]

Rather then use select boxes, why not use checkboxes with the value of accid? That way you would get a list of the chosen accessories' ids and can just operate against those and the passed carid.

If you are going to use select boxes for each accessory, you could do the same thing. Have the value for No be an empty string, and the value for Yes be the accid of the accessory printed next to the box. As long as all the boxes are named the same you can loop through them on your processing page and operate on all of the sleected accessories, while skipping over any blanks ("No" selections)

Personally I would prefer the checkboxes, as that only requires a single click on "yes"'s, but it's up to you...

-T

 

[mjonson]

thanks tarwn

decided to put checkboxes in
now cant get the check box to return value of accid
and how do i loop through the list to put each checked value in the table?

code looks like this

keyacc = rs("accid")
<input type="checkbox" name="chkboxacc" value="keyacc">

when posting it uses this bit

x_accid = Request.Form("chkboxacc")
x_stockacc = Request.Form("x_stockacc")

strsql = "SELECT accid, dealid FROM `tbl_acclink`"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.Open strsql, conn, 1, 2
rs.AddNew
tmpFld = key
If Not IsNumeric(tmpFld) Then tmpFld = 0
rs("dealid") = (tmpfld)
tmpFld = x_accid
If Not IsNumeric(tmpFld) Then tmpFld = 0
rs("accid") = CLng(tmpFld)
rs.Update
rs.Close

 

[Tarwn]

On the checkboxes you need to make the value th actual accid, as that is what will be passed (but only if checked).

<input type="checkbox" name="chkboxacc" value="<%=keyacc=%>">

On the page receiving the form data you can simply do a For Each loop to get all the individual checked values, ie:

Dim an_accid
For Each an_accid In Request.Form("chkboxacc")
   'do something with this an_accid
Next

That should be all you need to pull out each one,

-T

 

[mjonson]

Microsoft VBScript compilation (0x800A03EA)
Syntax error
/dfasp/stockviewdeal.asp, line 401, column 22
Response.Write(keyacc=)
---------------------^

any ideas why i get this error?

 

[trollacious]

It should probably be:

Response.Write(keyacc & "=")


I don't think you can use an equals sign in a variable name.

 

[Genimuse]

At least in Tarwn's example, the error is that this:

<%=keyacc=%>

should be

<%=keyacc%>

(has an extra = after keyacc)

 

[trollacious]

That's probably it then. #

Lee

 

[Tarwn]

Wierd, I've never accidentaly typed a double = before, maybe I was having a really symetrical day or something

 

[Genimuse]

I find it fascinating that the error indicates that the statement was a Response.Write. I think I must never have made a mistake like that inside <%=x%> before, or at least not one that triggered that error message. It supports what the MS guys told me ages ago, about it literally being a Response.Write, except for the fact that it's faster than a "true" context switch. [ignore]<spock>Fascinating.</spock>[/ignore]

 

[mjonson]

thanks all

 

[mjonson]

now the loop stores comma separated values for each choice in each field

eg
acclinkid accid dealid
-----------------------
1 2,6 2
2 2,6 2

how do i make it put each value in a separate field

code below

x_accid = Request.Form("chkboxacc")
strsql = "SELECT accid, dealid FROM `tbl_acclink`"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.Open strsql, conn, 1, 2
dim an_accid
For Each an_accid In Request.Form("chkboxacc")
rs.AddNew
tmpFld = key
If Not IsNumeric(tmpFld) Then tmpFld = 0
rs("dealid") = (tmpfld)
tmpFld = x_accid
rs("accid") = (tmpFld)
rs.Update

thanks for your patience
ps how do u put code in TT code box?

 

[Genimuse]

You put code in code tags, like this

[ignore]

Your code here

[/ignore]

 

[mjonson]

jus to finish - the last prob was solved by changing
an_accid to x_accid which makes sense (jus wasnt thinkin) thanks to all again