recordset displaying 'double entry' issue.

[Miranda johnsen]

Hi,
I've run into a wall here. I am trying to display my clients transaction
history.
This is what I have displaying so far, notice the double entries, this
is my problem. Any help would be greatly appeciated in helping me
eliminate this 'double entry' issue.

Thanks in advance,

Miranda Johnsen

++++ FROM PAGE 2 +++
|========================================|
| Certificate ID:*106 |
| Transaction Date ||Transaction Amount ||Transaction Type |
|============================================= |
| 1/31/2004 || $276.24 || DIVC |
| 1/31/2004 || $276.24 || DIVC |
| 12/31/2003 || $279.52 || DIVC |
| 12/31/2003 || $279.52 || DIVC |
| 11/30/2003 || $291.11 || DIVC |
| 11/30/2003 || $291.11 || DIVC |
| 10/31/2003 || $287.18 || DIVC |
| 10/31/2003 || $287.18 || DIVC |
===============================================

=============
PAGE 2 - CODE - with no formating, PRODUCED TABLE ABOVE
=============
<%

var conn;
var rs;
var sSQL;

conn = Server.CreateObject("ADODB.connection");
rs=Server.CreateObject("ADODB.Recordset");
conn.Open("data");

// certID is a numeric value
var CertID;
CertID = Request.QueryString("CertificateID");

var memid;
memid = Request.QueryString("MemberID");

sSQL = "SELECT * ";
sSQL += " FROM Investors, MIC1ShareTransactions, MIC1Shares";
sSQL += " WHERE Investors.MemberID = '" + memid+ "'";
sSQL += " AND MIC1Shares.CertificateId = " + CertID + " ";
sSQL += " AND MIC1ShareTransactions.CertificateId = " + CertID + " ";
sSQL += " ORDER BY MIC1ShareTransactions.CertificateId,
MIC1ShareTransactions.TransactionDate DESC;";

rs=Server.CreateObject("ADODB.Recordset");
rs.Open(sSQL,conn);

//Response.write(sSQL);
//Response.write(CertID);


if (!rs.eof) {

Response.write ("Certificate ID:&nbsp;");
Response.write (CertID);
Response.write ("Transaction Date");
Response.write ("Transaction Amount");
Response.write ("Transaction Type");

if (!rs.eof) {
rs.movefirst();
while (!rs.eof){

Response.write (rs("TransactionDate"));
Response.write (rs("TransactionAmt"));
Response.write (rs("TransactionCode"));

rs.MoveNext();
}
}

}

rs.Close();
rs=null;
conn.Close();
conn=null;
%>

=============
PAGE 1 - CODE AND TABLE - with no formating, PRODUCED TABLE BELOW
=============
| Certificate ID || Transaction History |
=========================================
| 106 || View Transactions
| 47 || View Transactions
| 18 || View Transactions
===========================================


<%
// MIC I Mortgages
var conn;
var rs;
var sSQL;

conn = Server.CreateObject("ADODB.connection");
rs=Server.CreateObject("ADODB.Recordset");
conn.Open("data");
var memid;
memid = Request.QueryString("MemberID");

sSQL = "SELECT *";
sSQL += " FROM MIC1Shares, Investors, InvestorAdmin";
sSQL += " WHERE InvestorAdmin.MemberID = '" + memid+"'";
sSQL += " AND Investors.MemberID = '" + memid+"'";
sSQL += " AND Investors.InvestorID=MIC1Shares.InvestorId";
sSQL += " ORDER BY MIC1Shares.DateIssued DESC;";

rs=Server.CreateObject("ADODB.Recordset");
rs.Open(sSQL,conn);

var CertID;
CertID = rs("CertificateID");

if (!rs.eof) {
rs.movefirst();
while (!rs.eof){

Response.write (CertID);
Response.write("<a href='TEST.asp?CertificateID=" + CertID +
"&MemberID=" + memid +" '>");
Response.write ("View Transactions");
Response.write ("</a>");
rs.MoveNext();

}
}
%>



===========================
Look and you will find it -- what is unsought will go undetected.
SOPHOCLES


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bullschmidt]

To avoid reshowing duplicate entries on successive records you could do
SELECT DISTINCT in your SQL statement or in your loop where you write
out the data you might handle things something like this:

If varPrevMyID <> objRS("MyID") Then
[Write out the record]
End If

varPrevMyID = objRS("MyID")

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
[url]http://www.Bullschmidt.com[/url]
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

Miranda johnsen wrote:

> Hi,
> I've run into a wall here. I am trying to display my clients
> transaction history.
> This is what I have displaying so far, notice the double entries, this
> is my problem. Any help would be greatly appeciated in helping me
> eliminate this 'double entry' issue.
>
> Thanks in advance,
>
> Miranda Johnsen
>
> ++++ FROM PAGE 2 +++

>> ========================================|
>> Certificate ID: 106 |
>> Transaction Date ||Transaction Amount ||Transaction Type |
>> ============================================= |
>> 1/31/2004 || $276.24 || DIVC |
>> 1/31/2004 || $276.24 || DIVC |
>> 12/31/2003 || $279.52 || DIVC |
>> 12/31/2003 || $279.52 || DIVC |
>> 11/30/2003 || $291.11 || DIVC |
>> 11/30/2003 || $291.11 || DIVC |
>> 10/31/2003 || $287.18 || DIVC |
>> 10/31/2003 || $287.18 || DIVC |
>> ===============================================

>

First of all, I do not advocate Bullschmidt's advice to use DISTINCT. There
are two problems with using DISTINCT: performance, and the masking of
problems in your database. DISTINCT should never be used without
understanding why it needs to be used. It should be a last resort, not a
first.

To start: always tell us what kind and version of database you are using. It
is always relevant.

After that, we need to determine whether or not there are supposed to be
duplicate records in your database. You never told us whether or not you
expected to see these duplicates. So the question is: are the duplicates
supposed to be in the data, and you are simply looking for a way to not show
them to the user?

If your answer is that the tables do not contain duplicates, but your query
is generating them, then you need to further describe your tables,
especially telling us the relationships between them (what fields are used
to relate the records?).

I see there are three tables involved: Investors, MIC1ShareTransactions,
MIC1Shares. Part of the problem is that you are not including the
information required to join these tables together in your query. Part of
the reason I can't make a guess is because you've used the bad practice of
using "Select *" ([url]http://www.aspfaq.com/show.asp?id=2096[/url]), so I can't even
see the names of the fields in the tables. I can tell you that you are
pulling more data across the network than you need because of this.

It is not enough to provide the filtering criteria as you have done. You
also need to tell the query engine how to relate the records in each table
to the records in the other tables.

The easiest way to describe your tables to us is to show us the data from
each table that resulted in the above results (show us only the relevant
fields, we don't need to see all 50 fields from each table).

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"

[Miranda johnsen]

Hi Bob,
Thanks for your reply!
I am using a access 2000 database. The double entries are not in the
database. I know I should be using a DSN-less connection, but I do not
have time to convert as this project is overdue (I know that you did not
mention that, but everybody mentions it eventually).
--------------------------------------------
Here is the proper SELECT statement.
--------------------------------------------------
sSQL="SELECT
MIC1ShareTransactions.CertificateId,Investors.Memb erID,Investors.Investo
rId,Investors.InvestorName,MIC1Shares.CertificateI d,MIC1Shares.Certifica
teId,MIC1Shares.InvestorId,MIC1ShareTransactions.C ertificateId,MIC1Share
Transactions.TransactionDate,MIC1ShareTransactions .TransactionCode,MIC1S
hareTransactions.TransactionAmt";

sSQL += " FROM Investors, MIC1ShareTransactions, MIC1Shares";
sSQL += " WHERE Investors.MemberID = '" + memid+"'";
sSQL += " AND Investors.InvestorId = MIC1Shares.InvestorId";
sSQL += " AND MIC1Shares.CertificateId =
MIC1ShareTransactions.CertificateId";
sSQL += " ORDER BY MIC1ShareTransactions.CertificateId,
MIC1ShareTransactions.TransactionDate DESC;";

rs=Server.CreateObject("ADODB.Recordset");
rs.Open(sSQL,conn);
--------------------------------------------------
ALSO, HERE IS THE PERTINENT TABLES:
===========================================
INVESTORS
==========================================
MemberID MICType InvestorId
-------------------------------------
CP00122 MIC1 1
CP01241 MIC1 2
CP00321 MIC1 3

=========
MIC1SHARES
==========
CertificateID | InvestorId
========================
313 | 2
328 | 2
3 | 3
173 | 3
298 | 3

=========
MIC1SHARETRANSACTIONS
==========
CertificateId TransactionDate TransactionCode TransactionAmt
-------------------------------------------
313 31-Dec-03 DIVS $0.00
313 31-Aug-03 DIVS $10.00
313 30-Sep-03 DIVS $9.00
313 31-Jul-03 DIVS $11.00
328 31-Dec-03 DIVS $1.00
328 31-Dec-03 DIVS $0.00
328 31-Dec-03 REDE $161.00
328 31-Jul-03 DIVS $1.00

===========================================
Thanks again!
Miranda Johnsen
[url]www.mirandajohnsen.com[/url]
===========================
Look and you will find it -- what is unsought will go undetected.
SOPHOCLES


Miranda Johnsen
[url]www.mirandajohnsen.com[/url]
===========================
Look and you will find it -- what is unsought will go undetected.
SOPHOCLES


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows [MVP]]

Miranda johnsen wrote:

> Hi Bob,
> Thanks for your reply!
> I am using a access 2000 database. The double entries are not in the
> database. I know I should be using a DSN-less connection, but I do not
> have time to convert as this project is overdue (I know that you did
> not mention that, but everybody mentions it eventually).

OK, I've entered your data into an Access database and modified your query
as follows (you don't need to retrieve all the columns from all the tables -
that's one of the reasons to avoid "select *" - you were retrieving a lot of
duplicate information - I also removed the InvestorID column - that did not
seem to be useful information):

SELECT t.CertificateId, i.MemberID, t.TransactionDate,
t.TransactionCode, t.TransactionAmt
FROM Investors AS i INNER JOIN
(MIC1ShareTransactions AS t INNER JOIN MIC1Shares AS s
ON t.CertificateId = s.CertificateID)
ON i.InvestorId = s.InvestorId
WHERE i.MemberID='CP01241'
ORDER BY t.CertificateId, t.TransactionDate

You should make a practice of developing your queries in the Access query
builder, as I did above. It makes debugging them so much easier. Open your
database in Access and paste the above sql statement into the SQL View of
the Access query builder. When run, these are the results:


CertificateId MemberID TransactionDate TransactionCode TransactionAmt
313 CP01241 7/31/2003 DIVS $11.00
313 CP01241 8/31/2003 DIVS $10.00
313 CP01241 9/30/2003 DIVS $9.00
313 CP01241 12/31/2003 DIVS $0.00
328 CP01241 7/31/2003 DIVS $1.00
328 CP01241 12/31/2003 REDE $161.00
328 CP01241 12/31/2003 DIVS $0.00
328 CP01241 12/31/2003 DIVS $1.00

Now - you need to tell me what results you really want to see. I do not see
any duplicate records here. As a guess, I suspect you really want to
aggregate the results so a single row for each date, certificate and code is
shown, like this:

CertificateId MemberID TransactionDate TransactionCode TransactionAmt
313 CP01241 7/31/2003 DIVS $11.00
313 CP01241 8/31/2003 DIVS $10.00
313 CP01241 9/30/2003 DIVS $9.00
313 CP01241 12/31/2003 DIVS $0.00
328 CP01241 7/31/2003 DIVS $1.00
328 CP01241 12/31/2003 REDE $161.00
328 CP01241 12/31/2003 DIVS $1.00

For that, you need a Group By clause, like this:

SELECT t.CertificateId, i.MemberID, t.TransactionDate, t.TransactionCode,
Sum(t.TransactionAmt) AS TransactionAmt
FROM Investors AS i INNER JOIN
(MIC1ShareTransactions AS t INNER JOIN MIC1Shares AS s
ON t.CertificateId = s.CertificateID)
ON i.InvestorId = s.InvestorId
ON t.CertificateId = s.CertificateID) ON i.InvestorId = s.InvestorId
WHERE i.MemberID='CP01241'
GROUP BY t.CertificateId, i.MemberID, t.TransactionDate, t.TransactionCode
ORDER BY t.CertificateId, t.TransactionDate;

Now, if my guess is correct, you are finished. Modify your jscript to
produce the above query. However, you can make your life much easier (and
improve performance at the same time) by parameterizing and saving this
query. Change it to this:

SELECT t.CertificateId, i.MemberID, t.TransactionDate, t.TransactionCode,
Sum(t.TransactionAmt) AS TransactionAmt
FROM Investors AS i INNER JOIN
(MIC1ShareTransactions AS t INNER JOIN MIC1Shares AS s
ON t.CertificateId = s.CertificateID)
ON i.InvestorId = s.InvestorId
ON t.CertificateId = s.CertificateID) ON i.InvestorId = s.InvestorId
WHERE i.MemberID=[pMemberID]
GROUP BY t.CertificateId, i.MemberID, t.TransactionDate, t.TransactionCode
ORDER BY t.CertificateId, t.TransactionDate;

Save it (File|Save) as qTransactionsByMember. Close Access. In your asp
page, do this:

var memid;
memid = Request.QueryString("MemberID");
conn = Server.CreateObject("ADODB.connection");
conn.Open("data");
rs=Server.CreateObject("ADODB.Recordset");
conn.qTransactionsByMember(memid, rs);
//etc.

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

[Miranda johnsen]

Hi Bob,
BELOW IS THE TABLE WITH THE DUPLICATED RECORDS:
\------------------
Certificate ID: 106 |
\--------------------
| Transaction Date ||Transaction Amount ||Transaction Type
||============================================= |
| 1/31/2004 || $276.24 || DIVC |
| 1/31/2004 || $276.24 || DIVC |
| 12/31/2003 || $279.52 || DIVC |
| 12/31/2003 || $279.52 || DIVC |
\---------------------------------------------

AND IT SHOULD BE:
\-----------------
Certificate ID: 106 |
\--------------------
| Transaction Date ||Transaction Amount ||Transaction Type
||============================================= |
| 1/31/2004 || $276.24 || DIVC |
| 12/31/2003 || $279.52 || DIVC |
------------------------------------------


Miranda Johnsen
[url]www.mirandajohnsen.com[/url]
===========================
Look and you will find it -- what is unsought will go undetected.
SOPHOCLES


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows [MVP]]

I am not seeing these records in the sample data you provided. I cannot
reproduce this problem. Have you tried verifying that your query is not
producing the duplicate records using the Access query builder? You still
need to answer whether it's the query causing this problem, or your jscript
code that displays the data. The only way to determine this is to run the
query in Access.

If you can reproduce the problem when you run the query in Access, then you
need to provide me with the data used to reproduce the problem.

If the problem turns out to be in your jscript code, then we will shift our
focus to that.

Bob Barrows

Miranda johnsen wrote:

> Hi Bob,
> BELOW IS THE TABLE WITH THE DUPLICATED RECORDS:
> \------------------
> Certificate ID: 106 |
> \--------------------

>> Transaction Date ||Transaction Amount ||Transaction Type

>>> ============================================= |

>> 1/31/2004 || $276.24 || DIVC |
>> 1/31/2004 || $276.24 || DIVC |
>> 12/31/2003 || $279.52 || DIVC |
>> 12/31/2003 || $279.52 || DIVC |
>> \---------------------------------------------

>
> AND IT SHOULD BE:
> \-----------------
> Certificate ID: 106 |
> \--------------------

>> Transaction Date ||Transaction Amount ||Transaction Type

>>> ============================================= |

>> 1/31/2004 || $276.24 || DIVC |
>> 12/31/2003 || $279.52 || DIVC |
>> ------------------------------------------

>

[Miranda johnsen]

This is from the db table:

CertificateId TransactionDate TransactionAmt
106 30-Nov-03 $291.11
106 31-Oct-03 $287.18
106 30-Sep-03 $230.24
106 31-Dec-03 $279.52
106 30-Nov-02 $271.79

==================================

This is from the webpage:

CertificateId=106

Transaction Date Transaction Amount
12/31/2003 $279.52
12/31/2003 $279.52
11/30/2003 $291.11
11/30/2003 $291.11
10/31/2003 $287.18
10/31/2003 $287.18
9/30/2003 $230.24
9/30/2003 $230.24


Miranda Johnsen
[url]www.mirandajohnsen.com[/url]
===========================
Look and you will find it -- what is unsought will go undetected.
SOPHOCLES


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

Miranda johnsen wrote:

> This is from the db table:

:-)
I hope you meant that this was the output from the query you tested in
Access. I'd hate to start digging into your jscript code only to find out
that the query was really the problem.

I'll get back to you. Of course, others are free to jump in here ... :-)

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"

[Bob Barrows]

Miranda johnsen wrote:

> This is from the db table:
>
> CertificateId TransactionDate TransactionAmt
> 106 30-Nov-03 $291.11
> 106 31-Oct-03 $287.18
> 106 30-Sep-03 $230.24
> 106 31-Dec-03 $279.52
> 106 30-Nov-02 $271.79
>
> ==================================
>
> This is from the webpage:
>
> CertificateId=106
>
> Transaction Date Transaction Amount
> 12/31/2003 $279.52
> 12/31/2003 $279.52
> 11/30/2003 $291.11
> 11/30/2003 $291.11
> 10/31/2003 $287.18
> 10/31/2003 $287.18
> 9/30/2003 $230.24
> 9/30/2003 $230.24
>
>

Well, I'm stumped. the jscript code looks fine. I don't see anything there
that would cause records to be written twice. So I think we need to return
to your query. Have you tried my suggested changes? If so, and the problem
is still occurring, you need to show me the relevant records from MIC1Shares
(it looks now as if you only showed the relevant records from
MIC1ShareTransactions) so I can attempt to reproduce your problem


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"

[Miranda johnsen]

I will be trying your suggested queries in about 1 hour.


Miranda Johnsen
[url]www.mirandajohnsen.com[/url]
===========================
Look and you will find it -- what is unsought will go undetected.
SOPHOCLES


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Miranda johnsen]

Hi Bob,
I was just looking at your results from your query and it brings up:
CertificateId MemberID TransactionDate TransactionCode TransactionAmt
313 CP01241 7/31/2003 DIVS $11.00
313 CP01241 8/31/2003 DIVS $10.00
313 CP01241 9/30/2003 DIVS $9.00
313 CP01241 12/31/2003 DIVS $0.00
328 CP01241 7/31/2003 DIVS $1.00
328 CP01241 12/31/2003 REDE $161.00
328 CP01241 12/31/2003 DIVS $0.00
328 CP01241 12/31/2003 DIVS $1.00
--------------------------------
I have it doing this already, but what I'm needing is to query for the
certificates seperately 313 and 328.

So I need that to seperate into:
--------------------------------
CertificateId MemberID TransactionDate TransactionCode TransactionAmt
313 CP01241 7/31/2003 DIVS $11.00
313 CP01241 8/31/2003 DIVS $10.00
313 CP01241 9/30/2003 DIVS $9.00
313 CP01241 12/31/2003 DIVS $0.00

---------------------------------
AND
------------------------------------
CertificateId MemberID TransactionDate TransactionCode TransactionAmt

328 CP01241 7/31/2003 DIVS $1.00
328 CP01241 12/31/2003 REDE $161.00
328 CP01241 12/31/2003 DIVS $0.00
328 CP01241 12/31/2003 DIVS $1.00
--------------------------------------
I tried putting in:
sSQL = "SELECT t.CertificateID, i.MemberID, t.TransactionDate,
t.TransactionCode,Sum(t.TransactionAmt) AS TransactionAmt";
sSQL +="FROM Investors AS i INNER JOIN";
sSQL +="(MIC1ShareTransactions AS t INNER JOIN MIC1Shares AS s";
sSQL +="ON t.CertificateID = s.CertificateID)";
sSQL +="ON i.InvestorId = s.InvestorId";
sSQL +="ON t.CertificateID = s.CertificateID) ON i.InvestorId =
s.InvestorId";
sSQL +="WHERE i.MemberID = '" + memid+"'";
sSQL +="GROUP BY t.CertificateID, i.MemberID, t.TransactionDate,
t.TransactionCode";
sSQL +="ORDER BY t.CertificateID, t.TransactionDate";
--------------------------------------
AND THIS IS THE ERROR i GOT:
------------------------------------
The SELECT statement includes a reserved word or an argument name that
is misspelled or missing, or the punctuation is incorrect.
------------------------------------------


??????????????
Thanks again,

Miranda Johnsen
[url]www.mirandajohnsen.com[/url]
===========================
Look and you will find it -- what is unsought will go undetected.
SOPHOCLES


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Miranda johnsen]

BTW here is the result of my SQL statement:

SELECT * FROM Investors, MIC1ShareTransactions, MIC1Shares WHERE
Investors.MemberID = 'CP00340'
AND MIC1Shares.CertificateId = 106
AND MIC1ShareTransactions.CertificateId = 106
ORDER BY MIC1ShareTransactions.CertificateId,
MIC1ShareTransactions.TransactionDate DESC;

Miranda Johnsen
[url]www.mirandajohnsen.com[/url]
===========================
Look and you will find it -- what is unsought will go undetected.
SOPHOCLES


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!