Pagination By Letter

[kenhutchings]

Hi all

What Im after is a little help with some ASP / SQL

What I want to do is pull all the records from the database where the company name begins with A when an A is clicked and all the B's when a B is clicked

any suggestions

Cheers!

 

[Dashley]

Ken,

This should help

Dan

dim  conn
dim rs1
dim sqldata 
dim name_like



Name_like = request.querystring 'or requestform("whatever you call it")

	set Conn =  server.CreateObject("ADODB.connection")
	set RS1 =  server.CreateObject("ADODB.recordset")

	conn.Open("Put your connection string here")
	
	sqldata="Select company_name from mytable where comapny_name like '" & name_like & "' "
	
	with rs1
	If rs1.State=1 then rs1.Close
		.ActiveConnection=conn
		.CursorType=adopendynamic
		.CursorLocation=aduseclient
		.Source=SqlData
		.Open
	end with
while not rs1.eof


response.write RS1("company_name") & "<BR>"   

rs1.movenext
wend


set conn=nothing
set rs1 = nothing

 

[vbkris]

and for listing out the A to Z links:

sqldata="SELECT DISTINCT SUBSTRING(CompanyName, 1, 1) AS Expr1 FROM mytable"

i bulit this in the SQL server...

Known is handfull, Unknown is worldfull

 

[DotNetGnat]

this should do it...

thread333-1112335

-DNG

 

[kenhutchings]

Cheers all - tis working like a dream - few little tweaks and I might be allowed to sleep this week!

 

[Genimuse]

So here's an add-on question of my own.

I'm about to write a letter-based thing like this, just figured I do a simple "LIKE 'A%'" kind of thing in the SQL (to MS Access). All well and good, easy enough stuff for me, I've even done it before.

BUT: What's a good query for stuff that doesn't start with an alpha character. I know the data has a few things that start with numbers and a few with punctuation, and I'd like to gather them all under one section, but it doesn't seem like

NOT LIKE 'A%' AND NOT LIKE 'B%' AND NOT LIKE 'C%' AND NOT LIKE 'D%' AND NOT LIKE 'E%'...

is going to be terribly efficient, nor the reverse of

LIKE '1%' OR LIKE '2%' OR LIKE '3%' OR LIKE '4%' ... OR LIKE '"%'...

is particularly swell, especially since I don't know every punctuation character that might some day end up in the data.

Any clever ideas, anyone?

 

[vbkris]

if i am correct LIKE operator accepts RegExps, try this:

LIKE '[^a-z]%'

it works in SQL server. have not tried it out in access...

Known is handfull, Unknown is worldfull