How to Join and show data in browser

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Join and show data in browser

Author

Topic

erico
Starting Member

34 Posts

Posted - 2007-03-21 : 17:01:22

This is the select statement I'm currently using but I can't seem to get the browser to show any data. It just shows the table structure.

I use the g and the p as aliases. Easier maintenance this way.

strSQL = "SELECT g.GuestID, g.ProgramID, p.ProgramID AS Expr1, g.GuestName, g.GuestDescription, p.URL, p.Description FROM dbo.T_ProgramGuests g INNER JOIN dbo.T_ProgramLinks p ON g.GuestID = p.LinkID WHERE p.ProgramID = " & Request("ProgramID")

I have an ASP page where the above sql code is embedded. I have two SQL tables. One is called T_ProgramGuests and the other is called T_ProgramLinks. The asp page basically does a read only of the two tables and is suppose to return the results inside of the html tables I have in the asp page. But all I see when I test is the html and the tables are devoid of data.

HERE IS THE PAGE CODE:

<%
on error resume next
 set con = Server.CreateObject("ADODB.Connection")
 con.Open "File Name=E:\webservice\Company\Company.UDL"
 set recProgram = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT g.GuestID, g.ProgramID, p.ProgramID AS Expr1, g.GuestName, g.GuestDescription, p.URL, p.Description FROM dbo.T_ProgramGuests g INNER JOIN dbo.T_ProgramLinks p ON g.GuestID = p.LinkID WHERE p.ProgramID = " & Request("ProgramID")

recProgram.Open strSQL,con

%>

<TABLE border="1" cellspacing="0" border="1" width="70%">

<p><b>Guest Name and Description:</b></p>
  <tr>
    <th width="35%" align="left">Guest Name</th>
		<th width="45%" align="left">Guest Description</th>
	<tr>
		<TD headers="t2"><%=recProgram.Fields("GuestName")%></TD>
	  <TD headers="t3"><%=recProgram.Fields("GuestDescription")%></TD>
  </tr>
</table>

<TABLE border="1" cellspacing="0" border="1" width="70%">
<br /><br />
<p><b>URL and Description:</b></p>
	<tr>
    <th width="35%" align="left">URL</th>
		<th width="45%" align="left">Description</th>
	<tr>
		<TD headers="t4"><%=recProgram.Fields("URL")%></TD>
	  <TD headers="t5"><%=recProgram.Fields("Description")%></TD>
<%'
recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
'response.write err.Description
%>
	   
  </body>
</html>

I am new to using the advanced features of SQL and could sure use some help with this.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-21 : 17:53:25

you need to loop through the rows; there is no loop in this code.


while not recProgram.EOF
  
  .. do stuff
  
  recProgram.MoveNext

loop

By the way, you should remove that ON ERROR RESUME NEXT statement! That will make debugging your code impossible!

In addition, you should really try to use stored procedures and not to construct in-line SQL like that on the ASP page; it makes things much harder to test and debug and update, and it leaves your code vulnerable to sql injection and other bad things -- especially when you simply concatenate the request parameter to your sql string without doing any checking at all to ensure that it is a valid value.

Sorry to sound like I am picking on you, I am not, if you take my advice not only will your code be more efficient, shorter, easier to read and debug and also safer, but you'll make yourself a much better programmer as well. For what it's worth.

More about sql injection here: http://en.wikipedia.org/wiki/SQL_injection
More about why you should always use parameters and/or stored procedures here: http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

erico
Starting Member

34 Posts

Posted - 2007-03-21 : 18:36:57

Wow thanks so much for all the good info. It will take some time for me to absorb it and understand it. But once I work with it enough things will get easier for me. I will continue to post follow up testing in this posting so keep an eye out. This way you can read up on how my testing and results are going.

Thanks again now I have to study, fix and test.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources