browsing records on a sql server

I am not familiar with sql server functions. What's the best method to
browse records of a table on sql server?

What I can think of, is to:
1. use sqlexec("select primary_key from the_table","t_key")
2. browse t_key
3. issue sqlexec("select data_fields from the_table where
primary_key="+t_key.primary_key) to pull and display the data from the
sql server.

Is there a better way not involing pulling the primary keys? Imagine
having a table with millions records.

--
.~. Might, Courage, Vision. In Linux We Trust.
/ v \ http://www.linux-sxs.org
/( _ )\ Linux 2.4.22-xfs
^ ^ 5:02pm up 13 days, 17:34, 0 users, load average: 0.99, 0.97, 0.91

toylet
Thu Feb 05 06:00:41 CST 2004

I figured out a solution using "select top 1", but how could I do this
with t-sql?

select field, recno() from a_table

> I am not familiar with sql server functions. What's the best method to
> browse records of a table on sql server?

--
.~. Might, Courage, Vision. In Linux We Trust.
/ v \ http://www.linux-sxs.org
/( _ )\ Linux 2.4.22-xfs
^ ^ 7:58pm up 13 days, 20:30, 1 user, load average: 0.99, 0.97, 0.91

Rick
Thu Feb 05 09:21:41 CST 2004

Toylet,
SQL server has no "recno()" - in fact there is no concept of record =
numbers in SQL. Use your primary key for record identification.

Rick

"toylet" <toylet@mail.hongkong.com> wrote in message =
news:eVgjp%2396DHA.3420@TK2MSFTNGP11.phx.gbl...
> I figured out a solution using "select top 1", but how could I do this =

> with t-sql?
>=20
> select field, recno() from a_table
>=20
> > I am not familiar with sql server functions. What's the best method =
to=20
> > browse records of a table on sql server?
>=20
> --=20
> .~. Might, Courage, Vision. In Linux We Trust.
> / v \ http://www.linux-sxs.org
> /( _ )\ Linux 2.4.22-xfs
> ^ ^ 7:58pm up 13 days, 20:30, 1 user, load average: 0.99, 0.97, =
0.91

toylet
Thu Feb 05 09:38:46 CST 2004

I will post a snapshot of the code used:

function thisform.gorec
lparameter m.cursor, m.mode, m.recno
local m.conn, m.sql, m.select

m.mode=upper(m.mode)

m.select=select()
m.conn=sqlconnect("myconn")

* just in case productid is of identity type
m.sql = "select"+;
" identity(int,1,1) as recno,"+;
" cast(productid as int) as productid, productname"+;
" into #temp_products"+;
" from products"+;
" where discontinued=0"+;
" order by productname"
=sqlexec(m.conn,m.sql,"t_temp")

=sqlexec(m.conn,"select * from #temp_products","t_temp")

* pull one record
m.sql="SELECT TOP 1 recno, productid, productname"+;
" FROM #temp_products"
do case
case m.mode="NEXT"
m.sql=m.sql+" where recno>"+alltrim(str(m.recno))
case m.mode="PREV"
m.sql=m.sql+" where recno<"+alltrim(str(m.recno))
endcase
m.sql=m.sql+" ORDER BY recno"
if inlist(m.mode,"BOTTOM","PREV")
m.sql=m.sql+" DESC"
endif
thisform.sql=m.sql

if used(m.cursor)
use in (m.cursor)
endif
=sqlexec(m.conn,m.sql,m.cursor)
select (m.cursor)

* cleanup
=sqlexec(m.conn,"drop table #temp_products","t_temp")

=sqldisconnect(m.conn)

if used("t_temp")
use in t_temp
endif

select (m.select)
return .t.

--
.~. Might, Courage, Vision. In Linux We Trust.
/ v \ http://www.linux-sxs.org
/( _ )\ Linux 2.4.22-xfs
^ ^ 11:36pm up 14 days, 8 min, 0 users, load average: 1.26, 1.51, 1.33

toylet
Thu Feb 05 10:08:22 CST 2004

btw, I wonder whether this piece of work should go to Microsoft's
knowledge base. I don't think VFP's KB has an introductory article on this.

what about Henzenwertz's book on C/S application?

toylet wrote:

> someone pointed me to use identity(int,1,1) to create a recno() column
> in sql server. after a bit of debuggin, attached is my solution, using
> the sample database northwind in sql server 7.

--
.~. Might, Courage, Vision. In Linux We Trust.
/ v \ http://www.linux-sxs.org
/( _ )\ Linux 2.4.22-xfs
^ ^ 12:06am up 14 days, 38 min, 0 users, load average: 1.03, 1.34,
1.32

toylet
Thu Feb 05 18:16:38 CST 2004

warning: the guru told me that identity(int,1,1) may not be reliable.
better use alter table to create the identity column.

> m.sql = "select"+;
> " identity(int,1,1) as recno,"+;
> " cast(productid as int) as productid, productname"+;
> " into #temp_products"+;
> " from products"+;
> " where discontinued=0"+;
> " order by productname"
> =sqlexec(m.conn,m.sql,"t_temp")

--
.~. Might, Courage, Vision. In Linux We Trust.
/ v \ http://www.linux-sxs.org
/( _ )\ Linux 2.4.22-xfs
^ ^ 8:14am up 14 days, 8:46, 0 users, load average: 1.13, 1.03, 1.01