|
| |
Using an ASP Form to query a database to create a table?
View related threads:
(in this forum
| in all forums)
|
Logged in as: Guest
|
|
|
remnatch
Posts: 27
Joined: 7/6/2004
Status: offline
 |
Using an ASP Form to query a database to create a table? - 8/9/2004 11:45:00
Hey all,
In an effort to make our site more dynamic, we're looking to update our On Call page.
As is now, the On Call page gives a hyperlink in the form of a person's name, with the contact information contained behind the link.
I'm attempting to use an ASP form to make these changes, by querying a database to call the link and the name upon a post.
As is, I'm a little lost.
Could anyone help point me in the right direction?
Thanks,
natch.
|
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/9/2004 13:06:26
quote:
I'm attempting to use an ASP form to make these changes, by querying a database to call the link and the name upon a post.
Sorry natch, but I am not clear on what you mean by the above statement. I would simply have a db table with all the data pertaining to each person who may be on call. I would then have a separate table, OnCall, containing the ID of the on call person and the period for which he/she is on call.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
remnatch
Posts: 27
Joined: 7/6/2004
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/9/2004 13:10:23
Thanks Duane,
I'll try to clarify.
Say Person A is the primary on call and Person B is the secondary on call.
Our company already has a wonderful personnel database, with more information then I could ever hope to compile. I want to use an ASP form to change the weekly on call, so person A becomes person C, and person B becomes person D, with the hyperlinks connecting to our "Who's who" Database reflecting these changes.
Sorry for the confusion.
natch.
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/9/2004 13:16:12
So is there an "OnCall" table that you will be changing via the form? If so, what are the fields in the table?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
remnatch
Posts: 27
Joined: 7/6/2004
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/9/2004 13:26:31
My table is entitled "OnCall"
I have a field for the hyperlink, a field for the Primary On Call individual, and a field for the Secondary On Call individual.
I figured that on the page, those are the three variables that I have, and need to change, so that would be the way to start.
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/9/2004 14:37:24
OK, next question. Are you going to do this in FrontPage or pure ASP? What have you tried so far? I would think you would have a user go to a form that would be prepopulated with the currect OnCall data. The user could then change the data. The form then would submit to a second ASP page that would perform an update on the OnCall table.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
remnatch
Posts: 27
Joined: 7/6/2004
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/9/2004 14:40:18
I'm using Frontpage and Dreamweaver to create an asp form.
I have the table in Access fully populated with information. I want to pull that information from the database at will, using the form as a tool to decide which name gets displayed and when... if that makes sense.
I was going to use a form to submit to the script to do the writing.
| |
|
|
|
reric
Posts: 105
Joined: 4/28/2004
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/10/2004 7:59:13
Here is a couple of things i would do.
1. Add a new column and name it "ocstatus" on call status. in the form, add 3 radio buttons primary, secondary, not on call. map that group to the new database column. from there you can just change the radio button after you pull up the name from a form. in the sql statement for the web page you now can have a select * WHERE ocstatus = "primary" and "secondary" order by OCstatus asc. Why order by oc status ascending? p comes before S so that all your primary's are listed first.
2. if your primaries and secondaries are predertermined far in advance you then add an "ocdate" column and use an sql statement that orders who is on call by the date. I don't know that sql statement.
hope this helps
_____________________________
I have no siggy yet
| |
|
|
|
remnatch
Posts: 27
Joined: 7/6/2004
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/11/2004 8:45:47
Hey reric, thanks for the help.
I do have a few questions.
Why do I need to use radio buttons? Wouldn't drop down menus be enough?
Also, my original plan was to use the drop downs to create an entry in the database, for a Primary OC, and a Secondary OC. From there, I was going to have the database pull the most recent entry in the form of a hyperlink.
Would that work, or would I need to approach this in a different way?
Thanks again,
natch.
| |
|
|
|
remnatch
Posts: 27
Joined: 7/6/2004
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/11/2004 10:19:51
Hey all,
After discussing with my teammate, I've decided to change my approach.
To start, here's my preliminary form. There are still minor edits to be made, but this is pretty much what it's going to look like.
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Team Member</title>
</head>
<body>
<form method="POST" action="OCTrans.asp">
<table border="0" cellspacing="1" style="border-collapse: collapse" bordercolor="#111111" id="AutoNumber1" height="429">
<tr>
<td width="168" height="32">
<p align="center"><b><font face="Verdana" size="2">Team Member</font></b></td>
<td width="97" height="32"><b><font face="Verdana" size="2">Primary OnCall</font></b></td>
<td width="103" height="32"><b><font face="Verdana" size="2">Secondary
OnCall</font></b></td>
<td width="71" height="32"><b><font face="Verdana" size="2">Off</font></b></td>
</tr>
<tr>
<td width="311" bgcolor="#800000" height="19" bordercolor="#800000" colspan="4"> </td>
</tr>
<tr>
<td width="168" height="20"><b><font face="Verdana" size="2">Person A</font></b></td>
<td width="97" height="20"><input type="checkbox" name="Primary" value="ON"></td>
<td width="103" height="20"><input type="checkbox" name="Secondary" value="ON"></td>
<td width="71" height="20"><input type="checkbox" name="Off" value="ON"></td>
</tr>
<tr>
<td width="311" bgcolor="#800000" height="19" bordercolor="#800000" colspan="4"> </td>
</tr>
<tr>
<td width="168" height="20"><b><font face="Verdana" size="2">Person B</font></b></td>
<td width="97" height="20"><input type="checkbox" name="Primary" value="ON"></td>
<td width="103" height="20"><input type="checkbox" name="Secondary" value="ON"></td>
<td width="71" height="20"><input type="checkbox" name="Off" value="ON"></td>
</tr>
<tr>
<td width="311" bgcolor="#800000" height="19" bordercolor="#800000" colspan="4"> </td>
</tr>
<tr>
<td width="168" height="20"><b><font face="Verdana" size="2">Person C</font></b></td>
<td width="97" height="20"><input type="checkbox" name="Primary" value="ON"></td>
<td width="103" height="20"><input type="checkbox" name="Secondary" value="ON"></td>
<td width="71" height="20"><input type="checkbox" name="Off" value="ON"></td>
</tr>
<tr>
<td width="311" bgcolor="#800000" height="19" bordercolor="#800000" colspan="4"> </td>
</tr>
<tr>
<td width="168" height="20"><b><font face="Verdana" size="2">Person D</font></b></td>
<td width="97" height="20"><input type="checkbox" name="Primary" value="ON"></td>
<td width="103" height="20"><input type="checkbox" name="Secondary" value="ON"></td>
<td width="71" height="20"><input type="checkbox" name="Off" value="ON"></td>
</tr>
<tr>
<td width="311" bgcolor="#800000" height="19" bordercolor="#800000" colspan="4"> </td>
</tr>
<tr>
<td width="168" height="20"><b><font face="Verdana" size="2">Person E</font></b></td>
<td width="97" height="20"><input type="checkbox" name="Primary" value="ON"></td>
<td width="103" height="20"><input type="checkbox" name="Secondary" value="ON"></td>
<td width="71" height="20"><input type="checkbox" name="Off" value="ON"></td>
</tr>
<tr>
<td width="311" bgcolor="#800000" height="19" bordercolor="#800000" colspan="4"> </td>
</tr>
<tr>
<td width="168" height="20"><b><font face="Verdana" size="2">Person F</font></b></td>
<td width="97" height="20"><input type="checkbox" name="Primary" value="ON"></td>
<td width="103" height="20"><input type="checkbox" name="Secondary" value="ON"></td>
<td width="71" height="20"><input type="checkbox" name="Off" value="ON"></td>
</tr>
<tr>
<td width="311" bgcolor="#800000" height="19" bordercolor="#800000" colspan="4"> </td>
</tr>
<tr>
<td width="168" height="20"><b><font face="Verdana" size="2">Person G</font></b></td>
<td width="97" height="20"><input type="checkbox" name="Primary" value="ON"></td>
<td width="103" height="20"><input type="checkbox" name="Secondary" value="ON"></td>
<td width="71" height="20"><input type="checkbox" name="Off" value="ON"></td>
</tr>
<tr>
<td bgcolor="#800000" bordercolor="#800000" colspan="4" height="8"> </td>
</tr>
<tr>
<td width="168" height="20"><b><font face="Verdana" size="2">Person H</font></b></td>
<td width="97" height="20"><input type="checkbox" name="Primary" value="ON"></td>
<td width="103" height="20"><input type="checkbox" name="Secondary" value="ON"></td>
<td width="71" height="20"><input type="checkbox" name="Off" value="ON"></td>
</tr>
<tr>
<td bgcolor="#800000" bordercolor="#800000" colspan="4" height="19"> </td>
</tr>
<tr>
<td width="168" height="20"><b><font face="Verdana" size="2">Person I</font></b></td>
<td width="97" height="20"><input type="checkbox" name="Primary" value="ON"></td>
<td width="103" height="20"><input type="checkbox" name="Secondary" value="ON"></td>
<td width="71" height="20"><input type="checkbox" name="Off" value="ON"></td>
</tr>
</table>
<p>
<input type="submit" value="Submit" name="B1" style="font-family: Verdana; font-size: 10pt; font-weight: bold"><input type="reset" value="Reset" name="B2" style="font-family: Verdana; font-size: 10pt; font-weight: bold"></p>
</form>
</body>
</html>
What I want my webpage to be able to do is display only the individuals for which Primary and Secondary are checked. How do I update my database with the appropriate information, if I even need to? And how do I get my web page to display the Primary and Secondary individuals based on which switches are pressed?
Thank you again, in advance, for all of your help and guidance.
natch.
| |
|
|
|
reric
Posts: 105
Joined: 4/28/2004
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/12/2004 7:15:38
If you have access then you can add the on call column and have your drop down menu point to that column. You don't need radio buttons, that is just what I use. I am still a little unsure how long someone is on call. However my original post should send you in the right direction so that the web page will pull the data that you need. hope this helps
| |
|
|
|
remnatch
Posts: 27
Joined: 7/6/2004
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/13/2004 8:11:55
Reric,
I'm still not sure what you mean. Could you provide an example for me?
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/13/2004 8:19:09
Not sure how much help this will be, but another user was also building a type of "On-call" system. Here is the discussion we had:
http://www.frontpagewebmaster.com/m_205657/tm.htm#214287
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
remnatch
Posts: 27
Joined: 7/6/2004
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/13/2004 8:46:24
Duane, thanks for the referral, but his DB and his set up is much more complicated then what I need.
Looking at the examples Nate gave, let me set up my scenario in a way that's easily understandable... so I can figure out what direction I need to head next.
- Form -
I have two drop down menus, PrimaryTeam and SecondaryTeam. The team members names are the values inside the dropdown.
I have two radio button groups, Primary and Secondary... each designed to choose what On Call duty they are assigned for. I also have two checkboxes, to turn On Call off for a selected person.
- DB -
I have 4 fields. TeamMember, which contains the hyperlink to be displayed, Primary, Secondary, and Off, yes/no fields, each of which determines the person's on call status.
I want my form to change the yes/no fields to reflect the current on call status, and create an asp page which can read and display the hyperlink from the DB according to which yes/no fields are selected.
I have a sinking feeling I'm going about this entirely the wrong way... and my rookie-ness isn't helping at all.
Thanks again,
natch.
EDIT:
I also have a form designed, without drop downs using radio buttons and checkboxes, with the thought of turning each yes/no field off individually for each Team Member. But now, that doesn't seem the way to go.
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/13/2004 9:05:10
So how many people are on call at a time? What is the distinction between the primary team and the secondary team? Are the names shown in the primary team different from those shown in the secondary team? I assume, with the final .asp page you simply want to display who is on call, right?
quote:
I also have a form designed, without drop downs using radio buttons and checkboxes, with the thought of turning each yes/no field off individually for each Team Member. But now, that doesn't seem the way to go.
Depending on the answers to my questions above, your second approach seems perhaps the correct way. For example, you say you have two dropdowns, but if you have to change the status for more than two people, how will that happen?
I am sure what you are trying to do is very possible, but not sure I have captured in my mind a clear vision of what the final product needs to be.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
remnatch
Posts: 27
Joined: 7/6/2004
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/13/2004 9:36:38
I have a primary and a secondary on call, two individuals, each being drawn from the same pool of team members.
For example, Person A can be Primary On Call, but may be Secondary On call a week or two later.
With the final ASP page, all I want to be able to do is pull the appropriate hyperlinks from my database so inquiring parties can reference our Who's Who database.
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/13/2004 9:56:04
OK. Next question(s). Do you only care about who is currently on call or do you want to be able to keep a history or perhaps even show is on-call 1 or 2 weeks down the road?
Personally I would probably create 2 tables, namely, Employees and OnCall. The fields would be:
The Employee table would include simply:
EmployeeID = Autonumber
Emp_Name = Text (the Employee's name)
Emp_Link = Text (the hyperlink to the employee's data in your other db)
Then the OnCall table:
ID = Autonumber
Primary_OnCall = Numeric (the EmployeeID from the Employee table)
Secondary_OnCall = Numeric (the EmployeeID from the Employee table)
Start_Date = Date
End_Date = Date
Then your form could still have a dropdown for Primary OnCall and Secondary OnCall. In the dropdown, you would display the Employees' names, but the value you submit will be the employee's Employee_ID. Then assuming both the Primary and Secondary OnCalls persons change on the same dates, add 2 textboxes, one for start date and one for end date.
So you submit the form to the db. You then can have a form wherein the user simply enters the date for which he wants to see who is on call. The form submits to say, OnCall.asp, whereon you have a db query along the lines of SELECT * FROM OnCall WHERE #::OnCallDate::# BETWEEN Start_Date AND End_Date.
Does that help?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
remnatch
Posts: 27
Joined: 7/6/2004
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/13/2004 9:58:24
Yes, it does, Duane.
Things are a lot clearer that way.
To answer your question, I only care about who is currently on call, not who was on call prior. We keep a calendar for posterity for ourselves, and the page is just to keep our customers in the loop.
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Using an ASP Form to query a database to create a t... - 8/13/2004 10:27:02
I would still use the dates as I suggested. But what you can do is bypass the page where the user enters a date and then in OnCall asp put the current date in your db query. Something like:
SELECT * FROM OnCall WHERE #"&Now()&"# BETWEEN Start_Date AND End_Date.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
