counting records in a recordset

hello,

I have a query that is supposed to return only 1 record, however I recently
found that because of an error in the database it can return more than 1.
So what I need to do is capture if it is more than 1 record and alert the
user to contact the database administrator. How can I get the count of
records returned by a recordset? Is there no way besides looping through
with a counter variable?

re: counting records in a recordset

mgm wrote:[color=blue]
> hello,
>
> I have a query that is supposed to return only 1 record, however I
> recently found that because of an error in the database it can return
> more than 1. So what I need to do is capture if it is more than 1
> record and alert the user to contact the database administrator. How
> can I get the count of records returned by a recordset? Is there no
> way besides looping through with a counter variable?[/color]

A better plan is to set up a unique index or primary key so that the
duplicates cannot occur. I cannot get into specifics because you did not
specify your database type and version, as well as the columns in your table
that are supposed to identify a unique record.

In the meantime, a grouping query will reveal the duplicate records:

select col1,col2
from tablename
group by col1,col2
having count(*) > 1

Bob Barrows

--
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: counting records in a recordset

You could use methods decribed here:
http://www.aspfaq.com/show.asp?id=2193.

Or you could use .GetRows on your recordset and take the UBound of dimension
2 and add 1 to it.

Or you could fix the "error" in the database!

Ray at work

"mgm" <martorellg@hotmail.com> wrote in message
news:OFFZIPGuEHA.3368@TK2MSFTNGP10.phx.gbl...[color=blue]
> hello,
>
> I have a query that is supposed to return only 1 record, however I
> recently
> found that because of an error in the database it can return more than 1.
> So what I need to do is capture if it is more than 1 record and alert the
> user to contact the database administrator. How can I get the count of
> records returned by a recordset? Is there no way besides looping through
> with a counter variable?
>
>
>[/color]

re: counting records in a recordset

Well, the error in the database was someone's input error so I need to catch
the error because it could happen again, can't fix it (plus it's not my
database).

But thanks for the info, i was wondering why recordcount didn't work :)

mgm

"Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in
message news:O5hkVXGuEHA.2536@TK2MSFTNGP11.phx.gbl...[color=blue]
> You could use methods decribed here:
> http://www.aspfaq.com/show.asp?id=2193.
>
> Or you could use .GetRows on your recordset and take the UBound of[/color]
dimension[color=blue]
> 2 and add 1 to it.
>
> Or you could fix the "error" in the database!
>
> Ray at work
>
> "mgm" <martorellg@hotmail.com> wrote in message
> news:OFFZIPGuEHA.3368@TK2MSFTNGP10.phx.gbl...[color=green]
> > hello,
> >
> > I have a query that is supposed to return only 1 record, however I
> > recently
> > found that because of an error in the database it can return more than[/color][/color]
1.[color=blue][color=green]
> > So what I need to do is capture if it is more than 1 record and alert[/color][/color]
the[color=blue][color=green]
> > user to contact the database administrator. How can I get the count of
> > records returned by a recordset? Is there no way besides looping[/color][/color]
through[color=blue][color=green]
> > with a counter variable?
> >
> >
> >[/color]
>
>[/color]

re: counting records in a recordset

Sorry I didn't explain thoroughly before. The SQL database is not returning
"duplicate" rows, just 2 rows. My query is searching for a serial number
that is within a specific range - this serial number range should only exist
once in the database, but somehow, because of someone else's input error I
have overlapping ranges which should never happen. This is an adhoc
database that is fed by other databases, used by many many people for our
own reporting purposes.

I was just thinking recordcount should work somehow, but I guess it doesn't
in my case.

Thanks Bob!

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uuGfiWGuEHA.2072@tk2msftngp13.phx.gbl...[color=blue]
>
> A better plan is to set up a unique index or primary key so that the
> duplicates cannot occur. I cannot get into specifics because you did not
> specify your database type and version, as well as the columns in your[/color]
table[color=blue]
> that are supposed to identify a unique record.
>
> In the meantime, a grouping query will reveal the duplicate records:
>
> select col1,col2
> from tablename
> group by col1,col2
> having count(*) > 1
>
> Bob Barrows
>
> --
> 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.[/color]

re: counting records in a recordset

Recordcount will work if you specify the proper type of cursor (the default
cursortype will not produce a bookmarkable recordset). If you specify a
client-side cursor, recordcount will work (do you have the ADO documentation
handy? If not, go to msdn.microsoft.com/library and drill down into the Data
Access node in the TOC)

I am positive that there is a way to build a query that will give you your
overlapping ranges. Since this is a SQL database (MS SQL Server, I assume -
what version?), I am sure it is possible to use either constraints or a
trigger to prevent the invalid data. It is probably possible to prevent the
invalid entry in your stored procedure, or if you aren't using a stored
procedure, in your vbscript code. You should not resort to checking the data
after it is entered using an expensive client-side cursor.

Bob Barrows

mgm wrote:[color=blue]
> Sorry I didn't explain thoroughly before. The SQL database is not
> returning "duplicate" rows, just 2 rows. My query is searching for a
> serial number that is within a specific range - this serial number
> range should only exist once in the database, but somehow, because of
> someone else's input error I have overlapping ranges which should
> never happen. This is an adhoc database that is fed by other
> databases, used by many many people for our own reporting purposes.
>
> I was just thinking recordcount should work somehow, but I guess it
> doesn't in my case.
>
> Thanks Bob!
>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:uuGfiWGuEHA.2072@tk2msftngp13.phx.gbl...[color=green]
>>
>> A better plan is to set up a unique index or primary key so that the
>> duplicates cannot occur. I cannot get into specifics because you did
>> not specify your database type and version, as well as the columns
>> in your table that are supposed to identify a unique record.
>>
>> In the meantime, a grouping query will reveal the duplicate records:
>>
>> select col1,col2
>> from tablename
>> group by col1,col2
>> having count(*) > 1
>>
>> Bob Barrows
>>
>> --
>> 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.[/color][/color]

--
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: counting records in a recordset

Well this is how it works: the data is entered into a database somewhere, I
have no access to it, that data is then replicated to another database that
I have limited access to but can't change anything there, that is then
replicated to another database by my boss, it is that data that I have to
work with. Right now I simply want to capture when there is this overlap so
I can let my boss know. I'll just display the information and contact
information to the user. I have figured how I can do that now, I will just
use getrows() method. I will mention it to my boss that this is happening
so that maybe the error can be caught by his replication -- he's away on
vacation now.

I do not know how to find the version of the database that I have limited
access to, but the version we are using is SQL Server Enterprise Edition
8.00.760(sp3).

I will have to read up on cursors however, I've read before but didn't quite
understand, thanks.

I was wondering if you may have an answer to another question I posted in
inetexplorer.scripting (no one is answering it there, maybe wrong
newsgroup?) This is the text from that posting:
---
I have a intranet web app. I built at work. On one person's machine, when
they hit the back button after filling a form and submitting, they get the
warning 'page has expired...hit refresh, etc.' and then the refresh
requeries the database instead of just displaying the data in the form as it
was.

This doesn't happen on my machine. I searched knowledgebase and the other
person does not have "Do not
save encrypted pages to disk" checked and has IE
6.0.2900.2180.xpsp_sp2_rtm.040803-2158. I have 6.0.3790.0 update version
3283 (i'm running server 2003).

Does anyone know how to fix the problem on the other person's machine? I
want anyone who uses it to be able to enter data, submit it, go back and
make a few changes and submit it again as a new record, so they don't have
to refill a bunch of data that should still be there.


Thanks!

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eP0U9vHuEHA.820@TK2MSFTNGP12.phx.gbl...[color=blue]
> Recordcount will work if you specify the proper type of cursor (the[/color]
default[color=blue]
> cursortype will not produce a bookmarkable recordset). If you specify a
> client-side cursor, recordcount will work (do you have the ADO[/color]
documentation[color=blue]
> handy? If not, go to msdn.microsoft.com/library and drill down into the[/color]
Data[color=blue]
> Access node in the TOC)
>
> I am positive that there is a way to build a query that will give you your
> overlapping ranges. Since this is a SQL database (MS SQL Server, I[/color]
assume -[color=blue]
> what version?), I am sure it is possible to use either constraints or a
> trigger to prevent the invalid data. It is probably possible to prevent[/color]
the[color=blue]
> invalid entry in your stored procedure, or if you aren't using a stored
> procedure, in your vbscript code. You should not resort to checking the[/color]
data[color=blue]
> after it is entered using an expensive client-side cursor.
>
> Bob Barrows
>
> mgm wrote:[color=green]
> > Sorry I didn't explain thoroughly before. The SQL database is not
> > returning "duplicate" rows, just 2 rows. My query is searching for a
> > serial number that is within a specific range - this serial number
> > range should only exist once in the database, but somehow, because of
> > someone else's input error I have overlapping ranges which should
> > never happen. This is an adhoc database that is fed by other
> > databases, used by many many people for our own reporting purposes.
> >
> > I was just thinking recordcount should work somehow, but I guess it
> > doesn't in my case.
> >
> > Thanks Bob!
> >
> > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:uuGfiWGuEHA.2072@tk2msftngp13.phx.gbl...[color=darkred]
> >>
> >> A better plan is to set up a unique index or primary key so that the
> >> duplicates cannot occur. I cannot get into specifics because you did
> >> not specify your database type and version, as well as the columns
> >> in your table that are supposed to identify a unique record.
> >>
> >> In the meantime, a grouping query will reveal the duplicate records:
> >>
> >> select col1,col2
> >> from tablename
> >> group by col1,col2
> >> having count(*) > 1
> >>
> >> Bob Barrows
> >>
> >> --
> >> 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.[/color][/color]
>
> --
> 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.
>
>[/color]

re: counting records in a recordset

mgm wrote:[color=blue]
> Well this is how it works: the data is entered into a database
> somewhere, I have no access to it, that data is then replicated to
> another database that I have limited access to but can't change
> anything there, that is then replicated to another database by my
> boss, it is that data that I have to work with. Right now I simply
> want to capture when there is this overlap so I can let my boss know.
> I'll just display the information and contact information to the
> user. I have figured how I can do that now, I will just use
> getrows() method. I will mention it to my boss that this is
> happening so that maybe the error can be caught by his replication
> -- he's away on vacation now.
>
> I do not know how to find the version of the database[/color]

I sometimes use "database" as shorthand for "rdbms" or "database server"
[color=blue]
> that I have
> limited access to, but the version we are using is SQL Server
> Enterprise Edition
> 8.00.760(sp3).
>
> I will have to read up on cursors however, I've read before but
> didn't quite understand, thanks.[/color]

Don't misunderstand: I was not talking about T-SQL cursors. I was referring
to the cursorlocation and cursortype properties of ADO cursors. GetRows is a
good method for both determining the record count, and displaying your data.
See:
http://www.aspfaq.com/show.asp?id=2467

[color=blue]
>
> I was wondering if you may have an answer to another question I
> posted in inetexplorer.scripting (no one is answering it there, maybe
> wrong newsgroup?) This is the text from that posting:
> ---
> I have a intranet web app. I built at work. On one person's machine,
> when they hit the back button after filling a form and submitting,
> they get the warning 'page has expired...hit refresh, etc.' and then
> the refresh requeries the database instead of just displaying the
> data in the form as it was.
>[/color]

AFAIK, there is no way to prevent this security message from occurring.

Bob Barrows
--
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"

re: counting records in a recordset

Try adding TOP 1 to your query, e.g.

select TOP 1 field1 from table1



--
Ben Strackany
www.developmentnow.com


"mgm" <martorellg@hotmail.com> wrote in message
news:OFFZIPGuEHA.3368@TK2MSFTNGP10.phx.gbl...[color=blue]
> hello,
>
> I have a query that is supposed to return only 1 record, however I[/color]
recently[color=blue]
> found that because of an error in the database it can return more than 1.
> So what I need to do is capture if it is more than 1 record and alert the
> user to contact the database administrator. How can I get the count of
> records returned by a recordset? Is there no way besides looping through
> with a counter variable?
>
>
>[/color]