Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access ASP
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access ASP Using ASP with Microsoft Access databases. For Access questions not specific to ASP, please use the Access forum. For more ASP forums, please see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access ASP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old September 21st, 2005, 06:25 PM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using two tables in ASP

Hello P2P - I'm a new user here, so please bear with me.

I have an html form pulling from ASP from one table. I found the table structure to be faulty and am fixing it, however, need to know how to accomplish this task since the restructure.

With the restructure I have two tables:
1st with fields ID and common contact information
2nd table with three fields ID, Date and TakingBus (boolean)

Once the online user fills out the form, I'd need the form to update both tables:
The first with their contact info (ID is an autonumber within Access)
The second with the dates they choose to participate (could be multiple dates) and if they will be riding the bus to the event.

What I am trying to do, is once I update the first table, I'd like to use the autonumber generated to populate the second tables ID field in order to "tie" the two tables together. Can I do that?

Here's the code I was playing with (form fields not included here):

        strID=Request.form("ID")
    strDID=Request.form("DID")

    strFirstName=Request.form("FirstName")
    strLastName=Request.form("LastName")
    strPhone=Request.form("Phone")
    strAddress=Request.form("Address")
    strCity=Request.form("City")
    strState=Request.form("State")
    strZip=Request.form("Zip")
    strEmail=Request.form("Email")
    strShirtSize=Request.form("ShirtSize")
    strCommunityService=Request.form("CommunityService ")
    strOver18=Request.form("Over18")
    strDate=Request.form("Date")
    strBus=Request.form("Bus")


    Set objConn = Server.CreateObject("ADODB.Connection")
    ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("rose_parade.mdb")
    objConn.Open ConnectionString

    sqlQuery = "SELECT * FROM tblVolunteers"
    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.Open sqlQuery, objConn, 1, 2

    objRS.AddNew
    objRS("ID")=strID
    objRS("FirstName")=strFirstName
    objRS("LastName")=strLastName
    objRS("Phone")=strPhone
    objRS("Address")=strAddress
    objRS("City")=strCity
    objRS("State")=strState
    objRS("Zip")=strZip
    objRS("Email")=strEmail
    objRS("ShirtSize")=strShirtSize
    objRS("CommunityService")=strCommunityService
    objRS("Over18")=strOver18
    objRS("Date")=strDate
    objRS("Bus")=strBus

    objRS.Update
    objRS.Close
    Set objRS = Nothing

    sqlQuery = "SELECT * FROM tblDates"
    Set objRSD = Server.CreateObject("ADODB.Recordset")
    objRSD.Open sqlQuery, objConn, 1, 2

    objRSD.AddNew
    objRSD("DID")=objRS("ID")
    objRSD("Date")=strDate
    objRSD("Bus")=strBus

    objRSD.Update
    objRSD.Close
    Set objRSD = Nothing

Thank you for any and all help you can provide!

Tanisha

Reply With Quote
  #2 (permalink)  
Old October 16th, 2005, 02:08 PM
Friend of Wrox
 
Join Date: Jul 2005
Location: Oklahoma City, OK, USA.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try this:

    objRS("Bus")=strBus

    SavedKey = objRS("ID") ' <<<< new line


    objRS.Update
    objRS.Close
    Set objRS = Nothing

    sqlQuery = "SELECT * FROM tblDates"
    Set objRSD = Server.CreateObject("ADODB.Recordset")
    objRSD.Open sqlQuery, objConn, 1, 2

    objRSD.AddNew
    objRSD("DID")= SavedKey ' <<<< changed


If this does not work and you are using JET4 drivers or SQL server lookup how to use @@IDENITY in Microsoft's KB.

See this post for more info


Boyd
"Hi Tech Coach"
Access Based Accounting/Business Solutions developer.
http://www.officeprogramming.com
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to relate tables in ASP .NET busybee BOOK: ASP.NET Website Programming Problem-Design-Solution 1 April 2nd, 2006 04:36 PM
How to Relate Tables in ASP .NET busybee ASP.NET 1.0 and 1.1 Basics 4 March 29th, 2006 12:25 AM
how query 2 tables in asp method Classic ASP Databases 5 March 19th, 2005 11:24 AM
how to query 2 tables in asp.net method ASP.NET 1.0 and 1.1 Basics 3 March 18th, 2005 03:35 PM
Using Asp to Add/Delete Tables in a SQL database? morpheus Classic ASP Databases 2 April 23rd, 2004 07:59 AM



All times are GMT -4. The time now is 01:24 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.