Hi there!

I have set up a database consisting of 3 tables (users, orders and notes to customer)

The database works fine regarding the login.

The errors occur when I try to exctract data onto the dynamic customer page.

The dynamic page displays all the data from the tables instead of filtering it
for the right logged in customer.

I have tried the recordset filter, but it shows the wrong order for the customer.
For example it shows an order for customer #1, when instead it should show
an order for customer #2.

I have made a pic of the database setup:
home.online.no/~chrvik/database_setup.jpg


All help regarding database relation or filtering in DW is greatly appreciated!



Regards,
Sarre

Let's face it, designing is fun!

It is probably an error(s) in your sql select statement. Can you show what you have?

did you realise you are using RESERVED words in your table names

DATE is a reserved word and WILL cause errors in the DB. You should look into this to see if you are making any more mistakes.

on the relationship side of things, what kind of relatinships are they?

use noteDate

& orderDate

will wait and see what your SQL looks like as wdglide suggested.

Regards,
Dave

[DWMX 2004]|[FlashMX 2004 Pro]|[Studio MX 2004]|[SQL]|[Access2000/2002]|[ASP/VBScript]|[XP-Pro]
If you like online gaming, please register @ www.nova-multigaming.com

Hi guys!

Thank you for your quick respond!

First, regarding the date. The database is written in norwegian, so the reserved word "date" was
only used when I illustrated the setup for you in english. I'm currently using the norwegian word for date which is "dato". Think that one is ok. <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

When it comes to the sql statements, I have copied some text from the dynamic page for you to look at:


Dim rsShowOrders
Dim rsShowOrders_numRows

Set rsShowOrders = Server.CreateObject("ADODB.Recordset"
rsShowOrders.ActiveConnection = MM_liteconn_STRING
rsShowOrders.Source = "SELECT * FROM orders WHERE orderID = " + Replace(rsShowOrders__MMColParam, "'", "''" + " ORDER BY dato DESC"
rsShowOrders.CursorType = 0
rsShowOrders.CursorLocation = 2
rsShowOrders.LockType = 1
rsShowOrders.Open()

rsVisOrdrer_numRows = 0

---------------------------------------------------------------------------------------
When it comes to extracting the information in the RS I use Dynamic Text.
&lt;%=(rsShowOrders.Fields.Item("cost".Value)%&gt;



I think perhaps the error might be in the filtering done by the recordset.
I'm not that good in filtering the information.
A setup of my filter can be seen here:

home.online.no/~chrvik/dw_recordset_filter.jpg
(dato=date)



Any idea?



Regards,
Sarre


Let's face it, designing is fun!

The only thing that seems to jump out is that you are passing the script a UserID via the url. Your select statement seems to be expecting an OrderID. Is the UserID and OrderID the same thing?

Also, you have not mentioned what errors you are getting.

The userID and orderID is not the same.
UserID is just to separate all users by a unique number.
I would think that using userID in the orders table would link the information back to the right
customer in the users table. OrderID is just to make any order unique in the orders table.

The error with this whole thing is that the page is not showing the right order for the right customer.
In the orders table I have a test order going to customer #1, and only to him.
When I log in as testcustomer #2 that same order appears on the page. So in my oppinion
the filtering might be the problem. The page is not passing any errors, just the wrong information.

And the filter is certainly not separating what customer logs into the page, it shows whatever is
in the orders table.

Let's face it, designing is fun!

SOLUTION:

Finally, after many hours of brain-storming I came up with a solution.
By applying a session on the loginpage called UserID I was able to sort
out the different orders to the right customers by filtering UserID=Session variable UserID.

So filtering the recordset on the applied session "UserID" seems to be the clue.
Now whenever a customer logs into the dynamic customer page, all the details around
the orders seems to be ok.


Thank you for your time guys!



Regards,
Sarre the happy webdesigner <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>





Let's face it, designing is fun!