Diplay results form a db query 3

[cschaffer]

This must be possible....I just need a quick example of how to display results from a query to make my results page faster then searching the entire db.....

here is my current code
*
sSQL = "SELECT * FROM Table"
Cm.CommandText = sSQL
Cm.CommandType = 1
Set Rs = Cm.Execute
*

how could I modify this to display a query and not the table????????


Thanks!

CS ***************

http://www.digital6.com

***************

 

[ToddWW]

Here's an example.

<%
dim objRst
dim strQuery
dim strProvider

strProvider = &quot;Your Connection String or DSN&quot;

Set objRst = Server.CreateObject(&quot;ADODB.recordset&quot;)
objRst.CursorType = 3
strQuery = &quot;SELECT * FROM table WHERE field = '&quot; & something & &quot;'&quot; _
  & &quot; ORDER BY somfield&quot;
objRst.Open strQuery, strProvider
set objRst.ActiveConnection = Nothing
%>

<tr>

<%
WHILE NOT objRst.EOF
%>
  <td width=&quot;50%&quot;><%=objRst(&quot;field1&quot;)%></td>
  <td width=&quot;50%&quot;><%=objRst(&quot;field2&quot;)%></td>
<%
  objRst.MoveNext
WEND
set objRst = Nothing
%>

</tr>

ToddWW

 

[cschaffer]

thanks....I should be able to work with that!

CS ***************

http://www.digital6.com

***************

 

[cschaffer]

look slike this is still searching the table....I have a query in the database.....how can I display this query??

so the db is doing the workand not the 'ASP'

]

CS ***************

http://www.digital6.com

***************

 

[ToddWW]

Oh, I see. You want to execute a query you've already written in the database program. I don't think you can do this and display the results with ASP. You need to use some convention of what I've shown you above, and regardless, ASP is going to be the mechanism to display the results as I have show above.

Issuing the query through the method I have shown above would be identical, if not MORE efficient than running the query that you've already built.

What challenges have you come across. Is there a reason you are striving for that convention ?? The example I provided really works great. I'd be happy to help you with database connections, SQL, etc..

ToddWW

 

[cschaffer]

k...I have 525 records and the search is deadly slow?? So I though a query that already has info in it would produce faster results......??I am only searching though one field.....if field matches display that record to the screen....now I understand it is searching all the records....so a query of only 18-20 records already there, diplay them....faster in my mind....

Any was to make this faster......

***************

http://www.digital6.com

***************

 

[ToddWW]

First thing's first. You can only execute SQL queries on an ADO connected database using ASP. You won't be able to execute the query you've already written in Access in that sense of the word.

Unless you're using a very, very, very slow computer as the server, something else is not right there. ASP should blow through those 525 records in a fraction of a second.

Have you already tested the example that I have provided ?? Do you need help getting setup to test that example ??

ToddWW

 

[cschaffer]

k...I will try your code....shouldn't need help..we'll see though...thanks...I will let you know my progress!

CS ***************

http://www.digital6.com

***************

 

[cschaffer]

k...I got it to display all the code...I then entered some of my own code which inputs variables from the previous page...

Now a I get the

Operation is not allowed when the object is closed.

It doensn't 'look' like it is closed...

Thanks

CS
***************

http://www.digital6.com

***************

 

[cschaffer]

k...I got it to display all the data...I then entered some of my own code which inputs variables from the previous page...

Now a I get the

Operation is not allowed when the object is closed.

It doensn't 'look' like it is closed...

Thanks

CS
***************

http://www.digital6.com

***************

 

[lobstah]

do you close your object more than once?

ie.

objRst.Close

.
.
further down....
.
.

objRst.Close

 

[ToddWW]

CS, you're going to have to display your revised page back here and tell us what line the error is pointing to..

I looked over my example and I only close the recordset with one line set objRst = Nothing after it's done executing. I don't see anything else that would cause that error. But again, if you've modified it I think we'll need to see your modifications..

ToddWW

 

[cschaffer]

Thanks here is the code.....line 18 is the given line error...... I marked it eith a ****It all looks good to me!!??



<%
' These are the Check Variables
isit1 = Request.querystring(&quot;DisasterType&quot
Found = 0

dim objRst
dim strQuery
dim strProvider

strProvider = &quot;disastersdb&quot;

Set objRst = Server.CreateObject(&quot;ADODB.recordset&quot

objRst.CursorType = 3

strQuery = &quot;SELECT * FROM Disasters&quot;

****Do Until objRst.EOF
DName = objRst(&quot;DisasterName&quot
DType = objRst(&quot;DisasterType&quot
Detail = objRst(&quot;Detail&quot


objRst.Open strQuery, strProvider
set objRst.ActiveConnection = Nothing

' This checks for the Disaster and displays if found
If DType = isit1 Then
%>

<CENTER>
<%Found = Found + 1%>

<h4>Here is result # <%=Found%></h4>
<b>isit1= <%=isit1%></b><br>
<b>Disaster Name: </b> <%=DName %><br>
<b>Disaster Type: </b> <%=DType %><br></center><br><br>
<LEFT><%=Detail %><br></LEFT>

<%
End if
' Check the next record
objRst.MoveNext
Loop
'If the found variable is false display not found message
If Found = 0 then
%>

<center><h4>OOPS! No disaster found, Please try again...</h4></center>

<%
End if
%>

***************

http://www.digital6.com

***************

 

[lobstah]

You need to open the recordset before you can access it.
This statement needs to come before the objRst.EOF:

objRst.Open strQuery, strProvider

 

[lobstah]

(because if it's not open, it's closed)

 

[cschaffer]

k....time for a break......yummmy...food and sleep are good!!!!!

HAHA


coffee bad!

CS ***************

http://www.digital6.com

***************

 

[lobstah]

put the open statement above checking for end of file:

objRst.Open strQuery, strProvider
Do Until objRst.EOF


And kill the connection outside of your loop:

Loop
set objRst.ActiveConnection = Nothing

 

[cschaffer]

thanks....works now..........time for eating...going blind now......

thanks all!!

CS ***************

http://www.digital6.com

***************

 

[ToddWW]

Lobstah,

Actually, you can safely disonnect the recordset from the connection before reading from (not writing to) the recordset.

set objRst.ActiveConnection = Nothing

Can come before..

IF NOT objRst.EOF

All that does is disconnect the recordset from the database connection and essentially close the connection, but the recordset is still valid and can be read from or scrolled through until you issue..

set objRst = Nothing

I use the ActiveConnection = Nothing as just a standard practice so I can free up resources as quickly as possible. I don't know, I feel like the less connections to a database, the better right ??

My problem was that I failed to tell CS to specify CursorLocation = 3 (Client). If you don't do that, you'll get the Cannot do it while object is open error. But if you set the cursor location to client, you can safely disconnect from the database and still have a valid recordset to read from and scroll through with one less connection to your DB.

Also, setting the CursorType to 3 aka adOpenStatic in most situations will automatically disconnect the recordset from the connection after the recordset is populated. But my reference says Depending on the Provider, so I implicitly disconnect from the database with the ActiveConnection = Nothing method.

In short, the following code works great for me. I just ran it on my server.

<%@ Language=VBScript %>
<% Option Explicit %>
<% Response.Buffer = True %>
<%
dim objRst
dim strQuery
dim strProvider
strProvider = &quot;DSN=mydsn&quot;

set objRst = Server.CreateObject(&quot;ADODB.Recordset&quot;)
objRst.CursorType = 3
objRst.CursorLocation = 3
strQuery = &quot;SELECT firstname FROM user&quot;
objRst.Open strQuery, strProvider
set objRst.ActiveConnection = Nothing
WHILE NOT objRst.EOF
  Response.Write objRst(&quot;firstname&quot;) & &quot;<br>&quot;
  objRst.MoveNext
WEND
set objRst = Nothing
%>

Another note, I only use this method when I'm reading from a recordset only. Any batch updating or writing obviously won't work with this. However, 75% of all my database connections are for read only purposes anyways.

Well, just some food for thought !!

CS, you can successfully disconnect that recordset from the database like I did in the original example. Just add the CursorLocation = 3 line to your code and / or follow the example above. But it's not really necessary. I just do it because I like to preserve as many resources as possible on the server. Every little bit counts !!

ToddWW

 

[lobstah]

aaaaaaaahhhhhhh.... good to know, and thank you ToddWW.

We always close the recordsets and connections prior to setting them to nothing, read somewhere that it takes longer to free up the memory otherwise...........