SQL JOIN & View Page

This topic is locked

SQL JOIN & View Page	4/22/2007 10:51:50 PM
ASPRunnerPro General questions
J jtforstner author My master table contains fields: FranchiseNbr, FranchiseName, Lineup, Basic, Premium, Modem In my detail table I have fields: Lineup, System, ChannelNbr, ChannelName. I have the tables inner joined by the Lineup field so that I can display some additional information on the master table's view page. The System field will indicate whether each channel is Analog, Digital, HiDef or Premium On the view page I would like several HTML tables. The first table would list all of the analog channels for that lineup in numerical order, the second would list all digital channels, etc. Any insight? Thanks!

Mark as Solved Mark as not solved

15 replies


	Sergey Kornilov admin 4/23/2007
	Jeremy, it's kind of difficult to visualize what you trying to build. Can you show us some screenshot or mockup page?


J	jtforstner author 4/23/2007
	Does this make sense???


	Sergey Kornilov admin 4/24/2007
	I would create a ViewOnLoad Event and display all those tables in that event.


J	jtforstner author 4/24/2007
	I would create a ViewOnLoad Event and display all those tables in that event. Can you give me an example of how to do this? Or point me to some information? Thanks!


J	Jane 4/25/2007
	Jeremy, here is a sample code: Sub ViewOnLoad() ' Parameters: ' where - string with WHERE clause pointing to record to be viewed str = "select FieldName from DetailTableName where System='Analog' and Lineup=" & dict("Lineup") Set rsTemp = server.CreateObject("ADODB.Recordset") rsTemp.open str, dbConnection Response.write "<table><tr><td>Analog</td></tr>" Response.write "<td><tr>" & rsTemp("FieldName") & "</td></tr></table>" rsTemp.Close : set rsTemp = Nothing End Sub Also you can find some examples on the ASPRunner Forum, in the ASPRunner Help or here: http://webcheatsheet.com/asp/


J	jtforstner author 4/25/2007
	Jeremy, here is a sample code: Also you can find some examples on the ASPRunner Forum, in the ASPRunner Help or here: http://webcheatsheet.com/asp/ Okay... so I've modified this to do what I want... however, it's not working correctly. When I run it as seen below it returns absolutely nothing. But if I remove the "and Lineup=" & dict("Lineup")" from the end of the SQL it will properly return the analog channels from ALL lineups. Did I do something wrong here? `str = "select * from [CHANNELS] where System='Analog' and Lineup=" & dict("Lineup") Set rsTemp = server.CreateObject("ADODB.Recordset") rsTemp.open str, dbConnection Response.write "<table><tr><td>Analog</td></tr>" do while not rsTemp.eof Response.write "<tr><td>" & rsTemp("ChannelNbr") & "</td><td>" & rsTemp("ChannelName") & "</td></tr>" rsTemp.MoveNext loop rsTemp.Close : set rsTemp = Nothing`


	Sergey Kornilov admin 4/25/2007
	Jemery, unfortunately the code Jane provided is not correct as dict variable is not available in ViewOnLoad event. In this event you can use Request.QueryString("editid1") to access key column value (664 in your example). Use this value to retrieve data related to current record. I hope this helps a little bit. Also when you build SQL queries make sure text values are wrapped by single quote characters.


J	jtforstner author 4/25/2007
	Jemery, unfortunately the code Jane provided is not correct as dict variable is not available in ViewOnLoad event. In this event you can use Request.QueryString("editid1") to access key column value (664 in your example). Use this value to retrieve data related to current record. I hope this helps a little bit. Also when you build SQL queries make sure text values are wrapped by single quote characters. Using the key column value will not work for this application. I need it to pull based on the LINEUP field that is displayed on the page. Is this do possible or should I just give up hope? Please help!


	Sergey Kornilov admin 4/25/2007
	Jeremy, you giving up too early. Using key column value you can retrieve the current record and proceed from there. strSQL = "select * from TableName where KeyColumn=" & Request.QueryString("editid1")


J	jtforstner author 4/26/2007
	Please excuse me being a total novice with some of this, I am still learning. I just can't figure this piece out. This is what I came up with... and it is returning the error below it. strSQL = "select * from [FRANCHISE] where KeyColumn=" & Request.QueryString("editid1") set rsLineup = server.CreateObject("ADODB.Recordset") rsLineup.open strSQL, dbConnection str = "select * from [CHANNELS] where System='Analog' and Lineup=" & rsLineup("LINEUP") Set rsTemp = server.CreateObject("ADODB.Recordset") rsTemp.open str, dbConnection Response.write "<div align=center><table border=0 width=200><tr><td colspan=2 align=center><b>ANALOG</b></td></tr>" do while not rsTemp.eof Response.write "<tr><td align=center bgcolor=#E4EBEC width=30>" & rsTemp("ChannelNbr") & "</td><td bgcolor=#E4EBEC align=left>" & rsTemp("ChannelName") & "</td></tr>" rsTemp.MoveNext loop Response.write "</table></div>" rsTemp.Close : set rsTemp = Nothing Error number -2147217904 Error description [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. URL /interact/interact/FRANCHISE_view.asp SQL query select * from [FRANCHISE] where KeyColumn=402 Solution This error means one of the following: your query contains parameters. Don't use query with parameters in ASPRunnerPro. One of field names in SQL query above is incorrect. Make sure all field names in this query are correct.


	Sergey Kornilov admin 4/26/2007
	Jeremy, in my sample code I highlighted table and field names that need to be replaced with the actual names. KeyColumn is not a real column name. Other than that code looks pretty good.


J	jtforstner author 4/26/2007
	Jeremy, in my sample code I highlighted table and field names that need to be replaced with the actual names. KeyColumn is not a real column name. Other than that code looks pretty good. It doesn't work. I'm getting a data type mismatch error now. Take a look, I uploaded it to my demo acct. http://demo.asprunner.net/jforstner_mn_usc...om/USCINTERACT/


J	Jane 4/27/2007
	Jeremy, if Lineup is the text field you need to use single quotes in the query: strSQL = "select from [FRANCHISE]where KeyColumn=" & Request.QueryString("editid1") set rsLineup = server.CreateObject("ADODB.Recordset") rsLineup.open strSQL, dbConnection str = "select from [CHANNELS] where System='Analog' and Lineup='" & rsLineup("LINEUP")& "'" Set rsTemp = server.CreateObject("ADODB.Recordset") rsTemp.open str, dbConnection Response.write "<div align=center><table border=0 width=200><tr><td colspan=2 align=center><b>ANALOG</b></td></tr>" do while not rsTemp.eof Response.write "<tr><td align=center bgcolor=#E4EBEC width=30>" & rsTemp("ChannelNbr") & "</td><td bgcolor=#E4EBEC align=left>" & rsTemp("ChannelName") & "</td></tr>" rsTemp.MoveNext loop Response.write "</table></div>" rsTemp.Close : set rsTemp = Nothing


J	jtforstner author 4/27/2007
	Jane you're my hero! <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=17376&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' /> One last question, is there a way to make sure that the output is sorted by rsTemp("ChannelNbr") in ascending order? Thank you! Thank you!


J	jtforstner author 4/27/2007
	Jane you're my hero! <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=17379&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' /> One last question, is there a way to make sure that the output is sorted by rsTemp("ChannelNbr") in ascending order? Thank you! Thank you! Nevermind - ORDER BY - duh! Thanks again!

15 replies

Mark as Solved Reply