RE: multiple databases with fields (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

Sandalwood -> RE: multiple databases with fields (12/21/2005 16:14:16)

Options.aa is just symbols, like: "n[","tp", "zI", "m3"

Options.bb is the desriptions: "(ABS) ANTI-LOCK BRAKING SYSTEM", "0NE TOUCH 4X4", "0VER SIZE OFF ROAD TIRES", "1 OWNER"

VEHICLES.cstocknum just contains the stock numbers

VEHICLES.o1 has entries corresponding to Options.aa: "n[","tp", "zI", "m3"



yogaboy -> RE: multiple databases with fields (12/21/2005 16:53:49)

and VEHICLES.o2 links to the OPTIONS.aa???



Sandalwood -> RE: multiple databases with fields (12/21/2005 17:00:51)

Yes, VEHICLES.o1 thru VEHICLES.o8 all relate to OPTIONS.aa



yogaboy -> RE: multiple databases with fields (12/21/2005 17:09:08)

Ok, the design of the database is horribly flawed. By that I mean it will take a LOT of work to maintain.

If you want to link 1 record (VEHICLE) to multiple OPTIONS then you would be best off creating an intermediary table to link them. Something like this

VEHICLE
Id Description
1 Aston Martin
2 Monster Truck
3 General Lee....

OPTIONS
Id Item
1 Airbags
2 Stereo
3 Window wipers...

VEHICLESrelOptions
VehicleId OptionId
1 1
1 2
2 2
2 3
5 1
5 16...

Rule of thumb - if you are duplicating fields(columns) within a table because you have a 1 to many relationship, then it's probably best to move that information out of the table onto a new table.



Sandalwood -> RE: multiple databases with fields (12/22/2005 9:45:39)

Understood and agreed, however, I have to make it work with what I have. Also, it will be hard to create an intermediary table since the main database (Vehicles) will be changing and will need to be uploaded on a daily basis.



yogaboy -> RE: multiple databases with fields (12/22/2005 15:34:43)

Ok then, I can think of 2 options.

1. When the file is uploaded you run a collection of statements that one by one update each field using a JOIN and UPDATE.

2. Use regex to parse the file, match and replace the codes with the full descriptions.


Number 1 is probably easiest.


UPDATE VEHICLES
SET o1 = o.bb
FROM VEHICLES v
INNER JOIN OPTIONS o
ON o.aa = v.o1

UPDATE VEHICLES
SET o2 = o.bb
FROM VEHICLES v
INNER JOIN OPTIONS o
ON o.aa = v.o2

UPDATE VEHICLES
SET o3 = o.bb
FROM VEHICLES v
INNER JOIN OPTIONS o
ON o.aa = v.o3

continue to number 8

then you can just run this simple select statement

SELECT v.cstocknum, v.o1, v.o2, v.o3, v.o4, v.o5, v.o6, v.o7, v.o8"
FROM VEHICLES v
ORDER BY v.cstocknum ASC



Sandalwood -> RE: multiple databases with fields (12/22/2005 17:14:20)

1 sounds brilliant!

let me try...



Sandalwood -> RE: multiple databases with fields (12/22/2005 17:27:48)

This is my query, but it produced an error:
UPDATE VEHICLES SET o1 = OPTIONS.bb FROM VEHICLES INNER JOIN OPTIONS ON OPTIONS.aa = VEHICLES.o1




yogaboy -> RE: multiple databases with fields (12/22/2005 17:32:45)

When using a JOIN with an UPDATE you have to use aliases because you've already referred to the table at the beginning of the query, so this should read (at least)

UPDATE VEHICLES
SET o1 = OPTIONS.bb
FROM VEHICLES v
INNER JOIN OPTIONS
ON OPTIONS.aa = v.o1

but I'd be inclined to use aliases for all of the right-hand half of the query. Beware! You can't use aliases on the lefthand side of an UPDATE query (just incase you ever try and can't work out why it failed - I've been there! :)



Sandalwood -> RE: multiple databases with fields (12/23/2005 11:45:15)

Still getting an error on

fp_sQry="UPDATE VEHICLES SET o1 = OPTIONS.bb FROM VEHICLES v INNER JOIN OPTIONS ON OPTIONS.aa = v.o1"


Also tried

fp_sQry="UPDATE VEHICLES SET o1 = OPTIONS.bb FROM VEHICLES v INNER JOIN OPTIONS ON OPTIONS.aa = o1"

And


fp_sQry="UPDATE VEHICLES SET o1 = OPTIONS.bb FROM VEHICLES INNER JOIN OPTIONS ON OPTIONS.aa = o1"

Not really sure what you mean with the alias stuff.

BTW, thanks for sticking with me on this.



yogaboy -> RE: multiple databases with fields (12/24/2005 13:45:11)

It's no problem - what goes around comes around![:)]

Try using the query not using ASP, but directly by creating a new query in Access. If you never done it before, when you are looking at the first window Access opens showing all your tables etc, on the left under Objects select Queries. Double click Create Query in Design View. Don't add any tables and click Close. Then in the view menu select SQL view. Paste the query into there and press F5 or the green arrow to run it and see what happens.

That'll help show whether it works in Access or not - no point trying to get the ASP bit right if the query doesn't work!

Save the query and if it does work you can access it from there using ASP. I'll post some stuff to show you how. Let me know how you go.



Sandalwood -> RE: multiple databases with fields (1/17/2006 11:47:29)

Pasted Query into Access and got this message:

Syntax error (missing operator)
'OPTIONS.bb
FROM VEHICLES v
INNER JOIN OPTIONS
ON OPTIONS.aa = v.01'.



yogaboy -> RE: multiple databases with fields (1/17/2006 17:59:29)

Hi,

I'm looking at my posts and thinking I wasn't getting a lot of sleep, perhaps that's why I was writing such rubbish (I'm not getting a lot of sleep now either!)


You could try this instead of my crackpot posts from earlier.

Add 8 description fields to the Vehicles table, 1 for each of the codes.

ie o1, desc1, o2, desc2, o3, desc3....

then run a delete statement to clear the previous info (since you say you don't need it?)
DELETE from Vehicles
DELETE from OPTIONS

Then add the new data to the tables.

Then you could run 8 update statements like

UPDATE VEHICLES
SET desc1 = OPTIONS.bb
FROM VEHICLES v
INNER JOIN OPTIONS
ON OPTIONS.aa = v.o1

UPDATE VEHICLES
SET desc2 = OPTIONS.bb
FROM VEHICLES v
INNER JOIN OPTIONS
ON OPTIONS.aa = v.o2
....

then you could do
SELECT stocknumber, desc1, desc2 etc
from vehicles

i've tested this on a SQL Server 2000 database and it works, so if you fiddle around a bit it I reckon it'll work. Sorry for the sloppy code above, I really wasn't getting much sleep.[:(]



Page: <<   < prev  1 [2]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.109375