Results 1 to 6 of 6

Thread: ASP Count Query

  1. #1

    Thread Starter
    Member
    Join Date
    May 2022
    Posts
    34

    ASP Count Query

    Not sure if this is a place where one can ask a basic SQL question but figured I’d give it a shot. Working with a legacy Intranet system running classic ASP on IIS 5.1 and Microsoft Access, it’s old and refuses to die.

    The Intranet serves up pages for tracking, managing & shipping packages to existing customers along with adding new customers. I’d like to update the ASP code to only populate the Country & State dropdown lists so it only show locations of existing customers while still having the ability of showing a complete list when adding new customers.

    Not a database or SQL guy by any stretch of the imagination and not sure the best way of doing this without going through several steps and multiple queries.

    Added an ‘In-Use’ field to both the Country & State database tables so the ASP code can serve up records for either existing or new customers. The problem I’m having is maintaining the ‘In-Use’ field especially when deleting customers.

    When removing a customer say from Hawaii, I want to clear the ‘In-Use’ field from the State table if they are the only customer from that State. I tired a ‘count’ query in the ASP code to return the numbers of customers from Hawaii but couldn’t get it to work. So then setup a query requesting all customers from Hawaii and ran through all records keeping a running tally. If the count comes up zero, meaning there are no other customers from Hawaii, I then run an update query to clear the ‘In-Use’ field from the State table. And this is just for the State table, will need to duplicate all this for Country table as well.

    Was wondering if there is a better way of accomplishing this as my solution does not seem very clean or efficient.

    Thanks and sorry for the long post.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,296

    Re: ASP Count Query

    For future reference, if you post in the wrong forum, use the Report Post icon under your post to send a message to the mods to ask them to move it for you.

  3. #3
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: ASP Count Query

    Quote Originally Posted by Dev101 View Post
    Was wondering if there is a better way of accomplishing this as my solution does not seem very clean or efficient.
    You can update "In-Use" flag for all the rows in your States table w/ a single query -- that's the whole set-oriented reason for existence (raison d'être) of SQL as a language.

    You have to come up with an UPDATE query based on your table schema and yes, you can use COUNT here too, something as simple as this probably:

    Code:
    UDPATE State SET [In-Use] = (SELECT COUNT(*) FROM Customers WHERE Customers.StateID = State.ID)
    You'll have to hook up the above UPDATE query to be run exactly once after a Customer is added, modified or deleted in your system.

    cheers,
    </wqw>

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,414

    Re: ASP Count Query

    And if it were any other DBMS than Access you could put wqweto's UPDATE-Statement in a Trigger, and let the Database itself manage it.

    Upside to wqweto's approach: "In-Use" would NOT be a boolean, but an (unsigned) Integer, so you can use that Integer as a Boolean, a value of 0 (Zero) being False (Not In-Use), everything else being True (Yes, it is In-Use)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5

    Thread Starter
    Member
    Join Date
    May 2022
    Posts
    34

    Re: ASP Count Query

    Sorry took so long to respond, been sidetracked putting out other fires.

    jmcilhinney – Appreciate the heads up when miss-posting things, shouldn’t happen again but now know how to handle it.

    wqweto – Thanks for the SQL statement, way better solution then what I was doing, come Monday I’ll give it a shot and see how it goes.

    Zvoni – Setting up a trigger would be a great way to get this done, plan is to eventually upgrade everything but for now the system seems to be doing it’s job pretty well.

    The only problem I’ve run into was when trying to add code to validate addresses through an http request to an outside source. Not sure what’s going on, guessing has something to do with IIS 5.1 being so old. Keep getting an error message that the Access-Control-Allow-Origin header is missing. Added the header using both a specific URL and an asterisk (*), neither has worked.
    Attached Images Attached Images  

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,414

    Re: ASP Count Query

    Are you "internally" looking at "http://localhost/blablabla"?
    try FQDN instead of "localhost"

    https://stackoverflow.com/questions/...ut-not-working
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

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



Click Here to Expand Forum to Full Width