CODEWITHSUNDEEP
sqlms-accessasp-classic
chap
chap

Reputation: 1869

Why is ASP not finding anything in my database?

Initially my problem was that my posts were duplicating. I believe this was because I didn't link my tables in the SQL statement. Now that I have done that I am getting a "No Data Found" printed after my if info.eof statement.

I'm sure that there is information in the database as I have checked manually, however no success.

Here is my code: 

<% option explicit %>
<!DOCTYPE html>
<html>
<head>

    <link rel="stylesheet" type="text/css" href="normalize.css">
</head>

<body>
<!--#include file="header.asp"-->
<!--#include file="dbconn.asp"-->



<%


    dim sql, info

'                               0                 1      2        3         4                5              
 SQL = "select projectName, UserName, Category, Created, url, Projectstable.description "&_
        "from ProjectsTable, PictureTable, Usertable, CategoryTable "&_
        "where Projectstable.ID=PictureTable.projectNum AND "&_
        "categoryNum=CategoryTable.ID AND "&_
        "ProjectsTable.userNum=Usertable.ID AND "&_
        "UserTable.ID=PictureTable.userNum "&_
            "order by Created"

set info=conn.execute(SQL)

  if info.eof then
    response.write "<p>No Data Found</p>"
  else

    do 
      response.write "<h1>" & info(0) & "</h1> By: " & info(1) & " Posted in: " & info(2) & " Created on: " & info(3) & "<br>" &_
                     "<img src=""images/" &info(4)&""">" &_
                     "<p>" & info(5) & "</p>"

      info.movenext
    loop until info.eof
  end if 

  conn.close 

%>

</body>

</html>

Upvotes: 0

Views: 71

Answers (2)

chap
chap

Reputation: 1869

The answer was in fact errors in the database itself. More pointedly, it was incorrect linking between tables. The SQL statement might be outdated but it wasn't causing the errors.

Upvotes: 1

GarethD
GarethD

Reputation: 69759

I have rewritten your query using ANSI 92 explicit join syntax rather than the ANSI 89 version you have used (which as the names suggest was replaced over 20 years ago with explicit joins):

SELECT  projectName, UserName, Category, Created, url, Projectstable.description
FROM    ProjectsTable
        INNER JOIN PictureTable
            ON Projectstable.ID = PictureTable.projectNum 
        INNER JOIN Usertable
            ON UserTable.ID = PictureTable.userNum
            AND ProjectsTable.userNum = Usertable.ID
        INNER JOIN CategoryTable
            ON categoryNum = CategoryTable.ID
ORDER BY Created;

Which highlighted to me that you are limiting your results to where the User in the picture table is the same as the user in the project table. Is this intentional?

Do you get the required results if you remove one of the criteria? So either

SELECT  projectName, UserName, Category, Created, url, Projectstable.description
FROM    ProjectsTable
        INNER JOIN PictureTable
            ON Projectstable.ID = PictureTable.projectNum 
        INNER JOIN Usertable
            ON UserTable.ID = PictureTable.userNum
            --AND ProjectsTable.userNum = Usertable.ID
        INNER JOIN CategoryTable
            ON categoryNum = CategoryTable.ID
ORDER BY Created;

Or

SELECT  projectName, UserName, Category, Created, url, Projectstable.description
FROM    ProjectsTable
        INNER JOIN PictureTable
            ON Projectstable.ID = PictureTable.projectNum 
        INNER JOIN Usertable
            ON ProjectsTable.userNum = Usertable.ID
            --AND UserTable.ID = PictureTable.userNum
        INNER JOIN CategoryTable
            ON categoryNum = CategoryTable.ID
ORDER BY Created;

For further reading on ANSI 89 vs ANSI 92 join syntax Aaron Bertrand has written a great article about it in his bad habits to kick series.

Upvotes: 1

Related Questions