displaying database info via asp

[Staff: Warren Ashcroft]

Cant think if theres a better way to do this, but you could use subquieries:

SELECT *, (SELECT player_name FROM players WHERE id = player1) AS player1_name, (SELECT player_name FROM players WHERE id = player2) AS player2_name, (SELECT player_name FROM players WHERE id = player3) AS player3_name, (SELECT player_name FROM players WHERE id = player4) AS player4_name, (SELECT player_name FROM players WHERE id = player5) AS player5_name, (SELECT player_name FROM players WHERE id = player6) AS player6_name, (SELECT player_name FROM players WHERE id = player7) AS player7_name FROM fixtures WHERE team =1 AND date < (now()-1)ORDER BY date Asc

This gives you new coumns:

player1_name, player2_name, player2_name etc....

[RFH Reseller: Sol]

Actually, I would do this slightly differently.

Get the players in a drop down by calling a seperate function that would allow you to pass in the player_id and then output the dropdown list html with the player already selected.

This would allow you to use the following database call and cache the output so that you are not needing to run it against the database 16 times (number of players in a team).

'SELECT player_id, player_name FROM players'

Just to make things more clear, you would be using the same code you posted, but put this function call in during the 'DO WHILE intRecordCount < 30 and NOT objRS.EOF' loop. Something like:

Player:&nbsp;<%= GetPlayerDDL(objRS("player")) %>

Of course, if you need to capture if they make changes to the form you could use the following:

Player 1:&nbsp;<%= GetPlayerDDL(objRS("player1"), "Player1") %> 
Player 2:&nbsp;<%= GetPlayerDDL(objRS("player2"), "Player2") %>

The second parameter can be used as the field name and hence picked up on submission.

The tricky part would be figuring out how to cache the players in Classic ASP. In ASP.Net you could retrieve them as a DataTable and store them in the Cache, or alternatively use a PlayerCollection which would be strongly typed.

[RFH Reseller: UKFlyer]

Cant think if theres a better way to do this, but you could use subquieries:

SELECT *, (SELECT player_name FROM players WHERE id = player1) AS player1_name, (SELECT player_name FROM players WHERE id = player2) AS player2_name, (SELECT player_name FROM players WHERE id = player3) AS player3_name, (SELECT player_name FROM players WHERE id = player4) AS player4_name, (SELECT player_name FROM players WHERE id = player5) AS player5_name, (SELECT player_name FROM players WHERE id = player6) AS player6_name, (SELECT player_name FROM players WHERE id = player7) AS player7_name FROM fixtures WHERE team =1 AND date < (now()-1)ORDER BY date Asc

This gives you new coumns:

player1_name, player2_name, player2_name etc....

Ok tried that and it gives me:


Microsoft VBScript compilation error '800a03ea'Syntax error

Just to clarify when you say it gives me new columns 'player1_name' am I requesting <%=objRS("player1_name")%> or do I have to rename the fields in the database?

In relation to the second post, I don't want a drop down displayed - the drop down is purely for the player selection. I want the player_name of the selected player displayed instead of the player_id which is what I get at the moment.

[Staff: Warren Ashcroft]

What is the line where that error occurs?

[RFH Reseller: UKFlyer]

Sorry my error - I left off the " at the end of the string. However now get:

Microsoft JET Database Engineerror '80040e14'

Characters found after end of SQL statement. /test.asp, line 81

Line 81 being the last line of this:

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorType = 1
objRS.Open mySQL, objConn

[Staff: Warren Ashcroft]

"Characters found after end of SQL statement" - did you check?

[RFH Reseller: UKFlyer]

One of the characters had been changed right at the end - instead of < it was &lt; - paste error from the web page I guess...

Had no info displaying at all, however made a change so instead of id I put in player_id and now it is up and running and working fine. Exactly what I wanted - thanks.

[Staff: Warren Ashcroft]

Great - note that the query I suggested is very inefficient, all those sub queries may cause the response to be slow.

It would have been easier if you had another table for storing players: fixtures_players

This table would have two columns: fixture_id, player_id

Each fixture would have multiple entires in this table, one for each player; this would make it possible to load the data from the fixtures table and then load all the players in that fixture from fixtures_players whilst at the same time making a join on the players table to fetch the player data in each row.

[RFH Reseller: UKFlyer]

Have a similar problem with another site: I have used a different way of connecting to the database:
<%
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db/data.mdb")
%>

<% dim adoCn
dim strSQL
set adoCn = Server.CreateObject("ADODB.Connection")
adoCn.Open strConn
%>

And have used the following to display the fields:

<%
dim adoS1

strSQL = "Select * FROM CourseAvailability WHERE Coursedate > (now()-1) Order by CourseDate Asc"
set adoS1 = adoCn.Execute(strSQL)
%>

<%Do While Not adoS1.EOF%>
<tr>
<td width="100"><%=adoS1("CourseDate")%>&nbsp;</td>
<td width="500"><%=adoS1("CourseName")%>&nbsp;</td>
<td width="100"><a href="CourseLocation.asp?LocationId=<%=adoS1("Cour seLocation")%>" <font size="2" face="Verdana">
<font size="2" face="Verdana">Location</font></a></td>
<td width="100">
<a <font size="2" face="Verdana" href="BookingDetails.asp?CourseID=<%=adoS1("Course ID")%>">
<font size="2" face="Verdana">Book</font></font></a></td>
</tr>
<% adoS1.MoveNext
Loop
%>

Course Location is a number (the primary key from the locations table).

Using:

<%
dim adoS1
strSQL = "Select *,(SELECT LocationTown FROM Locations WHERE LocationID = CourseLocation) AS Course_Location FROM CourseAvailability WHERE Coursedate > (now()-1) Order by CourseDate Asc"
set adoS1 = adoCn.Execute(strSQL)
%>
<%Do While Not adoS1.EOF%>
<tr>
<td width="100"><%=adoS1("CourseDate")%>&nbsp;</td>
<td width="500"><%=adoS1("CourseName")%>&nbsp;</td>
<td width="100"><a href="CourseLocation.asp?LocationId=<%=adoS1("Cour seLocation")%>" <font size="2" face="Verdana">
<font size="2" face="Verdana"><%=adoS1("Course_Location")%></font></a></td>
<td width="100">
<a <font size="2" face="Verdana" href="BookingDetails.asp?CourseID=<%=adoS1("Course ID")%>">
<font size="2" face="Verdana">Book</font></font></a></td>
</tr>
<% adoS1.MoveNext
Loop
%>

I was hoping to display the town instead of the id number. However in this instance I get:

Error Type:
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
/CourseDates.asp, line 46

I'm pretty sure the select statement is the same one as used for the football players so I'm guessing it's something to do with the way I've connected to the database in this instance. Anything obvious I've done wrong?

[RFH Reseller: SKILLIT]

What type of field is cousedate?
Can remember access db's well - but if it were SQL server it would be a DateTime field which I think your would need to use DateAdd to add -1 days.,

Which is line 46 - The select?