473,544 Members | 1,781 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

counting records in a recordset

mgm
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?

Jul 21 '05 #1
8 1774
mgm wrote:
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?


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.
Jul 21 '05 #2
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" <ma********@hot mail.com> wrote in message
news:OF******** ******@TK2MSFTN GP10.phx.gbl...
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?

Jul 21 '05 #3
mgm
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:O5******** ******@TK2MSFTN GP11.phx.gbl...
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" <ma********@hot mail.com> wrote in message
news:OF******** ******@TK2MSFTN GP10.phx.gbl...
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?


Jul 21 '05 #4
mgm
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]" <re******@NOyah oo.SPAMcom> wrote in message
news:uu******** ******@tk2msftn gp13.phx.gbl...

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.

Jul 21 '05 #5
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:
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]" <re******@NOyah oo.SPAMcom> wrote in message
news:uu******** ******@tk2msftn gp13.phx.gbl...

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.


--
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.
Jul 21 '05 #6
mgm
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.sc ripting (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.x psp_sp2_rtm.040 803-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]" <re******@NOyah oo.SPAMcom> wrote in message
news:eP******** *****@TK2MSFTNG P12.phx.gbl...
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:
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]" <re******@NOyah oo.SPAMcom> wrote in message
news:uu******** ******@tk2msftn gp13.phx.gbl...

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.


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

Jul 21 '05 #7
mgm wrote:
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
I sometimes use "database" as shorthand for "rdbms" or "database server"
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.
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


I was wondering if you may have an answer to another question I
posted in inetexplorer.sc ripting (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.


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"
Jul 21 '05 #8
Try adding TOP 1 to your query, e.g.

select TOP 1 field1 from table1

--
Ben Strackany
www.developmentnow.com
"mgm" <ma********@hot mail.com> wrote in message
news:OF******** ******@TK2MSFTN GP10.phx.gbl...
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?

Jul 21 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
3207
by: shank | last post by:
I have a recordset that contains multiple records of product a user is purchasing. For clarity, I converted the recordset fields to variables. I need to take that entire recordset and insert it into another table on a remote server. The below code only inserts 1 record. How do I change the code to get all records inserted? thanks! <% Dim...
1
1920
by: Samuel Hon | last post by:
I seem to remember reading many moons ago about a function where you can retrieve a count of the last recordset you opened. For example: I've got a stored procedure that returns a recordset using the TOP 10 so I only get the top 10 records. I need to know the recordcount but I dont want to reuse the SELECT statement because its quite...
13
3444
by: Jan | last post by:
Hi I have a database that I use to keep track of the sales promotions that we send to companies. I normally send a mailing based on a subset of the companies in the database (found using the query: QryICTMassDistribution3) , I then use a form and the code below to create a new record in the corrispondence table to show what...
2
4330
by: mattytee123 | last post by:
I have about 20 tables, of which I would like to do a union query and count of how many of each different code there is? The simplified verson of the table is structured like this. Code Count 1234 1 2468 1 1234 1 2468 1
13
3147
by: Regnab | last post by:
The problem I'm having is the rsa.RecordCount always returns a '1' when I know there should be about 1600 records returned. I read somewhere that one should use '.MoveLast' before recordcount but that returns ar error 'Invalid Operation'. And when I do the bottom loop, the counter happily clicks over the right number of times. Me no...
2
6321
by: allyn44 | last post by:
Hello, I have built a serch form for users to edit records. I only want them to pull up the record they need, and I want to check for nulls. There should not be dupes becasue the underlying tables are indexed. So the user will pull up either one record or none. Here is my problem: I pass the form name through a tmpvariable and ther other...
10
6710
by: Robert | last post by:
How do you get an accurate count of the number of records returned from a query when using linked tables. I have an access 2003 database as a front end to another access 2003 database that contains only tables. I have linked the tables for the front end to the back end database. I am trying to set the recordsource of a form to a query...
6
11782
by: MLH | last post by:
If you're only furnished the table name of a table in your database, what can I do in VBA to count tablle records in a given table? Seeking simplest method. In other words, I don't have the name of a field to furnish to DLookUp - all I have is a table name.
3
1874
by: JacobZ | last post by:
Hey all, I've been struggling with this problem for a few days now. The table looks like this T_PROJECT_FILES fields: projectId, unitId Now, I want to count the number of records with different values in unitId, but with a specific value (e.g. 1) in projectId. The value of 'projectId' is coming from another form using e.g. ...
0
7416
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7360
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7600
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
5899
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5289
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3403
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3400
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1833
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
983
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.