recordcount = -1 ??

Hi,

I defined a query (name = myquery) in Access like this:
SELECT
IIf([wwk]='Maandag',[maandag],IIf([wwk]='Dinsdag',[dinsdag], IIf([wwk]='Woens
dag',[woensdag],IIf([wwk]='Donderdag',[donderdag],[vrijdag]) )))
FROM daguur;
When executing in Access and filling e.g. 'maandag' as parameter, it works
(10 records). No problem.

In ASP:
set OBJDC = Server.CreateObject("ADODB.Connection")
objdc.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source =c:\mydb.mdb")
set rs=Server.createObject("ADODB.recordset")
objdc.myquery wwk,rs
rec=rs.recordcount

My problem is: no error, but rec = -1.
Any idea why i don't get the 10 records?
Thanks
Mich

Re: recordcount = -1 ??

"Mich" wrote in message
news:O6iHv9WxEHA.2564@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> I defined a query (name = myquery) in Access like this:
> SELECT
>
IIf([wwk]='Maandag',[maandag],IIf([wwk]='Dinsdag',[dinsdag], IIf([wwk]='Woens
> dag',[woensdag],IIf([wwk]='Donderdag',[donderdag],[vrijdag]) )))
> FROM daguur;
> When executing in Access and filling e.g. 'maandag' as parameter, it works
> (10 records). No problem.
>
> In ASP:
> set OBJDC = Server.CreateObject("ADODB.Connection")
> objdc.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source =c:\mydb.mdb")
> set rs=Server.createObject("ADODB.recordset")
> objdc.myquery wwk,rs
> rec=rs.recordcount
>
> My problem is: no error, but rec = -1.
> Any idea why i don't get the 10 records?
> Thanks
> Mich

http://www.aspfaq.com/show.asp?id=2193

Re: recordcount = -1 ??

Thanks, but i need to run that query from Access (with parameter wwk). Is
there no parameter i can use to change the cursor in order to get the
recordcount (and also to get rs.fields("maandag").value)?


"McKirahan" wrote in message
news:C7Ijd.61194$HA.25228@attbi_s01...
> "Mich" wrote in message
> news:O6iHv9WxEHA.2564@TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I defined a query (name = myquery) in Access like this:
> > SELECT
> >
>
IIf([wwk]='Maandag',[maandag],IIf([wwk]='Dinsdag',[dinsdag], IIf([wwk]='Woens
> > dag',[woensdag],IIf([wwk]='Donderdag',[donderdag],[vrijdag]) )))
> > FROM daguur;
> > When executing in Access and filling e.g. 'maandag' as parameter, it
works
> > (10 records). No problem.
> >
> > In ASP:
> > set OBJDC = Server.CreateObject("ADODB.Connection")
> > objdc.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source =c:\mydb.mdb")
> > set rs=Server.createObject("ADODB.recordset")
> > objdc.myquery wwk,rs
> > rec=rs.recordcount
> >
> > My problem is: no error, but rec = -1.
> > Any idea why i don't get the 10 records?
> > Thanks
> > Mich
>
> http://www.aspfaq.com/show.asp?id=2193
>
>

Re: recordcount = -1 ??

It's explained in the article. You don't need to use Recordcount, but if you
insist on using it for some reason, you need to use either a client-side
cursor, or a server-side non-forward-only cursor. I would go with a static
server-side cursor (although a keyset or dynamic cursor would also work).

set rs=Server.createObject("ADODB.recordset")
rs.CursorType=3 '3=adOpenStatic
objdc.myquery wwk,rs
rec=rs.recordcount

HOWEVER, this is inefficient. You should take the advice given in the
article and use a GetRows array to get your record count. Like this:

dim arData
set rs=Server.createObject("ADODB.recordset")
objdc.myquery wwk,rs
if not rs.eof then arData = rs.GetRows
rs.close: set rs = nothing
objdc.close: set objdc=nothing
if isArray(arData) then
rec = Ubound(arData,2) + 1
else
rec = 0
end if

Looping through an array is much more efficient than looping through a
recordset. See:
http://www.aspfaq.com/show.asp?id=2467

HTH,
Bob Barrows

Mich wrote:
> Thanks, but i need to run that query from Access (with parameter
> wwk). Is there no parameter i can use to change the cursor in order
> to get the recordcount (and also to get rs.fields("maandag").value)?
>
>
> "McKirahan" wrote in message
> news:C7Ijd.61194$HA.25228@attbi_s01...
>> "Mich" wrote in message
>> news:O6iHv9WxEHA.2564@TK2MSFTNGP12.phx.gbl...
>>> Hi,
>>>
>>> I defined a query (name = myquery) in Access like this:
>>> SELECT
>>>
>>
>
IIf([wwk]='Maandag',[maandag],IIf([wwk]='Dinsdag',[dinsdag], IIf([wwk]='Woens
>>> dag',[woensdag],IIf([wwk]='Donderdag',[donderdag],[vrijdag]) )))
>>> FROM daguur;
>>> When executing in Access and filling e.g. 'maandag' as parameter,
>>> it works (10 records). No problem.
>>>
>>> In ASP:
>>> set OBJDC = Server.CreateObject("ADODB.Connection")
>>> objdc.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source
>>> =c:\mydb.mdb") set rs=Server.createObject("ADODB.recordset")
>>> objdc.myquery wwk,rs
>>> rec=rs.recordcount
>>>
>>> My problem is: no error, but rec = -1.
>>> Any idea why i don't get the 10 records?
>>> Thanks
>>> Mich
>>
>> http://www.aspfaq.com/show.asp?id=2193

--
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.

Re: recordcount = -1 ??

thanks again

"Bob Barrows [MVP]" wrote in message
news:%23bfJD2ZxEHA.3668@tk2msftngp13.phx.gbl...
> It's explained in the article. You don't need to use Recordcount, but if
you
> insist on using it for some reason, you need to use either a client-side
> cursor, or a server-side non-forward-only cursor. I would go with a static
> server-side cursor (although a keyset or dynamic cursor would also work).
>
> set rs=Server.createObject("ADODB.recordset")
> rs.CursorType=3 '3=adOpenStatic
> objdc.myquery wwk,rs
> rec=rs.recordcount
>
> HOWEVER, this is inefficient. You should take the advice given in the
> article and use a GetRows array to get your record count. Like this:
>
> dim arData
> set rs=Server.createObject("ADODB.recordset")
> objdc.myquery wwk,rs
> if not rs.eof then arData = rs.GetRows
> rs.close: set rs = nothing
> objdc.close: set objdc=nothing
> if isArray(arData) then
> rec = Ubound(arData,2) + 1
> else
> rec = 0
> end if
>
> Looping through an array is much more efficient than looping through a
> recordset. See:
> http://www.aspfaq.com/show.asp?id=2467
>
> HTH,
> Bob Barrows
>
> Mich wrote:
> > Thanks, but i need to run that query from Access (with parameter
> > wwk). Is there no parameter i can use to change the cursor in order
> > to get the recordcount (and also to get rs.fields("maandag").value)?
> >
> >
> > "McKirahan" wrote in message
> > news:C7Ijd.61194$HA.25228@attbi_s01...
> >> "Mich" wrote in message
> >> news:O6iHv9WxEHA.2564@TK2MSFTNGP12.phx.gbl...
> >>> Hi,
> >>>
> >>> I defined a query (name = myquery) in Access like this:
> >>> SELECT
> >>>
> >>
> >
>
IIf([wwk]='Maandag',[maandag],IIf([wwk]='Dinsdag',[dinsdag], IIf([wwk]='Woens
> >>> dag',[woensdag],IIf([wwk]='Donderdag',[donderdag],[vrijdag]) )))
> >>> FROM daguur;
> >>> When executing in Access and filling e.g. 'maandag' as parameter,
> >>> it works (10 records). No problem.
> >>>
> >>> In ASP:
> >>> set OBJDC = Server.CreateObject("ADODB.Connection")
> >>> objdc.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source
> >>> =c:\mydb.mdb") set rs=Server.createObject("ADODB.recordset")
> >>> objdc.myquery wwk,rs
> >>> rec=rs.recordcount
> >>>
> >>> My problem is: no error, but rec = -1.
> >>> Any idea why i don't get the 10 records?
> >>> Thanks
> >>> Mich
> >>
> >> http://www.aspfaq.com/show.asp?id=2193
>
> --
> 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.
>
>