Relationship between tables

[ulthane]

Hello all,
I've just started working with relationships between table, i had a big table that had the following records:
ID, Filename1, 2, 3, 4, Filesize1, 2 ,3 ,4, ContentType1, 2, 3, 4, BinaryData1, 2, 3, 4, PetDesc, PetName, PetType(Male/Female), Animalstatus, AnimalType(Dog/Cat)

Now as u can already understand, i had alot of empty records on that table, because not all of the records were required (not every pet had 4 photos, most pets had just 1 or 2..)
So i decided to saperate this table to 2 tables like the following:

Animals:
ID (AutoNum), PetName, PetDesc, PetType, AnimalType, AdoptionStatus

Pics:
ID (Num), FileSize, ContentType, BinaryData, FileName

Im going to need help with creating an upload form to upload all of the items at once to those 2 tables, how can i do that?

Atm the page i used to upload photos to the 1 big table looked like that...

...
sSQL = "animals WHERE FileID = 0"

RecordSet.AddNew

RecordSet.Fields("FileName").Value = Upload.Fields("File1").FileName
RecordSet.Fields("FileName2").Value = Upload.Fields("File2").FileName
RecordSet.Fields("FileName3").Value = Upload.Fields("File3").FileName
RecordSet.Fields("FileName4").Value = Upload.Fields("File4").FileName
RecordSet.Fields("FileSize").Value = Upload.Fields("File1").Length
RecordSet.Fields("FileSize2").Value = Upload.Fields("File2").Length
RecordSet.Fields("FileSize3").Value = Upload.Fields("File3").Length
RecordSet.Fields("FileSize4").Value = Upload.Fields("File4").Length
RecordSet.Fields("ContentType").Value = Upload.Fields("File1").ContentType
RecordSet.Fields("ContentType2").Value = Upload.Fields("File2").ContentType
RecordSet.Fields("ContentType3").Value = Upload.Fields("File3").ContentType
RecordSet.Fields("ContentType4").Value = Upload.Fields("File4").ContentType
RecordSet.Fields("BinaryData").AppendChunk Upload("File1").BLOB & ChrB(0)
RecordSet.Fields("BinaryData2").AppendChunk Upload("File2").BLOB & ChrB(0)
RecordSet.Fields("BinaryData3").AppendChunk Upload("File3").BLOB & ChrB(0)
RecordSet.Fields("BinaryData4").AppendChunk Upload("File4").BLOB & ChrB(0)
Recordset.Fields("Desc").Value = upload.fields("Desc1").Value
Recordset.Fields("PetName").Value = upload.fields("name1").Value
Recordset.fields("AnimalType").value = upload.fields("animalType1").value
Recordset.fields("adoptionStatus").value = upload.fields("adoptionStatus1").value
Recordset.fields("Type").value = upload.fields("type1").value

RecordSet.Update
...

Yeah, what a mess

[webber123456]

uploading of multiple files is actually more easily done with a component or ASP.net

for a component to use, consult your webhost to learn what is available to your website. Research that component to learn its usage.

If none is available from the webhost, go to the ASP.net group and they can provide guidance on the uploading of multiple files.

[ulthane]

well nvm it, i succeed building up a form that seems to be doing this nicely in ASP, but now i got stucked at the displaying part

sql1= "SELECT * FROM Animals WHERE (adoptionstatus= 'זמין')  AND (animaltype= 'חתול') ORDER BY FileID DESC"
RS.Open sql1, conn, 3, 3
If Not RS.EOF then
 Do While Not RS.EOF
  sName = RS("PetName")
  sDesc = RS("Desc")
  sDesc = RS(sDesc, vbNewLine, "<br/>")
  Response.Write "<tr>"
  ' Code that displays Pet's infos
  iPetID = RS("FileID")
  sql2 = "SELECT * FROM pictures WHERE FileID=" & iPetID
  RS2.Open sql2, conn, 3, 3 '  Error on this line, Object Required...
  Response.Write "<td width=260>"
  Do While Not RS2.EOF
   sImage = RS2("FileID")
   ' Code that displays Pets images
    RS2.MoveNext
  Loop
  Response.Write "</td>"
  RS2.close
  Set RS2 = Nothing
  RS.MoveNext
 Response.Write "</tr>"
 Loop
RS.close

Error on the line i marked...

[webber123456]

did you create the object "RS2" ?

[ulthane]

Yeah its all set at the top of the page..

Dim conn, RS, RS2
ADB = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("db/animals.mdb")
Set conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.recordset")
Set RS2 = Server.CreateObject("ADODB.recordset")
RS.Open ADB

Very strange uh ? :P

[webber123456]

There's only two objects in that line so it has to be one or the other.

Create a separate connection (conn2) for use with rs2 and see if it gets resolved.

[ulthane]

still didnt solved :/

[ulthane]

solved it >_<
The problem was that i setted RS2 = Nothing inside the Do While function...
So when it ran its like RS2 was not even created

[awasson]

ulthane, depending on how many records you've got, that could be a large amount of database action as you loop to display the records.

I suppose you can't use a single joined Query to grab your pet data and all of the pictures into a single recordset but you could run simplify things by running query 1, loading pet data into an array, run query 2 load picture data into another array. Then loop through the array to pull the data. Then you only have to transact with the database twice.

Database calls inside of a loop can get resource heavy so I like to reduce them as much as possible.

Also check with the guys in the databases section of the forum. They may have some good ideas about how to set up your databases and relationships between them. They may be able to help you consolidate your query into a single call.

EDIT: On further review of your code, it appears that it will have to be done with at least two calls to the database. One to grab all the pet data into an array and another to get all of the pictures but I'm not sure if you can put blob data into an array so you may have to settle for database calls within the loop.

[ulthane]

Hello awasson, Thank u for making this point, it was really important for me to hear it, because i've just restarted building my site a few days ago, with making my pages load as fast as possible as my main target.
Its the first time i hear about arrays, ill have to read around abit some tutorials and see some examples before i can start working with them..

i noticed my code is pretty long...but thats all i could do with the knowledge i got atm, what u see above is just a small part of the code i got...

here is the real thing, sorry its too long, but u guys got to see the real thing if i wanna get helped making my pages load faster in someway... if u guys can help me shorthen it alittle bit please it could be very usefull, doesnt must be on code itself, but on advices and examples could do aswell.

<%
Dim ConnToPets, CallToPets, CallToPets2
AnimalDB = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("db/animals.mdb")
Set ConnToPets = Server.CreateObject("ADODB.Connection")
Set CallToPets = Server.CreateObject("ADODB.recordset")
Set CallToPets2 = Server.CreateObject("ADODB.recordset")
ConnToPets.Open AnimalDB
GetMsg = "SELECT * FROM Animals WHERE (animaltype= 'הודעה מיוחדת')"
CallToPets.Open GetMsg, ConnToPets, 3, 3
If Not CallToPets.EOF then
  sDesc = CallToPets("Desc")
  sDesc = Replace (sDesc, vbNewLine, "<br/>")
  Response.Write "<tr>"
  %>
  <td width="260" align="right" style="color:#0066FF;"><br /><br /><b><%=sDesc %></b></td>
  <%
  iPetID = CallToPets("FileID")
  GetPics = "SELECT * FROM pictures WHERE FileID=" & iPetID
  CallToPets2.Open GetPics, ConnToPets, 3, 3
  Response.Write "<td width=260>"
  sImage = CallToPets2("FileID")
  %>
  <a href="GetPetsPhotos.asp?FileID=<%=sImage%>" rel="thumbnail"><img style="margin-bottom:5px;margin-top:5px;" border="0" width="260" height="310" src="GetPetsPhotos.asp?FileID=<%=sImage%>"/></a>
  <br />
  <%
  Response.Write "</td>"
  Response.Write "</tr>"
CallToPets2.close
End if
CallToPets.close
GetAnimals = "SELECT * FROM Animals WHERE (adoptionstatus= 'זמין')  AND (animaltype= 'כלב חודש דף מידע')  ORDER BY fileID DESC"
CallToPets.Open GetAnimals, ConnToPets, 3, 3
If Not CallToPets.EOF Then
  Do While Not CallToPets.EOF
   sName = CallToPets("PetName")
   sType = CallToPets("Type")
   sDesc = CallToPets("Desc")
   sDesc = Replace (sDesc, vbNewLine, "<br/>") 
   Response.Write "<tr>"
   %>
   <td align="right" style="color:#0066FF;"><%if sName <> "" then %><b><%if sType = "זכר" then%>כלב החודש: <%=sName %><%elseif sType = "נקבה" then%>כלבת החודש: <%=sName %><%elseif stype = "רבים" then %>כלב/ת החודש: <%=sName %><%end if %></b><br /><br /><%end if %><%=sDesc %></td>
   <%
   iPetID = CallToPets("FileID")
   GetPics = "SELECT * FROM pictures WHERE FileID=" & iPetID
   CallToPets2.Open GetPics, ConnToPets, 3, 3
   Response.Write "<td width=260>"
   Do While Not CallToPets2.EOF
    sImage = CallToPets2("FileID")
    %>
    <a href="GetPetsPhotos.asp?FileID=<%=sImage%>" rel="thumbnail"><img style="margin-bottom:5px;margin-top:5px;" border="0" width="260" height="310" src="GetPetsPhotos.asp?FileID=<%=sImage%>"/></a>
    <br />
    <%
    CallToPets2.MoveNext
   Loop
   Response.Write "</td>"
   CallToPets2.Close
   CallToPets.MoveNext
   Response.Write "</tr>"
  Loop
End if
CallToPets.close
GetDogs = "SELECT * FROM Animals WHERE (adoptionstatus= 'זמין')  AND (animaltype= 'כלב')  ORDER BY fileID DESC"
CallToPets.Open GetDogs, ConnToPets, 3, 3
If Not CallToPets.EOF Then
  Do While Not CallToPets.EOF
    sDesc = CallToPets("Desc")
    sName = CallToPets("PetName")
    sDesc = Replace (sDesc, vbNewLine, "<br/>") 
    Response.Write "<tr>"
    %>
    <td align="right"><%if sName <> "" then %><b><%=sName %></b><br /><br /><%end if %><%=sDesc %></td>
    <%
    iPetID = CallToPets("FileID")
    GetPics = "SELECT * FROM pictures WHERE FileID=" & iPetID
    CallToPets2.Open GetPics, ConnToPets, 3, 3
    Response.Write "<td width=260>"
    sCounter= 0
    Do While Not CallToPets2.EOF
     sImage = CallToPets2("FileID")
     %>
     <a href="GetPetsPhotos.asp?FileID=<%=sImage%>" rel="thumbnail"><img style="margin-bottom:5px;margin-top:5px;" border="0" width="260" height="310" src="GetPetsPhotos.asp?FileID=<%=sImage%>"/></a>
     <br />
     <%
     CallToPets2.MoveNext
    Loop
    Response.Write "</td>"
    CallToPets2.Close
   CallToPets.MoveNext
   Response.Write "</tr>"
   Loop
End if
CallToPets.close
%>
</table>
<b><font size="6" color="#0000FF" face="times new roman">כלבים שאומצו</font></b>
<br /><br />
<%
GetAdoptedOnes = "SELECT * FROM Animals WHERE (adoptionstatus= 'לא זמין')  AND (animaltype= 'כלב')  ORDER BY fileID DESC"
CallToPets.Open GetAdoptedOnes, ConnToPets, 3, 3
If Not CallToPets.EOF Then
 Response.Write "כל הכלבים שאומצו רשומים כאן"
 Response.Write "<br/>"
 Response.Write "<table align=center width=520 style='margin-bottom:10px; border-style:outset; border-color:#267411;' border=5 cellspacing=3 cellpadding=3>"
 Do While not CallToPets.EOF
  sDesc = CallToPets("Desc")
  sDesc = Replace (sDesc, vbNewLine, "<br/>") 
  sName = CallToPets("PetName")
  sType = CallToPets("Type")
  response.write "<tr>"
  %>
  <td width="260" align="right"><%if sName <> "" then %><b><%=sName%></b><br /><br /><%end if %><%=sDesc %><br /><br />
  <%if stype = "נקבה" then %><span class="adopted"><b>אומצה!</b></span><%elseif stype = "זכר" then%><span class="adopted"><b>אומץ!</b></span><%elseif stype = "רבים" then %><span class="adopted"><b>כולם אומצו!</b></span><%end if %></td>
  <%
  iPetID = CallToPets("FileID")
  GetPics = "SELECT * FROM pictures WHERE FileID=" & iPetID
  CallToPets2.Open GetPics, ConnToPets, 3, 3
  Response.Write "<td width=260>"
  Do While Not CallToPets2.EOF
   sImage = CallToPets2("FileID")
   %>
   <a href="GetPetsPhotos.asp?FileID=<%=sImage%>" rel="thumbnail"><img style="margin-bottom:5px;margin-top:5px;" border="0" width="260" height="310" src="GetPetsPhotos.asp?FileID=<%=sImage%>"/></a>
   <br />
   <%
   CallToPets2.MoveNext
  Loop
  Response.Write "</td>"
  CallToPets2.Close
  CallToPets.MoveNext
 Response.Write "</tr>"
 Loop
 Response.Write "</table>"
Else
 Response.Write "אין כלבים שאומצו כרגע"
 Response.Write "<br/>"
End if
CallToPets.close
ConnToPets.Close
Set CallToPets = Nothing
Set CallToPets2 = Nothing
Set ConnToPets = Nothing
%>

Ill make a topic in the database forum aswell now, asking how can i make my database calls shorter if possible...

[ulthane]

Hey guys, i got this code from the database forum:

SELECT a.FileID
     , a.PetName
     , a.Desc
  FROM Animals AS a
LEFT OUTER
  JOIN pictures AS p
    ON p.FileID = a.FileID
 WHERE a.adoptionstatus= 'adopted'
   AND a.animaltype= 'dog'
ORDER 
    BY a.FileID DESC

i tried to put this SQL code on that page instead of the first SQL line:

sql1="SELECT a.FileID
, a.PetName
, a.Desc
  FROM Animals AS a
LEFT OUTER
  JOIN pictures AS p
    ON p.FileID = a.FileID
  WHERE a.adoptionstatus= "זמין" AND a.animaltype= "חתול" ORDER BY a.FileID DESC"
RS.Open sql1, conn, 3, 3
If Not RS.EOF then
 Do While Not RS.EOF
  sName = RS("PetName")
  sDesc = RS("Desc")
  sDesc = RS(sDesc, vbNewLine, "<br/>")
  Response.Write "<tr>"
  ' Code that displays Pet's infos
  iPetID = RS("FileID")
  sql2 = "SELECT * FROM pictures WHERE FileID=" & iPetID
  RS2.Open sql2, conn, 3, 3
  Response.Write "<td width=260>"
  Do While Not RS2.EOF
   sImage = RS2("FileID")
   ' Code that displays Pets images
    RS2.MoveNext
  Loop
  Response.Write "</td>"
  RS2.close
  RS.MoveNext
 Response.Write "</tr>"
 Loop
RS.close

Well, i dont really know how to progress from here, how can i loop through all the images that belong to the same a.FileID using the new SQL code without having to make a 2nd db call...?

[r937]

...how can i loop through all the images that belong to the same a.FileID using the new SQL code without having to make a 2nd db call...?

start by putting the pet picture into the SELECT clause (where you removed the "p.OtherPetInfos" from the query i gave you)

[ulthane]

K, so it would look like that

SELECT a.FileID
     , a.PetName
     , a.Desc
     , p.FileID
  FROM Animals AS a
LEFT OUTER
  JOIN pictures AS p
    ON p.FileID = a.FileID
 WHERE a.adoptionstatus= 'adopted'
   AND a.animaltype= 'dog'
ORDER 
    BY a.FileID DESC

the P.FileID is the only coluum needed from the pictures table, and then for each p.fileID it goes to "PhotoDownload.asp", which is used to download the image itself into the page

[r937]

the P.FileID is the only coluum needed from the pictures table, and then for each p.fileID it goes to "PhotoDownload.asp", which is used to download the image itself into the page

why do you have a separate page to display the animal's photo?

[ulthane]

Well, i found it on Lewis moten's guide once, found out it is matching what i was looking for and so started using it on my pages....
That page looks like the following:

FileID = Request.QueryString("p.FileID")
sql = "SELECT FileName, ContentType, BinaryData FROM Pictures WHERE FileID = " & FileID
RS.Open sql, ConnToPets, 3, 3
Response.AddHeader "content-disposition", "inline; filename=" & RS("FileName")
Response.AddHeader "content-length", RS("BinaryData").ActualSize
Response.ContentType = RS("ContentType")
Response.BinaryWrite RS("BinaryData")

[siteguru]

Note: you can't have multi-line strings in ASP - you need to break them down line by line using the &_ delimiter. Example:

sql = "SELECT a.FileID" &_
"     , a.PetName" &_
"     , a.Desc" &_
"     , p.FileID" &_
"  FROM Animals AS a" &_
"LEFT OUTER" &_
"  JOIN pictures AS p" &_
"    ON p.FileID = a.FileID" &_
" WHERE a.adoptionstatus= 'adopted'" &_
"   AND a.animaltype= 'dog'" &_
"ORDER " &_
"    BY a.FileID DESC"