Solved

Place specific records in ASP recordset on page

Posted on 2011-09-28
34
388 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 10
  • 9
34 Comments
 
LVL 8

Expert Comment

by:jawa29
ID: 36717620
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
ID: 36717647
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
ID: 36717762
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 31

Expert Comment

by:Wayne Barron
ID: 36717779
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
ID: 36717862
@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
ID: 36717912
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
ID: 36718380
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 31

Expert Comment

by:Wayne Barron
ID: 36718684
@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
ID: 36719163
@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 31

Expert Comment

by:Wayne Barron
ID: 36719392
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
ID: 36719406
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 31

Expert Comment

by:Wayne Barron
ID: 36719682
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
ID: 36719732
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 31

Expert Comment

by:Wayne Barron
ID: 36719821
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
ID: 36719835
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 31

Expert Comment

by:Wayne Barron
ID: 36815715
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
ID: 36815725
Looks like your using an image map, is that correct?

Jawa29
0
 
LVL 30

Author Comment

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

Expert Comment

by:jawa29
ID: 36815834
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
ID: 36815930
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 31

Expert Comment

by:Wayne Barron
ID: 36815991
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 31

Expert Comment

by:Wayne Barron
ID: 36816011
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
ID: 36816308
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
ID: 36911876
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 31

Expert Comment

by:Wayne Barron
ID: 36912023
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
ID: 36912185
@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 31

Assisted Solution

by:Wayne Barron
Wayne Barron earned 100 total points
ID: 36912279
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
ID: 36912440
I agree Carrzkiss. I'd rather keep my HTML separate from my ASP for simplicity's sake.
0
 
LVL 8

Expert Comment

by:jawa29
ID: 36915351
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
ID: 36916760
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
ID: 36916904
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
ID: 36916923
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
ID: 36917014
Yes that's right
0
 
LVL 30

Author Comment

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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

630 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