|
| |
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
 |
multiple databases with fields - 12/15/2005 14:01:30
A client is sending us multiple text databases. In the first database, all of the field values have symbols that relate to real information in the corresponding databases. For example, Database 1, Column 1 might have "car" as the entry. Database 2 says "car" equals "Candy Apple Red".
How do I go about displaying results that pull all of this together?
Thanks in advance.
|
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/15/2005 14:49:16
Looking at the supporting text documents again, ti looks as if they are in this format:
"C?","BODY","CLUB CAB"
I'm not sure what to make of this?
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: multiple databases with fields - 12/15/2005 16:53:28
I'm assuming by database you mean table? If you actually mean database then the first job would be to "merge" the databases into one, or at least copy the relevent data into one of them.
So you have Table1 with a column called ColourCode, and the first entry is CAR
Table1 might look like this
id ColourCode
1 CAR
2 ICB
3 CAR
You also have Table2, called ColourCodes (it's helpful to give them useful names!) and the first column is called ColourCode, and the second column is called Colour. The table might look like this
ColourCode Colour
CAR Candy Apple Red
ICB Ice Cold Blue
To combine the information you'd use a join like this
Select t.id, c.Colour
FROM Table1 t
INNER JOIN ColourCodes c
ON c.ColourCode = t.ColourCode
I'd give all the colour codes a number instead of using characters because the database will do the joins quicker.
Hope that helps.
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/15/2005 17:11:48
Thanks for the help, but I'm not sure that is the answer. The text files will be sent on a daily basis, and I need to be able to just upload them to the server as is. There are 4 seperate text files, 2 set up like databases (column names, etc) and 2 in the format I had demonstrated above.
Here is a link to the database/text file:
http://www.thebigcarlot.com/affordable/11_23/test.asp (note the abbreviations)
Here is a snipit of code from the other file:
"n[","(ABS) ANTI-LOCK BRAKING SYSTEM"
"tp","0NE TOUCH 4X4"
"zI","0VER SIZE OFF ROAD TIRES"
"m3","1 OWNER"
"Ca","1 SET BLIZZACKS SNOW TIRES"
Any help with this would be great, because I am a bit out of my league with this.
Thanks
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: multiple databases with fields - 12/15/2005 17:15:14
when you say "as is", do the text files already have the html formatting or do you add that?
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/15/2005 17:19:15
They are formatted like the sample above. Here is a link to samples of the 2 types of files:
http://www.thebigcarlot.com/test/test2.asp
Hope that helps
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: multiple databases with fields - 12/15/2005 19:04:40
This help any?
http://weblogs.asp.net/fmarguerie/archive/2003/10/01/29964.aspx
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/16/2005 10:34:14
Duane,
I was able to use the code below (with the help of your suggested link) to attach to the text file. Thanks for that! This seems to be step 1.
Now, I have to script the part that says if database 1, column 1 says "CAR", use the line in this file that prints out the related text. I don't know how to go about doing this. Can anyone direct me to a solution?
<%
Dim sDSNFile
sDSNFile = "CSV.dsn"
Dim sScriptDir
sScriptDir = Request.ServerVariables("SCRIPT_NAME")
sScriptDir = StrReverse(sScriptDir)
sScriptDir = Mid(sScriptDir, InStr(1, sScriptDir, "/"))
sScriptDir = StrReverse(sScriptDir)
Dim sPath, sDSN
sPath = Server.MapPath(sScriptDir) & "\"
sDSN = "FileDSN=" & sPath & sDSNFile & _
";DefaultDir=" & sPath & _
";DBQ=" & sPath & ";"
Dim Conn, rs
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString = "Extended Properties='text;HDR=Yes;FMT=Delimited';"
Conn.Open sDSN
Dim sql
sql = "SELECT * FROM CARPARTS.TXT"
set rs = conn.execute(sql)
Do WHile Not rs.EOF
Response.Write "Name: "
rs.MoveNext 'Move to the next record
Loop
rs.close
set rs = nothing
conn.close
set conn = nothing
%>
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: multiple databases with fields - 12/16/2005 11:27:04
3 is scary! :)
You could use BeTheBall's method to get the values and then insert them into a database like Access or mySQL, and then joining the data will be a lot easier. SQL was basically designed to do the job you want to do, so why not use it? You could either run insert statements from the page you've written, or directly import the csv file.
I see you're using ASP - do you have a database engine (Access, mySQL, SQL Server) that you can use???
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: multiple databases with fields - 12/16/2005 11:34:20
You should be able to perform a JOIN on the two CSV files to only display the information you want. In other words, you join the table that has the descriptions so that you can then display the description itself instead of the cryptic code. Something like:
SELECT field1, field2, field3 FROM CARPARTS.TXT INNER JOIN DESCRIPTION.TXT ON [CARPARTS.TXT].field = [DESCRIPTION.TXT].field
Replace field with whatever the common field name is between the two tables.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/16/2005 11:37:12
Iain,
Yes, I have experience with database driven .asp pages and have used this method with much success in the past. It's just the text files that are throwing me for a loop.
The deeper I get into this, the more I think you were correct initially about just converting the raw text documents to databases.
So far, I opened up the text file in access and used the wizard to created an .mdb file, but when I went to write a query against it, there was no table.
< Message edited by Sandalwood -- 12/16/2005 11:45:21 >
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: multiple databases with fields - 12/16/2005 11:44:39
I'd create the tables myself (you'll do a lot better than Access will), and then import the first set of files so you can see how the data pans out. Then you can change your code to open the files and run insert statements so that you don't have to do the imports every day.
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/16/2005 11:47:53
quote:
ORIGINAL: yogaboy
create the tables myself and then import the first set of files so you can see how the data pans out. Then you can change your code to open the files and run insert statements so that you don't have to do the imports every day.
Not sure how to do this, but it sounds right in theory.
BTW, thank you both for ll the help. I think we are closinig in on it!
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/16/2005 14:36:53
I was able to import the text file into access and export a database with 3 named columns.
Seems, though, that in order to create an INNER JOIN, all the tables have to be in the same database?
And, if so, not sure how to display the results on a page?
Finally, I don't know where the code Iain suggested needs to go, or in what application.
Sorry to be a pain, but all of this is pretty new to me. My experience has been limited to creating and editing databases right through FrontPage.
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/21/2005 13:25:12
I may have been celebrating too soon. 
Here is my Select statement:
fp_sQry = "SELECT VEHICLES.cstocknum, VEHICLES.o1, VEHICLES.o2, OPTIONS.aa, OPTIONS.bb"
fp_sQry = fp_sQry & " FROM VEHICLES, OPTIONS"
fp_sQry = fp_sQry & " WHERE (VEHICLES.o1=OPTIONS.aa AND VEHICLES.o2=OPTIONS.aa) ORDER BY VEHICLES.cstocknum ASC"
It seems to pull the information correctly, BUT it pulls multiple results from both tables. For example, the VEHICLES table contains stock numbers and the OPTIONS table contains, you gussed it... options! I was hoping to display the stock number once, followed by all the options.
To complicate this even further, the options table contains similar symbols with different cases. For example, "AC" could be the symbol for air conditioning, while "Ac" is the symbol for airbags.
Anyone shed some light onto this dismal situation?
Thanks
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: multiple databases with fields - 12/21/2005 13:38:05
you're doing a cross join
try this
SELECT v.cstocknum, v.o1, v.o2, o.aa, o.bb"
FROM VEHICLES v
INNER JOIN o
ON o.aa = v.o1
WHERE v.o2 = o.aa)
ORDER BY v.cstocknum ASC
as for the case problem, that's a real problem. I'd change everything so it used unique numbers. Any chance you can get the people sending you this stuff to change their work practices??? It'd be good for you and probably them too (if you can give them a shiny new Access database they can use...)
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/21/2005 13:51:28
Got this:
Database Results Error
The operation failed. If this continues, please contact your server administrator.
This is what I tried:
fp_sQry = "SELECT VEHICLES.cstocknum, VEHICLES.o1, VEHICLES.o2, OPTIONS.aa, OPTIONS.bb"
fp_sQry = fp_sQry & " FROM VEHICLES, OPTIONS"
fp_sQry = fp_sQry & " INNER JOIN OPTIONS"
fp_sQry = fp_sQry & " ON OPTIONS.aa = VEHICLES.o1"
fp_sQry = fp_sQry & " WHERE VEHICLES.o2 = OPTIONS.aa)"
fp_sQry = fp_sQry & " ORDER BY VEHICLES.cstocknum ASC"
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: multiple databases with fields - 12/21/2005 13:58:13
get rid of
FROM VEHICLES, OPTIONS
and replace with
FROM VEHICLES
the join specifies the addition of OPTIONS with the INNER JOIN OPTIONS statement
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/21/2005 14:00:44
Same Error.
fp_sQry = "SELECT VEHICLES.cstocknum, VEHICLES.o1, VEHICLES.o2, OPTIONS.aa, OPTIONS.bb"
fp_sQry = fp_sQry & " FROM VEHICLES"
fp_sQry = fp_sQry & " INNER JOIN OPTIONS"
fp_sQry = fp_sQry & " ON OPTIONS.aa = VEHICLES.o1"
fp_sQry = fp_sQry & " WHERE VEHICLES.o2 = OPTIONS.aa)"
fp_sQry = fp_sQry & " ORDER BY VEHICLES.cstocknum ASC"
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: multiple databases with fields - 12/21/2005 14:07:55
...and remove the right-side bracket on the WHERE line
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/21/2005 14:15:40
OK, that worked, but I am getting results like this:
056461, POWER STEERING
056461, MATCHING CAP
056461, POWER SIDEVIEW MIRRORS
056461, CENTER ARM REST
08495, CENTER ARM REST
08495, MATCHING CAP
08495, POWER SIDEVIEW MIRRORS
08495, POWER STEERING
09426, CENTER ARM REST
I think it might be because I am using DRW. I will try that SQL with pure .ASP. Here is my DRW code:
<!--#include file="fpdbrgn1.inc"-->
<%=FP_FieldVal(fp_rs,"cstocknum")%>, <%=FP_FieldVal(fp_rs,"bb")%><br>
<!--#include file="fpdbrgn2.inc"-->
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/21/2005 14:20:44
Same results:
<%
Dim adoCon
Dim rsGuestbook
Dim fp_sQry
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & Server.MapPath("/fpdb/all.mdb")
Set rsGuestbook = Server.CreateObject("ADODB.Recordset")
fp_sQry = "SELECT VEHICLES.cstocknum, VEHICLES.o1, VEHICLES.o2, OPTIONS.aa, OPTIONS.bb"
fp_sQry = fp_sQry & " FROM VEHICLES"
fp_sQry = fp_sQry & " INNER JOIN OPTIONS"
fp_sQry = fp_sQry & " ON OPTIONS.aa = VEHICLES.o1"
fp_sQry = fp_sQry & " WHERE VEHICLES.o1 = OPTIONS.aa"
fp_sQry = fp_sQry & " ORDER BY VEHICLES.cstocknum ASC"
rsGuestbook.Open fp_sQry, adoCon
Do While not rsGuestbook.EOF
%>
<%response.write rsGuestbook("cstocknum")%><%response.write rsGuestbook("bb")%><br>
<%
rsGuestbook.MoveNext
Loop
%>
<%
rsGuestbook.Close
Set rsGuestbook = Nothing
Set adoCon = Nothing
%>
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: multiple databases with fields - 12/21/2005 16:03:06
quote:
I was hoping to display the stock number once, followed by all the options.
I don't have the data anymore - can you clarify what the fields Options.aa and Options.bb are?
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
I can think of 2 ways I would want to deal with this 
)
