Start Free Trial

asked on

Place specific records in ASP recordset on page

Hey Experts!!

I'm building a dynamic seating chart. We're storing everything in SQL Server 2000 and using Classic ASP/VBScript to connect.

What I need to know is this: How can I call certain records to certain parts of the page?

For example: I'm showing the entire seating chart and I also want to show whose sitting in which seat. How can I make a call on the page to pull just that particular seat ID?
So in row 1, let's say I have 5 desks. Seat ID's 1-5. I need to pull records 1-5 in the recordset. The recordset is pulling all of the rows, but I need to place the data in each particular desk.

I hope I explained that correctly.

Hi

this issue is easily resovled with the right database structure. You should have two tables, one for the seats themselves and one for the seating plan that has references to the seats.

For example:

Table one is constructed of two columns (TableID, SeatID)

Table two is constructed of two columns (SeatID, Person)

This way when you layout the seating plan you can run a SQL statement like this:
SELECT Person from TableTwo WHERE SeatID = 1/2/3/4 etc...

Jawa29

ASKER

But then I would be running a ton of recordsets wouldn't I? To give you a better example:

1) Load the page and it displays over 100 desks
2) I want each desk to populate with the proper record from the seatID
3) When you click on each of them, I can pass the seatID to the next page and get more details.

Is there no way to focus on a single id for each section?

Ok the way to do that would be to utilise the INNER JOIN function. So the SQL would look like this:

SELECT TableOne.SeatID, TableTwo.Person FROM TableOne INNER JOIN TableTwo ON TableOne.SeatID = TableTwo.SeatID

This would pull you a recordset back something like this

SeatID Person
1 Bob
2 Dave
3 Sally
4 Lisa
5 Maggie

Jawa29

Have a look at this project code that I made specifically for teaching here on EE and PSC
http://ee.cffcs.com/Q_24801116/Q_24801116.asp
code
http://ee.cffcs.com/Q_24801116/Q_24801116.zip

This will show you how to write out the code to show your records.
If you study the code, you should be able to do what you need to do.

If you have any questions about the code, and how to implement something like it into your project, please feel free to ask.

Have a good one
Carrzkiss

ASKER

@Jawa29. That's fine and all, but now how do I place a specific record in a specific place when I have over 100???

Record 1 goes to SeatID 1
Record 2 goes to SeatID 2
Record 3 goes to SeatID 3
etc.

Your seating plan page needs to be dynamic, then you can include a section like this.

Do While Not rs.eof
sSeatID = rs("SeatID")
sPerson = rs("Person")

'In here would be the code for the seating plan
Response.Write("<a href=somepage.asp?SeatID="" & sSeatID & "">" & sPerson & "</a>")

rs.MoveNext
Loop

Jawa29

ASKER

Sorry jawa29, I'm still pretty new to ASP. Where does the Do While Not go???

My recordset is below:

<%
Dim rsSeating
Dim rsSeating_cmd
Dim rsSeating_numRows

Set rsSeating_cmd = Server.CreateObject ("ADODB.Command")
rsSeating_cmd.ActiveConnection = MM_towneData_STRING
rsSeating_cmd.CommandText = "SELECT * FROM dbo.tblSeatingChart" 
rsSeating_cmd.Prepared = true

Set rsSeating = rsSeating_cmd.Execute
rsSeating_numRows = 0
%>

Open in new window

@LZ1
Did you have a look at the links and code that I provided above?
Thew will chow you what you need to know to get started in your project
And will also show you how to start coding properly in ASP Classic.

Good Luck
Carrzkiss

ASKER

@carrzkiss:So is Jawa29 on the right track at least? I think I see what I need to do, I just don't know how to do it.

Yes he is on the right track, but if you do not know how to code, then I strongly suggest that you look at what I provided, that code has taught over 100+ people from EE, PSC, and from my personal site.

Just download the code, and upzip it in a folder, link it to your IIS, and then call it, and you will see how simple and easy it is to understand what you need to do.
How to show recods, delete records, create records, update records.
Everything you need to know to get yourself going in the right direction.

Good Luck
Carrzkiss

ASKER

I am currently going over it. However, I'm still trying to wrap my head around how to apply it to individual rows vs. a whole dump of a table.

If you can give me some kind of idea of what you are wanting to accomplish, I will see if I have the extra time either this evening or first thing in the morning to make you an example, as long as it is not to involved.

Just give me your idea, and I will try to go from there.

Also, do you use "Dreamweaver"?
I ask, because the connection code to your database, reminds me of DW code.
I use DW to work work, but I code everything by hand, the only thing that
I generate, is my SQL Script for my database within Access or SQL Server.

Let me know your idea.
Carrzkiss

ASKER

I do use DW for my ASP code(I'm sorry, please forgive me) ;) However, I'm not beyond hand coding if I need to.

The idea is this:
1) Land on a page that has a background of a seating chart.
2) I have placed div elements over each desk
3) Inside the divs I want to show the appropriate name from the DB based on seatID.

I've hard coded the entire page because I have ID's based on each desk for the CSS positioning.

I've attached an image to hopefully better illustrate what I'm after.
ee1.jpg

Few questions.

#1: I see in the image, that there is only 23 desk, is that all that is needed in the project is the 23 desk locations, as well as the CEO and other locations?

#2: If this Dynamic Or Static?
Dynamic = means that if there is only 5 seats taken, then it will only show 5 seats
Static = means that it shows all seats and will show the names on the ones taken and on the other ones, it shows "Available".

ASKER

1) I have well over 100 desks, and I may need to add more. So the more flexible the solution, the better.
2) Right now it is static. Meaning that all the seats show all the time. Which is what the request was for, simply because if we need to add someone we can see the empty desks.

Yes, this is going to take some thinking.
@jawa29, any idea's from you would be great a well.

Looks like your using an image map, is that correct?

Jawa29

ASKER

The image is set as a background in CSS. I'm placing the divs statically using CSS

Can you supply an example DIV please?

I think I might be able to help but need to put my thinking head on :)

Jawa29

ASKER

This is the middle div. It contains everything in the middle of the seating chart.

<div class="mid">
						<a id="desk24" class="link desk" href="form.asp?seatId=24" title="Edit Desk">Desk 24</a>
						<a id="desk25" class="link desk" href="form.asp?seatId=25" title="Edit Desk">Desk 25</a>
						<a id="desk26" class="link desk" href="form.asp?seatId=26" title="Edit Desk">Desk 26</a>
						<a id="desk27" class="link desk" href="form.asp?seatId=27" title="Edit Desk">Desk 27</a>
						<a id="desk28" class="link desk" href="form.asp?seatId=28" title="Edit Desk">Desk 28</a>
						<a id="desk29" class="link desk" href="form.asp?seatId=29" title="Edit Desk">Desk 29</a>
						<a id="desk30" class="link desk" href="form.asp?seatId=30" title="Edit Desk">Desk 30</a>
						<a id="desk31" class="link desk" href="form.asp?seatId=31" title="Edit Desk">Desk 31</a>
						<a id="desk32" class="link desk" href="form.asp?seatId=32" title="Edit Desk">Desk 32</a>
						<a id="desk33" class="link desk" href="form.asp?seatId=33" title="Edit Desk">Desk 33</a>
						<a id="desk34" class="link desk" href="form.asp?seatId=34" title="Edit Desk">Desk 34</a>
						<a id="desk35" class="link desk" href="form.asp?seatId=35" title="Edit Desk">Desk 35</a>
						<a id="desk36" class="link desk" href="form.asp?seatId=36" title="Edit Desk">Desk 36</a>
						<a id="desk37" class="link desk" href="form.asp?seatId=37" title="Edit Desk">Desk 37</a>
						<a id="desk38" class="link desk" href="form.asp?seatId=38" title="Edit Desk">Desk 38</a>
						<a id="desk39" class="link desk" href="form.asp?seatId=39" title="Edit Desk">Desk 39</a>
						<a id="desk40" class="link desk" href="form.asp?seatId=40" title="Edit Desk">Desk 40</a>
						<a id="desk41" class="link desk" href="form.asp?seatId=41" title="Edit Desk">Desk 41</a>
						<a id="desk42" class="link desk" href="form.asp?seatId=42" title="Edit Desk">Desk 42</a>
						<a id="desk43" class="link desk" href="form.asp?seatId=43" title="Edit Desk">Desk 43</a>
						<a id="desk44" class="link desk" href="form.asp?seatId=44" title="Edit Desk">Desk 44</a>
						<a id="desk45" class="link desk" href="form.asp?seatId=45" title="Edit Desk">Desk 45</a>
						<a id="desk46" class="link desk" href="form.asp?seatId=46" title="Edit Desk">Desk 46</a>
						<a id="desk47" class="link desk" href="form.asp?seatId=47" title="Edit Desk">Desk 47</a>
						<a id="desk48" class="link desk" href="form.asp?seatId=48" title="Edit Desk">Desk 48</a>
						<a id="desk49" class="link desk" href="form.asp?seatId=49" title="Edit Desk">Desk 49</a>
						<a id="desk50" class="link desk" href="form.asp?seatId=50" title="Edit Desk">Desk 50</a>
						<a id="desk51" class="link desk" href="form.asp?seatId=51" title="Edit Desk">Desk 51</a>
						<a id="desk52" class="link desk" href="form.asp?seatId=52" title="Edit Desk">Desk 52</a>
						<a id="desk53" class="link desk" href="form.asp?seatId=53" title="Edit Desk">Desk 53</a>
						<a id="desk54" class="link desk" href="form.asp?seatId=54" title="Edit Desk">Desk 54</a>
						<a id="desk55" class="link desk" href="form.asp?seatId=55" title="Edit Desk">Desk 55</a>
						<a id="desk56" class="link desk square" href="form.asp?seatId=56" title="Edit Desk">Desk 56</a>
						<a id="desk57" class="link desk vertical" href="form.asp?seatId=57" title="Edit Desk">Desk 57</a>
						<a id="desk58" class="link desk" href="form.asp?seatId=58" title="Edit Desk">Desk 58</a>
						
						<a id="desk108" class="link desk vertical" href="form.asp?seatId=108" title="Edit Desk">Desk 108</a>
						<a id="desk109" class="link desk vertical" href="form.asp?seatId=109" title="Edit Desk">Desk 109</a>
						
						
					</div><!--end mid -->

Open in new window

I am thinking something like this maybe.

Create your variables, to help keep everything clean.

SeatID = rs("SeatID") ' This gets the seat ID's
' Now for the seats, we might be able to do something like this.

<%if SeatID<>"" then%>
<a id="desk24" class="link desk" href="form.asp?seatId=24" title="Edit Desk">Desk 24</a>
<%else%>Desk 24 is Available<%end if%>

But, this theory will have to be put on every seat.
This is a way to get it going anyway, this will make it so that it will show all the seats that are available, and all the seats that are taken.

That is one Theory, will continue to think about it.

Hold it, that is way wrong.

You have the 2 tables joining together, so that it will see if the table is available.

So, are all the tables already listed in the database?
As in, do you have
24
25
26
exc...
In the database already, and then you have your Table with the peoples name in it.
If that is the case, then we can use the example I provided above, with some changes of course to make it work.

Let me know

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Sorry for the delay guys, I had a bit of a fire to put out in another project.

If you guys are still with me:
@Jawa29: So doing 2 tables is really the only way to go about it? Or the only normalized way to go about it?

@carrzkiss: I have 1 table and all of the records in there already. I have about 110 of them right now.

Do as Jawa29 has suggested.
If you are unable to get the results that you are needing, let me know and I will try to assist further, but it looks like Jawa29 has it here.

Except, I would change the Response.write.
Removing them completely, so that you can design the page around the code.
Using Response.Write is not a good way to design your pages.

Good Luck
Carrzkiss

ASKER

@Jawa29: All of my positioning is handled through CSS via the ID. Am I still going to keep it this way? I'd like to just in case this application gets more attention from the PM than I'm anticipating.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I agree Carrzkiss. I'd rather keep my HTML separate from my ASP for simplicity's sake.

I agree with you Carrkiss, I normally do as you have done but I know out there people prefer to use response.write.

Two tables is the normalized way to do this, it just separates the static data (desks) with the trasiant data (person sitting there)

Jawa29

ASKER

Ok, so I've broke my DB table into 2 as you suggested jawa29. Now, for the coding. I have 3 sections so would I need 3 different select statements?

Hi

Yes you would, the code I supplied would need to be run three time to generate the code you require hench the addition of location column. this allows you to pull the records back for each section when you want them.

Jawa29

ASKER

Ok, no problem. So would I just run each recordset and loop through them to create the code?

Yes that's right

ASKER

I got it working perfectly! Thanks guys for all the help, I really appreciate it.