seek issue

seek issue

Post by Daniel Bu » Mon, 23 Aug 1999 04:00:00



Quote:>Even thought there is no strict "AB" value in that column, it would put the cursor on column 4 as it being the first closest value to my seek string.   Then I could cycle through the records after that looking for one that fit my other criteria.  Is there a way to do this with SQL Server and ODBC that won't take forever (like cycling through the records manually until I get where I want)??    Thanks

>                            -Dan

You can open a recordset ( a group of rows which fit your "seek"
criteria) as follows:

select value from table where value like 'AB%' order by value

You can then cycle through the results (recordset) as you would in
Foxpro.

However, the most efficient way is to build your conditions into the
SQL statement itself rather than stepping through each row. I usually
go through a process in my program where I am building the select
statement with the different conditions I am looking for. For example,
in VB:

dim strSql as string, strWhere as string
dim rs as new ADODB.recordset

strSql = "select valueA, valueB from table "
strWhere = ""

if conditionA then
   strWhere = strWhere & "valueA like 'AB%' and "
end if

if conditionB then
   strWhere = strWhere & "valueB in ('X','Y','Z') and "
end if

' many many if then else etc etc

if strWhere > "" then
   strWhere = "where " & left(strWhere, len(strWhere)-5)
end if

rs.open strSql & strWhere,adoConn,adOpenStatic, adLockReadOnly

Dan Bush
Battle Ground.wa.us
Please No Spam, to email, change .com to .net

 
 
 

seek issue

Post by bmce.. » Tue, 24 Aug 1999 04:00:00




Quote:> In foxpro I could do a seek on a index and get the closest value to

the one that I wanted.

Quote:> For example.
>  AABBE-111-12
>  AACBE-111-12
>  AADBE-111-12
>  ABBBE-111-12
>  ABCBE-111-12
>  ABDBE-111-12
>  ACBBE-111-12
>  ACCBE-111-12
>  ACDBE-111-12
>  ACEBE-111-12
>  Let's say that I wanted to find the first row with
> an "AB" value, in Foxpro I would say

> seek "AB"
> Even thought there is no strict "AB" value in that
> column, it would put the cursor on column 4 as it being the first
closest
> value to my seek string.   Then I could cycle through the
records
> after that looking for one that fit my other criteria.  Is there
a
> way to do this with SQL Server and ODBC that won't take forever (like
cycling
> through the records manually until I get where I want)??

If you want a resultset of everything that starts with AB, in an
ordered list:

select c1
from   mytable
where  c1 like 'AB%'
order by c1

If you want the primary key value of the row with the lowest AB value,
you could try:

select id
from   mytable
where c1 = (
    select min(c1) from mytable
    where c1 like 'AB%'
    )

--
Ben McEwan
Geist, LLC

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

 
 
 

1. seek issue

    Here's what I want to do.  In foxpro I could do a seek
on a index and get the closest value to the one that I
wanted.  For example.

row 1    AABBE-111-12
row 2    AACBE-111-12
row 3    AADBE-111-12
row 4    ABBBE-111-12
row 5    ABCBE-111-12
row 6    ABDBE-111-12
row 7    ACBBE-111-12
row 8    ACCBE-111-12
row 9    ACDBE-111-12
row 10  ACEBE-111-12

    Let's say that I wanted to find the first row with an
"AB" value, in Foxpro I would say

                        seek "AB"

    Even thought there is no strict "AB" value in that
column, it would put the cursor on column 4 as it being the
first closest value to my seek string.   Then I could cycle
through the records after that looking for one that fit my
other criteria.  Is there a way to do this with SQL Server
and ODBC that won't take forever (like cycling through the
records manually until I get where I want)??    Thanks

                            -Dan

2. memory problems with foxpro extended under ms-dos 6.2

3. Index Seek issues

4. Building Search Button

5. seek issue

6. DISL: Change a MSSQL70 date from GMT to local time without processing it

7. Wanted: FoxBase+/Mac Dist. Kit

8. Seeking programing help for a custom page size issue on dot matrix printer

9. Using Index Seek, Seek

10. Help: Seek command won't seek

11. Question about SEEK vs SEEK()