SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    vi
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Insert statment for two tables

    Here is my entire Insert statement. Below.
    ContentID is primary_key ID and it is automaticlly created upon this insert, but want to also insert that content id in table Category column ContentId. HELP
    Two table insert sort of.


    [CODE]
    sql = "INSERT INTO Content" & _
    "(" & _
    "user_id," & _
    "CreatedBy," & _
    "ContentTypeID," & _
    "CatID," & _
    "Title," & _
    "Author," & _
    "RelatedURL," & _
    "DownloadURL," & _
    "DateAdded," & _
    "ShortDesc," & _
    "LongDesc," & _
    "Display," & _
    "EMail," & _
    "Filename," & _
    "Image1," & _
    "Thumbnail," & _
    "Expire," & _
    "Priority," & _
    "Status," & _
    "Impressions," & _
    "ClickThrus," & _
    "AvgRating," & _
    "PrevContentId," & _
    "NextContentId," & _
    "ParentContentId," & _
    "DateAlert," & _
    "Ratings" & _
    ") VALUES (" & _
    "" & to_sql(user_id,"number") & "," & _
    "" & to_sql(CreatedBy,"number") & "," & _
    "" & to_sql(ContentTypeID,"number") & "," & _
    "" & to_sql(CatID,"number") & "," & _
    "" & to_sql(Title,"text") & "," & _
    "" & to_sql(Author,"text") & "," & _
    "" & to_sql(RelatedURL,"text") & "," & _
    "" & to_sql(DownloadURL,"text") & "," & _
    "" & to_sql(now(),"date") & "," & _
    "" & to_sql(ShortDesc,"text") & "," & _
    "" & to_sql(LongDesc,"text") & "," & _
    "" & to_sql(Display,"number") & "," & _
    "" & to_sql(EMail,"text") & "," & _
    "" & to_sql(Filename,"text") & "," & _
    "" & to_sql(Image1,"text") & "," & _
    "" & to_sql(Thumbnail,"text") & "," & _
    "" & to_sql(Expire,"date") & "," & _
    "3," & _
    "" & to_sql(Status,"number") & "," & _
    "0," & _
    "0," & _
    "0," & _
    "" & to_sql(PrevContentId,"number") & "," & _
    "" & to_sql(NextContentId,"number") & "," & _
    "" & to_sql(ParentContentId,"number") & "," & _
    "" & to_sql(DateAlert,"absdate") & "," & _
    "0)" & _
    ""

  2. #2
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use 2 inserts

    Once you have defined the variable sql with your first insert, do this (assuming you use SQL Server or Access, for MySQL the syntax is different):
    sql = "SET NOCOUNT ON;" & sql & ";SELECT @@IDENTITY AS LastRecId"
    Set rs = db.Execute(sql)
    lastRecord = rs.Fields("LastRecId")

    Then you can do your next insert
    sql = "INSERT INTO otherTable (fKey, somedata) values(" & lastRecord & ",'jim_was_here')"

  3. #3
    SitePoint Guru SSJ's Avatar
    Join Date
    Jan 2007
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Jim's code is perfect. Also helped me..
    Thanks Jim


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
  •