multiple databases with fields (Full Version)

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



Message

Sandalwood -> 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 -> 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 -> 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 -> 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 -> 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 -> 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



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

With my limited knowledge, and ultimate strive for laziness![:)] I can think of 2 ways I would want to deal with this

1. Change the text file to an xml file and deal with it that way.

2. Import the text files into a database engine.

and a 3rd way that I wouldn't want to do

3. Parse the file using Regular Expressions

Both methods (I'm not going to think about the 3rd because RegEx have given me much suffering) depend on the technology you're using and how much work you want to put in. I'd try hard to get it into a database engine, and if the files were in XML that would be a lot easier! I think I've seen RDouglass post some pretty nifty stuff about using text files, but I could be mistaken.



BeTheBall -> 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



Sandalwood -> 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
%>



Sandalwood -> RE: multiple databases with fields (12/16/2005 10:44:53)

Iain,

I wouldn't be against trying your methods, but I would need to do some research on how to go about doing 1 & 2. (3 sounds pretty scary [sm=fie.gif])



yogaboy -> 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 -> 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.




Sandalwood -> 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.



Sandalwood -> RE: multiple databases with fields (12/16/2005 11:44:25)

Duane,

The problem I see is that the CSV file that has the descriptions has no column names. Seems as if that is necessary for your script, no?

Seems as if converting the CSV files to databases is the way to go, unless I am missing something. [sm=ask.gif]



yogaboy -> 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 -> 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!



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

what version of Office are you using? I've imported the 2 files you posted up, into an Access 2003 db. If you want I'll mail it to you.

You'll need to run these against each field/column as it's put quotes around everything.

UPDATE LOTDATA
SET field = RIGHT( field, LEN( field ) - 1 )
go

UPDATE LOTDATA
SET field = LEFT( field , LEN( field ) - 1 )
go

I'll let you do that![:D]



Sandalwood -> 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.



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

In order for you to keep all your hair, the tables all need to be in the same database.[:D]

The code I suggested right at the top of this thread should go either hard-coded on the ASP page (use this), or can go into a query/stored proc.

Once you've done the join the results come out like any other result set, treat it as if it came from one table and do what you'd normally do with a straightforward SELECT statement. You've written some code earlier in the thread that would deal with this, with only a few small changes.

Let me know if you need more help, it's no bother.



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

Success! [sm=yupi3ti.gif]

Thank you both for your help. You've saved me a lot of work.

And, BTW, I was able to choose the "s as text qualifiers in the import wizard in Access, which kept them from appearing in the final database.

Thanks again.




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

quote:

And, BTW, I was able to choose the "s as text qualifiers in the import wizard in Access, which kept them from appearing in the final database


I did that and they were still there! Some people have all the luck [:(]

[;)]



Sandalwood -> 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 -> 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 -> 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 -> 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 -> 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 -> RE: multiple databases with fields (12/21/2005 14:07:55)

...and remove the right-side bracket on the WHERE line



Sandalwood -> 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 -> 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 -> 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?



Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.15625