Out Of Box - Asp

[gowans007]

Guys here's a bit of a tester!
For Me Anyway! (newbie to ASP have worked with PHP)

I have access to a SQL database but it only has 5 field

Name
TL
Clearway
Comments
Type

However I have 10 peices of information to store.

I have linked these by puting the info on different records and having name as primary key.

Name:Name
Clearway:tel
Comments:email
Type:1

Name:Name
TL:job
Clearway:age
Comments:description
Type:2

Name:Name
TL:location
Clearway:school
Comments:hobbie
Type:3

I have a page that savs this info fine but when it comes to displaying it, i can become jumbled with each record. How can I amend this so it displays the reocords of type 2 and 3 where its equal to name of displayed type 1.

PAGE Currently displays all results but not in any order and can show on one line a made up a part 1 part 2 part 3 of different Names.

************ CODE SECTION ***************

DBConnection.Open DSN

response.write ("<table border='0' width='100%'>")

SQL= "SELECT * FROM ProcessExpert WHERE Type = '1'"

Set RS = DBConnection.Execute(SQL)

do until rs.eof

response.write ("<tr class='bodytext'>")
response.write ("<td><div align='center'><strong>") & rs("Name")
response.write ("</strong></div></td>")
response.write ("<td><div align='center'>") & rs("TL")
response.write ("</div></td>")
response.write ("<td><div align='center'>") & rs("Clearway")
response.write ("</div></td>")
response.write ("<td><div align='center'>") & rs("Comments")
response.write ("</div></td>")

rs.movenext
loop
response.write ("</table></td><td width='41%' colspan='3'><table border='0' width='100%'>")

rs.close

SQL= "SELECT * FROM ProcessExpert WHERE Type = '2'"

Set RS = DBConnection.Execute(SQL)

do until rs.eof

response.write ("<tr class='bodytext'>")
response.write ("<td><div align='center'>") & rs("TL")
response.write ("</div></td>")
response.write ("<td><div align='center'>") & rs("Comments")
response.write ("</div></td>")
response.write ("<td><div align='center'>") & rs("Clearway")
response.write ("</div></td>")

rs.movenext
loop

response.write ("</table></td><td width='32%' colspan='4'><table border='0' width='100%'>")

rs.close


SQL= "SELECT * FROM ProcessExpert WHERE Type = '3'"

Set RS = DBConnection.Execute(SQL)

do until rs.eof

response.write ("<tr class='bodytext'>")
response.write ("<td><div align='center'><strong>") & rs("TL")
response.write ("</strong></div></td>")
response.write ("<td><div align='center'>") & rs("Clearway")
response.write ("</div></td>")
response.write ("<td><div align='center'>") & rs("Comments")
response.write ("</div></td>")


response.write ("<td><div align='right'><a href='bcin.asp?res=")
response.write rs("Name") & ("' class='LHNavLink'>DELETE</a></div></td></tr>")

rs.movenext
loop

response.write ("</table></td></tr>")

rs.close

[buntine]

You may have to write two SQL queries. One to select the name field from where type equals 1.

"SELECT DISTINCT Name FROM TableName WHERE Type = 1;"

Then use your recordSet object that contains the returned value to create the next query.

"SELECT * FROM TableName WHERE (Type = 2 OR Type = 3) AND (Name = '" & rs("Name") & "');"

However, it seems like a very 'messy' approach. Is there any way you can change the design of your database?

Regards.

[gowans007]

I wish there was but there isn't any way at all.

There are the only fields I can have.

I'll try the 2nd option (don't really now about DISTINCT)

And let you know shortly how i get on!

[gowans007]

Still no look with the 2nd option just got error message.

I have included the actual page in the ZIP which I just can't get to work to see if you guys can get a better idea.

This has took me all day now with no luck (thought it worked then realised it didnt!) at all.

[buntine]

Oh sorry. I actually meant that you needed both of them. The first one will get the name of the record with 'Type' 1. And the second query will get the other records accordingly.

Regards.

[gowans007]

Oh, OK.

How do I fit that into the above script as I can't get it to work

:)

[gowans007]

Guys any ideas on how to edit the code to work?

Im still stuck!??