INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to display in one row the results of a query on two tables

How to display in one row the results of a query on two tables

(OP)
Hi,
I try to display all the soccer matches (from table 1) with at the end of each row, the name of the ref (from table 2)
(Uploaded database to the server = Access)

table 1 (tblmatch) = data about the soccer matches : date, field .... + the number of the ref
table 2 (tblrefs) = all the refs = number, name, phone ....

in table 1, I made a field <ref-id> with the number of the ref for the match (e.g. number 10 for ref. John)
in tabel 2 is also a field with the <refnr> (e.g. for ref. John is the refnr : 10)

I tried the code below, but i receive the follwing error :

ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.

Thanks for help - Leifoet


Dim adoCon 'Holds the Database Connection Object
Dim rstest 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database
.../...
Set rstest = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT tblmatch.* , tblrefs.refnr FROM tblmatch INNER JOIN tblrefs ON tblmatch.ref-ID=tblrefs.refnr;"

Do While not rstest.EOF

Response.Write ("<br>")
Response.Write (rstest("date"))
' Response.Write ("<br>")
Response.Write (rstest("field"))
' Response.Write ("<br>")
Response.Write (rstest("ABC"))
' Response.Write ("<br>")
Response.Write (rstest("XYZ")
' Response.Write ("<br>")
Response.Write (rstest("ref-ID"))

' Response.Write ("<br>")
if (rstest("ref_ID"))<> 0 then
Response.Write (rstest("tblrefs.refnr")) 'the error message comes here
' This line of code should be instead of the above => Response.Write (rstest("tblrefs.name"))

end if


Response.Write ("<br>")

rstest.MoveNext

Loop
.../...

RE: How to display in one row the results of a query on two tables

Quote:

How to display in one row the results of a query

Don't put any <br> elements between the items.

Quote:

ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.

Says that there is no column with that name, possibly caused by a misspelling in one of the column names

And depending on the database server 'ref-ID' may not be the same as 'ref-id'.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

RE: How to display in one row the results of a query on two tables

(OP)
Hi Chris,

Your reply : <Says that there is no column with that name, possibly caused by a misspelling in one of the column names>
I think this code possibly is not correct because the field from table tblrefs is not 'found' : Response.Write (rstest("tblrefs.refnr"))
My question remains: is this the correct syntax to display a field from the table 2 ?

Sorry for the mistype : it must be <ref_ID> - the problem remains !

In short, I'm looking for the right code - possibly an example (at www) = to display data from 2 tables in one row.

Thanks for help - Leifoet

RE: How to display in one row the results of a query on two tables

Quote:

to display data from 2 tables in one row.

It does not matter how many tables your data is extracted from. In a JOIN query, it is only one record set, in your case 'rstest'.

If "by a single row" you mean "all on one line in a HTML document"

CODE --> asp-vbscript

Response.Write ("<br>")
Response.Write (rstest("date"))
Response.Write (rstest("field"))
Response.Write (rstest("ABC"))
Response.Write (rstest("XYZ")
Response.Write (rstest("ref-ID")) 
Will do that.

If you do not mean a single line please elaborate, as "in one row" is an ambiguous term in HTML/CSS as there are several ways of making textual information be displayed in a single horizontal plane.

Certainly, I could take a guess and assume you mean "in one table row" but I don't do "guessing" where code is concerned as it simply wastes time in getting to what you want to happen.


Quote:

it must be <ref_ID>
There is no "must be something", it has to be EXACTLY the same as it is defined in the table.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

RE: How to display in one row the results of a query on two tables

(OP)
Hi Chris,

Thanks to your explanation I can now display a list of all matches (= records of Table 1) for which a referee (= REF_ID) was completed; and at he end of each row comes also the name of the referee and his phone number => OK

But there are also a number of records for which no referee is indicated, and of which the corresponding field REF_ID is still empty in Table 1.
Consequently, in these cases there is not a join-connection between Table 1 (matches) and Table 2 (refs).

How can I also include and display these records in the list?

Thanks for help - Leifoet

RE: How to display in one row the results of a query on two tables

If there is no match criteria to join on, those rows will not be in the recordset so cannot be displayed.

This is because you are using an INNER JOIN (aka RIGHT JOIN) that includes rows from both tables ONLY IF there is a match in the 'right' table ('left' and 'right' being used as in the 'sides' of an equation).

So you have to use a different type of JOIN, which is the OUTER JOIN (aka LEFT JOIN) which includes the results from the 'left' table even if there are no records in the 'right' table.

Take Note:
The results from the 'right' table will have 'NULL' "values" so make sure you display routine can handle NULL records.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

RE: How to display in one row the results of a query on two tables

(OP)
Hi Chris,

I used <left join> and it works now - Thanks

I have still the following two problems

1=> in table 1 (matches) there are sometimes identical fields for certain records (e.g. date, number of players ...)
If these records are in the table sequentially, then only one (=the first) record is displayed.
Is there a possibility to admits such "duplicates"? How to display all the records?

2=> I want to display the list as follows: alternating a 'lightblue' and a 'white' line with this code
.../...
<tr>
<td align="right">
<%Dim x, bgcolor
if x=1 then
bgcolor="lightblue"
response.write "bgcolor='lightblue'"
x=2
Else
bgcolor="white"
response.write "bgcolor='white'"
x=1
End if%>

<%response.write (rstest("MatchID"))%>
</td>
.../...

The lines are colored, but as the beginning of the white line, exact the following text appears => bgcolor='white'
and as the beginning of white line => bgcolor='lightblue'

What is wrong with my code?

Thanks for help - Leifoet

RE: How to display in one row the results of a query on two tables

bgcolor as an attribute for arbitrary elements is really ancient history, almost pre-history as far as HTML is concerned, long since deprecated and is not supported at all in newer document types.
Even when it was valid it HAD to be an attribute of the <body> or <table> element and set the background colour of the entire document or element.

So <table bgcolor="blue" ...> would be its correct use, just putting directly in the text means that it is just text, and appears as just text.

When writing data to the output stream you HAVE to write the whole HTML structure, so an example using the "with" statement

CODE

<% with response %>
<span style="background-color="blue">
     <% .write("whatever you want to write here") %>
</span>
<% end with %> 
And just jump in and out of ASP when you need to write to some data to the output stream, that way you do not need to try an build all the HTML/CSS elements in response.write statements.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

RE: How to display in one row the results of a query on two tables

(OP)
Hi Chris,

Excuse me, but I do not understand how you come to alternately blue and white background with the specified code.
I do not understand how to implement the code (in my code). Do you want to clarify your explanation please ?

Thanks - Leifoet

May I also have a tip for this problem in my prvious message

1=> in table 1 (matches) there are sometimes identical fields for certain records (e.g. date, number of players ...)
If these records are in the table sequentially, then only one (=the first) record is displayed.
Is there a possibility to admit such "duplicates"? How to display 'all' the records?

RE: How to display in one row the results of a query on two tables

Quote:

Excuse me, but I do not understand how you come to alternately blue and white background with the specified code.

It won't, because it is not real code, it is one suggestion and a pseudo code example of how to write it

The idea is that YOU adapt what I suggest to suit YOUR needs so combine your "if x = 1" and write blue or white into the style attribute ... background-colour:

This is made much easier if you use a MOD() function to test for odd or even numbers

CODE

if x mod 2 = 0 then
    .write "white" 'even number
else
    .write "blue" 'odd number
end if 

or it could be a function

CODE

setcolour (number)
if number mod 2 = 0 then
    setcolour = "white" 'even number
else
    setcolour = "blue" 'odd number
end if
[code]

then 

setcolour(x) will return with blue or white.

so 

[code]
<% with response %>
<span style="background-color: <% .write(setcolur(x))%>;">
     <% .write("whatever you want to write here") %>
</span>
<% end with %> 

Will write 'blue' or 'white' into the inline style attribute

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close