rs.RecordCount issue...

hi guys

I'm having a problem with the code below. The SQL statement runs fine and
i've even checked the database in the backgroud and it contains a perfect set
of records waiting to be picked up ... yet the rs.RecordCount funtion is
returning a value which is 1 less than the correct figure. i checked the
database on various instances and see that it always misses the last line, of
the recordset.

anyone have any ideas on how to fix this?


txtRecordset.CursorLocation = adUseClient
txtRecordset.Open "SELECT " & tab35 & ".prefix, " & tab35 & ".Indexing, " &
tab35 & ".extension, " & tab36 & ".Title FROM " & tab35 & " INNER JOIN " &
tab36 & " ON " & tab35 & ".Indexing = " & tab36 & ".Indexing", txtConnection,
adOpenStatic, adLockOptimistic
'msgbox "Check client_DB temp"
'results are perfect in the DB... the recCount value is wrong. not picking
up the last value.

Dim RecCount

RecCount = txtRecordset.RecordCount
TotalRecCount = TotalRecCount + RecCount
--- end.

Bob
Tue May 15 09:24:02 CDT 2007

Irfan wrote:
> hi guys
>
> I'm having a problem with the code below. The SQL statement runs fine
> and i've even checked the database in the backgroud and it contains a
> perfect set of records waiting to be picked up ... yet the
> rs.RecordCount funtion is returning a value which is 1 less than the
> correct figure. i checked the database on various instances and see
> that it always misses the last line, of the recordset.
>
> anyone have any ideas on how to fix this?
>
>
> txtRecordset.CursorLocation = adUseClient
> txtRecordset.Open "SELECT " & tab35 & ".prefix, " & tab35 &
> ".Indexing, " & tab35 & ".extension, " & tab36 & ".Title FROM " &
> tab35 & " INNER JOIN " & tab36 & " ON " & tab35 & ".Indexing = " &
> tab36 & ".Indexing", txtConnection, adOpenStatic, adLockOptimistic
> 'msgbox "Check client_DB temp"
> 'results are perfect in the DB... the recCount value is wrong. not
> picking up the last value.
>

There has to be something wrong with the sql string you are generating.
Assign it to a variable and inspect it (msgbox or debug.print if
available).


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Irfan
Tue May 15 10:25:02 CDT 2007

i did check it with msgbox command ... and it didn't show it but then i
checked the temporary table and the information was still there...
so i worked around the problem by adding one more record in the database
with blanks, then ran this same code and it works perfectly on 43 different
tests. definitely 1 less recordset being counted by the system though ... not
sure why.

i know there is something wrong somewhere and i'll figure it out one way or
the other, at a later stage, but this issue was way overdue to be resolved
and i just had to get it done for my boss.

thanks anyways ... i'll post the fix if i get it.

"Bob Barrows [MVP]" wrote:

>
> There has to be something wrong with the sql string you are generating.
> Assign it to a variable and inspect it (msgbox or debug.print if
> available).
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Bob
Tue May 15 10:45:05 CDT 2007

Irfan wrote:
> i did check it with msgbox command ... and it didn't show it but then
no, I meant for you to assign the sql statement to a variable and
display what it looks like at runtime:

dim sql
sql="SELECT " & tab35 & ".prefix, " & tab35 & ".Indexing, " & _
tab35 & ".extension, " & tab36 & ".Title FROM " & tab35 & _
" INNER JOIN " & _
tab36 & " ON " & tab35 & ".Indexing = " & tab36 & ".Indexing

msgbox sql
txtRecordset.Open sql, txtConnection,
adOpenStatic, adLockOptimistic



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Alexander
Wed May 16 08:41:59 CDT 2007

Irfan schrieb:
> i did check it with msgbox command ... and it didn't show it but then i
> checked the temporary table and the information was still there...
> so i worked around the problem by adding one more record in the database
> with blanks, then ran this same code and it works perfectly on 43 different
> tests. definitely 1 less recordset being counted by the system though ... not
> sure why.

What database-engine and ADO-Provider?
Is the Connection also clientside?

If it is VBScript, did you declare 'Const adUseClient = 3'
somewhere?

MfG,
Alex

Irfan
Fri May 18 01:58:00 CDT 2007

Alex ...

Engine - jet4
ADO = ADODB

i'm running the vbscript in an HTA file,
i have declared the adUseClient=3 but it still gives me problems.


thanks,

Irfan.
"Alexander Mueller" wrote:

> Irfan schrieb:
> > i did check it with msgbox command ... and it didn't show it but then i
> > checked the temporary table and the information was still there...
> > so i worked around the problem by adding one more record in the database
> > with blanks, then ran this same code and it works perfectly on 43 different
> > tests. definitely 1 less recordset being counted by the system though ... not
> > sure why.
>
> What database-engine and ADO-Provider?
> Is the Connection also clientside?
>
> If it is VBScript, did you declare 'Const adUseClient = 3'
> somewhere?
>
> MfG,
> Alex
>

Irfan
Fri May 18 02:01:02 CDT 2007

bob, sorry i didn't get what you meant earlier ... i haven't tried this
method yet but will do soon, and let you know if i see where it's going wrong.

thanks,

Irfan.

"Bob Barrows [MVP]" wrote:

> Irfan wrote:
> > i did check it with msgbox command ... and it didn't show it but then
> no, I meant for you to assign the sql statement to a variable and
> display what it looks like at runtime:
>
> dim sql
> sql="SELECT " & tab35 & ".prefix, " & tab35 & ".Indexing, " & _
> tab35 & ".extension, " & tab36 & ".Title FROM " & tab35 & _
> " INNER JOIN " & _
> tab36 & " ON " & tab35 & ".Indexing = " & tab36 & ".Indexing
>
> msgbox sql
> txtRecordset.Open sql, txtConnection,
> adOpenStatic, adLockOptimistic
>
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Bob
Fri May 18 06:35:38 CDT 2007

Irfan wrote:
> Alex ...
>
> Engine - jet4
> ADO = ADODB

No. Given that you are using Jet, the only correct answer to the
"ADO-Provider" provider question is either "Microsoft.Jet.OLEDB.4.0" (the
OLE DB Provider for Microsoft Jet) or "MSDASQL (the OLE DB Provider for ODBC
Databases). Alexander was trying to discover whether you are using OLE DB
(recommended - see http://www.aspfaq.com/show.asp?id=2126) or ODBC to access
your database. The easiest way for you to answer this question is to show us
your connection's connection string.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Irfan
Fri May 18 09:05:01 CDT 2007

sorry guys, i'm new to all this, i actually did use OLEDB and now i realize i
have mix of ADO and OLE ... does that matter a lot? ...

following is my connection method...

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" &
NetPath & dbfile
Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM " & tab2 & "" , objConnection,
adOpenStatic, adLockOptimistic

this is the normal way i have handled all the databases.

thanks,

Irfan.

"Bob Barrows [MVP]" wrote:

> Irfan wrote:
> > Alex ...
> >
> > Engine - jet4
> > ADO = ADODB
>
> No. Given that you are using Jet, the only correct answer to the
> "ADO-Provider" provider question is either "Microsoft.Jet.OLEDB.4.0" (the
> OLE DB Provider for Microsoft Jet) or "MSDASQL (the OLE DB Provider for ODBC
> Databases). Alexander was trying to discover whether you are using OLE DB
> (recommended - see http://www.aspfaq.com/show.asp?id=2126) or ODBC to access
> your database. The easiest way for you to answer this question is to show us
> your connection's connection string.
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

Bob
Fri May 18 11:08:16 CDT 2007

Irfan wrote:
> sorry guys, i'm new to all this, i actually did use OLEDB and now i
> realize i have mix of ADO and OLE ... does that matter a lot? ...

:-)
ADO uses OLE DB Providers to connect to databases. So no, it does not
matter. ;-)
>
> following is my connection method...
>
> Set objConnection = CreateObject("ADODB.Connection")
> objConnection.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source ="
> & NetPath & dbfile
> Set objRecordSet = CreateObject("ADODB.Recordset")
> objRecordset.CursorLocation = adUseClient
> objRecordset.Open "SELECT * FROM " & tab2 & "" , objConnection,
> adOpenStatic, adLockOptimistic
>
> this is the normal way i have handled all the databases.
>

... and a fine method it is. This is not relevant to the problem you are
experiencing.

Have you gotten a sql statement for us to look at yet?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"