I think there is something wrong in using ASP to connect to the database.
Something wrong this usng this code:
Set connTest = Application("default_ConnectionString")
connTest.Open
Set rsTest = connTest.Execute(sTestSQL)
After several days of searching I found a way to use Frontpage DRW to give
me a drop-down database populated list.
On the Insert menu, point to Database, and select Results.
In step 1 of the wizard, select your database connection (Default). Click
Next.
In step 2 of the wizard, select your record source (Results). Click Next.
In step 3 of the wizard, select Edit List and remove all of the fields,
except the field that will be used to query the database. We are going to use
"Session1" for the query. Click OK. Click Next.
In step 4 of the wizard, click to select Drop-Down List from the list of
formatting options. Click Next.
In step 5 of the wizard, click Finish.
Save the page and Preview in Browser. You will notice that each "Course" is
displayed in the dropdown list more than once. To have a limit on the
courses, in FrontPage, switch to Code view, and find the SQL for the
dropdown, in the gray-colored code. It should look like this: s-sql="SELECT *
FROM Results" and change it s-sql="SELECT Session1 From Results GROUP BY
Session1 HAVING COUNT(*) < 20". Save the changes while still in Code view.
You will notice that once you've saved your changes they are also made to the
maroon colored code - that is by design. The maroon colored code should never
be touched; it's generated by the gray code at save time.
Website references:
http://www.spiderwebwoman.com/tutorials/picklist_with_dropdown.ht
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23611606.html
Stefan B Rusynko said:
If you get resulta from
SELECT Results.Session1, Count(Results.Session1) AS myCount FROM Results
GROUP BY Results.Session1
- then this shoutd limit your diplayed results to those where the count is less than 20
(after you get your results you need to limit the ones displayed to 20 or less w/ the IF statement wrapping the
optiosn to display)
<SELECT name=TestBox>
<option value="PS">Please Select Available Course</option>
< %
MaxReg = 20+1 'limit ot max reg plus 1
sTestSQL = "SELECT Results.Session1, Count(Results.Session1) AS myCount FROM Results
GROUP BY Results.Session1"
Set connTest = Application("default_ConnectionString")
connTest.Open
Set rsTest = connTest.Execute(sTestSQL)
Do While Not rsTest.EOF
myCount=rsTest("myCount") ' gets your count value
Session1=rsTest("Session1") 'gets your table field named Session1
IF myCount<MaxReg THEN
%>
<option value ="<%=Session1%>"><%=Session1%>< /option>
<%
END IF
rsTest.MoveNext
Loop
rsTest.Close
Set rsTest = Nothing
connTest.Close
Set connTest = Nothing
%>
</SELECT>
--
_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________
| Well I tried that query inside the access database and no results but I used
| this query:
| SELECT Results.Session1, Count(Results.Session1) AS myCount FROM Results
| GROUP BY Results.Session1
| and I got several results that had numbers above and below 20.
| So I used this query in the ASP code and still nothing.
|
| "Stefan B Rusynko" wrote:
|
| > Or your data table is returning an EOF for the fields you enter
| > - no records found
| >
| > --
| >
| > _____________________________________________
| > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > "Warning - Using the F1 Key will not break anything!" (-;
| > _____________________________________________
| >
| >
| > | %>
| > | <option value ="<%=Session1%>"><%=Session1%>< /option>
| > | <%
| > | This returns an error so I modified it so it's this:
| > | Response.Write "< Option >" & Session1 & "< /option >"
| > |
| > | But no success.
| > | It seems that the ASP code is not connecting to my Frontpage access database
| > |
| > |
| > |
| > |
| > |
| > | "Stefan B Rusynko" wrote:
| > |
| > | > The field "varchar_session" is not in your query,
| > | > - only "mycount" and "Session1" are in your SQL query
| > | >
| > | > Try
| > | >
| > | > <SELECT name=TestBox>
| > | > <option value="PS">Please Select Available Course</option>
| > | > < %
| > | > sTestSQL = "SELECT Session1,COUNT(*) FROM Results AS myCount"
| > | > Set connTest = Application("default_ConnectionString")
| > | > connTest.Open
| > | > Set rsTest = connTest.Execute(sTestSQL)
| > | > MaxReg = 20+1 'limit ot max reg plus 1
| > | > Do While Not rsTest.EOF
| > | > myCount=rsTest("myCount")
| > | > Session1=rsTest("Session1")
| > | > IF myCount<MaxReg THEN
| > | > %>
| > | > <option value ="<%=Session1%>"><%=Session1%>< /option>
| > | > <%
| > | > END IF
| > | > rsTest.MoveNext
| > | > Loop
| > | > rsTest.Close
| > | > Set rsTest = Nothing
| > | > connTest.Close
| > | > Set connTest = Nothing
| > | > %>
| > | > </SELECT>
| > | >
| > | >
| > | > --
| > | >
| > | > _____________________________________________
| > | > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > | > "Warning - Using the F1 Key will not break anything!" (-;
| > | > _____________________________________________
| > | >
| > | >
| > | > | Thank-you for the tip!
| > | > | Ok I have go the ASP method.
| > | > | My database name is Default
| > | > | Table name is Results
| > | > | Column name is Session1
| > | > | Database has no password
| > | > |
| > | > | In Session1 there can be 10 different Courses.
| > | > |
| > | > | Here is what I have so far:
| > | > | <SELECT name=TestBox>
| > | > | <option value=PS>Please Select</OPTION>
| > | > | < %
| > | > | sTestSQL = "SELECT Session1,COUNT(*) FROM Results AS myCount"
| > | > | Set connTest = Application("default_ConnectionString")
| > | > | connTest.Open
| > | > | Set rsTest = connTest.Execute(sTestSQL)
| > | > | Do While Not rsTest.EOF
| > | > | Response.Write "< Option value ='" & rsTest("varchar_session") & "' >" & _
| > | > | rsTest("varchar_session") & "< /option ">
| > | > | rsTest.MoveNext
| > | > | Loop
| > | > | rsTest.Close
| > | > | Set rsTest = Nothing
| > | > | connTest.Close
| > | > | Set connTest = Nothing
| > | > | %>
| > | > | </SELECT>
| > | > |
| > | > | But it seems that it doesn't open the Frontpage database to use the query.
| > | > | I think I'm missing something small.
| > | > |
| > | > | Thanks again for your help!
| > | > |
| > | > |
| > | > |
| > | > |
| > | > |
| > | > |
| > | > | "Stefan B Rusynko" wrote:
| > | > |
| > | > | > You don't want to use JavaScript
| > | > | > - and this is beyond the capability of the DBRW for server side coding
| > | > | > You will need to write custom ASP to populate the dropdown using a DB SQL Query that:
| > | > | > a) opens your list of courses table to get the course names for the dropdown
| > | > | > b) then does a count of registrations in your DB for each of those course names in a) to find which counts (from Jens
code
| > | > sample)
| > | > | > are less than 20
| > | > | > b) then retrieves just the course from a) that meet the requirements of b)
| > | > | >
| > | > | > --
| > | > | >
| > | > | > _____________________________________________
| > | > | > SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
| > | > | > "Warning - Using the F1 Key will not break anything!" (-;
| > | > | > _____________________________________________
| > | > | >
| > | > | >
| > | > | > | Thank-you for your reply. That brings me one step closer.
| > | > | > |
| > | > | > | I'll try to explain more what I'm trying to accomplish:
| > | > | > | I have 10 different courses.
| > | > | > | People register for the course by entering their information and then
| > | > | > | choosing a course from a dropdown list.
| > | > | > | I would like to have a cap/limit on the course. Like maybe a maximum of 20
| > | > | > | per course. So if the course is at it's maximum then the course would be
| > | > | > | disabled from the form's dropdown box.
| > | > | > | How would I go about setting the course limit so it will disable the option
| > | > | > | on the dropdown box.
| > | > | > |
| > | > | > |
| > | > | > | Im not sure where to put this query you provided. If I put it in a DWR
| > | > | > | custom query it results in the number that I'm looking for but I would like
| > | > | > | to use this number on a variable and then test the variable to see if it's >
| > | > | > | 20 and it if is then disable a form drop down option.
| > | > | > |
| > | > | > | To disable a drop down option I can use
| > | > | > | document.frm.course.remove(1);
| > | > | > | where course is the name of the drop down box. But I think this is
| > | > | > | javascript code so can I still use this? And if I can use this; where and how
| > | > | > | do I code it into the Code tab of the page?
| > | > | > |
| > | > | > | Thank-you very much for your help!
| > | > | > |
| > | > | > | "Jens Peter Karlsen" wrote:
| > | > | > |
| > | > | > | > You would use a query like this:
| > | > | > | > SELECT COUNT(*) FROM tablename AS myCount.
| > | > | > | > You then read the value in myCount and see if it is greater than your
| > | > | > | > limit and if it is, remove the course from the options.
| > | > | > | >
| > | > | > | > Regards Jens Peter Karlsen.
| > | > | > | >
| > | > | > | > On Thu, 11 Sep 2008 04:16:01 -0700, tech3677
| > | > | > | >
| > | > | > | > >I'm using frontpage forms to accept course registrations.
| > | > | > | > >The result is submitted to a database.
| > | > | > | > >I would like to query the database for a count of each course and if the
| > | > | > | > >course is at it's maximum then the form would disable that course option off
| > | > | > | > >of the form when the form loads.
| > | > | > | > >
| > | > | > | > >Any Suggestions? How to I do this? I'm just starting to working with
| > | > | > | > >frontpage databases.
| > | > | > | >
| > | > | >
| > | > | >
| > | > | >
| > | >
| > | >
| > | >
| >
| >
| >
