120 Database Connections

Results 1 to 3 of 3

Thread: 120 Database Connections

  1. #1
    Eric Lebetsamer Guest

    Default 120 Database Connections

    I am Building a Conference Room Scheduler for my intranet. I have it built and working but the problem is that I am making 120 connections to the database at once. As you can imagine this gets very slow. This is only temporary but I had to make it work for now. The reason I make 120 connections is because I am using 120 select statements to pull the data for each room and date and time slot. I have time slots in half n hour intervals. Starting at 7:00 am to 6:30 pm. And I have 4 rooms. That is what gives me the 120 connections. I know that there must be a way to only pull one recordset then do something like:<BR>room1a=recordset("room")=Room1<BR><BR>but I have not found a way yet. Please if anyone can help, let me know. Thanks.

  2. #2
    Mark B Guest

    Default RE: 120 Database Connections

    Hi Eric<BR><BR>I&#039ve just completed a very similar project, I&#039m not sure how you&#039ve designed your database but for myself I did the following. I created a table to store information on the rooms(4 records in your case), another to store representations of the half hour time slots (30 records in your case), I then set another table to associate those ID&#039s from the time slots and the rooms when a booking is made. Okay thats the background, how it works is relatively simple. In the ASP I create a connection to return all the rooms using a stored procedure, as the ASP loops through the records it runs a second stored procedure which opens another connection and selects the 30 time slots then closes.<BR>e.g.<BR> call rooms<BR> While Not rs.EOF<BR>&#037;&#062;<BR> room<BR> &#060;%<BR> call time slots<BR> While Not rs2.EOF<BR> &#037;&#062;<BR> time slots<BR> &#060;%<BR> rs2.MoveNext<BR> Wend<BR><BR> rs2.close<BR> set rs2 = nothing<BR> ConObj2.close<BR> set ConObj2 = nothing<BR><BR>rs.MoveNext<BR>Wend<BR><BR>rs.close <BR>set rs = nothing<BR>ConObj.close<BR>set ConObj = nothing<BR> <BR>What this means is that you have a total of 5 connections (in your case) with only 2 being open at any one time. I don&#039t know if this is of much help to you (not sure I&#039ve explained myself very well either) or not but I hope so.<BR><BR>Mark

  3. #3
    Eric Lebetsamer Guest

    Default RE: 120 Database Connections

    I am fairly new to ASP and even more so to SQL. I grasp the basic concept of what you said but I would appreciate more depth if possible. From what I understand, you have a total of three tables. One that contains only one field, the room name. Another that contains one field, the half hour time slots. And the other that relates the two. What is the layout of that table. And how do you insert a new record into it. And let me give you a bit more depth into what I am trying to accomplish. I have a search page that allows people to select the date they want to veiw. Then when they submit the form, it displays a table. It there is an event schedules for lets just say, May 6 at 730 am then the eventname for that day,time,room is assigned to a variable then the table displays the variable that is assigned to that slot in the table. If it is not found, it displays "Available" which when clicked goes to a new form to input a new event for that day and time and room. I have the eventname clickable so you can go to a screen that lets you veiw detailed info about the event. I perform the link via the eventID that I pull with the record. If you would like, I can provide the source code for you, so you can see what I am dealing with. Thanks for your help. O yeah, and if possible could you give me an example of how to write and use a stored procedure. Thank you very much.

Posting Permissions

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