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

[leifoet]

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

 

[ChrisHirst]

How to display in one row the results of a query

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

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

 

[leifoet]

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

 

[ChrisHirst]

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"

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.

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

 

[leifoet]

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

 

[ChrisHirst]

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

 

[leifoet]

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

 

[ChrisHirst]

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

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

 

[leifoet]

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?

 

[ChrisHirst]

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

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

or it could be a function

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