ado 2.6 question

I am trying to do something very simple. I merely want to get a field from an SQL table. Here is my code. Everything works fine except when I look at the lRecords, I get -1, even though the iRc=0. Please tell me what is wrong?

iRc = ADODB_New_Connection (NULL, 1, LOCALE_NEUTRAL, 0, &CAOConnect);
iRc = ADODB_Connection15Open (CAOConnect, NULL, "Provider=MSDASQL;DSN=CHIW",
"XXXXX", "yyyyyy", -1);
for (a=0;a<100;a++)
{
iRc = ADODB__ConnectionExecute (CAOConnect, NULL,
"SELECT * from doc_ids WHERE Doc_ID<>''",&varInt, -1, &CAORecordSet);

iRc = ADODB__RecordsetGetRecordCount (CAORecordSet, &errinfoTest,&lRecords);
}
iRc=ADODB_Recordset15Close (CAORecordSet, NULL);
iRc=ADODB__ConnectionClose (CAOConnect, NULL);RecordCount returns always -1 when you implement a dynamic cursor.
Try to use a static one, or count the records by yourself using SELECT COUNT(*) from your_table WHERE your_condition

Originally posted by wooliewillie
I am trying to do something very simple. I merely want to get a field from an SQL table. Here is my code. Everything works fine except when I look at the lRecords, I get -1, even though the iRc=0. Please tell me what is wrong?

iRc = ADODB_New_Connection (NULL, 1, LOCALE_NEUTRAL, 0, &CAOConnect);
iRc = ADODB_Connection15Open (CAOConnect, NULL, "Provider=MSDASQL;DSN=CHIW",
"XXXXX", "yyyyyy", -1);
for (a=0;a<100;a++)
{
iRc = ADODB__ConnectionExecute (CAOConnect, NULL,
"SELECT * from doc_ids WHERE Doc_ID<>''",&varInt, -1, &CAORecordSet);

iRc = ADODB__RecordsetGetRecordCount (CAORecordSet, &errinfoTest,&lRecords);
}
iRc=ADODB_Recordset15Close (CAORecordSet, NULL);
iRc=ADODB__ConnectionClose (CAOConnect, NULL);|||Excuse my ignorance, but where am I defining a dynamic cursor? And to count it myself, how would I code it exactly? Still in a loop?|||First of all, I'd suggest to use the SQLOLEDB Provider instead of MSDASQL (you are using a SQL table after all).

I noticed that you used an ODBC connection. Try to declare a connection object without using the ODBC layer. The connection object has a property "CursorType";you can choose between dynamic,static,Keyset and ForwardOnly (see ado documentation for more).

To count the records directly you can use the SELECT statement posted before (SELECT COUNT(*) as TotalRecords FROM your_table WHERE your_condition). It will return a recordset of one->examine the value of TotalRecords.

Originally posted by wooliewillie
Excuse my ignorance, but where am I defining a dynamic cursor? And to count it myself, how would I code it exactly? Still in a loop?|||>>First of all, I'd suggest to use the SQLOLEDB Provider instead of MSDASQL (you are using a SQL table after all).

>>I noticed that you used an ODBC connection.

This is true. First I go to the Data Sources(ODBC) icon under Admin tools on my ms 2000 machine. Then I set up the DSN. When I chose a driver, SQL server is the choice I used. It automatically chose MSDASQL for me. I don't see a choice for any other SQL driver. Do I download this driver from somewhere so I can create a DSN with the proper provider?|||To change the provider, simply replace MSDASQL with SQLOLEDB in your connection string, but I'd rather declare a connection object and use the object properties afterwards.

Originally posted by wooliewillie
>>First of all, I'd suggest to use the SQLOLEDB Provider instead of MSDASQL (you are using a SQL table after all).

>>I noticed that you used an ODBC connection.

This is true. First I go to the Data Sources(ODBC) icon under Admin tools on my ms 2000 machine. Then I set up the DSN. When I chose a driver, SQL server is the choice I used. It automatically chose MSDASQL for me. I don't see a choice for any other SQL driver. Do I download this driver from somewhere so I can create a DSN with the proper provider?