|
 |
asp_databases thread: Record Set Boject reutrns duplicate records
Message #1 by "Dan" <none> on Mon, 26 Nov 2001 23:04:11 -0400
|
|
Hi,
Does anaybody knows why does my recordset returns duplicate records? On a
table wich I know has only 7 records, when I execute the connection string
using SQL, I get 14 records in the set.
I iterate thrue the .fields collection to print all of them. I do it until
the record set get EOF = true.
what am I doing wrong?
Message #2 by "Dallas Martin" <dmartin@z...> on Mon, 26 Nov 2001 22:28:29 -0500
|
|
No, I 've lost my crystal ball, but if you'll post the SELECT statement may
I can divine the answer.
----- Original Message -----
From: "Dan" <none>
Newsgroups: asp_databases
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, November 26, 2001 10:04 PM
Subject: [asp_databases] Record Set Boject reutrns duplicate records
> Hi,
>
> Does anaybody knows why does my recordset returns duplicate records? On a
> table wich I know has only 7 records, when I execute the connection string
> using SQL, I get 14 records in the set.
>
> I iterate thrue the .fields collection to print all of them. I do it until
> the record set get EOF = true.
>
> what am I doing wrong?
>
>
>
>
$subst('Email.Unsub')
>
Message #3 by "Dan" <none> on Mon, 26 Nov 2001 23:27:23 -0400
|
|
Here it is....Make sure you polish that ball!!!!
"SELECT tblSerialNumbers.* FROM tblSerialNumbers INNER JOIN (tblRelation
INNER JOIN tblUserInfo ON tblUserInfo.Company = tblRelation.Company) ON
tblSerialNumbers.CompanyCode = tblRelation.CompanyCode WHERE
tblUserInfo.Company = '" & Session("Compny") & "'" & " ORDER BY
tblSerialNumbers." & vSort
sql = "SELECT tblSerialNumbers.* FROM (tblRelation INNER JOIN tblUserInfo
ON tblRelation.Company = tblUserInfo.Company) INNER JOIN tblSerialNumbers ON
tblRelation.CompanyCode = tblSerialNumbers.CompanyCode"
sql = sql & " WHERE tblUserInfo.Company = '" & Session("Compny") & "'" & "
ORDER BY tblSerialNumbers." & vSort
strConn = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("Data/protArea_pwords.mdb")
"Dallas Martin" <dmartin@z...> wrote in message
news:123459@a..._databases...
>
> No, I 've lost my crystal ball, but if you'll post the SELECT statement
may
> I can divine the answer.
>
> ----- Original Message -----
> From: "Dan" <none>
> Newsgroups: asp_databases
> To: "ASP Databases" <asp_databases@p...>
> Sent: Monday, November 26, 2001 10:04 PM
> Subject: [asp_databases] Record Set Boject reutrns duplicate records
>
>
> > Hi,
> >
> > Does anaybody knows why does my recordset returns duplicate records? On
a
> > table wich I know has only 7 records, when I execute the connection
string
> > using SQL, I get 14 records in the set.
> >
> > I iterate thrue the .fields collection to print all of them. I do it
until
> > the record set get EOF = true.
> >
> > what am I doing wrong?
> >
> >
> >
> >
> $subst('Email.Unsub')
> >
>
>
>
Message #4 by "Dallas Martin" <dmartin@z...> on Mon, 26 Nov 2001 23:08:37 -0500
|
|
Dan,
Open SQL Query Design View in Access and insert this statement in the design
pane
and run it.
Replace the SOME_COMPANY with a known value for a company.
SELECT *
FROM tblSerialNumbers
INNER JOIN tblRelation ON tblSerialNumbers.CompanyCode
tblRelation.CompanyCode
INNER JOIN tblUserInfo ON tblRelation.Company = tblUserInfo.Company
WHERE tblUserInfo.Company = 'SOME_COMPANY'
If it works, then copy the code from the SQL Designer to your ASP page and
replace
the tblUserInfo.Company = tblUserInfo.Company = 'SOME_COMPANY'
with tblUserInfo.Company = '" & Session("Compny") & "'"
Now if you are still getting duplicate records, then check the table
tblUserInfo.Company for duplicate entries
of the Company name.
"SELECT tblUserInfo.Company, count(*) FROM tblUserInfo GROUP BY
tblUserInfo.Company HAVING COUNT(*) > 1"
If the same company name occurs more than once in tblUserInfo, then for
every such record,
you will get a return row.
I suggest adding the DISTINCT modifier to the SELECT statement.
Don't use SELECT *, instead SELECT just the columns you need.
SELECT DISTINCT col1,col2,col3,col4
FROM tblSerialNumbers
INNER JOIN tblRelation ON tblSerialNumbers.CompanyCode
tblRelation.CompanyCode
INNER JOIN tblUserInfo ON tblRelation.Company = tblUserInfo.Company
WHERE tblUserInfo.Company = 'SOME_COMPANY'
Dallas
----- Original Message -----
From: "Dan" <none>
Newsgroups: asp_databases
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, November 26, 2001 10:27 PM
Subject: [asp_databases] Re: Record Set Boject reutrns duplicate records
> Here it is....Make sure you polish that ball!!!!
>
> "SELECT tblSerialNumbers.* FROM tblSerialNumbers INNER JOIN (tblRelation
> INNER JOIN tblUserInfo ON tblUserInfo.Company = tblRelation.Company) ON
> tblSerialNumbers.CompanyCode = tblRelation.CompanyCode WHERE
> tblUserInfo.Company = '" & Session("Compny") & "'" & " ORDER BY
> tblSerialNumbers." & vSort
>
> sql = "SELECT tblSerialNumbers.* FROM (tblRelation INNER JOIN tblUserInfo
> ON tblRelation.Company = tblUserInfo.Company) INNER JOIN tblSerialNumbers
ON
> tblRelation.CompanyCode = tblSerialNumbers.CompanyCode"
> sql = sql & " WHERE tblUserInfo.Company = '" & Session("Compny") & "'" &
"
> ORDER BY tblSerialNumbers." & vSort
>
>
> strConn = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("Data/protArea_pwords.mdb")
>
> "Dallas Martin" <dmartin@z...> wrote in message
> news:123459@a..._databases...
> >
> > No, I 've lost my crystal ball, but if you'll post the SELECT statement
> may
> > I can divine the answer.
> >
> > ----- Original Message -----
> > From: "Dan" <none>
> > Newsgroups: asp_databases
> > To: "ASP Databases" <asp_databases@p...>
> > Sent: Monday, November 26, 2001 10:04 PM
> > Subject: [asp_databases] Record Set Boject reutrns duplicate records
> >
> >
> > > Hi,
> > >
> > > Does anaybody knows why does my recordset returns duplicate records?
On
> a
> > > table wich I know has only 7 records, when I execute the connection
> string
> > > using SQL, I get 14 records in the set.
> > >
> > > I iterate thrue the .fields collection to print all of them. I do it
> until
> > > the record set get EOF = true.
> > >
> > > what am I doing wrong?
> > >
> > >
> > >
> > >
> > $subst('Email.Unsub')
> > >
> >
> >
> >
>
>
>
>
$subst('Email.Unsub')
>
Message #5 by "Dan" <none> on Tue, 27 Nov 2001 09:04:06 -0400
|
|
Dallas,
It didnt work, but I continued to work with the access SQL view and figured
it out. Thanks for the tip of using this tool to do my querys.
The problem was that I was including the same field twice, so it doubled the
records.
Dan
"Dallas Martin" <dmartin@z...> wrote in message
news:123463@a..._databases...
>
> Dan,
>
> Open SQL Query Design View in Access and insert this statement in the
design
> pane
> and run it.
>
> Replace the SOME_COMPANY with a known value for a company.
>
> SELECT *
> FROM tblSerialNumbers
> INNER JOIN tblRelation ON tblSerialNumbers.CompanyCode
> tblRelation.CompanyCode
> INNER JOIN tblUserInfo ON tblRelation.Company = tblUserInfo.Company
> WHERE tblUserInfo.Company = 'SOME_COMPANY'
>
> If it works, then copy the code from the SQL Designer to your ASP page and
> replace
> the tblUserInfo.Company = tblUserInfo.Company = 'SOME_COMPANY'
> with tblUserInfo.Company = '" & Session("Compny") & "'"
>
> Now if you are still getting duplicate records, then check the table
> tblUserInfo.Company for duplicate entries
> of the Company name.
>
> "SELECT tblUserInfo.Company, count(*) FROM tblUserInfo GROUP BY
> tblUserInfo.Company HAVING COUNT(*) > 1"
>
> If the same company name occurs more than once in tblUserInfo, then for
> every such record,
> you will get a return row.
>
> I suggest adding the DISTINCT modifier to the SELECT statement.
>
> Don't use SELECT *, instead SELECT just the columns you need.
>
> SELECT DISTINCT col1,col2,col3,col4
> FROM tblSerialNumbers
> INNER JOIN tblRelation ON tblSerialNumbers.CompanyCode
> tblRelation.CompanyCode
> INNER JOIN tblUserInfo ON tblRelation.Company = tblUserInfo.Company
> WHERE tblUserInfo.Company = 'SOME_COMPANY'
>
> Dallas
>
>
>
> ----- Original Message -----
> From: "Dan" <none>
> Newsgroups: asp_databases
> To: "ASP Databases" <asp_databases@p...>
> Sent: Monday, November 26, 2001 10:27 PM
> Subject: [asp_databases] Re: Record Set Boject reutrns duplicate records
>
>
> > Here it is....Make sure you polish that ball!!!!
> >
> > "SELECT tblSerialNumbers.* FROM tblSerialNumbers INNER JOIN (tblRelation
> > INNER JOIN tblUserInfo ON tblUserInfo.Company = tblRelation.Company) ON
> > tblSerialNumbers.CompanyCode = tblRelation.CompanyCode WHERE
> > tblUserInfo.Company = '" & Session("Compny") & "'" & " ORDER BY
> > tblSerialNumbers." & vSort
> >
> > sql = "SELECT tblSerialNumbers.* FROM (tblRelation INNER JOIN
tblUserInfo
> > ON tblRelation.Company = tblUserInfo.Company) INNER JOIN
tblSerialNumbers
> ON
> > tblRelation.CompanyCode = tblSerialNumbers.CompanyCode"
> > sql = sql & " WHERE tblUserInfo.Company = '" & Session("Compny") & "'"
&
> "
> > ORDER BY tblSerialNumbers." & vSort
> >
> >
> > strConn = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" &
> > Server.MapPath("Data/protArea_pwords.mdb")
> >
> > "Dallas Martin" <dmartin@z...> wrote in message
> > news:123459@a..._databases...
> > >
> > > No, I 've lost my crystal ball, but if you'll post the SELECT
statement
> > may
> > > I can divine the answer.
> > >
> > > ----- Original Message -----
> > > From: "Dan" <none>
> > > Newsgroups: asp_databases
> > > To: "ASP Databases" <asp_databases@p...>
> > > Sent: Monday, November 26, 2001 10:04 PM
> > > Subject: [asp_databases] Record Set Boject reutrns duplicate records
> > >
> > >
> > > > Hi,
> > > >
> > > > Does anaybody knows why does my recordset returns duplicate records?
> On
> > a
> > > > table wich I know has only 7 records, when I execute the connection
> > string
> > > > using SQL, I get 14 records in the set.
> > > >
> > > > I iterate thrue the .fields collection to print all of them. I do it
> > until
> > > > the record set get EOF = true.
> > > >
> > > > what am I doing wrong?
> > > >
> > > >
> > > >
> > > >
> > > $subst('Email.Unsub')
> > > >
> > >
> > >
> > >
> >
> >
> >
> >
> $subst('Email.Unsub')
> >
>
>
>
Message #6 by "nitai" <nitai@s...> on Wed, 28 Nov 2001 10:38:27 +0530
|
|
hi,
Can u send u sql statement ... may be there is some problem
BR
nitai
----- Original Message -----
From: "Dan" <none@s...>
Newsgroups: asp_databases
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, November 27, 2001 8:34 AM
Subject: [asp_databases] Record Set Boject reutrns duplicate records
> Hi,
>
> Does anaybody knows why does my recordset returns duplicate records? On a
> table wich I know has only 7 records, when I execute the connection string
> using SQL, I get 14 records in the set.
>
> I iterate thrue the .fields collection to print all of them. I do it until
> the record set get EOF = true.
>
> what am I doing wrong?
>
>
>
>
$subst('Email.Unsub')
|
|
|
View Cart
