adding data in two tables.
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=3821
Printed Date: 30 March 2026 at 1:37am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: adding data in two tables.
Posted By: edulog
Subject: adding data in two tables.
Date Posted: 25 June 2003 at 9:49am
|
I am creating a web interface for my Access database. I followed the example here: http://www.webwiz.net/asp/tutorials/add_to_database.asp This worked, but in my database I have to add data to two tables. The example used a statement like this: strSQL = "SELECT Clients.* FROM Clients;" Which works for updating just on of my tables. I'm using this SQL statement to VIEW the data: strSQL = "SELECT Clients.*, WebProducts.* FROM Clients, WebProducts WHERE Clients.CustomerID = WebProducts.CustomerID; This does not work for updating both tables. Any clarification would be helpful. Here is a sample of my code. <% 'Dimension variables' Dim adoCon 'Holds the Database Connection Object' Dim rsAddComments 'Holds the recordset for the new record to be added' Dim strSQL 'Holds the SQL query to query the database' 'Create an ADO connection object' Set adoCon = Server.CreateObject("ADODB.Connection") 'Set an active connection to the Connection object using a DSN-less connection' adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("TestHCG.mdb") 'Create an ADO recordset object' Set rsAddComments = Server.CreateObject("ADODB.Recordset") 'Initialise the strSQL variable with an SQL statement to query the database' strSQL = "SELECT Clients.*, WebProducts.* FROM Clients, WebProducts WHERE Clients.CustomerID = WebProducts.CustomerID;" 'Set the cursor type we are using so we can navigate through the recordset' rsAddComments.CursorType = 2 'Set the lock type so that the record is locked by ADO when it is updated' rsAddComments.LockType = 3 'Open the recordset with the SQL query' rsAddComments.Open strSQL, adoCon 'Tell the recordset we are adding a new record to it' rsAddComments.AddNew 'Add a new record to the recordset' rsAddComments.Fields("ClientName") = Request.Form("ClientName") rsAddComments.Fields("ContactTitle") = Request.Form("ContactTitle") rsAddComments.Fields("ContactFirstName") = Request.Form("ContactFirstName") rsAddComments.Fields("ContactLastName") = Request.Form("ContactLastName") rsAddComments.Fields("BillingAddress") = Request.Form("BillingAddress") rsAddComments.Fields("City") = Request.Form("City") rsAddComments.Fields("StateOrProvince") = Request.Form("StateOrProvince") rsAddComments.Fields("PostalCode") = Request.Form("PostalCode") rsAddComments.Fields("PhoneNumber") = Request.Form("PhoneNumber") rsAddComments.Fields("Extension") = Request.Form("Extension") rsAddComments.Fields("FaxNumber") = Request.Form("FaxNumber") rsAddComments.Fields("EmailAddress") = Request.Form("EmailAddress") rsAddComments.Fields("Notes") = Request.Form("Notes") rsAddComments.Fields("WebServer") = Request.Form("WebServer") rsAddComments.Fields("RemoteAccess") = Request.Form("RemoteAccess") rsAddComments.Fields("WebServerOS") = Request.Form("WebServerOS") rsAddComments.Fields("ServerRASInfo") = Request.Form("ServerRASInfo") 'WebProducts Table -webquery' rsAddComments.Fields("WebQuery") = Request.Form("WebQuery") rsAddComments.Fields("DateWQPurchsed") = Request.Form("DateWQPurchsed") rsAddComments.Fields("WQInstalled") = Request.Form("WQInstalled") rsAddComments.Fields("DateWQIntalled") = Request.Form("DateWQIntalled") rsAddComments.Fields("WQVersion") = Request.Form("WQVersion") rsAddComments.Fields("WQEmployee") = Request.Form("WQEmployee") 'WebProducts Table -webreports' rsAddComments.Fields("WebReports") = Request.Form("WebReports") rsAddComments.Fields("DateWRPurchased") = Request.Form("DateWRPurchased") rsAddComments.Fields("WRInstalled") = Request.Form("WRInstalled") rsAddComments.Fields("DateWRInstalled") = Request.Form("DateWRInstalled") rsAddComments.Fields("WRVersion") = Request.Form("WRVersion") rsAddComments.Fields("WREmployee") = Request.Form("WREmployee") 'WebProducts Table -webstudent' rsAddComments.Fields("WebStudent") = Request.Form("WebStudent") rsAddComments.Fields("DateWSPurchased") = Request.Form("DateWSPurchased") rsAddComments.Fields("WSInstalled") = Request.Form("WSInstalled") rsAddComments.Fields("DateWSInstalled") = Request.Form("DateWSInstalled") rsAddComments.Fields("WSVersion") = Request.Form("WSVersion") rsAddComments.Fields("WSEmployee") = Request.Form("WSEmployee") rsAddComments.Fields("DistrictComments") = Request.Form("DistrictComments") rsAddComments.Fields("Unresolved") = Request.Form("Unresolved") 'Write the updated recordset to the database' rsAddComments.Update 'Reset server objects' rsAddComments.Close Set rsAddComments = Nothing Set adoCon = Nothing 'Redirect to the guestbook.asp page' Response.Redirect "Intro.htm" %> Thanks FOR YOU HELP  |
Replies:
Posted By: pmormr
Date Posted: 25 June 2003 at 10:21am
|
alright let me get this straight, your trying to add data to two tables, am i right? i would use an INSERT INTO statement instead of recordset.addnew/recordset.update. ------------- Paul A Morgan http://www.pmorganphoto.com/" rel="nofollow - http://www.pmorganphoto.com/ |
Posted By: edulog
Date Posted: 25 June 2003 at 10:27am
|
Yes, I'm trying to add data to two different tables. Some of the replies I have got, mention the INSERT INTO statement. Why didn't I need to use the "INSERT INTO statement" if I'm adding to One table? Isn't this doing the "Insert Into statement", rsAddComments.AddNew? Thanks |
Posted By: pmormr
Date Posted: 25 June 2003 at 10:58am
|
thats the ado built in feature. it has always given me problems and i have never been able to get it to work. SQL is a little bit more advanced, thats why bruce didn't put it into a beginner article. the basic format for an insert into statement is: you can execute one of these statements for each table. Just remember that when you are putting the values for a request.form or something remember to close the quotes like this "INSERT INTO [tablename] (field1, field2) VALUES (" & request.form("value1") & "," & request.form("value2") & ")" hope i helped
------------- Paul A Morgan http://www.pmorganphoto.com/" rel="nofollow - http://www.pmorganphoto.com/ |