Response Write - Recordsets

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Response Write - Recordsets

Logged in as: Guest

arrSession:exec spGetSession 2,3,45274

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> ASP >> Response Write - Recordsets
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Response Write - Recordsets - 4/1/2007 5:43:29 AM

JBarbea1

Posts: 20
Score: 0
Joined: 3/27/2007
Status: offline

Hey everyone, this is my first post on VBScript, and I am a beginner programmer, so I could use a little help.

Background:
- I'm dealing with recordsets and ID's.
- Want to "response.write" column name where = ID
- Workin with 2 recordsets
- SQL Database
- ASP

Here goes.

I'll try to explain this in words. Im trying to write a simple script, if you will, that displays the name of a course in a recordset based on an ID from another recordset.

--------------------
Recordset 1 (Enrolled Courses) <----- This Recordset is based on the Student ID

C1_Enr -- C1_Enr
1               2

--------------------
Recorset 2 (Courses_ID's) <------ This recordset is all the courses offered
ID -- Course_Name
1    -- This Name
2    -- Another Name

My Code
<%

IF IsNULL((rsCEDUStudent.Fields.Item("C2_Enr").Value))
   Then Response.Write("Empty")
       ELSE IF
           (CStr((rsCEDUStudent.Fields.Item("C2_Enr").Value)) = CStr(rsCourses.Fields.Item("CEDU_Course_ID")))
           Then
               Response.Write(rsCourses.Fields.Item("Course_Name").Value) <---- This is where I am having a problem, its not displaying the correct Course Name because I dont think it knows which record its suppose to have, but I'm not sure how to specify. Its like, Write "Course_Name" where Course_ID = C2_Enr, but I dont know how to write that in VBScript (can I call a stored procedure (SQL) here?)
%>

I just want the write line to dispay the name of the course where its = to the ID.

Post #: 1

RE: Response Write - Recordsets - 4/3/2007 4:23:38 AM

Country73

Posts: 710
Score: 8
Joined: 8/25/2004
From: USA
Status: offline

So basically your connecting to one database that has the individuals name in one column followed by the courses they have enrolled in.
The classes they have enrolled in are designated by a number that ties into another database, by number, which lists the course name?

DB1 -
UserName, 1, 2, etc...

DB2 -
1 = Course Name
2 = Course Name
etc...

How is the name passed through your script?
In the first database you will need to go through each recordset until the name matches up with the name you have passed to the script.
If the name matches, you will then take the number (associated with the class) and go through each ID in the second database until it matches the number you passed from the first DB.

So this doesn't get too confusing, can you supply a little bit more of your code?

(in reply to JBarbea1)

Post #: 2

RE: Response Write - Recordsets - 4/3/2007 10:48:28 PM

JBarbea1

Posts: 20
Score: 0
Joined: 3/27/2007
Status: offline

Hey Country, thanks for the response. You are correct in your analysis!

A litte more background:

I am getting the "Name" I am looking for, or the student I am looking for by passing the _ID via "Request.QueryString("String"), so that recordset is working fine. (I've since change the Query.String to a Session Variable so that users cant manipulate the URL.

I guess I could also pass the Course_ID, but I have roughly 6 ID that would have to be passed.

How do I "go through each ID in the second database (table in my case) until it matches the number you passed from the first DB"?

Im working from One Database, just different tables, using 2 recordsets. The recordsets are filled based based on the Students ID, and the Course recordset is filled with ALL the courses I have available.

Im not positive that I have any other code to supply that would help, I have the two recordsets, i just need to know how to do what you said above. "Go through each ID of the second recordset, when the ID matches, Reponse.Write the course name"

<% ' Course Recordset for Continuing Education Units (CEU)
Dim rsCourses
Dim rsCourses_cmd
Dim rsCourses_numRows
Set rsCourses_cmd = Server.CreateObject ("ADODB.Command")
rsCourses_cmd.ActiveConnection = MM_HCI_STRING
rsCourses_cmd.CommandText = "SELECT CEDU_Course_ID, Course_Name, Course_Date, Department, Course_Cost FROM xxx.CEDU_COURSES WHERE Course_Date > GetDate()"
rsCourses_cmd.Prepared = true
Set rsCourses = rsCourses_cmd.Execute
rsCourses_numRows = 0
%>

Hope this helps a little, I could sure use the help. As I've said, little new to VBScript, but my confidence grows each day!

(in reply to Country73)

Post #: 3

RE: Response Write - Recordsets - 4/4/2007 2:59:07 AM

Country73

Posts: 710
Score: 8
Joined: 8/25/2004
From: USA
Status: offline

Alright, I'm going to take a stab at this, but just so you know it has been a while since I have messed with this.
Here is a sight that I reference whenever I have to get back into any SQL Queries:
http://www.w3schools.com/sql/sql_intro.asp
(Which may be able to assist you more than what I can do)

Here's my attempt.

When you connect to your first table, you are able to extract the student "Name", are you also able to extract the course numbers for that individuals "Name"?
I'm assuming that your table looks something like this:
Recordset1
ID C1 Enr C1 Enr C1 Enr
"Name" 1 2 3

For the classes the individual is enrolled in, do they all have the same title or are they more like "C1 Enr", "C2 Enr", "C3 Enr" etc... ?
Once you locate the "Name", set a variable for each course that individual has taken.
Use those variables to query Recordset2, the same way you queried the "Name" in your first table.

(in reply to JBarbea1)