SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Hybrid View

  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Relationship between tables

    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...
    Code:
    ...
    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

  2. #2
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

    Code ASP:
    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...

  4. #4
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    did you create the object "RS2" ?

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    523
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yeah its all set at the top of the page..
    Code:
    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

  6. #6
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Note: you can't have multi-line strings in ASP - you need to break them down line by line using the &_ delimiter. Example:

    Code:
    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"
    Ian Anderson
    www.siteguru.co.uk


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •