Disconnected Recordset

[jlkidd]

I read at devguru that in ASP you could disconnect a recordset from the connection and this would allow you to access the recordset in client side script. Here's my error and a copy of the code. I can't seem to be able to set the properties properly. I would appreciate any suggestions. Thanks in advance.

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'CursorLocation'

/DisconnectedRecordSet.asp, line 19


<%
'declare locktype and cursor locations
Const adLockBatchOptimistic = 4
Const adUseClient = 1

'declare and instantiate objects
dim cn, rs, ConnectionString, SQL

'cn = server.createobject(&quot;ADODB.Connection&quot
rs = server.createobject(&quot;ADODB.RecordSet&quot
SQL = &quot;SELECT * From Mill, Area, MillAreaCrossRef Where Mill.MID = MillAreaCrossRef.MID &quot; _
& &quot;And Area.AID = MillAreaCrossRef.AID;&quot;
set cn = server.createobject(&quot;ADODB.Connection&quot
ConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\InetPub\

http://wwwcore\CM1CM2Devl\fpdb\Level

one audits.mdb&quot;

cn.open ConnectionString

'Build the recordset
19>rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic
rs.open SQL, cn

'disconnect the recordset
set rs.ActiveConnection = Nothing
cn.close

%>

 

[Deadline]

Did you get the Intellisense ActivePopup when you press Dot after rs? And have you included the adovbs.inc file ?
If not try including that. And let us all know what happened

RR.

 

[jlkidd]

I set the variable adUseClient's value equal to initially, wouldn't that eleviate the need for me to include the adovbs.inc file?

 

[jlkidd]

I attempted to include adovbs.inc and now I recieve an include file not found error. I am running this on IIS 4. Any way, thank you for the help. Any suggestions?

B-)

 

[jlkidd]

I downloaded the adovbs.inc file and am still recieving an error about how the recordset variable doesn't support the property or method of CursorLocation.

B-)

 

[Deadline]

Hey,
I found the solution to your problem. Tell me what do I get if tell the answer.



RR.

 

[jlkidd]

What do you want?

 

[Deadline]

I want atleast a fortnight's supply of Milk Chocolates. I take it as your promise.

You forgot to add set keyword before this :
rs = server.createobject(&quot;ADODB.RecordSet&quot


Got it ?


RR

 

[jlkidd]

Thank you.
B-)

 

[jlkidd]

I thought that now that the recordset was disconnected the recordset would persist and would be able to be used in client side script. Was I wrong or am I just getting a type mismatch for some other odd reason?

 

[Deadline]

Hello,

What is the status of your problem now ?

RR

 

[jlkidd]

I am attempting to use this disconnected recordset in client side script so that when a department is chosen from a drop down menu another menu will then display all employees from a database whose area match the area chosen. All this information is information that is with in the recordset. Here is a copy of my code. I am using FrontPage as my editor so I have few luxuries.

<SCRIPT LANGUAGE=&quot;VBScript&quot; >
<!-- #include file=&quot;adovbs.inc&quot; --%>
<%
''''''''''''''''''''''''''''''''server side rs call '''''''''''''''''''''''''''''''''''''''''''''''
'retrieve rs's for the mill's and the areas within the mills
'open a connection to the database
dim cn
dim ConnectionString
set cn = server.createobject(&quot;ADODB.Connection&quot
ConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\InetPub\

http://wwwcore\CM1CM2Devl\fpdb\Level

one audits.mdb&quot;

cn.open ConnectionString

'open a recordset of Mill and area
dim MillAreaCrossRefSQL
dim rsMillAreaCrossRef

'Write the Query Statement based on the area description
MillAreaCrossRefSQL = &quot;SELECT * From Mill, Area, MillAreaCrossRef Where Mill.MID = MillAreaCrossRef.MID &quot; _
& &quot;And Area.AID = MillAreaCrossRef.AID;&quot;

'create recordsets
set rsMillAreaCrossRef = server.createobject(&quot;ADODB.RecordSet&quot

'Open rsMillAreaCrossRef so that it
rsMillAreaCrossRef.CursorLocation = adLockBatchOptimistic
rsMillAreaCrossRef.LockType = adUseClient
rsMillAreaCrossRef.open MillAreaCrossRefSQL, cn

'create a disconnected recordset
Set rsMillAreaCrossRef.ActiveConnection = Nothing
cn.close

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

%>
</SCRIPT>
<html>

<head>
<meta http-equiv=&quot;Content-Language&quot; content=&quot;en-us&quot;>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=windows-1252&quot;>
<meta name=&quot;GENERATOR&quot; content=&quot;Microsoft FrontPage 4.0&quot;>
<meta name=&quot;ProgId&quot; content=&quot;FrontPage.Editor.Document&quot;>
<title>Cold Mill Quality Audit Menu</title>
<script LANGUAGE=&quot;JavaScript&quot;>

<!-- Begin
function formHandler(form){
var URL = document.form.site.options[document.form.site.selectedIndex].value;
window.location.href = URL;
}
function formHandler2(form2){
var URL = document.form2.site2.options[document.form2.site2.selectedIndex].value;
window.location.href = URL;
}
function formHandler3(form3){
var URL = document.form3.site3.options[document.form3.site3.selectedIndex].value;
window.location.href = URL;
}
function formHandler4(form4){
var URL = document.form4.site4.options[document.form4.site4.selectedIndex].value;
window.location.href = URL;
}


// End -->
</script>




<Script Language = &quot;VBscript&quot;>

Sub Mill()

alert <% response.write NumberOfRecords %>

set frmAudit = document.forms(0)
set frmForm2 = document.forms(1)

dim Joseph

do while not rsMillAreaCrossRef.eof
dim MIDMessage
MIDMessage = <% response.write rsmillareaCrossref(&quot;MID&quot %>
MsgBox MIDMessage
rsMillAreaCrossRef.movenext
loop

''''''''display information in drop down menu based on selection''''''''''''''''''''''''''''''''''''''''''''''

dim i
dim Length
i = o

'determine the length of drop down menu
Length = frmAudit.site.Length

'delete all items in the drop down menus
For i = 1 TO Length -1
frmAudit.site.Remove 1
frmForm2.site2.remove 1
Next

'dependant upon the selected index display the proper items in the site drop down menu
dim ItemSelected
ItemSelected = frmAudit.MillIdentification.Value

'determine the length of the record
dim rsLength
rsLength = rsMillAreaCrossRef.recordcount

'Determine the mill of each record
rsMillAreaCrossRef.MoveFirst

do while NOT rsMillAreaCrossRef.EOF
MillID = rsMillAreaCrossRef(&quot;Mill.MID&quot

Select Case MillID
Case 1
response.write &quot;Mill ID = &quot; & MillID & &quot;<br>&quot; & &quot;Mill = &quot; & rsMillAreaCrossRef(&quot;Mill.Mill&quot & &quot;<br>&quot;
'place the data into the drop down menu based on selection
If ItemSelected = MillID Then
alert &quot;IF&quot;
Area = rsMillAreaCrossRef(&quot;Area Description&quot
alert area
Value = rsMillAreaCrossRef(&quot;Area.AID&quot
alert value
Set Element = document.createElement(&quot;OPTION&quot
Element.Text = Area
Element.Value = Value
frmAudit.Site.add Element
Set Element = document.createElement(&quot;OPTION&quot
Element.Text = Area
Element.Value = Value
frmForm2.Site2.add Element
End If

Case 2
'place the data into the drop down menu based on selection
If ItemSelected = MillID Then
Area = rsMillAreaCrossRef(&quot;Area Description&quot
Value = rsMillAreaCrossRef(&quot;Area.AID&quot
Set Element = document.createElement(&quot;OPTION&quot
Element.Text = Area
Element.Value = Value
frmAudit.Site.add Element
Set Element = document.createElement(&quot;OPTION&quot
Element.Text = Area
Element.Value = Value
frmForm2.Site2.add Element
End If
Case 3
'place the data into the drop down menu based on selection
If ItemSelected = MillID Then
Area = rsMillAreaCrossRef(&quot;Area Description&quot
Value = rsMillAreaCrossRef(&quot;Area.AID&quot
Set Element = document.createElement(&quot;OPTION&quot
Element.Text = Area
Element.Value = Value
frmAudit.Site.add Element
Set Element = document.createElement(&quot;OPTION&quot
Element.Text = Area
Element.Value = Value
frmForm2.Site2.add Element
End If

Case 4

'place the data into the drop down menu based on selection
If ItemSelected = MillID Then
Area = rsMillAreaCrossRef(&quot;Area Description&quot
Value = rsMillAreaCrossRef(&quot;Area.AID&quot
Set Element = document.createElement(&quot;OPTION&quot
Element.Text = Area
Element.Value = Value
frmAudit.Site.add Element
Set Element = document.createElement(&quot;OPTION&quot
Element.Text = Area
Element.Value = Value
frmForm2.Site2.add Element
End If

End Select

rsMillAreaCrossRef.MoveNext
response.write rsMillAreaCrossRef.Status

Loop


End Sub

</Script>

<body language=&quot;vbscript&quot; onload=&quot;Mill()&quot;>





<h1><font face=&quot;Arial&quot; color=&quot;#0000FF&quot;>Cold Mill Quality Audit Menu</font></h1>
<hr align=&quot;center&quot;>
<div align=&quot;center&quot; style=&quot;width: 753; height: 130&quot;>
<!--webbot BOT=&quot;GeneratedScript&quot; PREVIEW=&quot; &quot; startspan --><script Language=&quot;JavaScript&quot;><!--
function FrontPage_Form1_Validator(theForm)
{

if (theForm.site.selectedIndex == 0)
{
alert(&quot;The first \&quot;site\&quot; option is not a valid selection. Please choose one of the other options.&quot;
theForm.site.focus();
return (false);
}
return (true);
}
//--></script><!--webbot BOT=&quot;GeneratedScript&quot; endspan --><form method=&quot;POST&quot; name=&quot;FrontPage_Form1&quot; Action=&quot;

http://cmdev/JLKidd/AuditForm.asp&quot;

onsubmit=&quot;return FrontPage_Form1_Validator(this)&quot;>
<p align=&quot;left&quot;><font face=&quot;Arial&quot;>
<div align=&quot;left&quot;>
<table border=&quot;0&quot; width=&quot;25%&quot; height=&quot;35&quot;>
<tr>
<td width=&quot;776&quot; height=&quot;35&quot; align=&quot;center&quot;>
<p align=&quot;left&quot;><b>Mill ID</b></p>
</td>
<td width=&quot;776&quot; height=&quot;35&quot; align=&quot;center&quot;>
<p align=&quot;left&quot;><b>Team</b></p>
</td>
</tr>
<tr>
<td width=&quot;776&quot; height=&quot;35&quot; align=&quot;center&quot;>
<p align=&quot;left&quot;><select size=&quot;1&quot; name=&quot;MillIdentification&quot; onChange=&quot;Mill()&quot;>
<option value=&quot;1&quot;>CM1</option>
<option value=&quot;2&quot;>CM2</option>
<option value=&quot;3&quot;>CM3</option>
<option value=&quot;4&quot;>RS</option>

</select></p>
</td>
<td width=&quot;776&quot; height=&quot;35&quot; align=&quot;center&quot;>
<p align=&quot;left&quot;><select size=&quot;1&quot; name=&quot;Team&quot;>
<option value=&quot;1&quot;>A</option>
<option value=&quot;2&quot;>B</option>
<option value=&quot;3&quot;>C</option>
<option value=&quot;4&quot;>D</option>
</select></p>
</td>
</tr>
</table>
</div>


<h4 align=&quot;left&quot;>Go to audit form:</h4>


<h4 align=&quot;left&quot;><!--webbot bot=&quot;Validation&quot; B-Disallow-First-Item=&quot;TRUE&quot; --><select name=&quot;site&quot; size=&quot;1&quot;>
<option value>Select audit form and click go.... </option>

<option value=&quot;1&quot;>Prep</option> %>
<option value=&quot;2&quot;>Crane</option>
<option value=&quot;3&quot;>Entry</option>
<option value=&quot;4&quot;>Pulpit</option>
<option value=&quot;5&quot;>Band/Weigh</option>
<option value=&quot;6&quot;>Fluids</option>
<option value=&quot;8&quot;>Inspection</option>

</select><input type=&quot;submit&quot; value=&quot;Go&quot; name=&quot;B1&quot;>


</h4>


</font>


</form>


</div>

<% 'response.write rsMillAreaCrossRef(&quot;Area Description&quot %>

<h4><font face=&quot;Arial&quot;>Review audit completion rate report:</font></h4>


<form name=&quot;form2&quot; Method=&quot;Post&quot; Action=&quot;

http://cmdev/Audit_Report.asp&quot;>

<p><font face=&quot;Arial&quot;><select name=&quot;site2&quot; size=&quot;1&quot;>
<option value>Select report and click go.... </option>
<option value=&quot;1&quot;>Prep</option>
<option value=&quot;2&quot;>Crane</option>
<option value=&quot;3&quot;>Mill Entry</option>
<option value=&quot;4&quot;>Pulpit</option>
<option value=&quot;5&quot;>Band/Weigh</option>
<option value=&quot;6&quot;>Fluids</option>
<option value=&quot;7&quot;>Inspection</option>

</select> <input type=&quot;submit&quot; value=&quot;Go!&quot;>
<input type=&quot;hidden&quot; name=&quot;Mill&quot; value=&quot;1&quot;>
</form>
<h4><font face=&quot;Arial&quot;>Review audit conformance summary:</font></h4>

<form name=&quot;form3&quot;>
<p><font face=&quot;Arial&quot;><select name=&quot;site3&quot; size=&quot;1&quot;>
<option value>Select report and click go.... </option>
<option value=&quot;/Old_Reports/Cast_Bay/CB_conformance_summary_00_09.asp&quot;>Cast Bay - Sep/Oct 2000</option>
<option value=&quot;/Old_Reports/Melt_Bay/MB_conformance_summary_00_09.asp&quot;>Melt Bay - Sep/Oct 2000</option>
<option value=&quot;/Old_Reports/Scrap_Bay/SB_conformance_summary_00_09.asp&quot;>Scrap Bay - Sep/Oct 2000</option>
<option value=&quot;/Old_Reports/SWARF/SW_conformance_summary_00_09.asp&quot;>SWARF - Sep/Oct 2000</option>



</select> <input type=&quot;button&quot; value=&quot;Go!&quot; onClick=&quot;javascript:formHandler3(this)&quot;> </font> </p>


</form>



<h4><font face=&quot;Arial&quot;>Review detailed audit responses:</font></h4>


<form name=&quot;form4&quot;>
<p><font face=&quot;Arial&quot;><select name=&quot;site4&quot; size=&quot;1&quot;>
<option value>Select report and click go.... </option>
<option value=&quot;/Current_Reports/CastBay/CB_conformance_detail.asp&quot;>Cast Bay - current month</option>
<option value=&quot;/Current_Reports/MeltBay/MB_conformance_detail.asp&quot;>Melt Bay - current month</option>
<option value=&quot;/Current_Reports/ScrapBay/SB_conformance_detail.asp&quot;>Scrap Bay - current month</option>
<option value=&quot;/Current_Reports/SWARF/SW_conformance_detail.asp&quot;>SWARF - current month</option>
<option value>_______________________________</option>
<option value=&quot;/Old_Reports/Cast_Bay/CB_conformance_detail_00_09.htm&quot;>Cast Bay - Sep/Oct 2000</option>
<option value=&quot;/Old_Reports/Melt_Bay/MB_conformance_detail_00_09.htm&quot;>Melt Bay - Sep/Oct 2000</option>
<option value=&quot;/Old_Reports/Scrap_Bay/SB_conformance_detail_00_09.htm&quot;>Scrap Bay - Sep/Oct 2000</option>
<option value=&quot;/Old_Reports/SWARF/SW_conformance_detail_00_09.htm&quot;>SWARF - Sep/Oct 2000</option>




</select> <input type=&quot;button&quot; value=&quot;Go!&quot; onClick=&quot;javascript:formHandler4(this)&quot;> </font> </p>
</form>


<p><font face=&quot;Arial Narrow&quot;><a href=&quot;tvertrees/crid.asp#web2&quot;>Return to Coldmill Home
Page</a></font></p>


</font>

<hr align=&quot;center&quot;>
<div align=&quot;center&quot; style=&quot;width: 753; height: 130&quot;>

</body>

</html>

The present error is:

Type mismatch: 'rsmillareaCrossref'
line 86

Line 86 is in red



I know this is a lot of information that does not neccessarily pertain to the problem, but I felt you should know what I am attempting. At present I am just attempting to determine different areas dependant upon the department that is clicked (mill or rs). Any thoughts?

 

[hblackorby]

You are disconnecting the recordset fine it looks like, but you aren't sending the recordset to the client? It is very interesting what you are trying to do. You need some way to send that recordset back down to the client. Just disconnecting it helps, but when you stop processing ASP it will dump all your objects.

Have you tried perhaps writing all your values into an array on the output page and then having your client-side VBScript run through the array?

I think the problem you are having is there is no client-side object equal to a recordset (esp. not in JavaScript). You might have to &quot;fill in&quot; an array on the page and then have the script use that instead. Harold Blackorby
hblackorby@scoreinteractive.com
St. Louis, MO

 

[jlkidd]

I've tried that but using an array but seem to recieve the same error within the server side script. Also, you mentioned sending the recordset to the client. VBScript does have a recordset variable. Is their some way that I can send that recordset and use it within a client side VBScript recordset?

 

[hblackorby]

I'm not sure there's a way to pass an object from server to client, but I could be wrong. Normally when I pass data from server to client, I just do a response.write into a JavaScript (or VBScript) variable assignment like so:

<% value = 5 %>
<SCRIPT LANGUAGE=&quot;JavaScript&quot;>
value = <%=value%>

</SCRIPT>

When this page is sent to the client, it will say &quot;value=5&quot; in the JavaScript. The only way I know of to send a recordset to client-side array would be like so:

<SCRIPT LANGUAGE=&quot;VBScript&quot;>
dim ary()
redim ary(<%=rs.recordcount%>)
<% j = 0
while not rs.eof %>
ary(<%=j%>)=<%=rs(&quot;fieldname&quot%>
<% j = j + 1
rs.movenext
wend %>
</SCRIPT>

I'm not familiar how to DIM, setup and populate VBScript recordsets unless they are connected to a database.



Harold Blackorby
hblackorby@scoreinteractive.com
St. Louis, MO

 

[jlkidd]

Thank you for your help. I think I am simply going to create another page that way the recordset can be accessed on the server.

Thanks again.

 

[bahs]

As I understand your actual need is:&quot;I am attempting to use this disconnected recordset in client side script so that when a department is chosen from a drop down menu another menu will then display all employees from a database whose area match the area chosen. &quot;

I think a solution may be to reenter your same page a second time.... on the first pass you get the dept selected, then 'on change' or some such, reenter the same page passing the dept. id in a hidden or session variable.
You'll need a variable to tell your page you're doing '2nd pass' processing (you may be able to use the same variable for both purposes). On the second pass qualify the second query with the dept. id. Structure your user interface such that the user has no idea you've actually passed the same code twice....

I've not done this myself but have it in mind for when this circumstance arises....so I can't give you an example. Certainly going to another page is the simplest solution - as you proposed to do.