|
 |
access_asp thread: Opening multiple recordsets - HELP
Message #1 by "Chad" <cgaines@c...> on Mon, 25 Mar 2002 22:30:38
|
|
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 second recordset(adoRSbldg) 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 "Ken Schaefer" <ken@a...> on Wed, 27 Mar 2002 12:46:44 +1100
|
|
<% adoRSbldg.Open "Select * from tblBldg" %>
When you open a recordset you need to specify (at the very least), the
source of the data (eg an SQL statement), and a connection object or
connection string - otherwise, how does the recordset know what data source
you want to get the data from? :-)
<%
strSQL = _
"SELECT bldg, txt " & _
"FROM tblBldg "
adoRSbldg.Open strSQL, adoConn, adOpenForwardOnly, adLockReadOnly, adCmdText
%>
My personal preference would also be to try to separate out your
presentation and business logic. Get all your data at the top of the page,
and, optionally, move it into VBScript arrays or local variables. Then you
can dispose of your connection and recordset objects straight away
(objRS.close, Set objRS = Nothing, objConn.Close, Set objConn = Nothing).
<%
objConn.Open ...
strSQL = _
"SELECT bldg, txt " & _
"FROM tblBldg "
' www.adopenstatic.com/resources/code/arrayfromsql.asp
arrBuildings = ArrayFromSQL(objConn, strSQL)
' www.adopenstatic.com/resources/code/objdispose.asp
Call objDispose(objConn, True, True)
%>
Then you use the array later on to populate the select list. You can use
another routine to do this (included below)
<tr class="body">
<td class="body">Building</td>
<td><%=WriteFormSelectList("cboBldg", "", "", arrBuildings, 1, "",
"")%></td>
</tr>
(in your code you were missing <select></select> tags)
'------------------------------------------------------------
' WriteFormSelectList
'------------------------------------------------------------
' Returns HTML for a <select> element
' Accepts strName as string
' Accepts strID as string
' Accepts strHTMLAttributes as string - literal text for any other HTML
attributes
' Accepts form option elements as array
' Accepts array format as integer: 0 = rows/cols, 1 = cols/rows (eg from
getRows)
' Accepts optional text for first <option></option> tag
' Accepts strSelectedValue as option to be selected
'------------------------------------------------------------
Function WriteFormSelectList( _
ByVal strName, _
ByVal strID, _
ByVal strHTMLAttributes, _
ByVal arrOptions, _
ByVal intArrayFormat, _
ByVal strFirstOption, _
ByVal strSelectedValue _
)
Dim i ' array 1st dimension counter
If not isArray(arrOptions) then
Exit Function
End If
WriteFormSelectList = "<select name=""" & strName & """ ID=""" & strID &
""""
If Len(strHTMLAttributes & "") > 0 then
WriteFormSelectList = WriteFormSelectList & " " & strHTMLAttributes & """"
End If
WriteFormSelectList = WriteFormSelectList & ">" & vbCrLf
If Len(strFirstOption) > 0 then
WriteFormSelectList = WriteFormSelectList & strFirstOption & vbCrLf
End If
If intArrayFormat = 0 then
For i = 0 to Ubound(arrOptions, 1)
WriteFormSelectList = WriteFormSelectList & "<option value=""" &
arrOptions(i,0) & """>" & arrOptions(i,1) & "</option>" & vbCrLf
Next
Else
For i = 0 to Ubound(arrOptions, 2)
WriteFormSelectList = WriteFormSelectList & "<option value=""" &
arrOptions(0,i) & """>" & arrOptions(1,i) & "</option>" & vbCrLf
Next
End If
WriteFormSelectList = WriteFormSelectList & "</select>" & vbCrLf
If Len(strSelectedValue & "") > 0 then
WriteFormSelectList = Replace(WriteFormSelectList, "value=""" &
strSelectedValue & """>", "value=""" & strSelectedValue & """ selected>")
End If
End Function
'------------------------------------------------------------
' --- WriteFormSelectList
'------------------------------------------------------------
Cheers
Ken
Message #3 by "Vandael Tim" <vandael_tim@h...> on Wed, 27 Mar 2002 09:01:14 +0100
|
|
Thnx for the reply,
i am now going to try to make my code a little more readable. :-)
greets
Tim
----- Original Message -----
From: "Ken Schaefer" <ken@a...>
To: "Access ASP" <access_asp@p...>
Sent: Wednesday, March 27, 2002 2:46 AM
Subject: [access_asp] Re: Opening multiple recordsets - HELP
> <% adoRSbldg.Open "Select * from tblBldg" %>
>
> When you open a recordset you need to specify (at the very least), the
> source of the data (eg an SQL statement), and a connection object or
> connection string - otherwise, how does the recordset know what data
source
> you want to get the data from? :-)
>
> <%
> strSQL = _
> "SELECT bldg, txt " & _
> "FROM tblBldg "
>
> adoRSbldg.Open strSQL, adoConn, adOpenForwardOnly, adLockReadOnly,
adCmdText
> %>
>
> My personal preference would also be to try to separate out your
> presentation and business logic. Get all your data at the top of the page,
> and, optionally, move it into VBScript arrays or local variables. Then you
> can dispose of your connection and recordset objects straight away
> (objRS.close, Set objRS = Nothing, objConn.Close, Set objConn = Nothing).
>
> <%
> objConn.Open ...
>
> strSQL = _
> "SELECT bldg, txt " & _
> "FROM tblBldg "
>
> ' www.adopenstatic.com/resources/code/arrayfromsql.asp
> arrBuildings = ArrayFromSQL(objConn, strSQL)
>
> ' www.adopenstatic.com/resources/code/objdispose.asp
> Call objDispose(objConn, True, True)
> %>
>
> Then you use the array later on to populate the select list. You can use
> another routine to do this (included below)
>
> <tr class="body">
> <td class="body">Building</td>
> <td><%=WriteFormSelectList("cboBldg", "", "", arrBuildings, 1, "",
> "")%></td>
> </tr>
>
> (in your code you were missing <select></select> tags)
>
> '------------------------------------------------------------
> ' WriteFormSelectList
> '------------------------------------------------------------
> ' Returns HTML for a <select> element
> ' Accepts strName as string
> ' Accepts strID as string
> ' Accepts strHTMLAttributes as string - literal text for any other HTML
> attributes
> ' Accepts form option elements as array
> ' Accepts array format as integer: 0 = rows/cols, 1 = cols/rows (eg from
> getRows)
> ' Accepts optional text for first <option></option> tag
> ' Accepts strSelectedValue as option to be selected
> '------------------------------------------------------------
> Function WriteFormSelectList( _
> ByVal strName, _
> ByVal strID, _
> ByVal strHTMLAttributes, _
> ByVal arrOptions, _
> ByVal intArrayFormat, _
> ByVal strFirstOption, _
> ByVal strSelectedValue _
> )
>
> Dim i ' array 1st dimension counter
>
> If not isArray(arrOptions) then
> Exit Function
> End If
>
> WriteFormSelectList = "<select name=""" & strName & """ ID=""" & strID &
> """"
>
> If Len(strHTMLAttributes & "") > 0 then
> WriteFormSelectList = WriteFormSelectList & " " & strHTMLAttributes &
""""
> End If
>
> WriteFormSelectList = WriteFormSelectList & ">" & vbCrLf
>
> If Len(strFirstOption) > 0 then
> WriteFormSelectList = WriteFormSelectList & strFirstOption & vbCrLf
> End If
>
> If intArrayFormat = 0 then
> For i = 0 to Ubound(arrOptions, 1)
> WriteFormSelectList = WriteFormSelectList & "<option value=""" &
> arrOptions(i,0) & """>" & arrOptions(i,1) & "</option>" & vbCrLf
> Next
> Else
> For i = 0 to Ubound(arrOptions, 2)
> WriteFormSelectList = WriteFormSelectList & "<option value=""" &
> arrOptions(0,i) & """>" & arrOptions(1,i) & "</option>" & vbCrLf
> Next
> End If
>
> WriteFormSelectList = WriteFormSelectList & "</select>" & vbCrLf
>
> If Len(strSelectedValue & "") > 0 then
> WriteFormSelectList = Replace(WriteFormSelectList, "value=""" &
> strSelectedValue & """>", "value=""" & strSelectedValue & """ selected>")
> End If
>
> End Function
> '------------------------------------------------------------
> ' --- WriteFormSelectList
> '------------------------------------------------------------
>
> Cheers
> Ken
>
>
>
|
|
|
View Cart
