ADO Recordsets object method Open

Hello group!

I use MS Visual C++ 6.0, ADO, MS SQL Server 2000.
When I attempt to open my database I meet with a following problem:
when I try to get a bookmark of the current record in a Recordset
object a following run-time error occurs: Unhandled exception in
testdb.exe(KERNEL32.DLL):
0xE06D7363: Microsoft C++ Exception.

I created my database by 3 SQL commands:

create database testdb
create table testtable
(
i int
)
insert into testtable values(0)

The error occurs in the following code snippet:
#import "D:\Program Files\Common Files\System\ADO\msado15.dll" \
no_namespace rename("EOF", "EndOfFile")
int main()
{
CoInitialize(NULL);

bstr_t strCnn("Provider=sqloledb;Data Source=;"
"Initial Catalog=testdb;Trusted_Connection=YES;");

const char* tablename = "testtable";

_RecordsetPtr recs;
recs.CreateInstance(__uuidof(Recordset) );
recs -> Open(tablename, strCnn, adOpenStatic,
adLockOptimistic,adCmdTable);
_variant_t bm = recs -> Bookmark; // the error occurs here
recs -> Close();
CoUninitialize();
}

During the debugging this code I met that the error depended on a type
of locking. When I set adLockBatchOptimistic or adLockOptimistic
or adLockPessimistic the error occurs but when I set adLockReadOnly or
adLockUnspecified it doesn't occur. By the way this error doesn't
occur when
I open Pubs database with any type of locking. What is a cause of this
error?
Thank you.#import "D:\Program Files\Common Files\System\ADO\msado15.dll" \
no_namespace rename("EOF", "EndOfFile")
int main()
{
CoInitialize(NULL);

bstr_t strCnn("Provider=sqloledb;Data Source=;"
"Initial Catalog=testdb;Trusted_Connection=YES;");

const char* tablename = "testtable";

_RecordsetPtr recs;
recs.CreateInstance(__uuidof(Recordset) );
recs -> Open(tablename, strCnn, adOpenStatic,
adLockOptimistic,adCmdTable);
bool r = recs -> Supports(adBookmark);
_variant_t bm = recs -> Bookmark; // the error occurs here
recs -> Close();
CoUninitialize();
}

If I opened testdb then recs -> Supports(adBookmark)
returned true when I used adLockReadOnly or adLockUnspecified. If I
used other
LockTypes then this function returned false and the error occured in
this line:
__variant_t bm = recs -> Bookmark; // the error occurs here

If I opened the Pubs database then recs -> Supports(adBookmark)
returned true for any LockType. Why does a Recordset object support
bookmark functionality for any LockType if I open the Pubs database?!
I can't understand it!!!

ADO Recordset.find method Criteria

Can you use an adodb.recordset.find method to locate a record on an MS SQL 2000 server table? I can't figure out how to specify the criteria correctly and haven't found any good documentation. Ex:
Dim tblBusiness As New ADODB.Recordset
'Make an ADO connection to the database.
cn.ConnectionString = "XXXXXXXXXXXXXX'"
cn.Open
cn.Properties("Current Catalog") = "MyDB"
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
tblBusiness.Open "tblBusiness", cn, adOpenDynamic, adLockReadOnly
tblBusiness.Find (Criteria, SkipRecords, SearchDirection, Start)
I haven't found anything detailing how to specify the criteria for the find.
Thanks.
My recommendation would be not to use Find, but to use a select
statement with a WHERE clause, selecting only required data. What you
are trying to do is fetch an entire table, then throw away the rows
you don't want. Think for a moment about what happens to the network,
the server, and your client application when the table has 1,000,000
rows. "SELECT <column list> FROM <TableName> WHERE <some
field>=Criteria" is far more efficient than filtering after the fact.
--Mary
On Sat, 8 May 2004 16:31:01 -0700, "DB"
<anonymous@.discussions.microsoft.com> wrote:

>Can you use an adodb.recordset.find method to locate a record on an MS SQL 2000 server table? I can't figure out how to specify the criteria correctly and haven't found any good documentation. Ex:
> Dim tblBusiness As New ADODB.Recordset
>'Make an ADO connection to the database.
> cn.ConnectionString = "XXXXXXXXXXXXXX'"
> cn.Open
> cn.Properties("Current Catalog") = "MyDB"
> cmd.ActiveConnection = cn
> cmd.CommandType = adCmdText
> tblBusiness.Open "tblBusiness", cn, adOpenDynamic, adLockReadOnly
> tblBusiness.Find (Criteria, SkipRecords, SearchDirection, Start)
>I haven't found anything detailing how to specify the criteria for the find.
>Thanks.

ADO Recordset.find method Criteria

Can you use an adodb.recordset.find method to locate a record on an MS SQL 2
000 server table? I can't figure out how to specify the criteria correctly
and haven't found any good documentation. Ex:
Dim tblBusiness As New ADODB.Recordset
'Make an ADO connection to the database.
cn.ConnectionString = "XXXXXXXXXXXXXX'"
cn.Open
cn.Properties("Current Catalog") = "MyDB"
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
tblBusiness.Open "tblBusiness", cn, adOpenDynamic, adLockReadOnly
tblBusiness.Find (Criteria, SkipRecords, SearchDirection, Start)
I haven't found anything detailing how to specify the criteria for the find.
Thanks.My recommendation would be not to use Find, but to use a select
statement with a WHERE clause, selecting only required data. What you
are trying to do is fetch an entire table, then throw away the rows
you don't want. Think for a moment about what happens to the network,
the server, and your client application when the table has 1,000,000
rows. "SELECT <column list> FROM <TableName> WHERE <some
field>=Criteria" is far more efficient than filtering after the fact.
--Mary
On Sat, 8 May 2004 16:31:01 -0700, "DB"
<anonymous@.discussions.microsoft.com> wrote:

>Can you use an adodb.recordset.find method to locate a record on an MS SQL
2000 server table? I can't figure out how to specify the criteria correctly
and haven't found any good documentation. Ex:
> Dim tblBusiness As New ADODB.Recordset
>'Make an ADO connection to the database.
> cn.ConnectionString = "XXXXXXXXXXXXXX'"
> cn.Open
> cn.Properties("Current Catalog") = "MyDB"
> cmd.ActiveConnection = cn
> cmd.CommandType = adCmdText
> tblBusiness.Open "tblBusiness", cn, adOpenDynamic, adLockReadOnly
> tblBusiness.Find (Criteria, SkipRecords, SearchDirection, Start)
>I haven't found anything detailing how to specify the criteria for the find
.
>Thanks.

ADO find method too slow, how should I do this

I need an efficient way to get the absolute position of a record in a query matching a specific key value. The Find method is a serial search, too slow for big data sets. I am using both SQL Server Express and Jet 4 via ADO.

One example of why I need this .....

I have a list control with a subset of a table (controlled by where clause in query). I want to save the current state of the list control and later restore it when the app restarts. I want to preserve and restore the current line selection in the list control.

So, I save the key value for the current line, upon restart use the find method to locate the key, and set the list control current record index to the current absolute position.

This is too slow for big data sets since the find does a record by record search.

I cannot just save and restore the list control offset, the table may have changed.

The list control has owner data so the data is not all read into the control, so I can't just search through the controls image.

Any ideas. I did search for this answer and failed. Feel free to flame me as long as an answer is included too :-)

Z

Hi,

Here's a quick idea: instead of doing the Find, why don't you simply run a query like "SELECT COUNT(*) FROM mytable WHERE tablekey <= mycurrentkey", this would give you the desired "absolute position" in the recordset and you could simply manipulate the selection in the listbox.

I understand this may go out of sync with the original table, but you could mitigate that - for instance, enclosing both the listbox population and the query above into a transaction (probably with higher isolation level).

And one more (simpler) idea - many of the list and combo-box controls have their own Seek methods. You can use that and position into the control instead of going through the recordset. The search will be local into the already populated data.

HTH,

Jivko Dobrev - MSFT

--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Another idea that might work is manually do a binary search on the keys in the listbox (if the items in the listbox are sorted by key this will work). Should be faster than find.

Likewise if your key is ordered you could do what Jivko mentions but something like this:

select count(*) from table where <conditions that restrict items> and key=<saved pkey from last time>

Count should give you your absolute position or zero if the record is deleted.