How do I display a selected Recordset from a database into a combobox?

[genkidave]

Hi there,

I'm trying to create an Update page in ASP so that I can update a student's info. The problem I'm having is displaying the current teacher the student currently has in the combobox. I'd like to have the current teacher already selected in the combobox so that I don't accidentally select the same teacher. (E.g. If a Student changes his/her teacher from Mr. Smith over to Mr. Jones, the in the Update page, Mr. Smith is already selected in the combobox, and I can select Mr. Jones within the same combobox)

Here are my tables and fields that I have;

tblTeachers

fldTeacherID
1
2
3

fldTeacherName
Mrs. Avery
Mr. Jones
Mr. Smith

tblStudents

fldStudentID
1
2
3

fldTeacherID
3 (Student 1 has Mr. Smith)
2 (Student 2 has Mr. Jones)
1 (Student 3 has Mrs. Avery)


Could someone help out please?

Thx!

Genki Dave

[GazMathias]

You simply need to compare the teacher ID to the one on the student record and mark it selected if it matches:

Not sure if you are using code to populate the combo boxes, but this approach usually works for me. I've not used your fieldnames so you will need to change them.

<select id="cboteacher">
<%
Do While Not teacher.EOF ' where teacher is an open recordset
  If student("teacherID") = teacher("teacherID") then selected = " Selected " Else Selected = " "
  response.Write "<option" & selected & "value =" & teacher.ID & ">" & teacher("name") & "</option>"
 teacher.MoveNext
Loop
%>
</select>
 

Obviously the student recordset needs to be open before the teacher one.

Gaz

[genkidave]

Hmm, I tried it, but I kept on getting errors. Thanks though. It said that teacher and student were "Undefined"???

Dave

[GazMathias]

Hi,

It is not working for you because I did not use your fieldnames in the code. You need to change them.

I illustrated it with fictional record sets named "teacher" and "student" and assumed that the field being compared was called "teacherID".

Simply change the record sets and field names to the real ones, or post your code here.

Gaz

[genkidave]

I understand that part and used my actual field names "tblTeachers" "fldTeacherName", etc... I still couldn't get it to work.

I'll figure it out somehow...

Dave

[GazMathias]

Hi,

If you are still having problems, post the code that directly pertains to this problem and I'll have a look, including all code that relates to the relevant record sets (opening moving through and closing). Remember to use code tags too ;)

Gaz.

[genkidave]

Actually, I figured it out and slapped my head. I was using the incorrect recordset name. The recordset "rsTeacher" should have been, "rsTeacherUpdate". all is well now, LoL!

Thanks for your patience and help!!!

Dave