Displaying Database results

[STU]

I'm using this code to display results from a database:

<%
companyname = Request.QueryString( "companyname")





Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=C:mypath\mydatabase.mdb"

quote = "SELECT * FROM Results WHERE company LIKE '%"&
companyname &"%'"

Set quote = Conn.Execute( quote)

if quote.eof then
Response.write("No records found.")
Response.End
end if


%>

The problem I'm having is I'm searching for a company name
and there are more than one record with the same company
name but my results page will only display one record.
Also I'm displaying more fields than just the company
name, but company name is what field is used in the
query. All the fields together are formatted to make up a
quote form. I need this code to display all the records
not just the first one it comes to...and all the other
fields with it. The frontpage database results wizard
does this when you select how many records you want
displayed at one time. If you select 1 then a nav bar is
created at the bottom of the results page to take you to
the next record...this is what I'm trying to
accomplish...but without using the wizard...please help

 

[Jim Buyens]

OK, so you want one record per page, with backward and
forward paging; is that it?

The first thing to check is whether ADO is sending you a
record count. So, right after

Set quote = Conn.Execute(quote)

code

response.write "record count=" & quote.recordcount

If you always gets zero, even though there are records,
then you may need to change the recordset's cursor type or
cursor location.


Then, check request.querystring("recnr") for a numeric
value less than the recordset's record count, and deffault
to 1. For example:

if isnumeric(request.querystring("recnr")) then
recnr = cint(request.querystring("recnr"))
if recnr < 1 then
recnr = 1
end if
if recnr > quote.recordcount then
recnr = quote.recordcount
end if
else
recnr = 1
end if

Then, move forward that many records minus one (becuase
the open recordset is already at the first record).

if recnr > 1 then
quote.move(recnr - 1)
end if

Finally, to generate the foward and back links, code

if recnr > 1 then
response.write "<a href="?recnr=" & recnr - 1 & ">" & _
"Back</a>
end if

and

if recnr < quote.recordcount then
response.write "<a href="?recnr=" & recnr + 1 & ">" & _
"Next</a>
end if

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

 

[Stu]

When I tested for record count, it came back as -1 when
there are about 10 records with the company name i'm
searching for I added to code you suggested:

<%
companyname = Request.QueryString( "companyname")





Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=C:mypath\mydatabase.mdb"

quote = "SELECT * FROM Results WHERE company LIKE '%"&
companyname &"%'"

Set quote = Conn.Execute( quote)

if isnumeric(request.querystring("recnr")) then
recnr = cint(request.querystring("recnr"))
if recnr < 1 then
recnr = 1
end if
if recnr > quote.recordcount then
recnr = quote.recordcount
end if
else
recnr = 1
end if

if recnr > 1 then
quote.move(recnr - 1)
end if

if recnr > 1 then
response.write "<a href="?recnr=" & recnr - 1 & ">" & _
"Back</a>
end if

and

if recnr < quote.recordcount then
response.write "<a href="?recnr=" & recnr + 1 & ">" & _
"Next</a>
end if


if quote.eof then
Response.write("No records found.")
Response.End
end if


%>

There seems to be some sort of character error in the
making of the nav buttons, here is the error I get:

Microsoft VBScript compilation error '800a0408'

Invalid character

/quote/nwiebe/companyname.asp, line 32

response.write "<a href="?recnr=" & recnr - 1 & ">" & _

I feel I'm close, what am I doing wrong?

thanks,

Stu

 

[Jim Buyens]

To solve the problem with recordcount being -1, try opening the
recordset this way:

Const adOpenKeySet = 1
Const adLockReadOnly = 1
Const adUseClient = 3

sql = "SELECT * FROM Results WHERE company LIKE '%"& companyname &"%'"
Set quote = Server.CreateObject("ADODB.Recordset")
quote.CursorLocation = adUseClient
quote.Open sql, Conn, adOpenKeySet, adLockReadOnly

as to the compilation error (Invalid character) that code should be:

if recnr > 1 then
response.write "<a href=""?recnr=" & recnr - 1 & """>" & _
"Back</a>
end if

if recnr < quote.recordcount then
response.write "<a href=""?recnr=" & recnr + 1 & """>" & _
"Next</a>
end if

Just so you know, I cold-typed all this code by hand. You should
expect to do a little fine-tuning so it fits into your application.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

 

[Stu]

Jim, thanks for all the help. This is working to some
extent. When i do a search from my form field
called "companyname" for the company Alltel it pulls up
the first record it finds with the company name of Alltel
and creates the next button, the link looks like this:

https://in.northern-tech.com/quote/nwiebe/companyname.asp?
companyname=alltel

Where is falls apart is when you hit the next button. It
doesn't take you to the next record with Alltel in the
company name field, it just takes you to another record
and the link changes accordingly:

https://in.northern-tech.com/quote/nwiebe/companyname.asp?
recnr=2

Here is how the asp code looks:

<%
companyname = Request.QueryString( "companyname")





Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open "DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=mypath\mydatabase.mdb"

Const adOpenKeySet = 1
Const adLockReadOnly = 1
Const adUseClient = 3

sql = "SELECT * FROM Results WHERE company LIKE '%"&
companyname &"%'"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient
rs.Open sql, Conn, adOpenKeySet, adLockReadOnly



if isnumeric(request.querystring("recnr")) then
recnr = cint(request.querystring("recnr"))
if recnr < 1 then
recnr = 1
end if
if recnr > rs.recordcount then
recnr = rs.recordcount
end if
else
recnr = 1
end if

if recnr > 1 then
rs.move(recnr - 1)
end if

if recnr > 1 then
response.write "<a href=""?recnr=" & recnr - 1 & """>" &
_
"Back</a>"
end if

if recnr < rs.recordcount then
response.write "<a href=""?recnr=" & recnr + 1 & """>" &
_
"Next</a>"

end if

if rs.eof then
Response.write("No records found.")
Response.End
end if


%>

How can i make it keep looking for the name i searched for?

 

[Jim Buyens]

Are you sure you're recycling the value in the companyname text box?
You probbly need something like:

<input type="text" name="companyname" value="<%=companyname %>">

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------