Insert statment for two tables

[danjapro]

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)" & _
""

[jimfraser]

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')"

[SSJ]

Jim's code is perfect. Also helped me..
Thanks Jim