Solved

Place specific records in ASP recordset on page

Posted on 2011-09-28
34
379 Views
Last Modified: 2012-08-14
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.
0
Comment
Question by:LZ1
  • 15
  • 10
  • 9
34 Comments
 
LVL 8

Expert Comment

by:jawa29
Comment Utility
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
0
 
LVL 30

Author Comment

by:LZ1
Comment Utility
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?
0
 
LVL 8

Expert Comment

by:jawa29
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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
0
 
LVL 30

Author Comment

by:LZ1
Comment Utility
@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.
0
 
LVL 8

Expert Comment

by:jawa29
Comment Utility
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
0
 
LVL 30

Author Comment

by:LZ1
Comment Utility
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

0
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
@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
0
 
LVL 30

Author Comment

by:LZ1
Comment Utility
@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.
0
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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
0
 
LVL 30

Author Comment

by:LZ1
Comment Utility
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.
0
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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
0
 
LVL 30

Author Comment

by:LZ1
Comment Utility
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
0
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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".

0
 
LVL 30

Author Comment

by:LZ1
Comment Utility
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.
0
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
Yes, this is going to take some thinking.
@jawa29, any idea's from you would be great a well.
0
 
LVL 8

Expert Comment

by:jawa29
Comment Utility
Looks like your using an image map, is that correct?

Jawa29
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 30

Author Comment

by:LZ1
Comment Utility
The image is set as a background in CSS. I'm placing the divs statically using CSS
0
 
LVL 8

Expert Comment

by:jawa29
Comment Utility
Can you supply an example DIV please?

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

Jawa29
0
 
LVL 30

Author Comment

by:LZ1
Comment Utility
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

0
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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.
0
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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
0
 
LVL 8

Accepted Solution

by:
jawa29 earned 400 total points
Comment Utility
If you want to make this as dynamic as possible then a couple of things need to be changed in the database before we move forward.

If we go back to my original post and modify the database slightly as follows:

Table one is constructed of three columns (SeatID, Location, Pitch)

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

This would enable us to specify in the first DB table some useful information which will help when we build the page. This would be how some of your mid section would be held in the database.

SeatID  Location  Pitch
55         Mid          NULL
56         Mid          square
57         Mid          vertical
58         Mid          NULL

So now to Dynamically build your mid section I would construct like this:

SELECT TableOne.SeatID, TableOne.Pitch, TableTwo.Person FROM TableOne LEFT OUTER JOIN TableTwo ON TableOne.SeatID = TableTwo.SeatID WHERE TableOne.Location = 'Mid'
'Enter code to open recordset

Response.Write("<div class=""Mid"">")

Do While Not rs.eof
   sSeatID = rs("SeatID")
   sPitch = rs("Pitch")
   If sPitch <> "" Then
       sPitch = " " & sPitch
   End If
   sPerson = rs("Person")

   If sPerson <> "" Then
       Response.Write("<a id=""desk" & sSeatID & """ class=""link desk" & sPitch & """ href=""form.asp?seatId=" & sSeatID & """ title=""Edit Desk"">" & sPerson & "</a>")
   Else
       Response.Write("<a id=""desk" & sSeatID & """ class=""link desk" & sPitch & """ href=""form.asp?seatId=" & sSeatID & """ title=""Edit Desk"">Desk " & sSeatID & "</a>")
   End If

   rs.MoveNext
Loop

Response.Write("</div><!--end mid -->")

Jawa29
0
 
LVL 30

Author Comment

by:LZ1
Comment Utility
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.
0
 
LVL 30

Expert Comment

by:Wayne Barron
Comment Utility
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
0
 
LVL 30

Author Comment

by:LZ1
Comment Utility
@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.
0
 
LVL 30

Assisted Solution

by:Wayne Barron
Wayne Barron earned 100 total points
Comment Utility
This is what I was referring too.
This will allow you to continue working in your page, as you have been.
Using Response.Write, is only good when you are doing Mail messages and the such.

Good Luck
Carrzkiss
<div class="Mid">
<%
Do While Not rs.eof
   sSeatID = rs("SeatID")
   sPitch = rs("Pitch")
   If sPitch <> "" Then
       sPitch = " " & sPitch
   End If
   sPerson = rs("Person")

   If sPerson <> "" Then%>
   
<a id="desk<%=sSeatID%>" class="link desk<%=sPitch%>" href="form.asp?seatId=<%=sSeatID%>" title="Edit Desk"><%=sPerson%></a>
   <%Else%>
<a id="desk<%=sSeatID%>" class="link desk<%=sPitch%>" href="form.asp?seatId=<%=sSeatID%>" title="Edit Desk">Desk <%=sSeatID%></a>
  <% End If

   rs.MoveNext
Loop%>

</div>
<%'end mid%>

Open in new window

0
 
LVL 30

Author Comment

by:LZ1
Comment Utility
I agree Carrzkiss. I'd rather keep my HTML separate from my ASP for simplicity's sake.
0
 
LVL 8

Expert Comment

by:jawa29
Comment Utility
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
0
 
LVL 30

Author Comment

by:LZ1
Comment Utility
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?
0
 
LVL 8

Expert Comment

by:jawa29
Comment Utility
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
0
 
LVL 30

Author Comment

by:LZ1
Comment Utility
Ok, no problem. So would I just run each recordset and loop through them to create the code?
0
 
LVL 8

Expert Comment

by:jawa29
Comment Utility
Yes that's right
0
 
LVL 30

Author Comment

by:LZ1
Comment Utility
I got it working perfectly!  Thanks guys for all the help, I really appreciate it.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now