Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

CheckBoxes

lemme set this up:
(4 tables)

<--customer table-->
tblCustomers: custID(PK), custName

<--attributes table-->
tblAttributes: attID(PK), custID(FK)

<--this table exists so customers may have multiple logins with different attributes-->
tblCustomerLogin: custLogID(PK)

<--simply a relationship table with no primary keys, just ties the customer login to its attributes-->
tblCustLoginAtt: custLogID, attID

OK, now all i wanna do is when you are editing a particular customer login, not only show ALL of the attributes that are associated with that customer in the form of checkboxes, but also check the ones that are already associated with the given CUSTOMER LOGIN.

I have done things similar to this in the past, but with radio buttons, where theres only ONE possible match, and i could open 2 recordsets and test to see:

<%
do while not rs.eof

if rs("attID")=rs2("attID") then
%>
<input type=radio name="myAtt" value=<%=rs("attID")%> checked>
<%
else
%>
<input type=radio name="myAtt" value=<%=rs("attID")%>>
<%
end if

rs.movenext
loop
%>

easy enough....loop thru rs and when come to match on rs2 then check the radio button, but now, it seems like i have to have a loop nested inside of another loop....which ive tried many different renditions of to no avail.....

any code examples or even explanations would be VERY MUCH appreciated! thx experts in advance! I would post some code, but im basically startin from scratch here, so pls someone show me the light! :D

giving my max 300pts, cuz id like to get resolved asap!

webdude
0
webdude
Asked:
webdude
  • 14
  • 11
  • 7
  • +3
1 Solution
 
webdudeAuthor Commented:
i forgot to include the custID in the tblCustomerLogin table...sorry

webdude
0
 
mgfranzCommented:
Maybe this will help;

sql = "select * from [information] where ID in (" & build & ")Order by "Lastname" Asc"

<input type="checkbox" name="build" id="build" value="<%=rs("ID")%>">
0
 
webdudeAuthor Commented:
hrm....i have no clue how that will solve my problem. i dont think its in the query but the logic behind displayin the checked and unchecked boxes...unless im missing something here :)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mgfranzCommented:
My bad... I think I need to re-read the Q...
0
 
webdudeAuthor Commented:
hehe...np...u still da man anyway :)
0
 
John844Commented:
here is some basic logic that you can apply

to check a box, you add the keyword checked like
<input type=checkbox name=myname checked>

In your loop to show checkboxes.  loop through the attributes recordset to find any existing settings.  If you find one, then add checked when you show the checkbox.  drop out of the inner loop at this point, since there is no point in continuing if you found a match already.  If you don't find a match and get to the end of the recordset then just show the checkbox.

here is some sample code to get you started...

dim strChecked
dim blnContinue

while not outter.eof
    inner.movefirst
    strChecked = ""
    blnContinue = True
    while not inner.eof and blnContinue
        'if you find a matching attribute for this checkbox
        if inner("chkid") = outter("checkid") then
            strChecked = " CHECKED "
            blnContinue = False
        end if
        inner.movenext
    loop
    outter.movenext
loop

John
0
 
John844Commented:
oops, forgot to add the line to show the checkbox...

dim strChecked
dim blnContinue

while not outter.eof
   inner.movefirst
   strChecked = ""
   blnContinue = True
   while not inner.eof and blnContinue
       'if you find a matching attribute for this checkbox
       if inner("chkid") = outter("checkid") then
           strChecked = " CHECKED "
           blnContinue = False
       end if
       inner.movenext
   loop
   'show checkbox with checked
   response.write "<input type=checkbox name=chk" & outter("checkid") & strChecked & ">
   outter.movenext
loop
0
 
webdudeAuthor Commented:
ya...unfortunately...thats my EXACT logic to a tee, and have it that way, but it will not work, i NEVER get a check box...the only thing i DONT have is exiting out of the inner loop when a match is found....how exactly do i exit from the inner loop? or could that even be my prob?
0
 
phuctranCommented:
Does this help you any idea?

select * from test1
select * from test2

select A.ATID, (select 1 from test2 as B where A.ATID=B.ATID) as HasID from test1 as A Where CustId=1
>>from CustLoginID you can find CustID

CustID      ATID        
----------- -----------
1           1
1           2
1           3
2           4

(4 row(s) affected)

CustLoginID ATID        
----------- -----------
1           1
1           2

(2 row(s) affected)

ATID        HasID      
----------- -----------
1           1
2           1
3           NULL

(3 row(s) affected)

0
 
webdudeAuthor Commented:
here is my current code segment for your viewing pleasure...


<%
         
          do while not rs.eof
          %>
     <tr>
          <td>&nbsp;</td>
          <%
          do while not rs2.eof
               if rs2("projID")=rs("projID") then
                    doCheck="checked"
               else
                    doCheck="&nbsp;"
               end if
          rs2.movenext
          loop
          %>
          <td><input type=checkbox name="myProj" value=<%=rs("projID")%> <%=doCheck%>><%=rs("projName")%></td>
     </tr>

     <tr>
          <td colspan=5><img src="images/space.gif" width=1 height=3></td>
     </tr>
     <%
          rs.movenext
          loop
     %>
0
 
phuctranCommented:
if you want to exit out of the inner loop, use
EXIT DO
<%
         
         do while not rs.eof
         %>
    <tr>
         <td>&nbsp;</td>
         <%
         do while not rs2.eof
              if rs2("projID")=rs("projID") then
                   doCheck="checked"
                   EXIT DO
              else
                   doCheck="&nbsp;"
              end if
         rs2.movenext
         loop
         %>
         <td><input type=checkbox name="myProj" value=<%=rs("projID")%> <%=doCheck%>><%=rs("projName")%></td>
    </tr>

    <tr>
         <td colspan=5><img src="images/space.gif" width=1 height=3></td>
    </tr>
    <%
         rs.movenext
         loop
    %>
0
 
webdudeAuthor Commented:
thx for the exit do (duh..) hehe

still same tho....i never get a check box....grrr

dont give up on me yet guys!
0
 
thunderchickenCommented:
Ouch... Ummmm opening 2 recordsets?  Can't you just Join statements to accomplish this?  Post your database schema and maybe we can make this more efficient, thus making your code better.
0
 
webdudeAuthor Commented:
thats what i thought at first too, trying an outer join...but the db schema is in the first post above....here it is again:

<--customer table-->
tblCustomers: custID(PK), custName

<--attributes table-->
tblAttributes: attID(PK), custID(FK)

<--this table exists so customers may have multiple logins with different attributes-->
tblCustomerLogin: custLogID(PK), custID

<--simply a relationship table with no primary keys, just ties the customer login to its attributes-->
tblCustLoginAtt: custLogID, attID
0
 
phuctranCommented:
Webdude,

have you tried my SQL statement above? then you don't have to go through 2 do while loop.
0
 
phuctranCommented:
Webdude,

have you tried my SQL statement above? then you don't have to go through 2 do while loop.
0
 
thunderchickenCommented:
Sorry, didn't see them above, what information are you trying to get out of these tables?  (what are your SQL statements)
0
 
phuctranCommented:
Change rs2("projID")=rs("projID") to
Trim(rs2("projID")) = Trim(rs("projID"))
to see if it works.  The = will compare 2 variables case sensitive.  Also, usually, when you get from database you will have trailing spaces.
0
 
webdudeAuthor Commented:
no i havent tried it yet phunc, cuz its a lil confusing....sorry....i understand your doing like subqueries with aliases but its still cornfusing :)
0
 
phuctranCommented:
These are SQL statements.
select * from test1
select * from test2
select A.ATID, (select 1 from test2 as B where A.ATID=B.ATID) as HasID from test1 as A Where CustId=1
>>from CustLoginID you can find CustID

These are the results of those statements above
CustID      ATID        
----------- -----------
1           1
1           2
1           3
2           4

(4 row(s) affected)

CustLoginID ATID        
----------- -----------
1           1
1           2

(2 row(s) affected)

The last SQL statement will give you this recordset:
ATID        HasID      
----------- -----------
1           1
2           1
3           NULL

then do loop, if HasID = 1 then "checked".

0
 
webdudeAuthor Commented:
first off phuc....the trim didnt werk

secondly, i understood all that u re-explained, but still have no clue what you are doing in that subquery sorry
0
 
phuctranCommented:
Webdude,

remember to have "" in you html code :)

 value=<%=rs("projID")%> <%=doCheck%>><%=rs("projName")%

if rs("projID") = "hello"
then you will have
 value=hellochecked> that is why you don't get "checked"

change doCheck="checked"
to doCheck=" checked "

0
 
thunderchickenCommented:
Try this query:

Select custName,tblCustomers.custID,tblCustLoginAtt.attID,tblCustomerLogin.custLogID from tblCustomers Join tblAttributes On tblCustomers.custID = tblAttributes.addID
  Join tblCustLoginAtt on tblCustLoginAtt.attID = tblAttributes.attID
  Join tblCustomerLogin on tblCustLoginAtt.custLogID = tblCustomerLogin.custLogID
Where custName = 'webdude'

this should give you what you need
0
 
phuctranCommented:
Oh, what I meant to have "" in your html code is

 <input type="text" name="txtName" value="txtValue">

so you should have
value="<%=rs("projID")%>" <%=doCheck%>><%=rs("projName")%

one thing might happen is if rs("projID") has two words then you will have trouble later.
0
 
thunderchickenCommented:
With my above query, try this

set cn = rs.execute ("Select custName,tblCustomers.custID,tblCustLoginAtt.attID,tblCustomerLogin.custLogID from tblCustomers
Join tblAttributes On tblCustomers.custID = tblAttributes.addID
 Join tblCustLoginAtt on tblCustLoginAtt.attID = tblAttributes.attID
 Join tblCustomerLogin on tblCustLoginAtt.custLogID = tblCustomerLogin.custLogID")

while not rs.EOF
  %>
  <input type=radio name="myAtt" value=<%=rs("attID")
    if rs("CustName") = "webdude" then
      response.write ("  checked  ")
    end if
  %>
  ><%=rs("CustName")%><br>
<%rs.movenext
Wend%>
0
 
webdudeAuthor Commented:
phuc...that makes no difference.....i tried " checked" same problem....

and also btw....u DONT wanna use "" around numbers, it makes it a string FYI

chicken...im tryin urs now

brb
0
 
thunderchickenCommented:
I just don't know if I'm following what you are doing right, but it should be that complicated with the right query (try to always avoid 2 recordsets if you can)
0
 
phuctranCommented:
I agree with thunderchicken.  You should try to rewrite your SQL query to avoid loop into loop.  But because you want to get it works asap, then we will help you with your code.

Let do step by step:
Change

         do while not rs2.eof
              if rs2("projID")=rs("projID") then
                   doCheck=" checked "
              else
                   doCheck=" checked "
              end if
         rs2.movenext
         loop

to see if you actually go into the inner while loop.

Then try to do:
<td><input type=checkbox name="myProj" value=<%=rs("projID")%> <%=doCheck%>><%=rs("projName")%></td>
to
<td><input type=checkbox name="myProj" value=<%=rs("projID")%> <%=doCheck%>><%=rs("projID")%> and <%=rs2("projID")%></td>
to see their values.

0
 
phuctranCommented:
Sorry, it should be read:

Then try to do:
        do while not rs2.eof
             if rs2("projID")=rs("projID") then
                  doCheck=" checked "
                  strTemp = rs("projID")
                  exit do
             else
                  doCheck=" checked "
                  strTemp = rs("projID")
             end if
        rs2.movenext
        loop

<td><input type=checkbox name="myProj" value=<%=rs("projID")%> <%=doCheck%>><%=rs("projName")%></td>
to
<td><input type=checkbox name="myProj" value=<%=rs("projID")%> <%=doCheck%>><%=rs("projID")%> and <%=strTemp%></td>
to see their values.

One more thing is that rs2 does not get any record at all.  check your sql statement again.

0
 
phuctranCommented:
oops,
strTemp = rs2("projID")
0
 
webdudeAuthor Commented:
well guys....i got it...i ended up just putting it all into a function and mysteriously it werked, even tho its my same logic as b4......so i really hate these positions where multiple ppl help with same q, and i find answer somewhere else....

so on that note, instead of deleting question, i would like to give the 300 points to phuc and thunder...so i will accept phucs response, and if u could be so kind, mebbe split the points with thunder for me?

thx again guys and sorry for the point-split
0
 
webdudeAuthor Commented:
pls split points with thunder...thx guys for all your efforts!
0
 
thunderchickenCommented:
Thanks, if you want to split, you will need to post in Community Support.
0
 
webdudeAuthor Commented:
me or phuc?

i was thinkin phuc could just ask a question for 150 pts, and accept your comment as answer....if that wont werk lemme know

0
 
thunderchickenCommented:
they will use that for their own questioning, but don't worry about it, phuctran posted a 0 point question in CS and they will take care of it

glad we could help
0
 
MoondancerCommented:
I can fix this for you be reducing this question to 150 and then post another question for 150 for the other expert here, or you can do this directly, including the link to this question.

Is that what you want, 150 to each (just want to confirm)?

Thanks,

Moondancer
Community Support Moderator @ Experts Exchange
0
 
MoondancerCommented:
Checked the CS Question which confirms a 150/150 point split.  Reduced this to 150 points, and created another for thunderchicken at the following link:

http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=asp&qid=20180575

All now completed.

Moondancer
Community Support Moderator @ Experts Exchange
0
 
webdudeAuthor Commented:
thanks again!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 14
  • 11
  • 7
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now