Multiple Table Access Problems

Results 1 to 2 of 2

Thread: Multiple Table Access Problems

  1. #1
    ASP Newcomer Guest

    Default Multiple Table Access Problems

    I am an ASP newcomer and I am trying to retrieve information from three linked Access database tables. There are a total of four tables in my database, a Lecturer table which is linked with a one-to-many relationship to a Booking table, which in turn is linked to a Booking Item table with a one-to-many relationship. Finally an AV Item table is linked to the Booking Item table with another one-to-many relationship. The query I am using makes use of a session object, storing the Lecturer&#039;s ID which is a foriegn key in the Booking table, therefore the Lecturer table is not included in the query. Also two other session objects are used to store the date of the booking and also the start time of the booking. The idea is this known information will point to one record, however there may be special circumstances when more than one record is retrieved. Once a booking record is identified the Booking Item table will be accessed where the Booking ID is a foriegn key, another foriegn key is used in this table the AV Item ID, also a Quantity field is located here. Using the compound key a specific piece of equipment from the AV Item table can be associated with the booking. The name of this piece of equipment is also supposed to be retrieved by the query. Because of the nature of the system it is likely that a booking will have more than one piece of equipment associated with it. This means for one booking retrieved the booking item and av item tables will have to be accessed more than once for each record stored in the recordset. How do I differentiate between these repeating groups when retreiving them from the recordset and displaying the results in text boxes?<BR><BR>To the point now, I am confused as how to best implement a cursor to allow for users to view information. I have my page designed to display the code in a form with two buttons allowing for the viewing of the next and previous records. <BR><BR>I am also having a problem with the following code which does not return errors, however it fails to return any records. If you have any suggestions or can point me to any relevant information I will be truly greatful.<BR><BR>Set objConn = Server.CreateObject ("ADODB.Connection") <BR>objConn.ConnectionString = "DSN=AVBookingSystem.dsn" <BR>objConn.Open <BR><BR>&#039;The SQL statement which uses an INNER JOIN to select the required information from the database.<BR>strSQL = "SELECT DISTINCTROW Lecturer.[Lecturer ID], Booking.[Booking Number], Booking.[Lecturer ID], Booking.Room, Booking.Date, Booking.Day, Booking.[Start Time], Booking.[Finish Time], Booking.Frequency, Booking.Notes, Booking.State, Booking.[Made On Date], [Booking Item].[AV Item ID], [Booking Item].[Booking Number], [AV Item].Name, [Booking Item].Quantity " & _<BR>" FROM Lecturer INNER JOIN (Booking INNER JOIN ([AV Item] INNER JOIN [Booking Item] ON [AV Item].[AV Item ID] = [Booking Item].[AV Item ID]) ON Booking.[Booking Number] = [Booking Item].[Booking Number]) ON Lecturer.[Lecturer ID] = Booking.[Lecturer ID]" & _<BR>" WHERE Booking.[Lecturer ID] = " & Session("LecturerID") & _ <BR>" AND Booking.Date = #" & Session("Date") & "#" & _ <BR>" AND Booking.[Start Time] = #" & Session("StartTime") & "#" & _ <BR>" AND [Booking Item].[Booking Number] = Booking.[Booking Number] " & _ <BR>" AND [AV Item].[AV Item ID] = [Booking Item].[AV Item ID] " & _ <BR>" ORDER BY Booking.[Booking Number], [Booking Item].[AV Item ID], [AV Item].Name;"<BR><BR>&#039;Creating the Recordset object, declaring it&#039;s locktype and cursorlocation, and then using the execute method to invoke the SQL statement and fill the recordset with data.<BR>SET objRS = Server.CreateObject("ADODB.Recordset")<BR>objRS.Lo ckType = adOpenStatic<BR>objRS.CursorLocation = adUseClient<BR>SET objRS = objConn.execute(strSQL)<BR><BR>PS. The reason I have created the RS object and then used the execute method is because when I tried to explicitly open the object I was recieving errors with the FROM statement. If this code is improper please let me know.<BR>

  2. #2
    Join Date
    Dec 1969

    Default Nice homework project...

    You are simply asking too much of a human being.<BR><BR>I can&#039;t read that SQL and tell you if there is a mistake in it!<BR><BR>You need to simplify it, down to maybe just one or two fields per table.<BR><BR>AT A MINIMUM, you must learn to DEBUG, DEBUG, DEBUG.<BR><BR>For starters, Response.Write the SQL statement, to be sure it is getting the values you *think* it is getting.<BR><BR>Make queries on one DB at a time, to be sure they contain records that will fit in this Join.<BR><BR>******************<BR><BR>On another topic:<BR><BR>The first three lines of this code are an ABSOLUTE WASTE OF TIME AND ENERGY:<BR><BR>SET objRS = Server.CreateObject("ADODB.Recordset")<BR>objRS.Lo ckType = adOpenStatic<BR>objRS.CursorLocation = adUseClient<BR>SET objRS = objConn.execute(strSQL)<BR><BR>Reason: The last line there asks objConn to create a *NEW* recordset object and assign "objRS" to refer to that NEW object.<BR><BR>In the process, objRS will *COMPLETELY FORGET* about the object you created separately. That object will be reclaimed as garbage by the garbage collection process.<BR><BR>This looks suspiciously like the same thing Lynn is working on. Hmmmm??? Same college?<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts