Writing to two tables (Full Version)

All Forums >> [Web Development] >> ASP, PHP, and Database



Message

JDSpilio -> Writing to two tables (6/18/2002 9:58:13)

Is there any way to write to two tables in the same database without using a query?

Or at the least, writing to one table at the same time as passing information to another page?

I'm using FP2K & Access2K.

Thanks!

"Hard work beats talent when talent doesn't work hard..."



rdouglass -> RE: Writing to two tables (6/18/2002 13:07:14)

If you're using the DRW, I think you can put 2 DRW's (both with custom querys in Step 2) on the same update page. Or you could write pure ASP to do it in one DB connection. However, I do not know of any way of doing it without a query....

 



JDSpilio -> RE: Writing to two tables (6/18/2002 14:44:04)

Thanks for the response rdouglass.

What I'm creating is a Media catalog from which users can search for and request media (books, CD's, etc).

I have 3 tables:

tblUsers which contains a userID (text - ID is a SSN) and other user info.

tblMedia which contains a mediaID (autonum) and other media info including a mediaSearch field (yes/no)

tblRequest which contains a requestID (autonum), mediaID (num), userID (text) and a requestStatus to track the current place in the borrowing cycle (pending, borrowed, overdue, returned).


What should happen is when a user enters search parameters, the results will return records from tblMedia where the mediaSearch field is set to yes.

The user can then click on a request button which will pass the mediaID to the next page (finalizeRequest.asp).

finalizeRequest.asp will take the mediaID and input it into a hidden field to be written into tblRequest, and will have a text box asking the user for their userID. This information will
also be written to tblRequest. I also want to change the value of the mediaSearch field in the tblMedia to "no" to remove it from the searches.

Following that I want to pass the userID to the next page (user-info.asp) which is also something I haven't yet figured out how to do.

When in user-info.asp, users will enter their user information if they already haven't done so. If they have once before, they can skip the page and go to the end (end.asp).
Information from here will be written to tblUsers. The userID text box will be automatically populated with the userID value from the previous page.

The problem with queries is that if I join tblMedia and tblRequest, I wind up getting multiple recordsets for one media - one recordset per request per media. The same is true of queries linking tblRequest with tblUsers, so query joins are out.

The mediaSearch field in tblMedia will be reset using an Access UI by the person in charge of maintaining the media catalog.

Any ideas?

Thanks in advance!

Jonathan



rdouglass -> RE: Writing to two tables (6/19/2002 9:04:59)

quote:
Following that I want to pass the userID to the next page (user-info.asp)
which is also something I haven't yet figured out how to do.


You may be able to set a session and just grab it when you need it.
Write the session as soon as you get / confirm it: 

Session("UserID") = Request.Form("UserID")


then just grab it whenever you need it like: 

UserID = Session("UserID")


quote:
The problem with queries is that if I join tblMedia and tblRequest, I wind up getting multiple
recordsets for one media - one recordset per request per media. The same is true of queries
linking tblRequest with tblUsers, so query joins are out.


I'm unsure about this; you should be able to organize / criteria the query (from within
Access) to return one specific record (or just one users record, or one media's
record, etc.) if the query is structured properly. Can you elaborate more on this?

I think you'll probably need to do some pure ASP work on this one. I would suggest (if
you haven't already done this) to build an ADO connection and use that for your
updates. Then you'd be able to iterate thru your tables for updating. It could look
something like this: 

DIM MyDSN, conntemp 
 myDSN="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("/fpdb/db1.mdb") 
 set conntemp=server.createobject("adodb.connection") 
 conntemp.open myDSN


Then, you would be able to iterate thru your SQL statements for each table: 

conntemp.execute(SQLStatement1) 
 conntemp.execute(SQLStatement2) 
 ....


Then close your connection: 

conntemp.close 
 set conntemp=nothing


Of course, this is just an example and just how I would do it. However, I have not
really found any satisfactory way of doing this (updating more than 1 table) with the DRW.

Hope it helps...

Edited by - rdouglass on 06/19/2002 15:40:40



JDSpilio -> RE: Writing to two tables (6/19/2002 17:26:58)

Hi RDouglass,

Your help was invaluable! My project is on track again and is going perfectly.

I don't know if I'll ever thank you enough!

Thanks RD,

Jonathan

**********************************************************************

For anybody who was following this thread, my solution was as follows.


I created my request page which passed a the mediaID value from searchResults.asp
to mediaRequest.asp using a typical Request.Form.

Then on mediaRequest.asp, I created another form which contained the mediaID and
now added an associateID. I passed these values to an intermediary page (which
the user does not see) called server.asp. It's here that the tables are written
to and the Session variable is created. (see code below).

Server.asp redirects to the last page, user-info.asp which called the session
variable flawlessly. All my options work!

One snag that I ran into that I had to seek out the Knowedge Base for help on,
was the fact that I was getting an HTTP Header Error when I tried to redirect
from server.asp. The solution was to reset the page buffering by placing the
following code before any ASP scripts: 

Response.Buffer = True


I then placed the response.redirect("user-info.asp") after all the scripting
was completed.

**********************************************************************

The Code that wrote to two tables, created a Session variable, and redirected
to another page is enclosed here.(it is the server.asp page discussed above) 

 
  
 	% Response.Buffer = True % 
  
        % 
        	dim myDSN, conntemp 
 			myDSN="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("fpdb/HRLibrary-NBackEnd.mdb") 
 			set conntemp=Server.CreateObject("ADODB.Connection") 
 			conntemp.open myDSN  
  
  
 		'Create Session Variable and set to AssocID 
 			Session("UserID") = Request.Form("AssocID")  
  
  
 		'declare my variables 
 			dim sSql1	'SQL Statement to tblRequest 
 			dim sSql2	'SQL Statement to tblMedia 
 			dim uID		'Associate ID Number 
 			dim rStatus	'Media Status 
 			dim mID		'Media ID Number 
  
  
 		'set my variables 
 			mID = Request.Form("mediaID") 
 			uID = Request.Form("AssocID") 
 			rStatus = "Pending" 
  
  
 		'Build the SQL Statements 
 			sSql1 = "INSERT INTO tblRequest (RequestMediaID,RequestUserID,RequestStatus) VALUES ("&mID&",'"&uID&"','"&rStatus&"');" 
 			sSql2 = "UPDATE tblMedia SET mediaSearchable='No' WHERE mediaID="&mID&";" 
  
  
 		'Debugging Routine to see the SQL Statements 
 			'response.write(sSql1) 
 			'response.write("<BR><BR>") 
 			'response.write(sSql2) 
  
  
 		'Execute the SQL Statements	 
 			conntemp.execute(sSql1) 
 			conntemp.execute(sSQL2) 
  
  
 		'Close the connection 
 			conntemp.close 
 			set conntemp=nothing 
  
  
        % 
 	% Response.Redirect("user-info.asp") %







[edit: clarity]

Edited by - jdspilio on 06/19/2002 17:28:07



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125