SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Do you need IIS restart if a new field is added to a table?

    First off, I'm not a developer who understands ASP, but I am quite familiar with Access.

    I look after a website which has a tiny one-table Access DB behind it to store contact details of music schools manipulated by what looks like ASP generated by Front Page.

    I didn't do the database design (if I had it would be different!) and the limitations are starting to show. The website owner now wants to add a 'top of the page' position listing for schools who have paid a fee to be No.1 on the page.

    In conventional desktop Access I would achieve this by writing a query. However, I'm out of my depth when it comes to calling queries written in Access with SQL, so I decided to add an extra field to the table named 'Position' so I could add numbers to it and then use the (resource hogging) ORDER BY statement to sort them in an ascending or descending numerical order. (Sorting by the autonumbers in the primary key field wasn't an option by the way.)

    Anyway, for a test I edited the existing SQL statement that displayed music school details for a geographical area and added ORDER BY Surname DESC to the end. (Surname being an existing field in the table.) The result was, as expected, all the details for music schools in one particular area were displayed by the school owners surname in descending order.

    My next test was to use ORDER BY in conjunction with the new 'Position' field and it's numerical data that I had added to the table. The full statement was as follows:

    SQL = "SELECT * FROM Table1 WHERE County = 'Merseyside' ORDER BY Position DESC"

    However, this resulted in an error message:

    error '80004005'
    Unspecified error
    /Merseyside.asp, line 171


    Looking at line 171 this corresponded to the database connection:

    171 objRS.Open SQL, Fconnection

    Now, the new 'Position' field I added worked fine in a conventional Access query, so theoretically, it had to be possible to manipulate it using SQL. With nothing appearing ilogical in the statement and the error message being a connection problem, is it a possibility that IIS has to be restarted to make a fresh conncetion to the database and pick up the new field on the table?

  2. #2
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    To your last question - no. At least, not unless you are creating and maintaining the DB connection (Fconnection) in a Session variable (BAD), or (God forbid) in an Application variable (BAD BAD BAD)!

    The proper structure for connecting via ASP to a database is ...

    Create connection
    Open connection
    Do stuff
    Close connection
    Kill connection

    ... i.e. Open late, Close Early. And do this in each page that requires to interface with the database. (You can find more info about ASP and databases [specifically Access] in my tutorial).

    The only other outside possibility is that Position is a reserved word. If so then you can work around this like so ...

    SQL = "SELECT * FROM Table1 WHERE County = 'Merseyside' ORDER BY [Position] DESC"

    HTH.
    Ian Anderson
    www.siteguru.co.uk

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2006
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unbelievable!

    Your suggestion to wrap Position between brackets worked!

    Thank you so much for that


Bookmarks

Posting Permissions

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