asp_databases thread: Opening multiple recordsets - HELP
Message #1 by "Chad Gaines" <cgaines@c...> on Mon, 25 Mar 2002 22:40:12
|
|
I am trying to pull information from an access database into form fields
that can be updated. The information pulls in fine. I wanted to take it
a step further and open up a second recordset for a table lookup on a
table called "tblBldg" for a field in the form called "building". I keep
getting an error and I am sure it is something with my syntax and how I am
opening the recordset to pull in the values from the building table.
=======================================
<%@ Language=VBScript %>
<html>
<head>
<meta NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<link rel="stylesheet" type="text/css" href="../style/cmc.css">
<body>
<!--#include file="../cmcnet/include/top.asa" -->
<table border="0" width="750">
<tr>
<td width="165" class="menu"><a href="http://www.gocmc.edu">CMC
Home</a></td>
<td width="585" class="header"><p align="left">Update Your Directory
Information...</p></td>
</tr>
<tr>
<td width="750" colspan="2"><hr width="750" noshade color="#000000">
</td>
</tr>
</table>
<%
'retrieve catalogue of products from DB and write to table
Dim adoConn
Dim adoCmd
Dim adoRS
Dim strConnectionString
Dim VarPassword
Dim VarEmail
VarPassword = CStr(Request.Form("password"))
VarEmail = CStr(Request.Form("email"))
'create ADO Objects
Set adoConn = Server.CreateObject("ADODB.Connection")
Set adoCmd = Server.CreateObject("ADODB.Command")
Set adoRS = Server.CreateObject("ADODB.Recordset")
Set adoRSbldg = Server.CreateObject("ADODB.Recordset")
strConnectionString="DSN=CMC;"
'set ADO Command Properties
adoCmd.CommandType = 1
adoCmd.CommandText = "SELECT tblStaff.* FROM tblStaff WHERE email = '" &_
VarEmail & "'"
'Open the Connection
adoConn.Open strConnectionString
'associate the Command with the Open Connection
adoCmd.ActiveConnection = adoConn
'retrieve recordset
Set adoRS = adoCmd.Execute
%>
<table border="0" width="771">
<tr>
<td width="174" valign="top"><!--#include
file="../cmcnet/include/side.asa" --></td>
<td class="body" width="597" valign="top"><table border="0"
cellPadding="1"
cellSpacing="1" width="590">
<table>
<form action="showprofile.asp" method="post">
<input type="hidden" name="newserial_no" size="20" value="<%=adoRS
("serial_no")%>">
<p><b>Welcome back <%=adoRS("first_name")%></b>
<tr class="body">
<td class="body">First Name</td>
<td><input type="text" name="newfname" size="20" value="<%=adoRS
("first_name")%>"></td>
</tr>
<tr class="body">
<td class="body">Middle Name</td>
<td><input type="text" name="newmname" size="20" value="<%=adoRS
("middle_name")%>"></td>
</tr>
<tr class="body">
<td class="body">Last Name</td>
<td><input type="text" name="newlname" size="20" value="<%=adoRS
("last_name")%>"></td>
</tr>
<tr class="body">
<td class="body">Title</td>
<td><input type="text" name="newtitle" size="30" value="<%=adoRS
("title")%>"></td>
</tr>
<tr class="body">
<td class="body">Department</td>
<td><input type="text" name="newdept" size="4" value="<%=adoRS("dept")%
>"></td>
</tr>
<tr class="body">
<td class="body">Employed Since</td>
<td><input type="text" name="newempyear" size="4" value="<%=adoRS
("emp_year")%>"></td>
</tr>
>>>>>I GET THE ERROR HERE:<<<<
<% adoRSbldg.Open "Select * from tblBldg" %>
<tr class="body">
<td class="body">Building</td>
<td><%do while not adoRSbldg.EOF%><option value="bldg=<%=adoRSbldg
("bldg")%>"><%adoRSbldg("txt")%>
<%adoRSbldg.MoveNext
loop
%>
<%adoRSbldg.close%>
</td>
</tr>
<tr class="body">
<td class="body">Room Number</td>
<td><input type="text" name="newroom" size="4" value="<%=adoRS("room")%
>"></td>
</tr>
<tr class="body">
<td class="body">Phone Number</td>
<td><input type="text" name="newphone" size="15" value="<%=adoRS
("phone")%>"></td>
</tr>
<tr class="body">
<td class="body">Fax Number</td>
<td><input type="text" name="newfax" size="15" value="<%=adoRS("fax")%
>"></td>
</tr>
<tr class="body">
<td class="body">Email</td>
<td><input type="text" name="newemail" size="20" value="<%=adoRS
("email")%>"></td>
</tr>
<tr><td> </td></tr>
<tr class="body"><td class="body"><b>Education Information
1</b></td></tr>
<tr class="body">
<td class="body">Institution</td>
<td><input type="text" name="newed1" size="40" value="<%=adoRS("ed1")%
>"></td>
</tr>
<tr class="body">
<td class="body">Institution City</td>
<td><input type="text" name="newcity1" size="20" value="<%=adoRS
("ed1_city")%>"></td>
</tr>
<tr class="body">
<td class="body">Institution State</td>
<td><input type="text" name="newstate1" size="2" value="<%=adoRS
("ed1_st")%>"></td>
</tr>
<tr class="body">
<td class="body">Degree Obtained</td>
<td><input type="text" name="newdegree1" size="40" value="<%=adoRS
("ed1_deg")%>"></td>
</tr>
<tr class="body">
<td class="body">Year Graduated</td>
<td><input type="text" name="newgradyr1" size="4" value="<%=adoRS
("ed1_grad_yr")%>"></td>
</tr>
<tr><td> </td></tr>
<tr class="body"><td class="body"><b>Education Information 2</b></td></tr>
<tr class="body">
<td class="body">Institution</td>
<td><input type="text" name="newed2" size="40" value="<%=adoRS("ed2")%
>"></td>
</tr>
<tr class="body">
<td class="body">Institution City</td>
<td><input type="text" name="newcity2" size="20" value="<%=adoRS
("ed2_city")%>"></td>
</tr>
<tr class="body">
<td class="body">Institution State</td>
<td><input type="text" name="newstate2" size="2" value="<%=adoRS
("ed2_st")%>"></td>
</tr>
<tr class="body">
<td class="body">Degree Obtained</td>
<td><input type="text" name="newdegree2" size="40" value="<%=adoRS
("ed2_deg")%>"></td>
</tr>
<tr class="body">
<td class="body">Year Graduated</td>
<td><input type="text" name="newgradyr2" size="4" value="<%=adoRS
("ed2_grad_yr")%>"></td>
</tr>
<tr><td> </td></tr>
<tr class="body"><td class="body"><b>Education Information 3</b></td></tr>
<tr class="body">
<td class="body">Institution</td>
<td><input type="text" name="newed3" size="40" value="<%=adoRS("ed3")%
>"></td>
</tr>
<tr class="body">
<td class="body">Institution City</td>
<td><input type="text" name="newcity3" size="20" value="<%=adoRS
("ed3_city")%>"></td>
</tr>
<tr class="body">
<td class="body">Institution State</td>
<td><input type="text" name="newstate3" size="2" value="<%=adoRS
("ed3_st")%>"></td>
</tr>
<tr class="body">
<td class="body">Degree Obtained</td>
<td><input type="text" name="newdegree3" size="40" value="<%=adoRS
("ed3_deg")%>"></td>
</tr>
<tr class="body">
<td class="body">Year Graduated</td>
<td><input type="text" name="newgradyr3" size="4" value="<%=adoRS
("ed3_grad_yr")%>"></td>
</tr>
<tr><td> </td></tr>
<tr class="body"><td class="body"><b>Education Information 4</b></td></tr>
<tr class="body">
<td class="body">Institution</td>
<td><input type="text" name="newed4" size="40" value="<%=adoRS("ed4")%
>"></td>
</tr>
<tr class="body">
<td class="body">Institution City</td>
<td><input type="text" name="newcity4" size="20" value="<%=adoRS
("ed4_city")%>"></td>
</tr>
<tr class="body">
<td class="body">Institution State</td>
<td><input type="text" name="newstate4" size="2" value="<%=adoRS
("ed4_st")%>"></td>
</tr>
<tr class="body">
<td class="body">Degree Obtained</td>
<td><input type="text" name="newdegree4" size="40" value="<%=adoRS
("ed4_deg")%>"></td>
</tr>
<tr class="body">
<td class="body">Year Graduated</td>
<td><input type="text" name="newgradyr4" size="4" value="<%=adoRS
("ed4_grad_yr")%>"></td>
</tr>
<tr><td> </td></tr>
<tr class="body">
<td class="body">Hobbies</td>
<td><textarea rows="4" cols="40" name="newhobbies"><%=adoRS("hobbies")%
></textarea></td>
</tr>
<tr><td> </td></tr>
<tr class="body">
<td class="body">Other</td>
<td><textarea rows="4" cols="40" name="newother"><%=adoRS("other")%
></textarea></td>
</tr>
<tr> </tr>
<tr>
<td>
<input type="submit" value="Update NOW" name="Submit">
</form>
</td>
</tr>
</table>
</td>
</table>
<p> </p>
</body>
<%
adoConn.Close
Set adoConn = nothing
Set adoCmd = nothing
Set adoRS = nothing%>
</html>
Message #2 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 25 Mar 2002 17:46:48 -0500
|
|
I don't usually write out my queries like this, but what if you were to
do this:
<%
'adoRSbldg.Open "Select * from tblBldg"
adoCmd.CommandText = "Select * from tblBldg"
'retrieve recordset
Set adoRS = adoCmd.Execute
%>
> -----Original Message-----
> From: Chad Gaines [mailto:cgaines@c...]
> Sent: Monday, March 25, 2002 10:40 PM
> To: ASP Databases
> Subject: [asp_databases] Opening multiple recordsets - HELP
>
>
> I am trying to pull information from an access database into
> form fields
> that can be updated. The information pulls in fine. I
> wanted to take it
> a step further and open up a second recordset for a table lookup on a
> table called "tblBldg" for a field in the form called
> "building". I keep
> getting an error and I am sure it is something with my syntax
> and how I am
> opening the recordset to pull in the values from the building table.
>
>
>
>
> =======================================
>
> <%@ Language=VBScript %>
> <html>
> <head>
>
> <meta NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
> <link rel="stylesheet" type="text/css" href="../style/cmc.css">
>
>
> <body>
> <!--#include file="../cmcnet/include/top.asa" -->
> <table border="0" width="750">
> <tr>
> <td width="165" class="menu"><a href="http://www.gocmc.edu">CMC
> Home</a></td>
> <td width="585" class="header"><p align="left">Update
> Your Directory
> Information...</p></td>
> </tr>
> <tr>
> <td width="750" colspan="2"><hr width="750" noshade
> color="#000000">
> </td>
> </tr>
> </table>
>
> <%
> 'retrieve catalogue of products from DB and write to table
> Dim adoConn
> Dim adoCmd
> Dim adoRS
> Dim strConnectionString
> Dim VarPassword
> Dim VarEmail
>
> VarPassword = CStr(Request.Form("password"))
> VarEmail = CStr(Request.Form("email"))
>
> 'create ADO Objects
> Set adoConn = Server.CreateObject("ADODB.Connection")
> Set adoCmd = Server.CreateObject("ADODB.Command")
> Set adoRS = Server.CreateObject("ADODB.Recordset")
> Set adoRSbldg = Server.CreateObject("ADODB.Recordset")
>
> strConnectionString="DSN=CMC;"
>
> 'set ADO Command Properties
> adoCmd.CommandType = 1
> adoCmd.CommandText = "SELECT tblStaff.* FROM tblStaff WHERE
> email = '" &_
> VarEmail & "'"
>
>
> 'Open the Connection
> adoConn.Open strConnectionString
>
> 'associate the Command with the Open Connection
> adoCmd.ActiveConnection = adoConn
>
> 'retrieve recordset
> Set adoRS = adoCmd.Execute
> %>
>
> <table border="0" width="771">
> <tr>
> <td width="174" valign="top"><!--#include
> file="../cmcnet/include/side.asa" --></td>
> <td class="body" width="597" valign="top"><table border="0"
> cellPadding="1"
> cellSpacing="1" width="590">
> <table>
> <form action="showprofile.asp" method="post">
> <input type="hidden" name="newserial_no" size="20" value="<%=adoRS
> ("serial_no")%>">
>
> <p><b>Welcome back <%=adoRS("first_name")%></b>
> <tr class="body">
> <td class="body">First Name</td>
> <td><input type="text" name="newfname" size="20" value="<%=adoRS
> ("first_name")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Middle Name</td>
> <td><input type="text" name="newmname" size="20" value="<%=adoRS
> ("middle_name")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Last Name</td>
> <td><input type="text" name="newlname" size="20" value="<%=adoRS
> ("last_name")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Title</td>
> <td><input type="text" name="newtitle" size="30" value="<%=adoRS
> ("title")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Department</td>
> <td><input type="text" name="newdept" size="4"
> value="<%=adoRS("dept")%
> >"></td>
> </tr>
> <tr class="body">
> <td class="body">Employed Since</td>
> <td><input type="text" name="newempyear" size="4" value="<%=adoRS
> ("emp_year")%>"></td>
> </tr>
>
>
> >>>>>I GET THE ERROR HERE:<<<<
>
> <% adoRSbldg.Open "Select * from tblBldg" %>
> <tr class="body">
> <td class="body">Building</td>
> <td><%do while not adoRSbldg.EOF%><option value="bldg=<%=adoRSbldg
> ("bldg")%>"><%adoRSbldg("txt")%>
> <%adoRSbldg.MoveNext
> loop
> %>
> <%adoRSbldg.close%>
> </td>
> </tr>
> <tr class="body">
> <td class="body">Room Number</td>
> <td><input type="text" name="newroom" size="4"
> value="<%=adoRS("room")%
> >"></td>
> </tr>
> <tr class="body">
> <td class="body">Phone Number</td>
> <td><input type="text" name="newphone" size="15" value="<%=adoRS
> ("phone")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Fax Number</td>
> <td><input type="text" name="newfax" size="15"
> value="<%=adoRS("fax")%
> >"></td>
> </tr>
> <tr class="body">
> <td class="body">Email</td>
> <td><input type="text" name="newemail" size="20" value="<%=adoRS
> ("email")%>"></td>
> </tr>
> <tr><td> </td></tr>
> <tr class="body"><td class="body"><b>Education Information
> 1</b></td></tr>
> <tr class="body">
> <td class="body">Institution</td>
> <td><input type="text" name="newed1" size="40"
> value="<%=adoRS("ed1")%
> >"></td>
> </tr>
> <tr class="body">
> <td class="body">Institution City</td>
> <td><input type="text" name="newcity1" size="20" value="<%=adoRS
> ("ed1_city")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Institution State</td>
> <td><input type="text" name="newstate1" size="2" value="<%=adoRS
> ("ed1_st")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Degree Obtained</td>
> <td><input type="text" name="newdegree1" size="40" value="<%=adoRS
> ("ed1_deg")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Year Graduated</td>
> <td><input type="text" name="newgradyr1" size="4" value="<%=adoRS
> ("ed1_grad_yr")%>"></td>
> </tr>
> <tr><td> </td></tr>
> <tr class="body"><td class="body"><b>Education Information
> 2</b></td></tr>
>
> <tr class="body">
> <td class="body">Institution</td>
> <td><input type="text" name="newed2" size="40"
> value="<%=adoRS("ed2")%
> >"></td>
> </tr>
> <tr class="body">
> <td class="body">Institution City</td>
> <td><input type="text" name="newcity2" size="20" value="<%=adoRS
> ("ed2_city")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Institution State</td>
> <td><input type="text" name="newstate2" size="2" value="<%=adoRS
> ("ed2_st")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Degree Obtained</td>
> <td><input type="text" name="newdegree2" size="40" value="<%=adoRS
> ("ed2_deg")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Year Graduated</td>
> <td><input type="text" name="newgradyr2" size="4" value="<%=adoRS
> ("ed2_grad_yr")%>"></td>
> </tr>
> <tr><td> </td></tr>
> <tr class="body"><td class="body"><b>Education Information
> 3</b></td></tr>
>
> <tr class="body">
> <td class="body">Institution</td>
> <td><input type="text" name="newed3" size="40"
> value="<%=adoRS("ed3")%
> >"></td>
> </tr>
> <tr class="body">
> <td class="body">Institution City</td>
> <td><input type="text" name="newcity3" size="20" value="<%=adoRS
> ("ed3_city")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Institution State</td>
> <td><input type="text" name="newstate3" size="2" value="<%=adoRS
> ("ed3_st")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Degree Obtained</td>
> <td><input type="text" name="newdegree3" size="40" value="<%=adoRS
> ("ed3_deg")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Year Graduated</td>
> <td><input type="text" name="newgradyr3" size="4" value="<%=adoRS
> ("ed3_grad_yr")%>"></td>
> </tr>
> <tr><td> </td></tr>
> <tr class="body"><td class="body"><b>Education Information
> 4</b></td></tr>
>
> <tr class="body">
> <td class="body">Institution</td>
> <td><input type="text" name="newed4" size="40"
> value="<%=adoRS("ed4")%
> >"></td>
> </tr>
> <tr class="body">
> <td class="body">Institution City</td>
> <td><input type="text" name="newcity4" size="20" value="<%=adoRS
> ("ed4_city")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Institution State</td>
> <td><input type="text" name="newstate4" size="2" value="<%=adoRS
> ("ed4_st")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Degree Obtained</td>
> <td><input type="text" name="newdegree4" size="40" value="<%=adoRS
> ("ed4_deg")%>"></td>
> </tr>
> <tr class="body">
> <td class="body">Year Graduated</td>
> <td><input type="text" name="newgradyr4" size="4" value="<%=adoRS
> ("ed4_grad_yr")%>"></td>
> </tr>
> <tr><td> </td></tr>
> <tr class="body">
> <td class="body">Hobbies</td>
> <td><textarea rows="4" cols="40"
> name="newhobbies"><%=adoRS("hobbies")%
> ></textarea></td>
> </tr>
> <tr><td> </td></tr>
> <tr class="body">
> <td class="body">Other</td>
> <td><textarea rows="4" cols="40" name="newother"><%=adoRS("other")%
> ></textarea></td>
> </tr>
> <tr> </tr>
> <tr>
> <td>
> <input type="submit" value="Update NOW" name="Submit">
> </form>
> </td>
> </tr>
> </table>
> </td>
> </table>
>
> <p> </p>
>
> </body>
> <%
> adoConn.Close
> Set adoConn = nothing
> Set adoCmd = nothing
> Set adoRS = nothing%>
> </html>
>
Message #3 by "Kim Iwan Hansen" <kimiwan@k...> on Mon, 25 Mar 2002 23:44:52 +0100
|
|
And the exact error is? (Pardon me if i missed it when i scanned through
your pasted code)
-Kim
-----Original Message-----
From: Chad Gaines [mailto:cgaines@c...]
Sent: 25. marts 2002 22:40
To: ASP Databases
Subject: [asp_databases] Opening multiple recordsets - HELP
I am trying to pull information from an access database into form fields
that can be updated. The information pulls in fine. I wanted to take it
a step further and open up a second recordset for a table lookup on a
table called "tblBldg" for a field in the form called "building". I keep
getting an error and I am sure it is something with my syntax and how I am
opening the recordset to pull in the values from the building table.
=======================================
<%@ Language=VBScript %>
<html>
<head>
<meta NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<link rel="stylesheet" type="text/css" href="../style/cmc.css">
<body>
<!--#include file="../cmcnet/include/top.asa" -->
<table border="0" width="750">
<tr>
<td width="165" class="menu"><a href="http://www.gocmc.edu">CMC
Home</a></td>
<td width="585" class="header"><p align="left">Update Your Directory
Information...</p></td>
</tr>
<tr>
<td width="750" colspan="2"><hr width="750" noshade color="#000000">
</td>
</tr>
</table>
<%
'retrieve catalogue of products from DB and write to table
Dim adoConn
Dim adoCmd
Dim adoRS
Dim strConnectionString
Dim VarPassword
Dim VarEmail
VarPassword = CStr(Request.Form("password"))
VarEmail = CStr(Request.Form("email"))
'create ADO Objects
Set adoConn = Server.CreateObject("ADODB.Connection")
Set adoCmd = Server.CreateObject("ADODB.Command")
Set adoRS = Server.CreateObject("ADODB.Recordset")
Set adoRSbldg = Server.CreateObject("ADODB.Recordset")
strConnectionString="DSN=CMC;"
'set ADO Command Properties
adoCmd.CommandType = 1
adoCmd.CommandText = "SELECT tblStaff.* FROM tblStaff WHERE email = '" &_
VarEmail & "'"
'Open the Connection
adoConn.Open strConnectionString
'associate the Command with the Open Connection
adoCmd.ActiveConnection = adoConn
'retrieve recordset
Set adoRS = adoCmd.Execute
%>
<table border="0" width="771">
<tr>
<td width="174" valign="top"><!--#include
file="../cmcnet/include/side.asa" --></td>
<td class="body" width="597" valign="top"><table border="0"
cellPadding="1"
cellSpacing="1" width="590">
<table>
<form action="showprofile.asp" method="post">
<input type="hidden" name="newserial_no" size="20" value="<%=adoRS
("serial_no")%>">
<p><b>Welcome back <%=adoRS("first_name")%></b>
<tr class="body">
<td class="body">First Name</td>
<td><input type="text" name="newfname" size="20" value="<%=adoRS
("first_name")%>"></td>
</tr>
<tr class="body">
<td class="body">Middle Name</td>
<td><input type="text" name="newmname" size="20" value="<%=adoRS
("middle_name")%>"></td>
</tr>
<tr class="body">
<td class="body">Last Name</td>
<td><input type="text" name="newlname" size="20" value="<%=adoRS
("last_name")%>"></td>
</tr>
<tr class="body">
<td class="body">Title</td>
<td><input type="text" name="newtitle" size="30" value="<%=adoRS
("title")%>"></td>
</tr>
<tr class="body">
<td class="body">Department</td>
<td><input type="text" name="newdept" size="4" value="<%=adoRS("dept")%
>"></td>
</tr>
<tr class="body">
<td class="body">Employed Since</td>
<td><input type="text" name="newempyear" size="4" value="<%=adoRS
("emp_year")%>"></td>
</tr>
>>>>>I GET THE ERROR HERE:<<<<
<% adoRSbldg.Open "Select * from tblBldg" %>
<tr class="body">
<td class="body">Building</td>
<td><%do while not adoRSbldg.EOF%><option value="bldg=<%=adoRSbldg
("bldg")%>"><%adoRSbldg("txt")%>
<%adoRSbldg.MoveNext
loop
%>
<%adoRSbldg.close%>
</td>
</tr>
<tr class="body">
<td class="body">Room Number</td>
<td><input type="text" name="newroom" size="4" value="<%=adoRS("room")%
>"></td>
</tr>
<tr class="body">
<td class="body">Phone Number</td>
<td><input type="text" name="newphone" size="15" value="<%=adoRS
("phone")%>"></td>
</tr>
<tr class="body">
<td class="body">Fax Number</td>
<td><input type="text" name="newfax" size="15" value="<%=adoRS("fax")%
>"></td>
</tr>
<tr class="body">
<td class="body">Email</td>
<td><input type="text" name="newemail" size="20" value="<%=adoRS
("email")%>"></td>
</tr>
<tr><td> </td></tr>
<tr class="body"><td class="body"><b>Education Information
1</b></td></tr>
<tr class="body">
<td class="body">Institution</td>
<td><input type="text" name="newed1" size="40" value="<%=adoRS("ed1")%
>"></td>
</tr>
<tr class="body">
<td class="body">Institution City</td>
<td><input type="text" name="newcity1" size="20" value="<%=adoRS
("ed1_city")%>"></td>
</tr>
<tr class="body">
<td class="body">Institution State</td>
<td><input type="text" name="newstate1" size="2" value="<%=adoRS
("ed1_st")%>"></td>
</tr>
<tr class="body">
<td class="body">Degree Obtained</td>
<td><input type="text" name="newdegree1" size="40" value="<%=adoRS
("ed1_deg")%>"></td>
</tr>
<tr class="body">
<td class="body">Year Graduated</td>
<td><input type="text" name="newgradyr1" size="4" value="<%=adoRS
("ed1_grad_yr")%>"></td>
</tr>
<tr><td> </td></tr>
<tr class="body"><td class="body"><b>Education Information 2</b></td></tr>
<tr class="body">
<td class="body">Institution</td>
<td><input type="text" name="newed2" size="40" value="<%=adoRS("ed2")%
>"></td>
</tr>
<tr class="body">
<td class="body">Institution City</td>
<td><input type="text" name="newcity2" size="20" value="<%=adoRS
("ed2_city")%>"></td>
</tr>
<tr class="body">
<td class="body">Institution State</td>
<td><input type="text" name="newstate2" size="2" value="<%=adoRS
("ed2_st")%>"></td>
</tr>
<tr class="body">
<td class="body">Degree Obtained</td>
<td><input type="text" name="newdegree2" size="40" value="<%=adoRS
("ed2_deg")%>"></td>
</tr>
<tr class="body">
<td class="body">Year Graduated</td>
<td><input type="text" name="newgradyr2" size="4" value="<%=adoRS
("ed2_grad_yr")%>"></td>
</tr>
<tr><td> </td></tr>
<tr class="body"><td class="body"><b>Education Information 3</b></td></tr>
<tr class="body">
<td class="body">Institution</td>
<td><input type="text" name="newed3" size="40" value="<%=adoRS("ed3")%
>"></td>
</tr>
<tr class="body">
<td class="body">Institution City</td>
<td><input type="text" name="newcity3" size="20" value="<%=adoRS
("ed3_city")%>"></td>
</tr>
<tr class="body">
<td class="body">Institution State</td>
<td><input type="text" name="newstate3" size="2" value="<%=adoRS
("ed3_st")%>"></td>
</tr>
<tr class="body">
<td class="body">Degree Obtained</td>
<td><input type="text" name="newdegree3" size="40" value="<%=adoRS
("ed3_deg")%>"></td>
</tr>
<tr class="body">
<td class="body">Year Graduated</td>
<td><input type="text" name="newgradyr3" size="4" value="<%=adoRS
("ed3_grad_yr")%>"></td>
</tr>
<tr><td> </td></tr>
<tr class="body"><td class="body"><b>Education Information 4</b></td></tr>
<tr class="body">
<td class="body">Institution</td>
<td><input type="text" name="newed4" size="40" value="<%=adoRS("ed4")%
>"></td>
</tr>
<tr class="body">
<td class="body">Institution City</td>
<td><input type="text" name="newcity4" size="20" value="<%=adoRS
("ed4_city")%>"></td>
</tr>
<tr class="body">
<td class="body">Institution State</td>
<td><input type="text" name="newstate4" size="2" value="<%=adoRS
("ed4_st")%>"></td>
</tr>
<tr class="body">
<td class="body">Degree Obtained</td>
<td><input type="text" name="newdegree4" size="40" value="<%=adoRS
("ed4_deg")%>"></td>
</tr>
<tr class="body">
<td class="body">Year Graduated</td>
<td><input type="text" name="newgradyr4" size="4" value="<%=adoRS
("ed4_grad_yr")%>"></td>
</tr>
<tr><td> </td></tr>
<tr class="body">
<td class="body">Hobbies</td>
<td><textarea rows="4" cols="40" name="newhobbies"><%=adoRS("hobbies")%
></textarea></td>
</tr>
<tr><td> </td></tr>
<tr class="body">
<td class="body">Other</td>
<td><textarea rows="4" cols="40" name="newother"><%=adoRS("other")%
></textarea></td>
</tr>
<tr> </tr>
<tr>
<td>
<input type="submit" value="Update NOW" name="Submit">
</form>
</td>
</tr>
</table>
</td>
</table>
<p> </p>
</body>
<%
adoConn.Close
Set adoConn = nothing
Set adoCmd = nothing
Set adoRS = nothing%>
</html>
Message #4 by Chad Gaines <cgaines@c...> on Tue, 26 Mar 2002 17:55:21 -0600
|
|
ERROR
ADODB.Recordset error '800a0e7d'
Operation is not allowed on an object referencing a closed or invalid
connection.
Not sure what is going on. Before I added the second recordset everything
worked fine. I just added the second "SELECT * from tblBldg" and got this
message. Any thoughts for this novice!
TIA
Chad
-----Original Message-----
From: Kim Iwan Hansen [mailto:kimiwan@k...]
Sent: Monday, March 25, 2002 4:45 PM
To: ASP Databases
Subject: [asp_databases] RE: Opening multiple recordsets - HELP
And the exact error is? (Pardon me if i missed it when i scanned through
your pasted code)
-Kim
-----Original Message-----
From: Chad Gaines [mailto:cgaines@c...]
Sent: 25. marts 2002 22:40
To: ASP Databases
Subject: [asp_databases] Opening multiple recordsets - HELP
I am trying to pull information from an access database into form fields
that can be updated. The information pulls in fine. I wanted to take it
a step further and open up a second recordset for a table lookup on a
table called "tblBldg" for a field in the form called "building". I keep
getting an error and I am sure it is something with my syntax and how I am
opening the recordset to pull in the values from the building table.
=======================================
<%@ Language=VBScript %>
<html>
<head>
<meta NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<link rel="stylesheet" type="text/css" href="../style/cmc.css">
<body>
<!--#include file="../cmcnet/include/top.asa" -->
<table border="0" width="750">
<tr>
<td width="165" class="menu"><a href="http://www.gocmc.edu">CMC
Home</a></td>
<td width="585" class="header"><p align="left">Update Your Directory
Information...</p></td>
</tr>
<tr>
<td width="750" colspan="2"><hr width="750" noshade color="#000000">
</td>
</tr>
</table>
<%
'retrieve catalogue of products from DB and write to table
Dim adoConn
Dim adoCmd
Dim adoRS
Dim strConnectionString
Dim VarPassword
Dim VarEmail
VarPassword = CStr(Request.Form("password"))
VarEmail = CStr(Request.Form("email"))
'create ADO Objects
Set adoConn = Server.CreateObject("ADODB.Connection")
Set adoCmd = Server.CreateObject("ADODB.Command")
Set adoRS = Server.CreateObject("ADODB.Recordset")
Set adoRSbldg = Server.CreateObject("ADODB.Recordset")
strConnectionString="DSN=CMC;"
'set ADO Command Properties
adoCmd.CommandType = 1
adoCmd.CommandText = "SELECT tblStaff.* FROM tblStaff WHERE email = '" &_
VarEmail & "'"
'Open the Connection
adoConn.Open strConnectionString
'associate the Command with the Open Connection
adoCmd.ActiveConnection = adoConn
'retrieve recordset
Set adoRS = adoCmd.Execute
%>
<table border="0" width="771">
<tr>
<td width="174" valign="top"><!--#include
file="../cmcnet/include/side.asa" --></td>
<td class="body" width="597" valign="top"><table border="0"
cellPadding="1"
cellSpacing="1" width="590">
<table>
<form action="showprofile.asp" method="post">
<input type="hidden" name="newserial_no" size="20" value="<%=adoRS
("serial_no")%>">
<p><b>Welcome back <%=adoRS("first_name")%></b>
<tr class="body">
<td class="body">First Name</td>
<td><input type="text" name="newfname" size="20" value="<%=adoRS
("first_name")%>"></td>
</tr>
<tr class="body">
<td class="body">Middle Name</td>
<td><input type="text" name="newmname" size="20" value="<%=adoRS
("middle_name")%>"></td>
</tr>
<tr class="body">
<td class="body">Last Name</td>
<td><input type="text" name="newlname" size="20" value="<%=adoRS
("last_name")%>"></td>
</tr>
<tr class="body">
<td class="body">Title</td>
<td><input type="text" name="newtitle" size="30" value="<%=adoRS
("title")%>"></td>
</tr>
<tr class="body">
<td class="body">Department</td>
<td><input type="text" name="newdept" size="4" value="<%=adoRS("dept")%
>"></td>
</tr>
<tr class="body">
<td class="body">Employed Since</td>
<td><input type="text" name="newempyear" size="4" value="<%=adoRS
("emp_year")%>"></td>
</tr>
>>>>>I GET THE ERROR HERE:<<<<
<% adoRSbldg.Open "Select * from tblBldg" %>
<tr class="body">
<td class="body">Building</td>
<td><%do while not adoRSbldg.EOF%><option value="bldg=<%=adoRSbldg
("bldg")%>"><%adoRSbldg("txt")%>
<%adoRSbldg.MoveNext
loop
%>
<%adoRSbldg.close%>
</td>
</tr>
<tr class="body">
<td class="body">Room Number</td>
<td><input type="text" name="newroom" size="4" value="<%=adoRS("room")%
>"></td>
</tr>
<tr class="body">
<td class="body">Phone Number</td>
<td><input type="text" name="newphone" size="15" value="<%=adoRS
("phone")%>"></td>
</tr>
<tr class="body">
<td class="body">Fax Number</td>
<td><input type="text" name="newfax" size="15" value="<%=adoRS("fax")%
>"></td>
</tr>
<tr class="body">
<td class="body">Email</td>
<td><input type="text" name="newemail" size="20" value="<%=adoRS
("email")%>"></td>
</tr>
<tr><td> </td></tr>
<tr class="body"><td class="body"><b>Education Information
1</b></td></tr>
<tr class="body">
<td class="body">Institution</td>
<td><input type="text" name="newed1" size="40" value="<%=adoRS("ed1")%
>"></td>
</tr>
<tr class="body">
<td class="body">Institution City</td>
<td><input type="text" name="newcity1" size="20" value="<%=adoRS
("ed1_city")%>"></td>
</tr>
<tr class="body">
<td class="body">Institution State</td>
<td><input type="text" name="newstate1" size="2" value="<%=adoRS
("ed1_st")%>"></td>
</tr>
<tr class="body">
<td class="body">Degree Obtained</td>
<td><input type="text" name="newdegree1" size="40" value="<%=adoRS
("ed1_deg")%>"></td>
</tr>
<tr class="body">
<td class="body">Year Graduated</td>
<td><input type="text" name="newgradyr1" size="4" value="<%=adoRS
("ed1_grad_yr")%>"></td>
</tr>
<tr><td> </td></tr>
<tr class="body"><td class="body"><b>Education Information 2</b></td></tr>
<tr class="body">
<td class="body">Institution</td>
<td><input type="text" name="newed2" size="40" value="<%=adoRS("ed2")%
>"></td>
</tr>
<tr class="body">
<td class="body">Institution City</td>
<td><input type="text" name="newcity2" size="20" value="<%=adoRS
("ed2_city")%>"></td>
</tr>
<tr class="body">
<td class="body">Institution State</td>
<td><input type="text" name="newstate2" size="2" value="<%=adoRS
("ed2_st")%>"></td>
</tr>
<tr class="body">
<td class="body">Degree Obtained</td>
<td><input type="text" name="newdegree2" size="40" value="<%=adoRS
("ed2_deg")%>"></td>
</tr>
<tr class="body">
<td class="body">Year Graduated</td>
<td><input type="text" name="newgradyr2" size="4" value="<%=adoRS
("ed2_grad_yr")%>"></td>
</tr>
<tr><td> </td></tr>
<tr class="body"><td class="body"><b>Education Information 3</b></td></tr>
<tr class="body">
<td class="body">Institution</td>
<td><input type="text" name="newed3" size="40" value="<%=adoRS("ed3")%
>"></td>
</tr>
<tr class="body">
<td class="body">Institution City</td>
<td><input type="text" name="newcity3" size="20" value="<%=adoRS
("ed3_city")%>"></td>
</tr>
<tr class="body">
<td class="body">Institution State</td>
<td><input type="text" name="newstate3" size="2" value="<%=adoRS
("ed3_st")%>"></td>
</tr>
<tr class="body">
<td class="body">Degree Obtained</td>
<td><input type="text" name="newdegree3" size="40" value="<%=adoRS
("ed3_deg")%>"></td>
</tr>
<tr class="body">
<td class="body">Year Graduated</td>
<td><input type="text" name="newgradyr3" size="4" value="<%=adoRS
("ed3_grad_yr")%>"></td>
</tr>
<tr><td> </td></tr>
<tr class="body"><td class="body"><b>Education Information 4</b></td></tr>
<tr class="body">
<td class="body">Institution</td>
<td><input type="text" name="newed4" size="40" value="<%=adoRS("ed4")%
>"></td>
</tr>
<tr class="body">
<td class="body">Institution City</td>
<td><input type="text" name="newcity4" size="20" value="<%=adoRS
("ed4_city")%>"></td>
</tr>
<tr class="body">
<td class="body">Institution State</td>
<td><input type="text" name="newstate4" size="2" value="<%=adoRS
("ed4_st")%>"></td>
</tr>
<tr class="body">
<td class="body">Degree Obtained</td>
<td><input type="text" name="newdegree4" size="40" value="<%=adoRS
("ed4_deg")%>"></td>
</tr>
<tr class="body">
<td class="body">Year Graduated</td>
<td><input type="text" name="newgradyr4" size="4" value="<%=adoRS
("ed4_grad_yr")%>"></td>
</tr>
<tr><td> </td></tr>
<tr class="body">
<td class="body">Hobbies</td>
<td><textarea rows="4" cols="40" name="newhobbies"><%=adoRS("hobbies")%
></textarea></td>
</tr>
<tr><td> </td></tr>
<tr class="body">
<td class="body">Other</td>
<td><textarea rows="4" cols="40" name="newother"><%=adoRS("other")%
></textarea></td>
</tr>
<tr> </tr>
<tr>
<td>
<input type="submit" value="Update NOW" name="Submit">
</form>
</td>
</tr>
</table>
</td>
</table>
<p> </p>
</body>
<%
adoConn.Close
Set adoConn = nothing
Set adoCmd = nothing
Set adoRS = nothing%>
</html>
|
View Cart
